
# 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

```properties
# 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
```

```text
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):

- `true`: in every build.
- `false`: in none, no code at all.
- not set: in debug and profile builds only, so a release build ships none of it. A profile (`application-dev.yml`) can still turn the switches on in a debug run. One that sets `show-sql` where the configuration without a profile does not is warned about by the build: a release build has no code for it.

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

- **Secret-looking names are masked.** The value of a name that, case and separators ignored, contains `password`, `passwd`, `passphrase`, `secret`, `credential`, `token`, `apikey`, `privatekey`, `accesskey`, `signingkey` or `encryptionkey`, or has the word `pwd` or `key`, is written `******`, in the repository's method line too. So `password_hash`, `apiToken`, `clientSecret` and `sslpassword` are masked; `monkey`, `keyword`, `tokenizer` and `secretary` are not.
- **Values without a name** (a plain list given to a `JdbcTemplate`) cannot be told from a secret: they show at trace, as in Spring, and are masked when `show-parameters` puts them at info.
- **Names that hide a secret** (`pw`, `pin`) are not masked. Name columns so the rule sees them, or keep trace off in production.
- **Values never go into the SQL line.** They are always parameters.

## 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)

```properties
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

- [JdbcTemplate and JdbcClient](jdbc.md)
- [Logging](../spring-core-dart/logging/index.md)
- [All properties](properties.md)
