# param-type-mismatch

Default severity: **error**

## What it does

Checks the arguments a call passes for its query's placeholders against the type each placeholder takes from its context: the column it is compared with or stored in, a cast, `LIMIT`, an `IN` list, `= ANY(%s)` (a list). It applies to calls of a [marked helper](<https://sqlty.kuncar.dev/guide/helpers/index.md>) whose parameters are spelled out: a `params` argument holding a tuple, list or dict literal, `*args`, or `**params`.

Driver calls are checked too: `execute` and `executemany` (each row) on a psycopg, sqlite3 or aiosqlite connection or cursor, `execute_fetchall` and `execute_insert` on an aiosqlite connection, and `execute`, `fetch`, `fetchrow`, `fetchval`, `executemany` and `fetchmany` (each row) on an asyncpg connection, when the file binds the receiver to `psycopg.connect(...)`, `sqlite3.connect(...)`, `aiosqlite.connect(...)`, `asyncpg.connect(...)`, `<connection>.cursor()` or a driver class annotation, and the `execute` and `scalar` helpers of `sqlty.drivers.psycopg`. The query is a literal, `SQL[T]("...")` of one, or a name assigned once to one. A single value passed as a driver call's parameters, `execute(query, user_id)`, is reported too: the driver raises `TypeError` and wants a sequence, `(user_id,)`. sqlite3 (and so aiosqlite) reads a `str` or `bytes` as a sequence, so those are left alone there.

The fields of a `--sql` t-string are checked as well: psycopg sends each `{value}` as a parameter. A t-string with a field that changes the SQL, such as `{part:q}`, is not.

An argument is judged only where the source states its type: a literal, a variable bound to a literal, annotated or assigned a class, a constant imported from a checked module, a call of a known class (`date(...)`, `UUID(...)`). An argument of unknown type is never reported, and neither is a `str`, which the server coerces to any type. `int`, `float` and `Decimal` are interchangeable. On PostgreSQL a `bool` is not a number and an `int` is not a `boolean`, since the server has no implicit cast between them; MySQL takes either. asyncpg encodes each argument as its parameter's type: it takes a `bool` for a number, but not a `str` for a number, a date or time, `bytes` or a list. A parameter with an explicit cast (`%s::text`) takes anything. `None` is reported only for a parameter stored in a `NOT NULL` column. SQLite is dynamically typed: with `dialect = "sqlite"` only `None` is checked.

## Why is this bad?

The database rejects the value, or compares it in a way that never matches, at run time and only on the path that runs the query.

## Example

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

`schema.sql`:

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

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

from sqlty import SQL


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


execute("SELECT name FROM users WHERE id = %s AND born > %s", (1, date(2000, 1, 1)))
# error: [param-type-mismatch] "parameter 1 expects int, got datetime.date"
execute("SELECT name FROM users WHERE id = %s", (date(2000, 1, 1),))
# error: [param-type-mismatch] "parameter 2 is not nullable, got None"
execute("INSERT INTO users (id, name) VALUES (%s, %s)", (1, None))
# error: [param-type-mismatch] "parameter 'id' expects int, got bytes"
execute("DELETE FROM users WHERE id = %(id)s", {"id": b"1"})
# error: [param-type-mismatch] "parameter 1 expects int, got bytes"
execute("SELECT name FROM users LIMIT %s", (b"1",))
```

## Fix

Pass a value of the type the query expects, or change the query. Turn the rule off with `param-type-mismatch = "ignore"` in `[tool.sqlty.rules]`.
