tysql

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;
• renders the statement to PostgreSQL text you could hand to a driver; and
• executes it on a psycopg connection you own — with the tysql[psycopg] extra — returning rows in the inferred shape.

It is a research prototype: the API may change and SQL coverage is narrow (see what works)

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:

import psycopg
from typing import Literal
from tysql import Col, Cols, SerialPrimaryKey, Select, Table, run


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


conn = psycopg.connect("postgresql://localhost/mydb")

# SELECT id, email FROM "user" — run on the connection you own
rows = run(
    Select[User, Cols[Col[User, Literal["id"]], Col[User, Literal["email"]]]],
    data=None,
    conn=conn,
)
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 statement's parameters and the return type to its rows — both computed from the statement type by the combinators in tysql/shapes.py. Pass a psycopg conn (the tysql[psycopg] extra) and it renders and executes the statement, returning those rows.

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.

Install

Install the core library from PyPI with uv or pip:

uv add tysql 
# or pip install tysql

That includes the runtime side: build statement types and render them to SQL. To also execute statements against PostgreSQL, add the psycopg driver extra:

uv add "tysql[psycopg]"
# or pip install tysql[psycopg]

Enable type-checking

The static rejection of ill-typed statements runs on a mypy fork that evaluates the PEP 827 combinators

uv add "mypy @ git+https://github.com/iliyasone/mypy-typemap.git"

uv run mypy .

Runnable demo snippets ship inside the package under tysql/examples/.

What works

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

What it rejects

The parameter and row types are exact TypedDicts, so a type checker rejects, 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:

Not implemented

Out of scope for this prototype — the SQL surface tysql does not cover yet:

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/tysql/examples/users.py, alongside the numbered demo snippets the playground serves.

Execute

Everything above is type-level; with the tysql[psycopg] extra, run also executes a statement on a connection you own and returns the rows in the inferred shape — SELECT as a list of dict rows, INSERT the RETURNING primary key, CREATE TABLE None:

import psycopg
from tysql import CreateTable, Insert, Select, run

with psycopg.connect("postgresql://localhost/mydb") as conn:
    run(CreateTable[User], data=None, conn=conn)
    new_id = run(Insert[User], data={"age": 30, "email": "a@b.c"}, conn=conn)
    rows = run(Select[User], data=None, conn=conn)   # [{"id": int, "age": int, "email": str}]

run never commits — you own the connection, its transaction and pooling. For asyncio, arun is the same contract on a psycopg AsyncConnection:

from tysql import arun

rows = await arun(Select[User], data=None, conn=aconn)

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 tests (the postgres marker) run against a real server via Docker/testcontainers; their dependencies are in the postgres dependency group.