Engine¶

sqlty's core operations (type inference, SELECT * expansion, rewrite, strip, check and fix) run in one engine written in Rust: the sqlty._core extension, built with PyO3 and maturin, parsing SQL with sqlparser-rs. The CLI, the sqlty.core functions, the magic codec all use it.

Python handles what needs the Python runtime: resolving row types such as TypedDicts and dataclasses for check, configuration, the CLI and the psycopg helpers.

Wheels include the compiled extension; building from source needs a Rust toolchain. There is no pure-Python fallback.

Dialects¶

dialect picks the sqlparser-rs dialect that reads queries and schemas: postgres (default), mysql, sqlite, and any other name sqlparser-rs knows (bigquery, snowflake, duckdb, clickhouse, mssql or tsql, redshift, oracle, ..., and generic for generic SQL). An unknown name is an error that lists the valid ones: a typo such as postgress would otherwise read queries as generic SQL. Type names are spelled the PostgreSQL way, so INT64 expands to BIGINT and NUMBER to DECIMAL. PostgreSQL, MySQL and SQLite are specified by the test suite; the others work on a best-effort basis. generic types functions and operators as PostgreSQL does, and the ones PostgreSQL lacks, such as IF and IIF, as MySQL or SQLite does. The --dialect CLI flag accepts the first three; set others with dialect in pyproject.toml.

PostgreSQL type resolution¶

PostgreSQL gives CASE, COALESCE, GREATEST/LEAST, VALUES, ARRAY[...] and UNION/INTERSECT/EXCEPT one type for all their inputs, and picks an operator by its operand types, by the rules of UNION, CASE and related constructs and operators. sqlty follows them, and tests/fixtures/pg_common_types.toml checks it: each query there has the type PostgreSQL 17 describes for its column, and tests/test_pg_common_types.py compares sqlty's type with it, with no server needed.

To add a query, put it in the fixture with any type, then fill in the types from a disposable server:

SQLTY_PG_DSN=postgresql://postgres:postgres@localhost:5432/postgres \
    uv run --group psycopg python scripts/refresh_pg_types.py

With SQLTY_PG_DSN set, the test also checks the fixture against the server.

Known gaps¶

A query whose type differs from PostgreSQL's is listed in KNOWN_GAPS in the test, and here. None is listed today.

Inference rules¶

Operators, aggregates and JSON accessors are typed by inference rules: TOML files in rust/crates/sqlty-sql/rules/, compiled into the engine. Each rule names the documentation it follows and why. sqlty check --explain prints the rule behind each column under a type diagnostic:

q.py:2: [narrow-type] SQL[tuple[int, str]] should be SQL[tuple[int | None, str | None]]
  total: type and nullability by inference rule postgres.aggregate.sum-integers
    Summing small integers widens to bigint so the sum fits.
    Reference: PostgreSQL 17 documentation, 9.21, Table 9.60 General-Purpose Aggregate Functions (sum ( smallint ) → bigint, sum ( integer ) → bigint)

A column gets the rules of its outermost operator or function call. A column reference, a cast, a literal or a set operation has none. From Python, sqlty.core.explain_select returns the same rules:

>>> from sqlty.core import explain_select
>>> [column] = explain_select("SELECT SUM(qty) AS total FROM orders", schema_sql="CREATE TABLE orders (qty SMALLINT NOT NULL)")
>>> column.type_rule.id, column.nullability_rule.id
('postgres.aggregate.sum-integers', 'postgres.aggregate.sum-integers')

The analyzer records the rules only when asked to, so check without --explain pays nothing for them. To add or change a rule, see rules/README.md.

Functions¶

A call to a built-in function is typed by the function's rules in the query's dialect; an unknown function leaves its column untyped (see unknown-function). PostgreSQL follows its "Functions and Operators" chapter. MySQL and SQLite type these:

The functions common to every dialect (COUNT, SUM, AVG, MIN, MAX, COALESCE, LENGTH, UPPER, LOWER, SUBSTR, REPLACE, ...) are typed everywhere. Every function's rule is in rust/crates/sqlty-sql/rules/, one file per dialect and topic; tests/mdtest/mysql/functions.md and tests/mdtest/sqlite/functions.md show each group.

Placeholders¶

A placeholder is a value of unknown type, so it does not stop inference. What each dialect reads:

The % forms are replaced before parsing, everywhere in the query, as psycopg does: write 'a%%' for a literal % in a string.

Changes from the sqlglot backend¶

Earlier versions also shipped a pure-Python backend built on sqlglot. Removing it changed:

• Unaliased calls to functions sqlty can't type are named after the function (NULLIF(a, b) is nullif, not an empty name) in column-name-mismatch and typeddict-key-mismatch.
• The magic codec adds imports after the module header, as sqlty rewrite does, instead of placing them with isort, and turns # coding: sqlty into # coding: utf-8 in the decoded text.
• Dialects other than PostgreSQL, MySQL and SQLite follow sqlparser-rs rather than sqlglot.
• An unsupported import-style is an error instead of falling back to from sqlty import SQL.
• SQLTY_LIB is gone.

Benchmarks¶

Median time per call from benchmarks/bench_core.py (500 iterations after warm-up, release build), on a 4-core Linux x86_64 machine:

Whole-file operations carry a fixed cost per call for compiling their patterns. Numbers vary between machines and runs. A debug build from plain maturin develop is much slower, so build in release mode first:

uv run maturin develop --release
uv run python benchmarks/bench_core.py