Grails SQL Logging from the Command Line

When wrestling with GORM queries you may find yourself needing to turn on SQL logging in grails. This is quite easy to setup in DataSource.groovy by setting logSql:

dataSource {
	// ...
	logSql = true
} 

However, I find it more convenient and safer to switch this value from the command line:

grails -DlogSql=true run-app


This is possible if you change your DataSource.groovy slightly:

dataSource {
	// ...
	logSql = Boolean.parseBoolean(System.properties.logSql ?: 'false')
}

The fun doesn’t stop there. We can do a bit better (although much more verbose) by also logging result sets and parameter bindings. To set this up, we need to modify Config.groovy:

log4j = {
	appenders {
		// ...
	}
	
	root {
		// ...
	}
	
	// Your application specific logging configuration here ...
	
	if (Boolean.parseBoolean(System.properties.traceSql ?: 'false')) {
		trace	'org.hibernate.SQL',
				'org.hibernate.type'
	}
}

Now, with the flip of some swiches, our command line interface to run-app now looks like this:

grails -DlogSql=true -DtraceSql=true run-app


This will give us the essential information we need to tackle some really hardcode SQL issues. The fact that it is set on the command line ensures that we don’t accidentally check-in these values for our production settings. This is a good thing since it creates a great deal of information that can quickly fill up a hard drive.

If you want terser output, at the expensive of being more invasive, you should consider the p6spy plugin.