Grails 2 – using multiple DataSources

Occasionally you may want to access more than one database, for example your lookup tables may be centralised away from your application datasource or you might want to write an admin application that can update tables in different environments.

1. In DataSource.groovy

Put additional datasources inside the environment, eg to add 2 datasources to the local environment:

local {
dataSource {
driverClassName =”com.mysql.jdbc.Driver”
url = “jdbc:mysql://localhost:3306/databaseSchema
username = “database_user
password = “database_password

dataSource_test  {
driverClassName = “com.mysql.jdbc.Driver”
username= “database_user
password = “database_password
url = “jdbc:mysql://databaseURLandport/databaseSchema

NB: the default database needs to be called ‘dataSource’ and subsequent databases need to be prepended with ‘dataSource_’. The data source is then accessed via the suffix eg ‘test’ in this case, or ‘DEFAULT’ in the case of the first.

2. Make the dataSources available to the Domain object, eg:

static mapping = {
datasource ‘ALL’

3. Select the dataSource to use for a specific action:

def list(Integer max) {
params.max = Math.min(max ?: 10, 100)
[contributorInstanceList: Contributor.test.list(params), contributorInstanceTotal: Contributor.count()]

NB: The change is Contributor.test.list instead of Contributor.list


(Tested using Grails 2.1, Java 1.6 and MySQL 5.0)



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s