Thursday, January 3, 2008

How to connect to a Grails HSQL Database

The following is how you can use DBVisualizer to connect to a Grails HSQL Database. Grails uses Spring and Hibernate and its default database uses HSQLDB. So why would you want to view the Grails database? Maybe you are curious of how GORM works; debug your Domains; perhaps you don't trust Grails, or finally you might be a control freak. Or like me you are all of the above.

Update Datasource.groovy
First, if you want to view the development database you need to change the url to not use an in-memory database because DBVisualizer will not have access to the Grails JVM memory. Open up the DataSource.groovy file and locate the development environment section and replace the existing url "jdbc:hsqldb:mem:devDB" with "jdbc:hsqldb:file:devDB;shutdown=true".







Start Grails

>grails run-app

Create New Database Connection in DBVisualizer
Open up DBVisualizer (my version is 6.0.7) and create a new Connection (if you are unable to use DBVisualizer you can also use the HSQL Database Manager - see this post).
  1. Type in a Connection Name: Grails Development
  2. Choose HSQLDB as the DatabaseType
  3. Choose HSQLDB embedded as your Driver using the hsqldb.jar located under your Grails home directory ($GRAILS_HOME/lib)
  4. Update the Database URL to include your grails app base directory (jdbc:hsqldb:file:{your-grails-app-base-dir}/devDB). For me this was jdbc:hsqldb:file:/workspace/checkout/netcds-admin/devDB. Note that according to the HSQL document, Window users don't have to specify the C: drive.
  5. Make sure your Userid is sa
  6. Click Connect













View Tables

Then on the left you should be able to see your tables under PUBLIC --> TABLE.
















Special thanks to the Nabble users who responded to my question: Christian Laakmann and Helmut Denk.

15 comments:

ramana said...

well all the examples i have seen are using inList with static data to populate list. Can any body give how to populate from database.
thanks
ramana
ramanakallil@rediffmail.com

schmichri said...

Thanks for your Post.

If somebody wants to have the data persistent after a restart of the grail-app,
dbCreate = "update"
is the option.

Fraaargh said...

Thanks for the tip. Just a note though: on step 4, it's not "debDB" but "devDB" ! ;) I spent 5 minutes wondering why I didn't see my data ! And it seems that the Grails app must not be running at the same time otherwise we can't access the database.
Thanks

Anonymous said...

Thanks very much

Anonymous said...

Thanks a lot!!!! needed it badly

Anonymous said...

This is great. Thanks for posting this. I had been attempting to reverse engineer a legacy DB and being able to see how Grails (gorm) would like its data laid out helps.

jlorenzen said...

Re: legacy database. A few more tips is to turn on sql logging. To do that see http://jlorenzen.blogspot.com/2007/10/grails-tip-enable-sql-logging.html. Also, in case you didn't know, grails supports mapping models to legacy databases really easily. So for example you can map Book.name to the database field book_name. See http://www.grails.org/GORM+-+Mapping+DSL

boyko said...

Hey man, thanks for the article. I just started playing with groovy and grails and your post helped. Cheers!

fenuk said...

I did everything as described, I was able to connect, but I do not see the tables that are created by grails...

fenuk said...

Sorry, all work :) I am stupid :)

Erik Weibust said...

Thanks for the post. I feel I have followed the directions acurately but am unable to see my tables show up in the public schema. Any tips on how I resolve the problem? Thanks...

Sean said...

Thanks for the tip.

If you want an even quicker (but dirtier) way to see if your app is inserting data have a look in the devDB.log file

Anonymous said...

i have created a domain class in my grails-application but i am not able to see that corresponding table in my db-visualizer.

Anonymous said...

Make sure your URL said "file" instead of "men"

This
jdbc:hsqldb:file:devDB;shutdown=true

Not this
jdbc:hsqldb:mem:devDB

Cesar Flores said...

The file name in step 4 must have no extension. The version 8.0.8 of DbVisualizer allow select the file name, just remove the extension and tables will be shown