Getting Started with Grails and MySQL

Out of the box, grails is set up with a HSQLDB database ready to go. While HSQLDB is great for early development, eventually most projects have to migrate to a real database system. If you’re using a hosting service for your app, such as CloudFoundry, it will probably end up being MySQL.

Here’s my checklist of things to do to convert a project from the default HSQLDB settings to MySQL:

  • Add the MySQL driver to your project. There are two ways to do this:
    • Download the jar file and put it in your ${project}/lib directory
    • Or, add a runtime dependency in grails-app/conf/BuildConfig.groovy. There will already be one commented out from the default BuildConfig, but you may want to bump the version number to the most recent. To resolve dependencies this way, you’ll have to make sure you’ve got a repository with the MySQL driver set up; I generally uncomment mavenCentral().
  • create a MySQL database and set the credentials (or get them from your service provider or DBA)
  • change the following in grails-app/conf/DataSource.groovy
    • The username and password of your MySQL database. I usually move these from the global dataSource section into the environment specific part since the prod password will probably be different than the dev and test.
    • The driverClassName. It should be com.mysql.jdbc.Driver.
    • The SQL dialect. MySQL will generally default to the MyISAM engine. InnoDB is a much better choice for the typical app for foreign key constraints and transactions, two features MyISAM lacks. Set it to org.hibernate.dialect.MySQL5InnoDBDialect.
    • Add show_sql=true to the global or development hibernate section if you want hibernate to log all the SQL statements it generates.
    • The JDBC URL. I recommend something like jdbc:mysql://HOSTNAME/DBNAME?useUnicode=yes&characterEncoding=UTF-8 because most MySQL installs will default to the latin-1 charset.

Your new DataSource.groovy should look something like this:

dataSource {
    pooled = true
    driverClassName = "com.mysql.jdbc.Driver"
    dialect = "org.hibernate.dialect.MySQL5InnoDBDialect" 
}
hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = true
    cache.provider_class = 'net.sf.ehcache.hibernate.EhCacheProvider'
}
// environment specific settings
environments {
    development {
        dataSource {
            dbCreate = "create" // one of 'create', 'create-drop','update'
            url = "jdbc:mysql://localhost/DEVDBNAME?useUnicode=yes&characterEncoding=UTF-8"
            username = "dev"
            password = "devpw"
        }
        hibernate {
            show_sql = true
        }
    }
    test {
        dataSource {
            dbCreate = "create-drop" // one of 'create', 'create-drop','update'
            url = "jdbc:mysql://localhost/TESTDBNAME?useUnicode=yes&characterEncoding=UTF-8"
            username = "test"
            password = "testpw"
        }
    }
    production {
        dataSource {
            dbCreate = "update"
            url = "jdbc:mysql://localhost/PRODDBNAME?useUnicode=yes&characterEncoding=UTF-8"
            username = "prod"
            password = "prodpw"
        }
    }
}

That’s it. Run your application in dev mode and check to ensure the tables were created in the database.

References: