How to log SQL statements in Grails

I want to log in the console or in a file, all the queries that Grails does, to check performance.

I had configured this without success.

Any idea would help.

Setting

datasource {
...
logSql = true
}

in DataSource.groovy (as per these instructions) was enough to get it working in my environment. It seems that parts of the FAQ are out of date (e.g. the many-to-many columns backwards question) so this might also be something that changed in the meantime.

I find it more useful to do the following, which is to enable Hibernate's logging to log the SQL along with bind variables (so you can see the values passed into your calls, and easily replicate the SQL in your editor or otherwise).

In your Config.groovy, add the following to your log4j block:

log4j = {

    // Enable Hibernate SQL logging with param values
    trace 'org.hibernate.type'
    debug 'org.hibernate.SQL'
    //the rest of your logging config
    // ...
    }

For grails 3.*

Option #1 add the following to logback.groovy

logger("org.hibernate.SQL", DEBUG, ["STDOUT"], false)
logger("org.hibernate.type.descriptor.sql.BasicBinder", TRACE, ["STDOUT"], false)

or

Option #2 add the following to dataSource in the application.yml. However this approach does not log the parameter values

environments:
  local:
    dataSource:
        logSql: true
        formatSql: true
        

Try this:

log4j = {
   ...
   debug 'org.hibernate.SQL'
   trace 'org.hibernate.type.descriptor.sql.BasicBinder'
}

It avoids the performance problems of trace logging the Hibernate type package. This works with Hibernate 3.6 and above. I got this from: https://burtbeckwith.com/blog/?p=1604

Solution is only for development, not production.

All the answers above work and are correct. But they do not show the complete query in a nice human readable way. If want to see the final (without any ?, ?) query you have two options.

A) proxy your jdbc connection with log4jdbc or p6Spy.

B) look at it on database level. For example really easy to do with mysql.

Find out where you general_log_file is. Active general log if no activated already.

mysql command line> show variables like "%general_log%";
mysql command line> set global general_log = true;

Now everything is logged to you log file. Mac / linux example to show nice stream of your queries.

tail -f path_to_log_file 

Next works for me:

grails-app/conf/application.yml

# ...
hibernate:
    format_sql: true # <<<<<<< ADD THIS <<<<<<<
    cache:
        queries: false
        use_second_level_cache: true
# ...
environments:
    development:
        dataSource:
            logSql: true // <<<<<<< ADD THIS <<<<<<<
            dbCreate: create-drop
            url: jdbc:h2:mem:...
# ...

grails-app/conf/logback.groovy

// ...
appender('STDOUT', ConsoleAppender) {
    encoder(PatternLayoutEncoder) {
        pattern = "%level %logger - %msg%n"
    }
}

// >>>>>>> ADD IT >>>>>>>
logger 'org.hibernate.type.descriptor.sql.BasicBinder', TRACE, ['STDOUT']
logger 'org.hibernate.SQL', TRACE, ['STDOUT']
// <<<<<<< ADD IT <<<<<<<
    
root(ERROR, ['STDOUT'])
    
def targetDir = BuildSettings.TARGET_DIR
// ...

Source: http://sergiodelamo.es/log-sql-grails-3-app/

Pure for reference only, but I use p6spy to log the SQL queries. It's a small intermediate jdbc driver. The exact query is logged as it would be send to the server (with parameters included).

include it in your project:

runtime 'p6spy:p6spy:3.0.0'

Change your datasource driver:

driverClassName: com.p6spy.engine.spy.P6SpyDriver

And your jdbc url:

url: jdbc:p6spy:mysql://

Configure it using spy.properties (in grails-app/conf).

driverlist=org.h2.Driver,com.mysql.jdbc.Driver
autoflush=true
appender=com.p6spy.engine.spy.appender.StdoutLogger
databaseDialectDateFormat=yyyy-MM-dd
logMessageFormat=com.p6spy.engine.spy.appender.MultiLineFormat

Don't forget to disable this for production!

I know this was asked and answered long back .But I just happened to see this question and couldn't stop myself in answering or sharing our sql logging implementation approach in our project. Hope it be of some help.

Currently it is in development environment. We are using "log4jdbc Driver Spy " to log sql.

Configuration:

In your BuildConfig.groovy: add below dependencies:

dependencies {
.....
runtime 'org.lazyluke:log4jdbc-remix:0.2.7'
}

And in your DataSource or other config related :[wherever you have defined the data source related configuration] , Add :

datasources{
.....
driverClassName: "net.sf.log4jdbc.DriverSpy",
url: "jdbc:log4jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = XXXXX.XX>XXX)(PORT = 1521))) (CONNECT_DATA = (SID = XXXX)(SERVER =DEDICATED)))",
....
}
log4j = {
    
    info 'jdbc.sqlonly' //, 'jdbc.resultsettable'

}

From my personal experience I found it quite useful and helpful while debugging. Also more information you can find in this site. https://code.google.com/p/log4jdbc-remix/

King Regards

If you have the console plugin installed, you can get sql logging with this little code snippet.

// grails 2.3
def logger=ctx.sessionFactory.settings.sqlStatementLogger

// grails 3.3  
def logger = ctx.sessionFactory.currentSession.jdbcCoordinator.statementPreparer.jdbcService.sqlStatementLogger

logger.logToStdout=true    
try {
   <code that will log sql queries>
}
finally {
    logger.logToStdout = false
}

This is a variation on many of the solutions above, but allows you to tweak the value at runtime. And just like the other solutions that deal with logToStdout it only shows the queries and not the bind values.

The idea was stolen from a burtbeckwith post I read some years ago that I can't find right now. It has been edited to work with grails 3.3.

A similar technique can be used to turn on logging for specific integration tests:

class SomeIntegrationSpec extends IntegrationSpec {

    def sessionFactory

    def setup() {
        sessionFactory.settings.sqlStatementLogger.logToStdout = true
    }

    def cleanup() {
        sessionFactory.settings.sqlStatementLogger.logToStdout = false
    }

    void "some test"() {
           ...
    }

This will turn on sql logging for just the tests in this one file.

For a particular Block of code we can also create a method that accept a closure. eg.

 static def executeBlockAndGenerateSqlLogs(Closure closure) {
    Logger sqlLogger = Logger.getLogger("org.hibernate.SQL");
    Level currentLevel = sqlLogger.level
    sqlLogger.setLevel(Level.TRACE)
    def result = closure.call()
    sqlLogger.setLevel(currentLevel)
    result }

executeBlockAndGenerateSqlLogs{DomainClazz.findByPropertyName("property value")}