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).
- Type in a Connection Name: Grails Development
- Choose HSQLDB as the DatabaseType
- Choose HSQLDB embedded as your Driver using the hsqldb.jar located under your Grails home directory ($GRAILS_HOME/lib)
- 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.
- Make sure your Userid is sa
- 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:
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
Thanks for your Post.
If somebody wants to have the data persistent after a restart of the grail-app,
dbCreate = "update"
is the option.
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
Thanks very much
Thanks a lot!!!! needed it badly
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.
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
Hey man, thanks for the article. I just started playing with groovy and grails and your post helped. Cheers!
I did everything as described, I was able to connect, but I do not see the tables that are created by grails...
Sorry, all work :) I am stupid :)
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...
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
i have created a domain class in my grails-application but i am not able to see that corresponding table in my db-visualizer.
Make sure your URL said "file" instead of "men"
This
jdbc:hsqldb:file:devDB;shutdown=true
Not this
jdbc:hsqldb:mem:devDB
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
Post a Comment