3.3 The DataSource - Reference Documentation
Authors: Graeme Rocher, Peter Ledbrook, Marc Palmer, Jeff Brown, Luke Daley, Burt Beckwith
Version: null
Table of Contents
3.3 The DataSource
Since Grails is built on Java technology setting up a data source requires some knowledge of JDBC (the technology that doesn't stand for Java Database Connectivity).If you use a database other than H2 you need a JDBC driver. For example for MySQL you would need Connector/JDrivers typically come in the form of a JAR archive. It's best to use Ivy to resolve the jar if it's available in a Maven repository, for example you could add a dependency for the MySQL driver like this:grails.project.dependency.resolution = {
inherits("global")
log "warn"
repositories {
grailsPlugins()
grailsHome()
grailsCentral()
mavenCentral()
}
dependencies {
runtime 'mysql:mysql-connector-java:5.1.16'
}
}mavenCentral() repository is included here since that's a reliable location for this library.If you can't use Ivy then just put the JAR in your project's lib directory.Once you have the JAR resolved you need to get familiar Grails' DataSource descriptor file located at grails-app/conf/DataSource.groovy. This file contains the dataSource definition which includes the following settings:
driverClassName- The class name of the JDBC driverusername- The username used to establish a JDBC connectionpassword- The password used to establish a JDBC connectionurl- The JDBC URL of the databasedbCreate- Whether to auto-generate the database from the domain model - one of 'create-drop', 'create', 'update' or 'validate'pooled- Whether to use a pool of connections (defaults to true)logSql- Enable SQL logging to stdoutformatSql- Format logged SQLdialect- A String or Class that represents the Hibernate dialect used to communicate with the database. See the org.hibernate.dialect package for available dialects.readOnly- Iftruemakes the DataSource read-only, which results in the connection pool callingsetReadOnly(true)on eachConnectionproperties- Extra properties to set on the DataSource bean. See the Commons DBCP BasicDataSource documentation.
dataSource {
pooled = true
dbCreate = "update"
url = "jdbc:mysql://localhost/yourDB"
driverClassName = "com.mysql.jdbc.Driver"
dialect = org.hibernate.dialect.MySQL5InnoDBDialect
username = "yourUser"
password = "yourPassword"
}When configuring the DataSource do not include the type or the def keyword before any of the configuration settings as Groovy will treat these as local variable definitions and they will not be processed. For example the following is invalid:
dataSource {
boolean pooled = true // type declaration results in ignored local variable
…
}dataSource {
pooled = true
dbCreate = "update"
url = "jdbc:mysql://localhost/yourDB"
driverClassName = "com.mysql.jdbc.Driver"
dialect = org.hibernate.dialect.MySQL5InnoDBDialect
username = "yourUser"
password = "yourPassword"
properties {
maxActive = 50
maxIdle = 25
minIdle = 5
initialSize = 5
minEvictableIdleTimeMillis = 60000
timeBetweenEvictionRunsMillis = 60000
maxWait = 10000
validationQuery = "/* ping */"
}
}More on dbCreate
Hibernate can automatically create the database tables required for your domain model. You have some control over when and how it does this through thedbCreate property, which can take these values:
- create - Drops the existing schemaCreates the schema on startup, dropping existing tables, indexes, etc. first.
- create-drop - Same as create, but also drops the tables when the application shuts down cleanly.
- update - Creates missing tables and indexes, and updates the current schema without dropping any tables or data. Note that this can't properly handle many schema changes like column renames (you're left with the old column containing the existing data).
- validate - Makes no changes to your database. Compares the configuration with the existing database schema and reports warnings.
- any other value - does nothing
dbCreate setting completely, which is recommended once your schema is relatively stable and definitely when your application and database are deployed in production. Database changes are then managed through proper migrations, either with SQL scripts or a migration tool like Liquibase (the Database Migration plugin uses Liquibase and is tightly integrated with Grails and GORM).
3.3.1 DataSources and Environments
The previous example configuration assumes you want the same config for all environments: production, test, development etc.Grails' DataSource definition is "environment aware", however, so you can do:dataSource {
pooled = true
driverClassName = "com.mysql.jdbc.Driver"
dialect = org.hibernate.dialect.MySQL5InnoDBDialect
// other common settings here
}environments {
production {
dataSource {
url = "jdbc:mysql://liveip.com/liveDb"
// other environment-specific settings here
}
}
}3.3.2 JNDI DataSources
Referring to a JNDI DataSource
Most Java EE containers supplyDataSource instances via Java Naming and Directory Interface (JNDI). Grails supports the definition of JNDI data sources as follows:dataSource {
jndiName = "java:comp/env/myDataSource"
}DataSource in Grails remains the same.Configuring a Development time JNDI resource
The way in which you configure JNDI data sources at development time is plugin dependent. Using the Tomcat plugin you can define JNDI resources using thegrails.naming.entries setting in grails-app/conf/Config.groovy:grails.naming.entries = [
"bean/MyBeanFactory": [
auth: "Container",
type: "com.mycompany.MyBean",
factory: "org.apache.naming.factory.BeanFactory",
bar: "23"
],
"jdbc/EmployeeDB": [
type: "javax.sql.DataSource", //required
auth: "Container", // optional
description: "Data source for Foo", //optional
driverClassName: "org.h2.Driver",
url: "jdbc:h2:mem:database",
username: "dbusername",
password: "dbpassword",
maxActive: "8",
maxIdle: "4"
],
"mail/session": [
type: "javax.mail.Session,
auth: "Container",
"mail.smtp.host": "localhost"
]
]3.3.3 Automatic Database Migration
ThedbCreate property of the DataSource definition is important as it dictates what Grails should do at runtime with regards to automatically generating the database tables from GORM classes. The options are described in the DataSource section:
createcreate-dropupdatevalidate- no value
dbCreate is by default set to "create-drop", but at some point in development (and certainly once you go to production) you'll need to stop dropping and re-creating the database every time you start up your server.It's tempting to switch to update so you retain existing data and only update the schema when your code changes, but Hibernate's update support is very conservative. It won't make any changes that could result in data loss, and doesn't detect renamed columns or tables, so you'll be left with the old one and will also have the new one.Grails supports Rails-style migrations via the Database Migration plugin which can be installed by running
grails install-plugin database-migrationThe plugin uses Liquibase and and provides access to all of its functionality, and also has support for GORM (for example generating a change set by comparing your domain classes to a database).
3.3.4 Transaction-aware DataSource Proxy
The actualdataSource bean is wrapped in a transaction-aware proxy so you will be given the connection that's being used by the current transaction or Hibernate Session if one is active.If this were not the case, then retrieving a connection from the dataSource would be a new connection, and you wouldn't be able to see changes that haven't been committed yet (assuming you have a sensible transaction isolation setting, e.g. READ_COMMITTED or better).The "real" unproxied dataSource is still available to you if you need access to it; its bean name is dataSourceUnproxied.You can access this bean like any other Spring bean, i.e. using dependency injection:class MyService { def dataSourceUnproxied
…
}ApplicationContext:def dataSourceUnproxied = ctx.dataSourceUnproxied
3.3.5 Database Console
The H2 database console is a convenient feature of H2 that provides a web-based interface to any database that you have a JDBC driver for, and it's very useful to view the database you're developing against. It's especially useful when running against an in-memory database.You can access the console by navigating to http://localhost:8080/appname/dbconsole in a browser. The URI can be configured using thegrails.dbconsole.urlRoot attribute in Config.groovy and defaults to '/dbconsole'.The console is enabled by default in development mode and can be disabled or enabled in other environments by using the grails.dbconsole.enabled attribute in Config.groovy. For example you could enable the console in production usingenvironments {
production {
grails.serverURL = "http://www.changeme.com"
grails.dbconsole.enabled = true
grails.dbconsole.urlRoot = '/admin/dbconsole'
}
development {
grails.serverURL = "http://localhost:8080/${appName}"
}
test {
grails.serverURL = "http://localhost:8080/${appName}"
}
}If you enable the console in production be sure to guard access to it using a trusted security framework.
Configuration
By default the console is configured for an H2 database which will work with the default settings if you haven't configured an external database - you just need to change the JDBC URL tojdbc:h2:mem:devDB. If you've configured an external database (e.g. MySQL, Oracle, etc.) then you can use the Saved Settings dropdown to choose a settings template and fill in the url and username/password information from your DataSource.groovy.
3.3.6 Multiple Datasources
By default all domain classes share a singleDataSource and a single database, but you have the option to partition your domain classes into two or more DataSources.Configuring Additional DataSources
The defaultDataSource configuration in grails-app/conf/DataSource.groovy looks something like this:dataSource {
pooled = true
driverClassName = "org.h2.Driver"
username = "sa"
password = ""
}
hibernate {
cache.use_second_level_cache = true
cache.use_query_cache = true
cache.provider_class = 'net.sf.ehcache.hibernate.EhCacheProvider'
}environments {
development {
dataSource {
dbCreate = "create-drop"
url = "jdbc:h2:mem:devDb"
}
}
test {
dataSource {
dbCreate = "update"
url = "jdbc:h2:mem:testDb"
}
}
production {
dataSource {
dbCreate = "update"
url = "jdbc:h2:prodDb"
}
}
}DataSource with the Spring bean named dataSource. To configure extra DataSources, add another dataSource block (at the top level, in an environment block, or both, just like the standard DataSource definition) with a custom name, separated by an underscore. For example, this configuration adds a second DataSource, using MySQL in the development environment and Oracle in production:environments {
development {
dataSource {
dbCreate = "create-drop"
url = "jdbc:h2:mem:devDb"
}
dataSource_lookup {
dialect = org.hibernate.dialect.MySQLInnoDBDialect
driverClassName = 'com.mysql.jdbc.Driver'
username = 'lookup'
password = 'secret'
url = 'jdbc:mysql://localhost/lookup'
dbCreate = 'update'
}
}
test {
dataSource {
dbCreate = "update"
url = "jdbc:h2:mem:testDb"
}
}
production {
dataSource {
dbCreate = "update"
url = "jdbc:h2:prodDb"
}
dataSource_lookup {
dialect = org.hibernate.dialect.Oracle10gDialect
driverClassName = 'oracle.jdbc.driver.OracleDriver'
username = 'lookup'
password = 'secret'
url = 'jdbc:oracle:thin:@localhost:1521:lookup'
dbCreate = 'update'
}
}
}Configuring Domain Classes
If a domain class has noDataSource configuration, it defaults to the standard 'dataSource'. Set the datasource property in the mapping block to configure a non-default DataSource. For example, if you want to use the ZipCode domain to use the 'lookup' DataSource, configure it like this;class ZipCode { String code static mapping = {
datasource 'lookup'
}
}DataSources. Use the datasources property with a list of names to configure more than one, for example:class ZipCode { String code static mapping = {
datasources(['lookup', 'auditing'])
}
}DataSource and one or more others, use the special name 'DEFAULT' to indicate the default DataSource:class ZipCode { String code static mapping = {
datasources(['lookup', 'DEFAULT'])
}
}DataSources use the special value 'ALL':class ZipCode { String code static mapping = {
datasource 'ALL'
}
}Namespaces and GORM Methods
If a domain class uses more than oneDataSource then you can use the namespace implied by each DataSource name to make GORM calls for a particular DataSource. For example, consider this class which uses two DataSources:class ZipCode { String code static mapping = {
datasources(['lookup', 'auditing'])
}
}DataSource specified is the default when not using an explicit namespace, so in this case we default to 'lookup'. But you can call GORM methods on the 'auditing' DataSource with the DataSource name, for example:def zipCode = ZipCode.auditing.get(42) … zipCode.auditing.save()
DataSource to the method call in both the static case and the instance case.Services
Like Domain classes, by default Services use the defaultDataSource and PlatformTransactionManager. To configure a Service to use a different DataSource, use the static datasource property, for example:class DataService { static datasource = 'lookup' void someMethod(...) {
…
}
}DataSource, so be sure to only make changes for domain classes whose DataSource is the same as the Service.Note that the datasource specified in a service has no bearing on which datasources are used for domain classes; that's determined by their declared datasources in the domain classes themselves. It's used to declare which transaction manager to use.What you'll see is that if you have a Foo domain class in dataSource1 and a Bar domain class in dataSource2, and WahooService uses dataSource1, a service method that saves a new Foo and a new Bar will only be transactional for Foo since they share the datasource. The transaction won't affect the Bar instance. If you want both to be transactional you'd need to use two services and XA datasources for two-phase commit, e.g. with the Atomikos plugin.XA and Two-phase Commit
Grails has no native support for XADataSources or two-phase commit, but the Atomikos plugin makes it easy. See the plugin documentation for the simple changes needed in your DataSource definitions to reconfigure them as XA DataSources.

