Using sqlite with Grails

To use sqlite from Grails, first make sure you have sqlite installed. On Ubuntu, use the command:

$ sudo apt-get install sqlite

- From the hibernate-sqlite site, download the jar files. Extract the HibernateSQLite_with_jar.rar file in your Downloads directory or another working directory. Copy the file SQLiteDialect.java from the src/main/java/dialect directory to your applications hibernate directory.

$ cp src/main/java/dialect/SQLiteDialect.java 
                  <appname>/grails-app/conf/hibernate

- From the Xerial site, download the SQLite JDBC file sqlite-jdbc-(VERSION).jar. I choose the sqlite-jdbc-3.7.2.jar file. Place the file in your grails application <appname>/lib directory.
- If you need to create a new sqlite database, execute the command:

$ sqlite3 mydb.sqlite

- Edit your DataSource.groovy file with the following settings for your sqlite database:

dataSource {
   dbCreate=""
   url="jdbc:sqlite:/(path to file)/dev.sqlite3"
   logSql="true"
   dialect="dialect.SQLiteDialect"
   driverClassName="org.sqlite.JDBC"
   readOnly="true"
}

- To access the tables in an existing sqlite database, you will also need to map the grails domain name to the sqlite table name as described in the grails documentation.

class IpadScalars implements Serializable {
    String key
    Integer daysSince1970
    Integer value

    static mapping = {
       table "Scalars"
       id composite:['key','daysSince1970','value'],generator:"assigned"
       version false
    }
}

Updated: June 7, 2011

In the DataSource.groovy when using an existing sqlite database, I removed dbCreate specification and set readOnly to true.

Also, when using an existing sqlite database, I had to set the static mapping for the sqlite table names in the grails domain specification.

Optional Ivy Install

- Install the ivy dependency manager plug-in to help us integrate the jar files.

$ sudo mkdir /usr/share/grails/downloads
$ grails install-plugin ivy

In my environment, I received the following error:

Error opening connection java.io.FileNotFoundException: http://www.apache.org/dist/ant/ivy/2.0.0-rc1/apache-ivy-2.0.0-rc1-bin-with-deps.zip

Workaround

I worked around the above problem by downloading the file here. Once downloaded, execute the following commands:

$ cd ~/Downloads
$ grails install-plugin
$

- Edit the ivy.xml file and add dependency org="org.xerial" name="sqlite-jdbc" rev="3.6.16"
Thanks to cheolho for his blog entry on this.