Jul 16 2008

Groovy with SQLite

Published by Kefah Issa at 12:45 am under Grails, groovy

SQLite is a very interesting database engine. Its performance, simplicity and that fact it runs as a library (not a server) make it the perfect candidate for small project with few users.

Today I experimented with accessing SQLite database from within groovy.

I tried the SQLite JDBC libraries and all went fine. SQLite JDBC jar file, magically contains both Native and Pure java versions, and it automatically chooses the native one when running on Linux, Mac and Windows.

If you want to try the following snippets on groovy, make sure to copy sqlitejdbc-v052.jar to your ~/.groovy/lib.

Here is a one-liner on creating the GSQL object of a sqlite database. One nice thing to note is that you don’t have to have the test.db file created. if its not there it will be automatically created for you.

sql = groovy.sql.Sql.newInstance("jdbc:sqlite:/home/kefah/test.db","org.sqlite.JDBC")

Creating schema, insert and query

sql.execute("create table students(name, age)")
sql.execute("insert into students(name, age) values( 'Ali Baba', 20)")
sql.eachRow("select rowid,name,age from students;") {
     println "${it.rowid}: ${it.name} ${it.age}"}

… a more elaborated DDL

sql.execute('''
create table teachers( login primary key, firstname, lastname);
create unique index teachers_name on teachers(firstname, lastname);
''')

Datasets are another nifty way to access the data

students = sql.dataSet('students') 
students.add(name:"Abu Mohammed", age:35)
students.each {println " ${it.name}, ${it.age}"}

Note that sqlite datatypes are automatically induced from the data you insert into the table, or you can specify them at DDL creation time. aka Optional Typing, just like groovy ;)

Further more, you can create both inmemory and persistent (on harddisk) sqlite databases.

sql = groovy.sql.Sql.newInstance("jdbc:sqlite::memory:", "org.sqlite.JDBC")

so all in all its a perfect fit for groovy and (once working for grails too).

[Update] A reader mentions below that he could successfully get sqlite running on grails too. Which is great news.

6 responses so far

6 Responses to “Groovy with SQLite”

  1. # Tom Gleesonon 21 Jul 2008 at 6:25 pm

    The latest Zentus V052 jar is also a universal jar, contains native binaries for Win, Mac and Linux.

    Tom

  2. # Kefah Issaon 21 Jul 2008 at 10:10 pm

    I didn’t know that.

    Thanks for the tip Tom.

    So, does that obsolete xerial library and how can we choose between native and pure java?

    - Kefah.

  3. # Tom Gleesonon 29 Jul 2008 at 1:54 pm

    Kejah,

    The universal library will by default use a native library, but revert back to the pure Java version if a suitable lib cannot be found, conn.getDriverVersion() will return “native” if a native lib is being used.

    The old format of separate jars is still provided, so to chose one or the other use the appropriate jar.

    Tom

  4. # Kefah Issaon 29 Jul 2008 at 2:52 pm

    Sweet.

    Thanks for the tip.

    I’ve done some research and found that SQLite is not Hibernate friendly (at least not yet). Which is really sad for such a great engine.

    I wonder if there is any initiative on this matter, as this would be a great boon for grails.
    - Kefah.

  5. # Jefon 30 Nov 2008 at 11:35 am

    I did run Grails with SQLite.

    - Drop the JDBC Driver in your libs/ directoy
    - Write a Hibernate SQLite Dialect (I found on on the web: http://elbart0.free.fr/SQLiteDialect.java.txt)
    - Adjust the DataSource settings with the SQLite-specific settings. Especially, you need to indicate your SQLite Dialect since Grails won’t detect it by itself.

    That should work. However, keep in mind that SQLite is not a multi-user database engine. I ran into the “Database Locked” problem (just search the web for this issue and you know what I mean), which I suspect, has to do with concurrent users. Therefore I would not recommend using SQLite in a web application.

  6. # Kefah Issaon 01 Dec 2008 at 12:23 am

    Hello Jef,

    Thanks for the tip. its really good news to me.

    As for running into Database locks, i find that strange … AFAIK, sqlite supports concurrent access from within the same process. i.e. its thread-safe. Its not multi-process-save, but it is multi-thread-safe. further investigation is needed to validate my argument of course.

Trackback URI |