Sunday, 18 December 2011

How to view HSQLDB grails contents

If you're working with Grails it's sometimes very useful to be able to view the layout and contents of the database that you're working with. If you're using the default HSQLDB engine that comes with Grails, one of the ways to achieve this is to launch the "Database Manager" application from your BootStrap file, giving you a GUI which you can use to explore the database.

class BootStrap {
def init = { servletContext ->
def destroy = {
This will result in the Database Manager GUI being launched:

You just need to change the last part of the URL to "devDB" (or whatever you've chosen to name your development database) and hit the "Ok" button. You should be presented with the Database Manager's default screen, showing all of the tables in a list on the left.

Selecting the + next to each table allows you to see the columns/fields of each table and so forth. Entering SQL commands in the text portion and hitting the 'Execute' button, while using the application will allow you to see the contents of your database in real-time.

Note that closing the Database Manager window will close the application.

Additionally the Database Manager program is available from the "universe" debian and ubuntu repositories under the package name "hsqldb-utils". However I was unable to connect to the in-memory HSQLDB instance from a Database Manager instance launched outside of Grails. I'm assuming this is because it's launched as a seperate process, it gets its own memory space. It should be possible to get it to connect to the HSQLDB instance when using a file based database.

NOTE: The above instructions apply to pre 2.0 Grails versions. Since Grails 2.0, the way to see the Database schema has been to access the web interface, which is launched automatically at: http://localhost:8080/[ApplicationName]/dbconsole. You might need to change the URL, but shouldn't have to enter a password.

No comments: