Using sqlite with Grails

http://flnkr.com/2011/06/using-sqlite-with-grails/

I’m working on a relatively small application for a some folks without an IT staff. In an effort to simplify the deployment, I’m planning on using H2 as the production database. It will work just fine for five users, and will prevent them from having to install and maintain MySQL or something else.

H2 is the default database in Grails, but you’ll need to make some changes to the production configuration for using a file-based H2 database with Tomcat. Here is the production section of my DataSource.groovy file:

production {
    dataSource {
        dbdir ="${System.properties['catalina.base']}/db/sample"
 
        dbCreate ="update"
        url ="jdbc:h2:file:${dbdir};MVCC=TRUE;LOCK_TIMEOUT=10000"
        pooled =true
        username ="sa"
        password ="db!admin"
 
        properties {
            maxActive =-1
            minEvictableIdleTimeMillis=1800000
            timeBetweenEvictionRunsMillis=1800000
            numTestsPerEvictionRun=3
            testOnBorrow=true
            testWhileIdle=true
            testOnReturn=true
            validationQuery="SELECT 1"}}}

Line 3 sets the location and name of the database file. In this case, I’m reading the system property set by Tomcat to determine the location of running instance (catalina.base) and specifying the db will be in a directory called sample in the db directory. You want to create the db directory first. For example, here’s what my Tomcat 7 directory looks like:

On line 5, you’ll want to use update or validate as the dbCreate option so as not to blow away the database each time when you start the application. I prefer update so that Grails can keep simple fast-forward changes in sync.

Finally, on line 6, we build the full datasource URL, substituting in the file location we built above on line 3. I’m setting a password for the database on line 9, but that is optional and really depends on how secure you want the database to be.

Now, when you deploy the WAR file of the Grails application to Tomcat, it will use the embedded H2 database for production, saving the file under the Tomcat location. You can also put the DB file anywhere you want, but my goal was to keep the whole application (Tomcat + Grails) together so I could provide it as a preconfigured Zip file.