Writing queries¶

sqlty works on string literals that start with --sql (or -- sql). It infers a row type for each one and sqlty rewrite wraps the string in SQL[T](...).

The --sql prefix¶

Put --sql on its own line, then the query. In a single-line string, use the \n (or \r\n) escape:

query = "--sql\nSELECT id::INTEGER, name::TEXT FROM users"
# rewritten: query = SQL[tuple[int, str]]("--sql\nSELECT id::INTEGER, name::TEXT FROM users")

--sql is a SQL line comment, so the database ignores it. A space instead of the newline turns the whole query into that comment: sqlty sees an empty query and leaves the string alone (sqlty check reports inline-sql-comment):

query = "--sql SELECT id::INTEGER FROM users"
# rewritten: query = "--sql SELECT id::INTEGER FROM users"

Single quotes work the same way:

query = '--sql\nSELECT id::INTEGER FROM users'
# rewritten: query = SQL[tuple[int]]('--sql\nSELECT id::INTEGER FROM users')

MySQL and MariaDB read -- as a comment only before whitespace, so their servers reject --sql. Use -- sql there: it is a comment in every dialect, and sqlty reads it as the marker when only blanks or a -- comment follow it on its line ("-- sql SELECT ..." is an ordinary comment and left alone). With dialect = "mysql", sqlty check reports --sql in plain string literals as mysql-sql-marker and --fix changes it.

[tool.sqlty]
dialect = "mysql"

query = "-- sql\nSELECT CAST(id AS SIGNED) FROM users"
# rewritten: query = SQL[tuple[int]]("-- sql\nSELECT CAST(id AS SIGNED) FROM users")

Multi-line queries¶

In a triple-quoted string, start the query on the line after --sql:

query = """--sql
SELECT id::INTEGER, email::TEXT
FROM users
WHERE active
"""
# rewritten-file: from sqlty import SQL
# rewritten-file: query = SQL[tuple[int, str]]("""--sql
# rewritten-file: SELECT id::INTEGER, email::TEXT
# rewritten-file: FROM users
# rewritten-file: WHERE active
# rewritten-file: """)

Implicitly concatenated strings are one query, wrapped as a whole:

query = ("--sql\nSELECT id::INTEGER, email::TEXT "
         "FROM users")
# rewritten: query = (SQL[tuple[int, str]]("--sql\nSELECT id::INTEGER, email::TEXT "
# rewritten:          "FROM users"))

The rewriter never changes the string itself, only the wrapper around it.

Casts declare column types¶

Without a schema, sqlty reads column types from PostgreSQL-style ::TYPE casts or from CAST(x AS TYPE):

query = "--sql\nSELECT created_at::TIMESTAMP, CAST(token AS UUID) FROM sessions"
# rewritten-file: import datetime
# rewritten-file: import uuid
# rewritten-file: from sqlty import SQL
# rewritten-file: query = SQL[tuple[datetime.datetime, uuid.UUID]]("--sql\nSELECT created_at::TIMESTAMP, CAST(token AS UUID) FROM sessions")

The rewriter adds import datetime, import uuid, import decimal, import ipaddress and import typing when the row type needs them. SQL and UNTYPED already imported by any top-level from sqlty import ... (a list, parentheses, *) are not imported again; a missing one gets a line of its own.

Literals, COUNT(*) and casts inside expressions are typed too. A column without a cast, schema entry or literal type, a column of a type sqlty does not know, and an expression it cannot type, such as arithmetic or NULL, make the row type unknown: the string is left as it is (SQL[UNTYPED] with untyped = "sentinel") and sqlty check skips the column. Predicates are bool, || is str, and arithmetic on typed operands promotes to the wider type. MySQL converts each operand to a number first, as the server does: unsigned, YEAR, BIT and BOOLEAN values become integers, strings become doubles, so text_col + 0 is float. SQLite converts a string or blob to an integer or a real, typed float, and reads a BOOLEAN as an integer. In PostgreSQL, built-in functions such as left, date_trunc, greatest, unnest and the advisory locks are typed too. In PostgreSQL, CASE, COALESCE, GREATEST, LEAST, VALUES, ARRAY[...] and UNION resolve one type for their inputs as the server does: string literals and NULL take the type of the others, and numbers promote, so COALESCE(int_col, 1.5) is numeric. In the other dialects COALESCE, GREATEST and LEAST take the first argument of known type, skipping NULL and placeholders. Cast the whole expression to type it:

unknown = "--sql\nSELECT COUNT(*), 1, x::INTEGER + 1, name FROM t"
typed = "--sql\nSELECT COUNT(*), 1, (x::INTEGER + 1)::INTEGER, name::TEXT FROM t"
# rewritten: unknown = "--sql\nSELECT COUNT(*), 1, x::INTEGER + 1, name FROM t"
# rewritten: typed = SQL[tuple[int, int, int, str]]("--sql\nSELECT COUNT(*), 1, (x::INTEGER + 1)::INTEGER, name::TEXT FROM t")

An unaliased column is named as PostgreSQL names it: the column (also under a cast), the function (count, max, now), the array of a subscript (tags[1] is tags), the field of (row).field, a keyword such as case or exists, the target of a cast of an unnamed value (1::int is int4, 'a'::text is text, true::bool is bool), else ?column? (a plain true too). A CASE takes the name of its ELSE expression, if it has one. A VALUES list has the columns column1, column2, ... (column_0, column_1, ... in MySQL). MySQL and SQLite name an unaliased expression by its text instead (max(id), 1 + 1), a column by its name, and MySQL a string literal by its value. Alias columns that a row type matches by name.

A column that can be NULL is typed T | None; see Nullability. Annotate the field of a row type the same way.

Which statements are wrapped¶

Only statements that return rows get a row type. DML gets one when it has RETURNING; DML without it, DDL, transaction control and invalid SQL stay plain strings:

deleted = "--sql\nDELETE FROM users WHERE id = 1 RETURNING id::INTEGER"
purge = "--sql\nDELETE FROM users WHERE id = 1"
broken = "--sql\nSELEC id FROM users"
# rewritten: deleted = SQL[tuple[int]]("--sql\nDELETE FROM users WHERE id = 1 RETURNING id::INTEGER")
# rewritten: purge = "--sql\nDELETE FROM users WHERE id = 1"
# rewritten: broken = "--sql\nSELEC id FROM users"

Queries that return rows of an unknown type, such as a SELECT that does not parse, are also left alone by default; see Untyped queries to mark them explicitly.

f-strings¶

An f-string is built at runtime, so it cannot be rewritten. sqlty skips every --sql string on a line that holds one:

# error: [fstring-skipped] "f-string with --sql prefix skipped (f-strings cannot be statically rewritten)"
query = f"--sql\nSELECT {column}::INTEGER FROM users"
# rewritten: query = f"--sql\nSELECT {column}::INTEGER FROM users"

sqlty check and sqlty rewrite report such strings as fstring-skipped. Pass values as query parameters instead of interpolating them, and keep the query a plain literal.

t-strings¶

A t-string (PEP 750, Python 3.14) keeps its values apart from its text, so psycopg 3.3 sends them as parameters. sqlty reads each plain field {value} as a placeholder and wraps the template as SQLTemplate[T]:

user_id = 1
query = t"--sql\nSELECT id::INTEGER, name::TEXT FROM users WHERE id = {user_id}"
# rewritten: user_id = 1
# rewritten: query = SQLTemplate[tuple[int, str]](t"--sql\nSELECT id::INTEGER, name::TEXT FROM users WHERE id = {user_id}")

string.templatelib.Template cannot be subclassed, so SQLTemplate[T] holds the template in .template: pass that to psycopg, or pass the query to the psycopg helpers, which run the template and type the rows as T. sqlty check checks it like SQL[T]: narrow-type and stale-type, and the row type checks of a named row type. Each field's value is a parameter, so it is checked against its placeholder like a driver call's (param-type-mismatch).

Format specs¶

psycopg's :s, :b and :t specs only pick the wire format of a value, so those fields are placeholders too:

user_id = 1
query = t"--sql\nSELECT id::INTEGER FROM users WHERE id = {user_id:b}"
# rewritten: user_id = 1
# rewritten: query = SQLTemplate[tuple[int]](t"--sql\nSELECT id::INTEGER FROM users WHERE id = {user_id:b}")

In a module that imports psycopg or sqlty.drivers.psycopg, sqlty also reads the specs that psycopg merges on the client:

import psycopg

user_id = 1
table = "users"
by_id = t"id = {user_id}"
query = t"--sql\nSELECT id::INTEGER FROM users WHERE {by_id:q}"
insert = t"--sql\nINSERT INTO {table:i} (name) VALUES ({user_id:l})"
# rewritten: import psycopg
# rewritten:
# rewritten: user_id = 1
# rewritten: table = "users"
# rewritten: by_id = t"id = {user_id}"
# rewritten: query = SQLTemplate[tuple[int]](t"--sql\nSELECT id::INTEGER FROM users WHERE {by_id:q}")
# rewritten: insert = t"--sql\nINSERT INTO {table:i} (name) VALUES ({user_id:l})"

Any other field, such as a conversion ({value!r}), an identifier the result columns may depend on, or a snippet sqlty can't find, changes the text of the SQL in a way sqlty can't follow. Such a template is left alone and reported as tstring-skipped, which names the field.

Type mapping¶

Per dialect, with the driver each follows and why some types differ, see Type mapping.

Dialect notes¶

• The types are the ones the dialect's reference driver returns: psycopg 3 for PostgreSQL, PyMySQL for MySQL, sqlite3 for SQLite (see Type mapping). In psycopg NUMERIC is decimal.Decimal, inet an address, or an interface when the value has a prefix (1.2.3.4/24). AVG of integers, SUM without a known argument type and fractional literals (1.5) are decimals too. Drivers for SQLite and Oracle return floats, so decimal.Decimal is used in the other dialects, MySQL included.
• A TEXT[] column gives list[str]. Narrowing the element type by hand (a list[str] field for list[int]) is not reported: elements are not compared. A JSON column gives Any, which any field accepts, and any annotation (dict, list[str], a TypedDict) in SQL[tuple[...]] is not reported.
• An array type needs an element type: x::INT[] is a list[int], but a bare x::ARRAY is rejected and nothing is inferred. A fixed size must be an integer literal (INT[3], not INT[n]); the size itself is ignored.
• MySQL has no INT[]; use CAST(x AS UNSIGNED ARRAY).
• In MySQL, ... UNSIGNED becomes U<type> (INT UNSIGNED is UINT), FLOAT UNSIGNED becomes UDOUBLE, and INT3/MIDDLEINT (MEDIUMINT) and INT8 (BIGINT) are aliases. UNSIGNED applies to DECIMAL/DEC, not to NUMERIC or FIXED.
• SET(...), SIGNED INTEGER and UNSIGNED INTEGER are MySQL-only. In other dialects they are rejected, and a schema CREATE TABLE that uses one is skipped entirely, so SELECT * from that table cannot be expanded.

See Schema inference for choosing a dialect.

Editing queries¶

sqlty rewrite never replaces an existing wrapper, so after you change the SQL inside SQL[T](...), T stays as it was. There are three ways to update it.

Edit in place¶

Change the SQL inside the wrapper and run sqlty check. It reports narrow-type when a SQL[tuple[...]] no longer admits the rows of the query, stale-type when it is only wider than them, and the row type rules when a named row type no longer fits:

from sqlty import SQL

# error: [narrow-type] "SQL[tuple[int, str]] should be SQL[tuple[int, str, str]]"
q = SQL[tuple[int, str]]("--sql\nSELECT id::INTEGER, name::TEXT, email::TEXT FROM users")

sqlty check --fix then writes the inferred T into every stale SQL[tuple[...]] and SQL[UNTYPED], keeping generics you parametrised by hand (list[str]); row types you wrote by hand are left for you to update:

from sqlty import SQL

q = SQL[tuple[int, str]]("--sql\nSELECT id::INTEGER, name::TEXT, email::TEXT FROM users")
# fixed: from sqlty import SQL
# fixed:
# fixed: q = SQL[tuple[int, str, str]]("--sql\nSELECT id::INTEGER, name::TEXT, email::TEXT FROM users")

This works for every file, including ones with row types you wrote by hand.

Strip and rewrite¶

For files whose wrappers are all SQL[tuple[...]], let sqlty regenerate them:

sqlty strip queries.py     # SQL[T]("--sql\n...") -> "--sql\n..."
# edit the SQL
sqlty rewrite queries.py   # "--sql\n..." -> SQL[T]("--sql\n...")

Neither command changes the string contents, only the wrapper and the from sqlty import SQL import. For a file whose --sql strings are all plain, strip(rewrite(x)) == x, unless it already has a hand-written import line that strip removes too: from sqlty import SQL, from sqlty import UNTYPED, or the configured import-style line.

user = SQL[User]("--sql\nSELECT id::INTEGER FROM users")
# stripped: user = "--sql\nSELECT id::INTEGER FROM users"

Import style¶

sqlty rewrite writes from sqlty import SQL and SQL[T](...) by default. import-style changes both:

[tool.sqlty]
import-style = "from sqlty import SQL as Q"

query = "--sql\nSELECT id::INTEGER FROM users"
# rewritten-file: from sqlty import SQL as Q
# rewritten-file: query = Q[tuple[int]]("--sql\nSELECT id::INTEGER FROM users")

Example project: examples/05_import_style/.