Schema and typing¶

Pysely checks table names, column names, filter values, and selected result types. Generate a stub with pysely typgen to enable these checks.

The stub is never imported by Python. No checker plugin is needed.

Exact result types¶

“Exact” means the checker knows both the selected key and its value type.

row = await (
    db.select_from("person")
    .select("id")
    .execute_take_first_or_throw()
)

person_id = row["id"]  # int
# row["first_name"]   # type error: not selected

Use one select call per column. With select(["id", "first_name"]), column names are checked, but result values are typed as object.

Column names and scope¶

A column is “in scope” if its table is part of the query.

• Before a join, "id" and "person.id" both work.
• After a join, qualify names shared by multiple schema tables: "person.id".
• Columns from tables you have not joined are rejected.

The static rule is conservative: after a join, a bare name must be unique in the whole schema, not just among the joined tables.

Aliases¶

query = db.select_from("person").select_as("id", "person_id")

The result has a "person_id" key of type int. Use a literal alias; dynamic aliases have checker-specific limits.

The combined string "id as person_id" is supported at runtime, not for exact static typing.

Nullable values¶

Declare a nullable column as str | None, int | None, or another optional type.

Use where("column", "is", None) for SQL NULL, not = None. See filter operators for accepted values.

Working with rows¶

Typed reads return FlatRow, an immutable mapping.

• row["id"]: read a known key with its exact type.
• row.get("missing"): unknown keys are allowed, as with ordinary mappings.
• row.to_dict(): copy all fields into a dict[str, object].

Exact lookup covers the 64 most recent selections. Use to_dict() for older fields. The regression suite checks every type in a mixed 50-field result.

Avoid duplicate result names. Runtime validation rejects duplicate output names; static alias inference alone does not guarantee a valid SQL result.

Current limits¶

For dynamic table names, use Pysely(schema=DatabaseSchema, dialect=dialect). That runtime-only path returns dictionary rows without schema-specific static checks.

Dynamic aliases¶

Prefer select_as("id", "person_id") over an alias computed at runtime.

• Pyright falls back to object values for a plain str alias.
• mypy does not enforce LiteralString here and can infer keys too broadly.
• A union alias such as Literal["a", "b"] exposes both keys statically, although only one exists at runtime.

These are known limitations, not guarantees of full safety for dynamic aliases.

Editor messages and suggestions¶

Enum suggestions may include values from unrelated columns. Invalid values still produce type errors in both checkers.

An invalid call can produce an error on the whole query chain as well as the argument. Pyright's strict mode may add “unknown type” errors on later calls.

The playground uses Pyright standard mode. Choose the same mode in Pylance for comparable diagnostics; Pysely does not filter them.

Advanced: query helpers¶

Generated query class names exist only in the stub. Use postponed annotations and import these names under TYPE_CHECKING.

from __future__ import annotations

from typing import TYPE_CHECKING, TypeVarTuple

if TYPE_CHECKING:
    from dbschema import PersonQuery

FieldsT = TypeVarTuple("FieldsT")


def first_ten(query: PersonQuery[*FieldsT]) -> PersonQuery[*FieldsT]:
    return query.limit(10)

The field pack preserves the caller's selected keys and types. Do not instantiate generated query classes or use them with isinstance.

Performance¶

The stub can still be large. That costs editor and type-checker time, not runtime imports.

Local measurements from September 12, 2026: 20 tables, 333 columns, five edit rounds.

These are local results, not latency guarantees. Exact select still needs column-specific overloads and remains the main scaling bottleneck.

Shared predicate generics reduce duplication. Flat field packs replace nested result types. Already-nullable columns now avoid redundant overloads, and the generator emits stub signatures directly. Exact projections still need per-column work.

Reproduce the benchmark:

uv run python scripts/benchmark_typing.py --tables 20 --rounds 5

See the design decision for implementation details.