How to log SQL statements in Grails

Ramp up your SQL logging

Environment-specific logging

Grails provides the capability to tweak logging for specific environments. Thus, ensuring SQL logging activates only in the desired environment:

environments:
  development:
    dataSource:
      logSql: true # Only for developers. Production will ignore this


Balancing log output and performance

Specify your log output destination in logback.groovy or application.yml as desired, such as console or a file. Utilize log rotation and archiving for smooth SQL log storage and performance.

Keep an eye on the performance

Continuous monitoring of performance is a must when logging every SQL statement. Be mindful of regulatory compliances, performance impacts, and security implications for your application's logging.

Deep dive into database interactions

log4jdbc or p6Spy comes in handy to examine your interaction with SQL databases. These tools beautifully unravel the SQL statements.

Database specific logging

Some databases offer built-in query logging facilities. Get acquainted with these features for an enhanced control over SQL statement logging.

Nailing SQL logging: key practices

Setting up SQL logging is no Herculean task, but ensuring it is efficient and effective does require special attention:

Formatted SQL for better readability

Ensure your SQL logs are easy to read by turning on hibernate.format_sql in application.yml:

hibernate:
  format_sql: true # Who doesn't like well formatted SQL?


Thorough logging of parameters and results

Configure logback to record both input parameters and output results:

logging:
  level:
    org.hibernate.type.descriptor.sql: TRACE # Like X-ray vision but for SQL parameters


Effective log analysis

Log analysis and visualization tools are a game changer, they transform raw SQL logs into actionable insights.

Production readiness

Always validate your logging configuration against performance, security, and privacy standards before pushing it to production.