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.

46 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

Data Science Course in Bangalore said...

Actually I read it yesterday I looked at most of your posts but I had some ideas about it . This article is probably where I got the most useful information for my research and today I wanted to read it again because it is so well written.
Data Science Course in Bangalore

Srigokul said...

Thanks for Sharing this useful information...

Data science training in chennai
Data science course in chennai

Pallavi reddy said...

i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
data science training in bangalore

Data Analytics Courses in Bangalore said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
data science course bangalore

madhavi reddy said...

I Want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging endeavors.
cyber security course in bangalore

Pallavi reddy said...

i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
data scientist course in bangalore

Data Analytics Courses in Bangalore said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
data science in bangalore

Datascience Books said...

Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
Data Science Training in Bangalore

data analytics books said...

I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, hope you will provide more information on these topics in your next articles.
data analytics training in bangalore

InstituteBlr said...

I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
data analytics course in bangalore

Datascience Course Analyst said...

Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
Data Science Course in Bangalore

Mallela said...

Thanks for posting the best information and the blog is very helpful.data science institutes in hyderabad

Deekshitha said...

Informative blog
data analytics courses in hyderabad

trainingcourses 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

Datascience Course Analyst said...

Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
Data Science Course in Bangalore

Pallavi reddy said...

i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
cyber security training in bangalore

trainingcourses 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

Pallavi reddy said...

i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
cyber security training in bangalore

Data Analytics Courses in Bangalore said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
data analytics courses in bangalore

InstituteBlr said...

I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
data analytics course in bangalore

Mallela said...

Thanks for posting the best information and the blog is very helpful.artificial intelligence course in hyderabad

Deekshitha said...

Informative blog
ai training in hyderabad

Pallavi reddy said...

i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
tableau training in bangalore

princika said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
data science course

Mallela said...

Thanks for posting the best information and the blog is very important.digital marketing institute in hyderabad

Data Science said...

I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
Data Science Course Syllabus

data analytics books said...

I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, hope you will provide more information on these topics in your next articles.
data analytics training in bangalore

Deekshitha said...

Informative blog
best digital marketing institute in hyderabad

trainingcourses said...

I am impressed by the information that you have on this blog. It shows how well you understand this subject.
digital marketing courses in hyderabad with placement

Pallavi reddy said...

i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
artificial intelligence training in chennai

Data Science Institute in Bangalore - 360DigiTMG said...

Truly mind blowing blog went amazed with the subject they have developed the content. These kind of posts really helpful to gain the knowledge of unknown things which surely triggers to motivate and learn the new innovative contents. Hope you deliver the similar successive contents forthcoming as well.

Data Science in Bangalore