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).
Essentially, if you are using another database other than HSQLDB you need to have a JDBC driver. For example for MySQL you would need
Connector/JDrivers typically come in the form of a JAR archive. Drop the JAR into your project's
lib
directory.
Once you have the JAR in place 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 driver
username
- The username used to establish a JDBC connection
password
- The password used to establish a JDBC connection
url
- The JDBC URL of the database
dbCreate
- Whether to auto-generate the database from the domain model or not - one of 'create-drop', 'create', 'update' or 'validate'
pooled
- Whether to use a pool of connections (defaults to true)
logSql
- Enable SQL logging to stdout
dialect
- A String or Class that represents the Hibernate dialect used to communicate with the database. See the org.hibernate.dialect package for available dialects.
properties
- Extra properties to set on the DataSource bean. See the Commons DBCP BasicDataSource documentation.
A typical configuration for MySQL may be something like:
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 local variable
…
}
Example of advanced configuration using extra properties:
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 the
dbCreate
property, which can take these values:
- create - Creates the schema on startup, clearing any existing tables and data first.
- create-drop - Same as create, but also drops the tables when the application shuts down.
- update - Updates the current schema without dropping any tables or data. Note that this can't properly handle certain schema changes like column renames (you're left with the old column containing the existing data).
- validate - Checks that the current schema matches the domain model, but doesn't modify the database in any way.
You can also remove the
dbCreate
setting completely, which is recommended once you have an application and database in production. Database changes then have to be managed through proper migrations, either via SQL scripts or a migration tool like
Liquibase (for which there is a plugin).