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.