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.

37 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

Unknown 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 Todorov 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

Srigokul said...

Thanks for Sharing this useful information...

Data science training in chennai
Data science course in chennai

traininginstitute said...


Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also.

digital marketing courses in hyderabad with placement

traininginstitute said...

I think I have never seen such blogs before that have completed things with all the details which I want. So kindly update this ever for us.

digital marketing courses in hyderabad with placement

traininginstitute said...

I truly like your composing style, incredible data, thank you for posting.
Best Data Science courses in Hyderabad

data science said...

Great to become visiting your weblog once more, it has been a very long time for me. Pleasantly this article i've been sat tight for such a long time. I will require this post to add up to my task in the school, and it has identical subject along with your review. Much appreciated, great offer. data science course in nagpur



business analytics course in chennai with placement said...

Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.cloud computing course in lucknow

Unknown said...

I see some amazingly important and kept up to length of your strength searching for in your on the site data science course in delhi with placement

traininginstitute said...

It is imperative that we read blog post very carefully. I am already done it and find that this post is really amazing.
data science course

Anonymous said...

Thanks for your sharing.


Digital Marketing Course can be a vital tool to cultivate your career and business. In this course work, you will discover what is online marketing, why it is significant, how all businesses are turning from traditional marketing to this online branding. For more details, visit our website


Click here

diploma in digital marketing training malaysia said...

Through this post, I realize that your great information in playing with all the pieces was exceptionally useful. I advise this is the primary spot where I discover issues I've been scanning for. You have a smart yet alluring method of composing.

PMP Training in Malaysia said...

360DigiTMG, the top-rated organisation among the most prestigious industries around the world, is an educational destination for those looking to pursue their dreams around the globe. The company is changing careers of many people through constant improvement, 360DigiTMG provides an outstanding learning experience and distinguishes itself from the pack. 360DigiTMG is a prominent global presence by offering world-class training. Its main office is in India and subsidiaries across Malaysia, USA, East Asia, Australia, Uk, Netherlands, and the Middle East.

Ayisha said...
This comment has been removed by the author.
Ayisha said...
This comment has been removed by the author.
Ayisha said...
This comment has been removed by the author.
Ayisha said...
This comment has been removed by the author.
Ayisha said...

Best Digital Marketing course with affordable price in chennai.Learn digital marketing and become a best digital marketer.



For more details, visit our website and contact us,
Click here!

diploma in digital marketing malaysia said...

It is perfect chance to make a couple of game plans for the future and the opportunity has arrived to be sprightly. I've scrutinized this post and if I may I have the option to need to suggest you some interesting things or recommendations. Perhaps you could create next articles insinuating this article. I have to examine more things about it!

PMP Course said...

I have bookmarked your site since this site contains significant data in it. You rock for keeping incredible stuff. I am a lot of appreciative of this site.

Unknown said...

Going to graduate school was a positive decision for me. I enjoyed the coursework, the presentations, the fellow students, and the professors. And since my company reimbursed 100% of the tuition, the only cost that I had to pay on my own was for books and supplies. Otherwise, I received a free master’s degree. All that I had to invest was my time. data science training in surat

Mahil mithu said...

I truly adored visiting your post and this content was very unique. Thanks a lot for sharing this...
Sole custody Virginia
Protective Order Virginia

tonnie marie said...

Really nice blog

VIAN NETs – Provides complete protection from malaria and dengue, it also gives freedom from skin cancer, sinusitis, asthma, etc. With a simple design and elegant features, it is the best net to protect your loved ones from mosquito menace. Install VIAN NETs and enjoy your dreams without any interruption. Mosquito nets in Chennai
View our Website

Home decor said...

Have you ever tried to recreate a look in your home that you once saw in a magazine or film, only to end up with an unsightly looking room? Then have a look to our store to decore your beautiful home with our perfect product

Buy Now