Using the H2 Database Console in Grails

We’ve switched the in-memory development database in Grails from HSQLDB to H2 for 1.4 and one of the cool features this provides is a web-based database console. It’s available as a standalone app runnable from the commandline, but it’s also easy to wire up in web.xml so it runs in-process.

Most of the time you’ll probably use it to access data in your development environment, but the console is not limited to H2 databases – it will work with any database that you have a JDBC driver for.

This is already implemented in 1.4; by default in development you can access it by opening http://localhost:8080/appname/dbconsole. The URL is configurable and you can enable or disable it per-environment. But there are only a couple of steps required to enable this in pre-1.4 applications.

Required changes

You’ll need the H2 JDBC driver and the best way to get that is with BuildConfig.groovy. Add a dependency for H2 in the dependencies block and enable the Maven central repo by adding mavenCentral() to the repositories block:

grails.project.class.dir = 'target/classes'
grails.project.test.class.dir = 'target/test-classes'
grails.project.test.reports.dir = 'target/test-reports'

grails.project.dependency.resolution = {

   inherits 'global'

   log 'warn'

   repositories {
      grailsPlugins()
      grailsHome()
      grailsCentral()

      mavenCentral()
   }

   dependencies {
      runtime('com.h2database:h2:1.3.146') {
         transitive = false
      }
   }
}

You’ll also need to edit web.xml to register the servlet. If you haven’t already, run

and edit src/templates/war/web.xml and add this servlet and servlet-mapping:

<servlet>
   <servlet-name>H2Console</servlet-name>
   <servlet-class>org.h2.server.web.WebServlet</servlet-class>
   <load-on-startup>1</load-on-startup>
   <init-param>
      <param-name>-webAllowOthers</param-name>
      <param-value>true</param-value>
   </init-param>
</servlet>

<servlet-mapping>
   <servlet-name>H2Console</servlet-name>
   <url-pattern>/dbconsole/*</url-pattern>
</servlet-mapping>

Use whatever url-pattern you want, e.g. you might want to change it to <url-pattern>/admin/dbconsole/*</url-pattern> to make it easier to secure with a wildcard rule saying that /admin/** requires ROLE_ADMIN.

Alternate approach

One downside to this approach is that there’s no way to disable the console per-environment since you’re hard-coding the change in web.xml. So be sure to guard access to this URL, e.g. with a security plugin. As an alternative, you can insert the servlet and servlet-mapping tags into web.xml programmatically.

To do this, create (or edit if you already have one) scripts/_Events.groovy and add this:

import grails.util.Environment

eventWebXmlEnd = { String filename ->

   if (Environment.current != Environment.DEVELOPMENT) {
      return
   }

   String consoleServlet = '''

   <servlet>
      <servlet-name>H2Console</servlet-name>
      <servlet-class>org.h2.server.web.WebServlet</servlet-class>
      <load-on-startup>1</load-on-startup>
      <init-param>
         <param-name>-webAllowOthers</param-name>
         <param-value>true</param-value>
      </init-param>
   </servlet>'''

   String consoleServletMapping = '''

   <servlet-mapping>
      <servlet-name>H2Console</servlet-name>
      <url-pattern>/dbconsole/*</url-pattern>
   </servlet-mapping>'''

   def insertAfterTag = { String original, String endTag, String addition ->
      int index = original.indexOf(endTag)
      original.substring(0, index + endTag.length()) +
            addition + original.substring(index + endTag.length())
   }

   String xml = webXmlFile.text
   xml = insertAfterTag(xml, '</servlet>', consoleServlet)
   xml = insertAfterTag(xml, '</servlet-mapping>', consoleServletMapping)

   webXmlFile.withWriter { it.write xml }
}

This checks if the environment is development and adds the tags at the correct location, then rewrites the file with the updates.

If you go with this approach, there’s no need to make any changes in the web.xml template since you’ll be making all of your changes programmatically.

We’ve switched the in-memory development database in Grails from HSQLDB to H2 for 1.4 and one of the cool features this provides is a web-based database console. It’s available as...

This entry was posted on Sunday, December 19th, 2010 at 4:39pm and is filed under database, grails, java. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.