# Inspecting SQL

Add `.sql()` to a query's terminal and Ferro returns the statement it would send, without sending it; turn on the `ferro.sql` logger and Ferro records every statement it does send.

Every example on this page uses one `Account` model. Its `token` is a `SecretStr`, so you can see what Ferro does with a value that must not reach a log:

```python
class Account(Model):
    id: int | None = Field(default=None, primary_key=True)
    owner: str
    token: SecretStr
```

```python
class Account(Model):
    id: Annotated[int | None, Field(primary_key=True)] = None
    owner: str
    token: SecretStr
```

Here is a whole program. It connects, opens a session and renders the query that finds Ada's account:

```python
from ferro import connect, engines, transaction

await connect("sqlite::memory:", auto_migrate=True)
async with engines.session():
    rendered = await Account.where(lambda account: account.owner == "Ada").all.sql()
    print(rendered)
    print(rendered.params)
```

It prints:

```text
SELECT "account"."id", "account"."owner", "account"."token" FROM "account" WHERE "account"."owner" = ?
('Ada',)
```

The first line is the SQL, with a placeholder where the value goes. The second is the value Ferro would bind to it. Nothing reached the database. Every example on this page runs inside a session like this one; see [Sessions](<https://ferro-orm.x54.sh/guide/connections/#sessions>) for why and how to open one.

## Rendering a statement with `.sql()`

Every terminal of a query has a `.sql()` form that you await in its place: `all`, `first`, `count`, `exists`, `update` and `delete`. It returns a `RenderedSql` with three fields, `sql`, `params` and `dialect`, and `print()` of one shows the SQL alone. Use it to check what a lambda predicate turned into, or to put a statement in a bug report.

`count` renders its aggregate, and `update` takes the same arguments it takes when it runs, either keyword values or a recipe lambda:

```python
counted = await Account.select().count.sql()
print(counted)

renamed = await Account.where(
    lambda account: account.owner == "Ada"
).update.sql(owner="Ada Lovelace")
print(renamed)
print(renamed.params)
```

It prints:

```text
SELECT COUNT(*) FROM "account" WHERE TRUE
UPDATE "account" SET "owner" = ? WHERE "account"."owner" = ?
('Ada Lovelace', 'Ada')
```

The `count` has no filter, so Ferro writes `WHERE TRUE`. The `update` lists the new value first and the filter's value second, which is the order of its placeholders. On PostgreSQL the placeholders are `$1` and `$2`, and `dialect` is `"postgres"`; nothing else in these statements changes.

The query renders for the database it would run on, so `.sql()` needs a connection and a session like the call itself. For the terminals that return model instances, see [Queries](<https://ferro-orm.x54.sh/guide/queries/index.md>).

## Turning on the statement log

The `ferro.sql` logger writes one `DEBUG` record for each statement Ferro sends, after the statement finishes. Ferro collects nothing while the logger is not enabled for `DEBUG`, so the switch costs nothing when it is off. To see the records in your terminal, give the logger a level and your application a handler, as [Logging](<https://ferro-orm.x54.sh/guide/connections/#logging>) shows with `logging.basicConfig`.

The examples that follow need the records themselves, so this handler keeps them in a list:

```python
records: list[logging.LogRecord] = []

class Collect(logging.Handler):
    def emit(self, record: logging.LogRecord) -> None:
        records.append(record)

logger = logging.getLogger("ferro.sql")
logger.addHandler(Collect())
logger.setLevel(logging.DEBUG)
```

Every statement Ferro sends from here on lands in `records`. The program above had already called `connect()`, so the schema statements it ran are not in the list; put these lines before `connect()` and they are, as `migrate` records.

## A log record

Save an account and read the record the `INSERT` left:

```python
await Account(owner="Ada", token=SecretStr("sk_live_ada")).save()

print(untimed(records[-1]))
```

A plain handler prints the record as one line, the statement, its parameters and how long it took. The page's example drops the timing, the last word of the line, because it differs on every run:

```text
INSERT INTO "account" ("owner", "token") VALUES (?, ?) RETURNING "id" params=('Ada', ***)
```

A structured handler, such as a JSON formatter, reads the same record as attributes instead of parsing the line:

```python
record = records[-1]
for name in ("sql", "params", "dialect", "operation", "subject", "connection"):
    print(f"{name}: {getattr(record, name)!r}")
print("rows:", record.rows)  # type: ignore[attr-defined]
```

It prints:

```text
sql: 'INSERT INTO "account" ("owner", "token") VALUES (?, ?) RETURNING "id"'
params: ('Ada', ***)
dialect: 'sqlite'
operation: 'save'
subject: None
connection: 'default'
rows: None
```

`operation` says which call sent the statement. `rows` is `None` here because the `INSERT` returns the new row's key instead of a count; it holds the rows affected for an `update()` or a `delete()`. Two more attributes, `duration_ms` and `error`, hold the time the database took and its error text when the statement failed. The [Logging](<https://ferro-orm.x54.sh/guide/connections/#logging>) section lists every attribute and every `operation`.

## Records you did not ask for

Not every record comes from a line you wrote. Clear the list, run a transaction and print the `operation` of each record it left:

```python
records.clear()
async with transaction():
    await Account(owner="Grace", token=SecretStr("sk_live_grace")).save()
    await Account.where(lambda account: account.owner == "Grace").all()
print([record.operation for record in records])  # type: ignore[attr-defined]
```

It prints:

```text
['transaction', 'save', 'fetch', 'transaction']
```

A `transaction()` block logs its own `BEGIN` and `COMMIT` with the operation `"transaction"`, and a session with settings logs a `"settings"` record. On PostgreSQL, the first operation that touches a table also reads the table's columns from the catalog, and that read is a `catalog` record; this program's `.sql()` call touched `Account` before the log was on, so none shows here. To keep the log to the statements your code wrote, skip those operations in your handler or filter.

## Secret columns

A `SecretStr` or `SecretBytes` column never shows its value in a record or in a rendered statement. Ferro knows which bound parameters are secrets and puts `***` in their place, in the line and in `params` alike. The record above already showed it for the `INSERT`. Filtering on the token does the same:

```python
looked_up = await Account.where(
    lambda account: account.token == "sk_live_ada"
).all.sql()
print(looked_up.params)

records.clear()
await Account.where(lambda account: account.token == "sk_live_ada").all()
print(untimed(records[-1]))
```

It prints:

```text
(***,)
SELECT "account"."id", "account"."owner", "account"."token" FROM "account" WHERE "account"."token" = ? params=(***,)
```

The first line is `.sql()`'s `params`, the second the record of the query that ran. The database received the real token; only what you see is masked. Each masked value is `REDACTED`, an object whose `repr` and `str` are `***`.

## Asserting on records in a test

pytest's `caplog` fixture is a handler on the root logger, so it collects the `ferro.sql` records once you set the logger's level. Use it to pin how many statements a call sends, or what it binds. This test runs under pytest-asyncio with the `db` fixture from [Testing](<https://ferro-orm.x54.sh/howto/testing/index.md>), against the `Account` model above:

```python
import logging

from pydantic import SecretStr

from myapp.models import Account


async def test_delete_sends_one_statement(db, caplog):
    await Account(owner="Grace", token=SecretStr("sk_live_grace")).save()

    with caplog.at_level(logging.DEBUG, logger="ferro.sql"):
        await Account.where(lambda account: account.owner == "Grace").delete()

    [record] = [record for record in caplog.records if record.operation == "delete"]
    assert record.params == ("Grace",)
```

The test finds the one `delete` record and checks the value it bound.

## See also

- [Queries](<https://ferro-orm.x54.sh/guide/queries/index.md>): the terminals `.sql()` renders
- [Raw SQL](<https://ferro-orm.x54.sh/guide/raw-sql/index.md>): statements you write yourself, which the log records as `"raw"`
- [Connections and databases](<https://ferro-orm.x54.sh/guide/connections/#logging>): every record attribute and `operation`
- [Auto-migrate](<https://ferro-orm.x54.sh/guide/schema/auto-migrate/index.md>): the `PassReport`, the structured answer to what a schema pass ran
- [Query reference](<https://ferro-orm.x54.sh/api/queries/index.md>): `RenderedSql`, `REDACTED` and every terminal
