Query helpers¶

Code that sends queries through a helper function needs no --sql marker and no SQL[T] wrapper at the call sites. Mark the helper's query parameter with Annotated[LiteralString, SQL] and sqlty check checks every string literal passed to it. The row type comes from the helper's type[T] parameter.

schema.sql:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email VARCHAR(255));

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

Marking the helper¶

The query parameter is annotated with the sqlty.SQL class as Annotated metadata. The parameter annotated type[T] (or TypeForm[T]), where T also appears in the return type, is the row type:

from typing import Annotated, LiteralString, TypedDict
from sqlty import SQL

def fetch_all[T](query: Annotated[LiteralString, SQL], form: type[T], /, **params: object) -> list[T]: ...

class User(TypedDict):
    id: int
    name: str

users = fetch_all("SELECT id, name FROM users", User)

# error: [column-type-mismatch] "User.name is str but query column 'name' infers int"
broken = fetch_all("SELECT id, id AS name FROM users", User)

Playground

Calls pass the query and the row type by position or by keyword, and a query may be several adjacent string literals. A query that is not a literal (a variable, an f-string) is skipped.

Row types¶

The row type is checked like the one in SQL[T] (see Row types), including types defined in the same file. A scalar takes one column and tuple[A, B] one column per item:

from typing import Annotated, LiteralString
from sqlty import SQL

def fetch_all[T](query: Annotated[LiteralString, SQL], form: type[T], /) -> list[T]: ...

names = fetch_all("SELECT name FROM users", str)

# error: [column-type-mismatch] "tuple[int, int] item 1 is int but query column 'name' infers str"
rows = fetch_all("SELECT id, name FROM users", tuple[int, int])

Playground

A --sql string passed with a row type is typed by it, so stale-rewrite doesn't ask to wrap it in SQL[T], and sqlty check --fix leaves it as it is. Without a row type it is still reported:

from typing import Annotated, LiteralString
from sqlty import SQL

def fetch_all[T](query: Annotated[LiteralString, SQL], form: type[T] | None = None, /) -> list[T]: ...

names = fetch_all("--sql\nSELECT name FROM users", str)
# error: [stale-rewrite]
rows = fetch_all("--sql\nSELECT name FROM users")

Playground

A helper without such a parameter is still parsed, and select-star applies. Row types that are not known (a class from another module, dict) are not checked.

Import styles¶

The marker and Annotated are recognized through any import form, whatever import-style says: from sqlty import SQL as Q, import sqlty as sy (sy.SQL), from typing import Annotated as A, import typing as t and typing_extensions.

import typing as t
import sqlty as sy

def fetch_all[T](query: t.Annotated[t.LiteralString, sy.SQL], form: type[T], /) -> list[T]: ...

# error: [column-type-mismatch]
ids = fetch_all("SELECT name FROM users", int)

Playground

Methods¶

Methods are marked the same way. self and cls are not counted when the arguments are bound. A call is checked when the receiver is evident: the class (Db.fetch_one(...), for static and class methods), self inside the class, a variable assigned Db(...) or annotated db: Db in the same scope (or the module), and self.db when the class body annotates it or __init__ assigns Db(...). Other receivers, and inherited methods, are skipped.

from typing import Annotated, LiteralString
from sqlty import SQL

class Db:
    def fetch_all[T](self, query: Annotated[LiteralString, SQL], form: type[T], /) -> list[T]: ...

    @staticmethod
    def fetch_one[T](query: Annotated[LiteralString, SQL], form: type[T], /) -> T | None: ...

db = Db()
# error: [column-type-mismatch]
ids = db.fetch_all("SELECT name FROM users", int)
# error: [column-type-mismatch]
one = Db.fetch_one("SELECT name FROM users", int)

Playground

Helpers you cannot annotate¶

A helper whose signature you cannot edit (a library, generated code) is registered in pyproject.toml instead:

[[tool.sqlty.functions]]
import-path = "pkg.db.fetch_all"   # or "pkg.db.Db.fetch_all" for a method
query-param = "query"
return-type-param = "form"

query-param names the parameter that holds the query and return-type-param the one holding the row type. Both are read from the definition of pkg.db, so calls may pass them by position. sqlty reads pkg/db.py (or pkg/db/__init__.py) from the checked paths, the project directory's src/ or the project directory itself, or sqlty's own sys.path, and never imports it. Without a definition, they are checked only when passed by keyword (fetch_all(query="...", form=int)). For an @overloaded helper, positions come from the implementation, or from the first overload naming both parameters when the implementation does not.

Re-exported names¶

import-path may name a function or class where your code imports it from, a package that re-exports it (from .core import Db): its parameters are read from the module that defines it.

[[tool.sqlty.functions]]
import-path = "store.Db.execute"
query-param = "sql"

store/__init__.py:

from .core import Db

store/core.py:

from typing import Any

class Db:
    async def execute(self, sql: str, parameters: Any = None) -> Any: ...

import store

async def save(db: store.Db) -> None:
    # error: [param-count-mismatch] "query has 2 parameters, the call passes 1"
    await db.execute("--sql\nINSERT INTO users (id, name) VALUES (%s, %s)", (1,))

Query strings of registered helpers¶

stale-rewrite doesn't report a --sql string passed to a registered helper, with or without a row type: the helper's signature is not yours to change, so wrapping the query in SQL[T] would type nothing.

[[tool.sqlty.functions]]
import-path = "pkg.db.pg_fetchone"
query-param = "query"

pkg/db.py:

from typing import Any

async def pg_fetchone(db: Any, query: str, *args: Any) -> Any: ...

from typing import Any

from pkg.db import pg_fetchone

async def first(db: Any) -> Any:
    # error:
    return await pg_fetchone(db, "--sql\nSELECT 1")

Queries stored in a name¶

A helper call that passes a name bound to a SQL[R](...) or SQL[R, P](...) call is checked as if it passed the query. The name can be a module-level constant or imported from a checked module.

from typing import Annotated, Any, LiteralString, TypedDict

from sqlty import SQL


def fetch_one[T](query: Annotated[LiteralString, SQL], form: type[T], /, **params: Any) -> T | None: ...


class ById(TypedDict):
    id: int


BY_ID = SQL[tuple[str], ById]("--sql\nSELECT name FROM users WHERE id = %(id)s")

# error: [param-type-mismatch] "parameter 'id' expects int, got str"
fetch_one(BY_ID, tuple[str], id="1")

Playground

With a P, each argument is compared with its type in P, as a type checker would: "1" is rejected for an int, although the server would convert it. This works without changing the helper's signature, so it also covers a helper registered in [[tool.sqlty.functions]].

Helpers in other modules¶

sqlty check indexes the helpers of every file it is given before checking calls, so a helper imported from another module of the checked paths is resolved by its module name: from pkg.db import fetch_all [as fa], import pkg.db as db with db.fetch_all(...), and relative imports. Only files that mention Annotated and SQL are parsed for the index.

Pass the directory that holds the helper together with the code that calls it (sqlty check ., not just the calling file). A helper re-exported by a package __init__ (from .db import fetch_all) is found through the package too: from pkg import fetch_all reaches it.

From Python, sqlty.core.check_source(source, helpers=index, module="pkg.app") takes a sqlty.core.HelperIndex filled with index.add("pkg.db", db_source).

sqlty rewrite and sqlty strip never touch these calls.

Completion¶

sqlty server completes table and column names in the strings passed to a helper, as it does in --sql strings: a string completes exactly when sqlty check checks it.

from typing import Annotated, LiteralString
from sqlty import SQL

def fetch_all[T](query: Annotated[LiteralString, SQL], form: type[T]) -> list[T]: ...

fetch_all("SELECT u.<CURSOR> FROM users u", tuple[int, str])
# completions: email column u
# completions: id column u
# completions: name column u

Playground

A call the index does not resolve to a helper, an f-string or a variable completes nothing. See VS Code for the editor side.

psycopg `sql.SQL` queries¶

With psycopg installed (sqlty[psycopg]), a psycopg.sql composition passed to the query parameter is checked too, so helpers annotated Annotated[LiteralString | sql.Composable, SQL] cover queries with dynamic identifiers. sql.SQL(...), .format(...), .join(...), sql.Composed([...]), sql.Identifier, sql.Placeholder and sql.Literal are recognized through any import form (from psycopg import sql, from psycopg.sql import SQL as PgSQL, import psycopg.sql).

When every part is a literal, sqlty rebuilds the object from the source with the real psycopg.sql classes (your code is never run) and renders it with as_string(None), which needs no connection since psycopg 3.2. A name bound once in the same function or module is followed, so shared fragments and module-level constants work. sql.Literal(x) renders as NULL, like a placeholder, whatever x is.

from typing import Annotated, LiteralString
from psycopg import sql
from sqlty import SQL

def fetch_all[T](query: Annotated[LiteralString | sql.Composable, SQL], form: type[T], /) -> list[T]: ...

USERS = sql.Identifier("users")
query = sql.SQL("SELECT {cols} FROM {table} WHERE id = {id}").format(
    cols=sql.SQL(", ").join([sql.Identifier("id"), sql.Identifier("name")]),
    table=USERS,
    id=sql.Placeholder("id"),
)

# error: [column-type-mismatch]
rows = fetch_all(query, tuple[int, int])

Playground

A name imported from another checked module (from .columns import USER_COLUMNS, or columns.USER_COLUMNS after import pkg.columns as columns) resolves the same way when that module binds it once at module level to a literal string or a composition built only from literals. Each module is parsed once, however many files import from it.

A part that is not a literal (sql.Identifier(column) with a variable) makes the query unchecked-query. Without psycopg installed these calls are skipped.

A --sql marker inside sql.SQL("--sql\n...") is fine; rewrite never wraps a string inside sql.SQL(...) and stale-rewrite does not report it.

Query helpers¶

Marking the helper¶

Row types¶

Import styles¶

Methods¶

Helpers you cannot annotate¶

Re-exported names¶

Query strings of registered helpers¶

Queries stored in a name¶

Helpers in other modules¶

Completion¶

psycopg sql.SQL queries¶

psycopg `sql.SQL` queries¶