How to log SQL statements in Grails

Question

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.

Hough

None of the solutions provided work for me. I'm writing this just to let everyone know how I am desperate. – Rintoul

Answer 1

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.

Elated

logSql=true alone is not enough. Hibernate logging must be turned on, too. See @Pete's answer. – Insanitary

I've noticed that this does not include the values that go in the SQL statements where the "?"s are. – Insanitary

This works, but for all queries. Is it also possible to print the generated sql for a particular Criteria without having to set logSql=true ? – Karlis

@Guus how can I print the generated sql for a particular Criteria? – Amatory

@biniam_Ethiopia As far as I know, this is not possible. I want this as well as it's annoying for debugging certain classes and not wanting to see other queries as well. – Karlis

@Tomislav_Nakic-Altirevic link is broken. – Mccullough

Where does it put the file? – Harv

Answer 2

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
    // ...
    }

Safar

I've used this many times. One thing to note: outputting the params is very expensive. I would recommend doing this only on your dev box. – Armistice

You can also add format_sql = true into the hibernate block of your DataSource.groovy for a nicely formatted output. – Torruella

Note: This will log both where clause parameters and column values extracted from query result sets. To log only where clause parameters, use trace 'org.hibernate.type.BasicBinder' – Rouault

Anyone know the equivalent for grails 3.3.8? – Harv

For some reason, syntactically invalid queries (generated by Hibernate itself, sadly!) don't get logged - all other queries do get logged... Possibly an issue with Hibernate itself? – Jeth

Answer 3

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

Broughton

Answer 4

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

Insanitary

Answer 5

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 

Ingress

Answer 6

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/

Inexplicable

Answer 7

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!

Domingo

Answer 8

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

Adventure

Answer 9

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.jdbcServices.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.

Matchwood

Answer 10

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")}

Vizcacha

Answer 11

logback.xml

Grails 5 and above only accepts logback.xml. Add the following inside the configuration tag:

<logger name="org.hibernate.SQL" level="DEBUG" additivity="false">
    <appender-ref ref="STDOUT" />
</logger>
<logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="TRACE" additivity="false">
    <appender-ref ref="STDOUT" />
</logger>

---

application.yml

To better visualize SQL queries, add the following:

dataSource:
    formatSql: true

If you want logback configuration for development only, you can add the following to the block environments > development with logging configuration in conf/logback-dev.xml:

logging:
  config: classpath: logback-dev.xml

Limited