JSON shapes¶

A JSON column is Any to sqlty: the schema says JSONB and nothing more. A query that builds the JSON says much more, and sqlty reads it. This is how a nested API response comes out of one query and still gets checked.

The examples on this page use this schema:

[tool.sqlty]
schema = "schema.sql"

schema.sql:

CREATE TABLE orders (id INTEGER PRIMARY KEY, placed TIMESTAMPTZ NOT NULL);
CREATE TABLE lines (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders (id),
    sku TEXT NOT NULL,
    qty INTEGER NOT NULL,
    note TEXT
);

An endpoint that returns nested JSON¶

An orders endpoint returns each order with its lines. One query builds the whole document, with no second query per order.

from typing import TypedDict

from sqlty import SQL


class Line(TypedDict):
    sku: str
    qty: int
    note: str | None


class Order(TypedDict):
    id: int
    lines: list[Line]


order = SQL[Order]("""--sql
SELECT
    o.id,
    coalesce(
        jsonb_agg(jsonb_build_object('sku', l.sku, 'qty', l.qty, 'note', l.note) ORDER BY l.id)
            FILTER (WHERE l.id IS NOT NULL),
        '[]'
    ) AS lines
FROM orders o LEFT JOIN lines l ON l.order_id = o.id
GROUP BY o.id""")
# error:

The lines column is Any for the type checker, but sqlty knows more:

SELECT coalesce(
    jsonb_agg(jsonb_build_object('sku', l.sku, 'qty', l.qty, 'note', l.note))
        FILTER (WHERE l.id IS NOT NULL),
    '[]'
) AS lines
FROM orders o LEFT JOIN lines l ON l.order_id = o.id
-- json: lines: [{sku: string, qty: integer, note: string | null}]

An array of objects: sku is a string, qty an integer, and note a string or null, because lines.note has no NOT NULL. coalesce(..., '[]') keeps that element shape and makes the column non-null. FILTER (WHERE l.id IS NOT NULL) drops the row a LEFT JOIN makes up for an order without lines; without it, every member would be null-able.

What goes wrong¶

The shape and the row type are written apart, so they drift. Rename a column, widen a NOT NULL, change qty to a decimal, and the Python types still say what they said:

from typing import TypedDict

from sqlty import SQL


class Line(TypedDict):
    sku: str
    qty: str
    note: str


class Order(TypedDict):
    id: int
    lines: list[Line]


# error: [json-shape-mismatch] "Order.lines is list[Line] but query column 'lines' builds JSON that does not fit: lines[].qty is str but the query builds an integer; lines[].note can be null but is declared str"
order = SQL[Order]("""--sql
SELECT
    o.id,
    coalesce(
        jsonb_agg(jsonb_build_object('sku', l.sku, 'qty', l.qty, 'note', l.note))
            FILTER (WHERE l.id IS NOT NULL),
        '[]'
    ) AS lines
FROM orders o LEFT JOIN lines l ON l.order_id = o.id
GROUP BY o.id""")

json-shape-mismatch reports every place the annotation cannot hold what the query builds, with the path to it (lines[].qty). The nested Line can be a TypedDict; list[...], dict[str, ...] and unions with None are followed too.

Shapes sqlty knows¶

Scalars follow the SQL type: integers are integer, numeric and floats number, boolean is boolean, and text, dates, UUIDs and enums are string. Arrays are arrays, and a composite type is an object of its fields. An aggregate over the optional side of an outer join has nullable members.

SELECT jsonb_build_object('id', id) AS doc FROM orders
UNION ALL
SELECT jsonb_build_object('id', id, 'note', note) FROM lines
-- json: doc: {id: integer, note?: string | null}

note? may be missing: only one branch builds it, so a TypedDict must declare it NotRequired. core.json_shapes() returns the shapes of a query's columns; -- json: in the examples above is that function.

What is not checked¶

A shape is known only when the query builds the JSON. A JSON column read from a table (SELECT prefs FROM users), ||, jsonb_set(), a -> into such a column and anything else sqlty cannot read has no shape, and every annotation fits it. So does an annotation of Any, object or a class sqlty cannot see into, such as an imported one or a Pydantic model. A dict[str, Any] fits any object or map, but not an array.

Shapes are inferred for PostgreSQL and for MySQL's JSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG and JSON_OBJECTAGG. MySQL has no boolean type, so a BOOLEAN column is a JSON number there; only TRUE and FALSE are JSON booleans. SQLite's JSON functions return text and have no shape.

Generated TypedDicts¶

sqlty rewrite types a JSON column typing.Any. With json-types = "typing.TypedDict", a column of known shape gets a type of its own instead: a TypedDict per object, list[...] for arrays, dict[str, ...] for maps and | None where a value can be NULL.

[tool.sqlty]
schema = "schema.sql"
json-types = "typing.TypedDict"

q = "--sql\nSELECT o.id, (SELECT jsonb_agg(jsonb_build_object('sku', l.sku, 'qty', l.qty, 'note', l.note)) FROM lines l WHERE l.order_id = o.id) AS lines FROM orders o"
# rewritten-file: import typing
# rewritten-file: from sqlty import SQL
# rewritten-file:
# rewritten-file:
# rewritten-file: # sqlty: generated
# rewritten-file: class LinesItem(typing.TypedDict):
# rewritten-file:     sku: str
# rewritten-file:     qty: int
# rewritten-file:     note: str | None
# rewritten-file:
# rewritten-file:
# rewritten-file: q = SQL[tuple[int, list[LinesItem] | None]]("--sql\nSELECT o.id, (SELECT jsonb_agg(jsonb_build_object('sku', l.sku, 'qty', l.qty, 'note', l.note)) FROM lines l WHERE l.order_id = o.id) AS lines FROM orders o")

Each class is written after the imports, under a # sqlty: generated line, and named after the column: lines gives Lines, an item of its array LinesItem, a nested object the member's name after its parent's. A name another definition of the module uses gets a number; a generated class of the same name and fields is reused. sqlty strip removes the generated classes with the wrappers.

The classes are yours once written: rewrite adds them for the strings it wraps, and check --fix for the queries it wraps or retypes, but neither rewrites a class that is there. json-shape-mismatch checks a row type that names them, so a query that changes is reported until you update or delete the class and run rewrite again.

Why opt in: a generated class is new code in your module, and a field typed typing.Any needs nothing. Turn it on where the JSON reaches typed code, such as an API response model.