Archive for the Category 'database'

Updated Grails Database Migration plugin

Friday, January 04th, 2013

Edit: January 5 – I released the plugin that adds support for JAXB-based classes; see the plugin page and the documentation for more information.


One of the downsides to releasing a lot of plugins is lots of reported issues. I’ve joked that since there aren’t good ways to know how much use a plugin gets, the best metric is the number of reported bugs and feature requests, and that is mostly true. Using that logic the database-migration plugin is very popular 🙂

I try to address serious issues, but most of this plugin’s issue have to do with generated code. My attitude towards generated code is that it should not be trusted, and should rarely be expected to be completely correct. For example, when you use the dbm-gorm-diff or dbm-generate-gorm-changelog scripts, they do most of your work for you. My hope is that it saves you lots of time and that you shouldn’t need to do much work to fix any issues, but that you should expect issues.

When I did the What’s new with Grails 2.0 talk at NEJUG a year ago I mentioned this plugin and focused on the GORM-based scripts because I think they’re the best approach to creating migrations. But one of the attendees who also uses Rails said that Rails migrations were better because they have a DSL that you can use to write the migrations. I realized that I was so used to running dbm-gorm-diff that I had neglected to even mention the extensive Groovy DSL that the plugin supports (it’s a 100% clone of the XML syntax in native Liquibase). It’s a good DSL and you can create migrations completely by hand using it, but I can’t see why you would do that given how much you can get for free with the scripts. I mention this story to point out why I think it’s ironic when people complain that it’s tedious to have to fix invalid code that a script generated; feel free to use the DSL directly and forego the broken scripts 😉


The bug list for the database-migration plugin was getting a bit big and there were quite a few open pull requests. The tipping point however was seeing this tweet and realizing that I should spend some time on the plugin again.

The pull request that Zan mentioned in his tweet was a big one, adding support for doing migrations on multiple databases, mirroring the multi-datasource support in Grails 2.0. It would be great if all pull requests were this high-quality, including documentation updates and lots of tests. While I was integrating that (I had made some changes since then that required a traditional pull request since the Github UI wouldn’t do an automatic merge, and there were a few conflicts) I worked on the other outstanding issues.

I merged in all of the open pull requests – many thanks for those. I also closed a few bugs that weren’t real bugs or were duplicates, and fixed several others. That made for an interesting JIRA 30-day issue graph:

Many of the other reported issues were variants of the same problem where Liquibase was specifying the size of database columns that don’t support a size (for example bytea(255)). Hibernate does a much better job of this, so I was able to rework things so the Hibernate data types are used where possible instead of what Liquibase generates. So hopefully the generated changelogs will be much more accurate and involve less tweaking.

You can see the release notes of the 1.3 release here and the updated docs here.

Note that the latest version of the plugin is 1.3.1 since there were issues with the JAXB code that I included in the 1.3 release. I removed the code since it depends on Java 7 (and wasn’t completely finished) and will release it as a separate plugin.

Grails SQL Logging part 2 – groovy.sql.Sql

Wednesday, October 31st, 2012

I discussed options for logging Hibernate-generated SQL in an earlier post but today I was trying to figure out how to see the SQL from groovy.sql.Sql and didn’t have much luck at first. The core problem is that the Sql class uses a java.util.logging.Logger (JUL) while the rest of the world uses a Log4j logger (often with a Commons Logging or SLF4J wrapper). I assumed that since I am using the Grails support for JUL -> Log4j bridging (enabled with the grails.logging.jul.usebridge = true setting in Config.groovy) that all I needed to do was add the class name to my log4j DSL block:

log4j = {
   error 'org.codehaus.groovy.grails',
         'org.springframework',
         'org.hibernate',
         'net.sf.ehcache.hibernate'
   debug 'groovy.sql.Sql'
}

but that didn’t work. Some googling led to this mailing list discussion which has a solution involving a custom java.util.logging.Handler to pipe JUL log messages for the 'groovy.sql.Sql' logger to Log4j. That seemed like overkill to me since theoretically that’s exactly what grails.logging.jul.usebridge = true already does. I realized I had no idea how the bridging worked, so I started looking at the implementation of this feature.

It turns out that this is handled by the Grails “logging” plugin (org.codehaus.groovy.grails.plugins.log4j.LoggingGrailsPlugin) which calls org.slf4j.bridge.SLF4JBridgeHandler.install(). This essentially registers a listener that receives every JUL log message and pipes it to the corresponding SLF4J logger (typically wrapping a Log4j logger) with a sensible mapping of the different log levels (e.g. FINEST -> TRACE, FINER -> DEBUG, etc.)

So what’s the problem then? While grails.logging.jul.usebridge = true does configure message routing, it doesn’t apply level settings from the log4j block to the corresponding JUL loggers. So although I set the level of 'groovy.sql.Sql' to debug, the JUL logger level is still at the default level (INFO). So all I need to do is programmatically set the logger’s level to DEBUG (or TRACE to see everything) once, e.g. in BootStrap.groovy

import groovy.sql.Sql
import java.util.logging.Level

class BootStrap {

   def init = { servletContext ->
      Sql.LOG.level = Level.FINE
   }
}

Updates for “Delayed SessionFactory Creation in Grails”

Wednesday, September 26th, 2012

Back in the beginning of 2010 I did a post on how to delay creating the SessionFactory based on a discussion on the User mailing list. This has come up again and I thought I’d look and see if things had changed for Grails 2.

The general problem is the same as it was; Grails and Hibernate create database connections during startup to help with configuration, so the information that is auto-discovered has to be explicitly specified. In addition any eager initialization that can wait should wait.

One such configuration item is the lobHandlerDetector bean. This hasn’t changed from before, so the approach involves specifying the bean yourself (and it’s different depending on whether you’re using Oracle or another database). Since it’s the same I won’t include the details here; see the previous post.

Another is the Dialect. Again, this is the same as before – just specify it in DataSource.groovy. This is a good idea in general since there might be particular features you need in a non-default Dialect class, and specifying org.hibernate.dialect.MySQL5InnoDBDialect for MySQL guarantees that you’ll be using transactional InnoDB tables instead of non-transactional MyISAM tables.

The remaining issues have to do with eager initialization. I started down the path of reworking how to lazily initialize the SessionFactory since using a Spring bean post-processor is significantly less involved (and brittle) than the approach I had previously used. But it turns out that the more recent version of Hibernate that we’re now using supports a flag that avoids database access during SessionFactory initialization, hibernate.temp.use_jdbc_metadata_defaults. So add this to the hibernate block in DataSource.groovy:

hibernate {
   ...
   temp.use_jdbc_metadata_defaults = false
}

And the last issue is the DataSource itself. Up to this point all of the changes will avoid getting a connection, but the pool might pre-create connections at startup. The default implementation in Grails is org.apache.commons.dbcp.BasicDataSource and its initial size is 0, so you’re ok if you haven’t configured a different implementation. If you have, be sure to set its initial size to 0 (this isn’t part of the DataSource so the setter method is implementation-specific if it even exists).


If you’re using multiple datasources, you can delay their database connectivity too. There is a lobHandlerDetector bean for each datasource, so for example if you have a second one with the name “ds2”, configure a lobHandlerDetector_ds2 bean in resources.groovy. Likewise for the Dialect; specify it in the dataSource_ds2 block in DataSource.groovy. Set the use_jdbc_metadata_defaults option in the hibernate_ds2 block:

hibernate_ds2 {
   ...
   temp.use_jdbc_metadata_defaults = false
}

And finally, as for the single-datasource case, if you’ve reconfigured secondary datasource beans’ pool implementations, set their initial sizes to 0.

Using the H2 Database Console in Grails

Sunday, December 19th, 2010

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

grails install-templates

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.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 License.