Using separate Postgres Schemas for the same database in a Grails App

Recently, I wanted to use the same Postgres Database but split my persistence layer into separate components which used separate schemas. The motivation was to promote modular design, separate concerns and stop developers tripping up over each other. Vertical domain models can be difficult to achieve but not impossible.

In my shopping application, I had a user component, a shopping component and a product component. Now this is pretty easy if you are using separate databases, but sometimes it's nice to just get the separation of concerns using separate schemas in the same database, since using the same database can make things like DR, log shipping, replication etc easier.

While I could find doc for separate databases, I found it difficult to find Grails doc to advice on my specific problem - how to use separate schemas when using the same database when using Postgres. So here is how I ended up doing it.

Here is my datasource.groovy.

String db_url = "jdbc:postgresql://localhost:5432/testdb"
String usernameVar = "db_user"
String passwordVar = "db_secret"
String dbCreateVar = "update"
String dialect = "net.kaleidos.hibernate.PostgresqlExtensionsDialect"

dataSource_user {
    pooled = true
    jmxExport = true
    dialect = dialect
    driverClassName = "org.postgresql.Driver"
    username = usernameVar
    password = passwordVar
    url = platform_url
    dbCreate= "validate"
}

hibernate_user {
    cache.use_second_level_cache = false
    cache.use_query_cache = false
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory' // Hibernate 3
    singleSession = true // configure OSIV singleSession mode
    default_schema = "user"
}

dataSource_shopping {
    pooled = true
    jmxExport = true
    dialect = dialect
    driverClassName = "org.postgresql.Driver"
    username = usernameVar
    password = passwordVar
    url = platform_url
    dbCreate = "validate"
}

hibernate_shopping {
    cache.use_second_level_cache = false
    cache.use_query_cache = false
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory' // Hibernate 3
    singleSession = true // configure OSIV singleSession mode
    default_schema = "shopping"
}

dataSource_product {
    pooled = true
    jmxExport = true
    dialect = dialect
    driverClassName = "org.postgresql.Driver"
    username = usernameVar
    password = passwordVar
    url = platform_url
    dbCreate= "validate"
}

hibernate_product {
    cache.use_second_level_cache = false
    cache.use_query_cache = false
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory' // Hibernate 3
    singleSession = true // configure OSIV singleSession mode
    default_schema = "product"
}

Note: there are some obvious optimisations in config above, but the above just makes explaining simple.

I then mapped each GORM object to the appropriate schema.

class Cart {
    // ...
    // ...
    static mapping = {
        datasource 'shopping'
        // ... 
    }
}

class Address {
    // ...
    // ...

    static mapping = {
        datasource 'user'
    }
}

class Stock {
    // ...
    // ...

    static mapping = {
        datasource 'product'
    }
}

I then started my app and said "yippe, this works" had a little tea break and moved onto the next problem. As can be seen the trick is to use a separate hibernate closure, specify the schema in there and name the closure using the same naming format for separate database, but make the database closures point to the same database.