Metadata-Version: 2.3
Name: tysql
Version: 0.3.0
Summary: Experimental, type-level PostgreSQL query builder prototype using PEP 827 typemaps.
Keywords: mypy,pep-827,postgresql,query-builder,typemap,typing
Author: Ilias Dzhabbarov
Author-email: Ilias Dzhabbarov <iliyas.dzabbarov@gmail.com>
License: MIT
Classifier: Development Status :: 2 - Pre-Alpha
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.14
Classifier: Typing :: Typed
Requires-Dist: python-typemap>=0.1.0
Requires-Dist: psycopg>=3.0.0 ; extra == 'psycopg'
Requires-Python: >=3.14
Project-URL: Homepage, https://github.com/iliyasone/tysql
Project-URL: Issues, https://github.com/iliyasone/tysql/issues
Provides-Extra: psycopg
Description-Content-Type: text/markdown

# 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](https://peps.python.org/pep-0827/);
- **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](#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:

```python
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`](src/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`](https://github.com/iliyasone/python-typemap),
and statically by a [`mypy` fork](https://github.com/iliyasone/mypy-typemap).

## Install

Install the core library from PyPI with [uv](https://docs.astral.sh/uv/) or pip:

```bash
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:

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

### Enable type-checking

The static rejection of ill-typed statements runs on a [mypy fork](https://github.com/iliyasone/mypy-typemap) that evaluates the PEP 827 combinators

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

```bash
uv run mypy .
```

Runnable demo snippets ship inside the package under
[`tysql/examples/`](src/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`).

| 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 (`SerialPrimaryKey[int]` → `int`) |       ✅       |      ✅       |
| `SELECT` projection — `Cols[...]` picks columns                                |       ✅       |      ✅       |
| Column alias — `As[col, Literal["name"]]`                                      |       ✅       |      ✅       |
| `WHERE` — `Param`s 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 `TypedDict`s, 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:

| 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 — the SQL surface tysql does not cover yet:

| Not implemented                                                         | Notes                                                         |
| ----------------------------------------------------------------------- | ------------------------------------------------------------- |
| `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

```python
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:

```sql
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`](src/tysql/examples/users.py),
alongside the numbered demo snippets the [playground](https://github.com/iliyasone/tysql-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`:

```python
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`:

```python
from tysql import arun

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

## Development

```bash
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.
