tysql 0.1.0

Experimental, type-level PostgreSQL query builder prototype using PEP 827 typemaps.

tysql

Install with uv — from PyPI, or straight from GitHub for the latest commit:

uv add tysql                                                        # core library, from PyPI
uv add "tysql @ git+https://github.com/iliyasone/tysql.git"         # or the latest from GitHub
uv add "mypy @ git+https://github.com/iliyasone/mypy-typemap.git"   # + the mypy fork for `tysql check`

tysql is an experimental, type-level query builder for PostgreSQL. A SQL statement is written as a type — Select[User] — and tysql

• statically infers the result type of every statement and rejects ill-typed statements with a type error, using the type operators from PEP 827; and
• renders the statement to PostgreSQL text you could hand to a driver.

It is a research prototype: the API is unstable and SQL coverage is deliberately narrow (see what works). There is no database bridge — tysql produces types and SQL strings, it does not execute anything.

The idea

PEP 827 lets a type checker evaluate small type-level programs. tysql uses that to make the shape of a query a static fact:

from typing import Literal
from tysql import Col, Cols, PrimaryKey, Select, Table, run


class User(Table):
    id: PrimaryKey[int]
    age: int
    email: str


# SELECT id, email FROM "user"
rows = run(Select[User, Cols[Col[User, Literal["id"]], Col[User, Literal["email"]]]], data=None)
rows[0]["id"]     # int   — inferred
rows[0]["email"]  # str   — inferred
rows[0]["age"]    # type error: "age" is not in the projected row

run's signature is the product: data is typed to the parameters the statement takes, and the return type is the list of rows it yields. Both are computed from the statement type by the combinators in tysql/shapes.py.

Until PEP 827 lands in a released type checker, the same evaluation is done two ways: at runtime by typemap, and statically by a mypy fork. tysql ships a CLI validator over that fork so the errors show up today, exactly where a stock type checker would raise them once PEP 827 is standard — see CLI.

What works

Type inference below holds on both tracks (the mypy fork and runtime eval_typing); SQL rendering is runtime (tysql.render.render).

Capability	Type inference	SQL rendering
CREATE TABLE (incl. SERIAL PRIMARY KEY, REFERENCES)	—	✅
INSERT — params inferred, RETURNING the primary key	✅	✅
SELECT * — full row, primary key unwrapped (PrimaryKey[int] → int)	✅	✅
SELECT projection — Cols[...] picks columns	✅	✅
Column alias — As[col, Literal["name"]]	✅	✅
WHERE — Params collected into the inferred parameter mapping	✅	✅
INNER JOIN with an explicit On predicate	✅	✅
Aggregate Count (result typed int)	✅	✅
GROUP BY / ORDER BY	✅	✅

What it rejects

The parameter and row types are exact TypedDicts, so a type checker (and the CLI) reject, at check time: a column that doesn't exist on its table (Col: no such column); a projected column whose table isn't in the FROM/JOIN (Col: table is not in the FROM clause — the "map the column to the table" check); reading a result column that wasn't selected; and an INSERT/WHERE data payload with a missing, extra, mis-named or wrong-typed key (the primary key may not be supplied on insert).

Column checks are per reference site. A ✅ is enforced on both tracks; a ❌ is currently accepted — a known false negative, not a guarantee:

Column reference site	exists on its table	belongs to the FROM	operand types compatible
SELECT projection	✅	✅	n/a
join ON predicate	✅	❌	❌
WHERE	❌	❌	❌
GROUP BY / ORDER BY	❌	❌	n/a

Not implemented

Out of scope for this prototype — tysql produces types and SQL text only, and no database is contacted:

Not implemented	Notes
Query execution / database bridge	run raises NotImplementedError; use render for SQL text
LEFT / RIGHT / FULL / CROSS JOIN	INNER JOIN only
OR / NOT / nested boolean in WHERE	flat conjunction (AND) of Eq only
Comparison operators other than = (<, >, LIKE, IN, BETWEEN)	Eq only
Aggregates other than Count (SUM, AVG, MIN, MAX)	—
HAVING, LIMIT, OFFSET, DISTINCT	—
Subqueries, CTEs (WITH), set operations (UNION)	—
UPDATE / DELETE statements	CREATE TABLE, INSERT, SELECT only
WHERE param type inferred from its column	declared explicitly via Param[name, T], not cross-checked
GROUP BY functional-dependency check	not enforced (unlike PostgreSQL)

Examples

from typing import Literal
from tysql import (
    As, Col, Cols, Count, Eq, GroupBy, InnerJoin, On, OrderBy, Param, Select, Where, run,
)

# WHERE, with parameters inferred from the clause
Select[User, Cols[Col[User, Literal["id"]]],
       Where[Eq[Col[User, Literal["age"]], Param[Literal["min_age"], int]]]]
# rows: {"id": int};  data: {"min_age": int}

# explicit INNER JOIN — columns from both tables in one row
Select[InnerJoin[User, Post, On[Eq[Col[User, Literal["id"]], Col[Post, Literal["author"]]]]],
       Cols[Col[User, Literal["email"]], Col[Post, Literal["text"]]]]
# rows: {"email": str, "text": str}

# aggregate + GROUP BY + ORDER BY
Select[InnerJoin[User, Post, On[Eq[Col[User, Literal["id"]], Col[Post, Literal["author"]]]]],
       Cols[Col[User, Literal["id"]], As[Count[Col[Post, Literal["id"]]], Literal["n_posts"]]],
       GroupBy[Col[User, Literal["id"]]],
       OrderBy[Col[User, Literal["id"]], Literal["asc"]]]
# rows: {"id": int, "n_posts": int}

Rendering the last statement with tysql.render.render yields:

SELECT "user"."id", count("post"."id") AS "n_posts"
FROM "user" INNER JOIN "post" ON "user"."id" = "post"."author"
GROUP BY "user"."id" ORDER BY "user"."id" ASC;

A larger example schema lives in src/examples/users.py.

CLI

Installing tysql provides a tysql command that wraps the pinned mypy fork, so PEP 827 combinator types resolve to real types instead of Any. The fork cannot be a PyPI dependency (direct URL), so install it next to tysql from GitHub: uv add "mypy @ git+https://github.com/iliyasone/mypy-typemap.git".

tysql check [PATH ...]   # type-check paths (default: .) — rejects ill-typed statements
tysql mypy  [ARG ...]    # forward arguments straight to the fork's mypy

tysql check some_file.py reports, for instance, Col: no such column when a statement references a column that does not exist — the same error a type checker will emit once PEP 827 is standard.

Development

uv sync --all-groups

uv run ruff check .   # lint
uv run mypy .         # static type-check — the primary type-level test layer
uv run pytest         # runtime tests

mypy is part of the test contract. Two conventions make it load-bearing:

• mypy_test_* functions (bodies under if TYPE_CHECKING:) are not collected by pytest but are checked by the fork — they assert inferred types with assert_type.
• --warn-unused-ignores is on, so every # type: ignore[code] is a negative assertion: if the fork stops emitting that error, the run fails.

PostgreSQL integration dependencies (for a future execution bridge) are staged in the postgres dependency group.