# Query parameters

A placeholder (`%s`, `%(name)s`, `$1`, `?`, `:name`) has no type of its own. sqlty gives it one from where it sits in the query, and `sqlty check` compares the arguments a call passes against it.

`schema.sql`:

```sql
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    born DATE
);
```

```toml
[tool.sqlty]
schema = "schema.sql"
```

## Placeholder styles

| Style | Spelling | Drivers |
| --- | --- | --- |
| format | `%s` | psycopg, MySQL drivers |
| pyformat | `%(name)s` | psycopg, MySQL drivers |
| numeric | `$1`, `$2` | asyncpg, PostgreSQL |
| qmark | `?`, `?1` | sqlite3, MySQL |
| named | `:name` | sqlite3, SQLAlchemy's `text()` (`dialect = "sqlite"` or `"postgres"`) |

`%%` is a literal `%`. A placeholder inside a string literal or a comment is text. `:name` is read as SQLAlchemy reads it: `::int` casts, array slices such as `a[1:2]`, and `:a::int` are not placeholders. In PostgreSQL `?` is also the JSONB operator, so it is a placeholder only when the query does not parse otherwise.

## Where a parameter gets its type

| Context | Example | Parameter |
| --- | --- | --- |
| Comparison | `age > %s` | the column's type (`int`) |
| `BETWEEN`, `IN (...)`, `IN (SELECT ...)` | `id IN (%s, %s)` | the other side's type |
| `LIKE` | `name LIKE %s` | `str` |
| `INSERT` and `UPDATE` | `INSERT INTO users (name) VALUES (%s)` | the target column's type |
| Cast | `%s::date`, `CAST(%s AS int)` | the cast type |
| `LIMIT`, `OFFSET`, `FETCH` | `LIMIT %s` | `int` |
| `= ANY(...)` | `id = ANY(%s)` | `list[int]` |
| Known function | `lower(%s)`, `coalesce(age, %s)`, `lpad(name, %s, %s)`, `pg_notify(%s, %s)` | the signature's types |
| Schema function | `finish_job(%s, %s)` with `CREATE FUNCTION finish_job(job_id bigint, ...)` | the declared argument types, when the overloads a call fits agree |

A `NOT NULL` target column also makes the parameter non-null, and a nullable one nullable. Where the context says nothing (a bare `SELECT %s`, an unknown table) the parameter stays untyped.

`sqlty.core.analyze_params` returns them:

```pycon
>>> from sqlty.core import analyze_params
>>> schema = "CREATE TABLE users (id INTEGER NOT NULL, name TEXT NOT NULL, age INTEGER)"
>>> [(p.number, p.python_type, p.nullable) for p in analyze_params("INSERT INTO users VALUES (%s, %s, %s)", schema_sql=schema)]
[(1, 'int', False), (2, 'str', False), (3, 'int', True)]
```

## Checking calls

A call of a [marked helper](<https://sqlty.kuncar.dev/guide/helpers/index.md>) is checked when it spells out its arguments. The helper takes them as a parameter named `params`, `parameters` or `args` holding a tuple, list or dict literal, as `*args`, or as `**params`:

```python
from typing import Annotated, LiteralString
from sqlty import SQL

def execute(query: Annotated[LiteralString, SQL], params: tuple[object, ...] = (), /) -> None: ...

execute("SELECT id FROM users WHERE id = %s AND age > %s", (1, 18))

# error: [param-type-mismatch] "parameter 2 expects int, got bytes"
execute("SELECT id FROM users WHERE id = %s AND age > %s", (1, b"1"))

# error: [param-count-mismatch] "query has 2 parameters, the call passes 1"
execute("SELECT id FROM users WHERE id = %s AND age > %s", (1,))
```

[`param-type-mismatch`](<https://sqlty.kuncar.dev/reference/rules/#param-type-mismatch>) judges an argument only where its type is stated: a literal, a variable annotated or assigned a class, a call of a known class. An argument of unknown type is never reported. Text (`str`) is accepted everywhere because the server coerces it, `int`, `float` and `Decimal` are interchangeable, and an explicit cast (`%s::text`) takes anything. `None` is reported only for a parameter stored in a `NOT NULL` column. SQLite is dynamically typed, so with `dialect = "sqlite"` only `None` and the parameter count are checked.

```python
from datetime import date
from typing import Annotated, LiteralString
from sqlty import SQL

def execute(query: Annotated[LiteralString, SQL], params: tuple[object, ...] = (), /) -> None: ...

def register(name: str, born: date, age: int | None, other) -> None:
    execute("INSERT INTO users (id, name, born) VALUES (%s, %s, %s)", (1, name, born))
    execute("INSERT INTO users (id, name) VALUES (%s, %s)", (1, other))
    # error: [param-type-mismatch] "parameter 3 expects datetime.date, got int"
    execute("INSERT INTO users (id, name, born) VALUES (%s, %s, %s)", (1, name, 5))
```

Named placeholders match a dict literal or keyword arguments; [`param-count-mismatch`](<https://sqlty.kuncar.dev/reference/rules/#param-count-mismatch>) reports names the call does not pass; extra keys are ignored, as the drivers do:

```python
from typing import Annotated, LiteralString
from sqlty import SQL

def execute(query: Annotated[LiteralString, SQL], params: dict[str, object], /) -> None: ...
def run(query: Annotated[LiteralString, SQL], /, **params: object) -> None: ...

execute("DELETE FROM users WHERE id = %(id)s", {"id": 1})
# error: [param-count-mismatch] "call is missing parameter 'id'"
execute("DELETE FROM users WHERE id = %(id)s", {"ident": 1})
# error: [param-type-mismatch] "parameter 'age' expects int, got bytes"
run("DELETE FROM users WHERE age = %(age)s", age=b"1")
```

## Driver calls

`cursor.execute(query, params)`, `executemany` (each row), and `connection.execute(...)` are checked on psycopg and sqlite3 receivers, and `execute`, `fetch`, `fetchrow`, `fetchval` on asyncpg ones. The receiver must be evident in the file: bound to `psycopg.connect(...)`, `sqlite3.connect(...)`, `asyncpg.connect(...)`, `<connection>.cursor()` (also as `with ... as name`), or annotated with a driver class. The query is a literal, `SQL[T]("...")` of one, or a name assigned once to one. A call with no parameters is a `param-count-mismatch` when the query has placeholders, and a single value passed as the parameters, `execute(query, user_id)`, is a `param-type-mismatch`: the driver wants a sequence, `(user_id,)`.

```python
import sqlite3

conn = sqlite3.connect("app.db")
# error: [param-count-mismatch] "query has 1 parameter, the call passes 0"
conn.execute("SELECT id FROM users WHERE id = ?")
```

asyncpg-style `*args` numbers its placeholders `$1`, `$2`, ...:

```python
from typing import Annotated, LiteralString
from sqlty import SQL

async def fetch(query: Annotated[LiteralString, SQL], *args: object) -> list[object]: ...

async def main() -> None:
    await fetch("SELECT id FROM users WHERE id = $1 AND name = $2", 1, "a")
    # error: [param-count-mismatch] "query has 2 parameters, the call passes 1"
    await fetch("SELECT id FROM users WHERE id = $1 AND name = $2", 1)
```

## Typed parameters

`SQL[R, P]` types the parameters of a query as `P`, next to its rows `R`. Your type checker then checks the values you pass, without running `sqlty check`. Turn on [`param-types`](<https://sqlty.kuncar.dev/reference/configuration/#param-types>) and `sqlty rewrite` writes `P`:

```toml
[tool.sqlty]
schema = "schema.sql"
param-types = true
```

```python
by_id = "--sql\nSELECT name FROM users WHERE id = %(id)s"
by_name = "--sql\nSELECT id, born FROM users WHERE name = %s AND age > %s"
count = "--sql\nSELECT count(*) FROM users"
# rewritten-file: import datetime
# rewritten-file: import typing
# rewritten-file: from sqlty import SQL
# rewritten-file:
# rewritten-file:
# rewritten-file: # sqlty: generated
# rewritten-file: class ByIdParams(typing.TypedDict):
# rewritten-file:     id: int | None
# rewritten-file:
# rewritten-file:
# rewritten-file: by_id = SQL[tuple[str], ByIdParams]("--sql\nSELECT name FROM users WHERE id = %(id)s")
# rewritten-file: by_name = SQL[tuple[int, datetime.date | None], tuple[str | None, int | None]]("--sql\nSELECT id, born FROM users WHERE name = %s AND age > %s")
# rewritten-file: count = SQL[tuple[int], tuple[()]]("--sql\nSELECT count(*) FROM users")
```

- Positional parameters are a `tuple`, one item per placeholder.
- Named parameters are a `TypedDict`, one key per name, generated after the imports below a `# sqlty: generated` line. `sqlty strip` removes it.
- A query without parameters takes `tuple[()]`.
- A parameter is `T | None` unless a `NOT NULL` column stores it, and `typing.Any` when its type is unknown or cast (`%s::int` takes anything).

`rewrite` writes `P` for every `SQL[R]` call that has none, and keeps a `P` you wrote. `sqlty check` compares a `P` you wrote with the query ([`params-type-mismatch`](<https://sqlty.kuncar.dev/reference/rules/#params-type-mismatch>)), and `R` as for any query. Its fixes rewrite `R` and keep `P`.

```python
from sqlty import SQL

# error: [stale-type] "SQL[tuple[str | None]] should be SQL[tuple[str]]"
by_id = SQL[tuple[str | None], tuple[int]]("--sql\nSELECT name FROM users WHERE id = %s")
# error: [params-type-mismatch] "tuple[int, int] has 2 items but the query has 1 parameter"
by_name = SQL[tuple[int], tuple[int, int]]("--sql\nSELECT id FROM users WHERE name = %s")
```

`P` defaults to `Any`, so `SQL[R]` takes any parameters and every `SQL[R]` you already have stays valid. A query is typed like a function from `P` to `R`: a query that takes `str | None` fits where one that takes `str` is expected, not the other way around.

> [!WARNING]
>
> **Type checkers become stricter than the driver**
>
> `P` says what the query needs, not everything psycopg accepts. A type checker rejects a list for a `tuple`, text for another type (the server would convert it), and a `dict[str, Any]` or extra keys for a `TypedDict`. That is why `param-types` is off by default.

### `bind`

`query.bind(params)` checks `params` against `P` and returns a `Bound[R]`, a `(query, params)` tuple. `cursor.execute(*bound)` runs it, and the [psycopg helpers](<https://sqlty.kuncar.dev/guide/psycopg/index.md>) take it in place of the query. You only need it where your type checker does not check `P` otherwise:

```python
from typing import TypedDict

from sqlty import SQL


class ById(TypedDict):
    id: int


by_id = SQL[tuple[str], ById]("--sql\nSELECT name FROM users WHERE id = %(id)s")
by_name = SQL[tuple[int], tuple[str]]("--sql\nSELECT id FROM users WHERE name = %s")
count = SQL[tuple[int], tuple[()]]("--sql\nSELECT count(*) FROM users")

by_id.bind({"id": 1})
by_name.bind(("alice",))
count.bind(())
# error:
```

### What each type checker checks

| Call | mypy | pyright | ty |
| --- | --- | --- | --- |
| `query.bind(params)` | yes | yes | yes |
| `execute(conn, query, params)` | no | yes | yes |
| `scalar(conn, query, params)` | no | no | no |

> [!NOTE]
>
> **Use `bind` to check `params` on mypy**
>
> mypy cannot infer `P` from a dict literal for a `TypedDict` ([python/mypy\#19201](<https://github.com/python/mypy/issues/19201>)), so `execute` keeps a signature that takes any parameters on mypy. `scalar` keeps it on every checker. `execute(conn, query.bind(params))` and `scalar(conn, query.bind(params))` check `params` everywhere.