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.