Logging SQL

Every statement the repositories and templates run is logged: the SQL and its time at debug, the bound values at trace, and a slow statement at warn. Four properties change what shows without touching log levels: spring.data.jdbc.show-sql, format-sql, show-parameters and slow-query-threshold.

Why

When a page is slow or a query returns the wrong rows, the first question is "what SQL ran, with which values?". The logs answer it. In production, values can be personal data or secrets, so they are kept at trace and masked when their name looks secret.

Example

# Development: statements and values at info, on several lines.
spring.data.jdbc.show-sql=true
spring.data.jdbc.format-sql=true
spring.data.jdbc.show-parameters=true

# Production: only slow statements, at warn.
spring.data.jdbc.slow-query-threshold=500ms
INFO  spring.jdbc.core.StatementCreatorUtils  [dataSource tx#12 Catalog.find]   title = Dune (String)
INFO  spring.jdbc.core.JdbcTemplate  [dataSource tx#12 Catalog.find] select * from "books" where "title" = ?  (1 row, 3 ms) {request=7f3a}
WARN  spring.jdbc.core.JdbcTemplate  [dataSource] slow statement, 812 ms: select ...

The loggers

Logger Level What
spring.jdbc.core.JdbcTemplate debug Each statement, its time and its row count; a failed one with its time.
spring.jdbc.core.StatementCreatorUtils trace The bound values, each with the name it is bound to.
spring.jdbc.core.JdbcTemplate warn A statement slower than slow-query-threshold.
spring.sql.migration info The migrations applied: versions, descriptions, counts, never values.

A line starts with [dataSource tx#12 Catalog.find]: the data source's name and, inside a transaction, its number and name, and ends with {request=7f3a} when the web starter's request id is set, so a slow page leads to its statements.

The switches

Key Default Meaning
spring.data.jdbc.show-sql false Statements at info instead of debug, so they show with the default level. Spring Boot's spring.jpa.show-sql.
spring.data.jdbc.format-sql false The SQL on several lines, one per main clause.
spring.data.jdbc.show-parameters false The bound values at info instead of trace. Secret-looking names stay masked.
spring.data.jdbc.slow-query-threshold none A slower statement is logged at warn, whatever the debug level.

The same switches can be set per logger with logging.level.spring.jdbc.core.JdbcTemplate=debug and logging.level.spring.jdbc.core.StatementCreatorUtils=trace.

In a release build

What show-sql, format-sql and show-parameters do is in a module of its own, which the generator puts into the build by spring.data.jdbc.show-sql (in application.yml or application.properties without a profile):

slow-query-threshold stays in every build, so slow statements are still logged at warn. Without the module the statements are logged at debug and their values at trace, as the loggers say.

What the values show

Each value is logged with the name it is bound to: the column of a repository's save, derived query or delete, or the :name of a @Query or a named template.

Which store, at start

The log says at start where the data goes. A data source's pool says it when it starts, at its first connection (at start with schema: validate, the default):

sqlite - Starting... (sqlite:file:library.db, max-size 4)
sqlite - Start completed.

Repositories without a data source keep their data in memory, and the first of them says so once, at info (logger spring.data.memory.InMemoryDataStore):

memory - Starting... (in memory: the data lives until the application stops)

An application meant to run on a database that shows this line has no spring.datasource.url.

Passwords in URLs

The password of a URL's user info is never logged, and neither is the value of a query parameter whose name looks secret (?password=..., sslpassword, token): the pool's "Starting..." line and every message about a URL show password=******.

In Spring (Java)

logging.level.org.springframework.jdbc.core.JdbcTemplate=debug
logging.level.org.springframework.jdbc.core.StatementCreatorUtils=trace

The loggers keep Spring's names without org.springframework.. show-sql, format-sql, show-parameters and slow-query-threshold are additions for Spring Data JDBC, which has no such switches (JPA's spring.jpa.show-sql is the model). Spring does not mask values.

See also