Writing queries¶

A query[T] takes one inline t-string. Conversions ({x!r}, {x=}), format specs and nested templates are rejected, so every interpolation is a bound parameter. Use query_unchecked for dynamic SQL; the checker does not describe its SQL, but it still resolves T.

import asyncio

from rowx.postgres import connect
from rowx import query, query_unchecked


async def main() -> None:
    async with await connect(DSN) as conn:
        # IN-lists: bind a list as an array with = ANY(...)
        wanted = ["alice", "bob"]
        found = await query[str](t"SELECT name FROM users WHERE name = ANY({wanted}) ORDER BY name").all(conn)
        assert found == wanted

        # literal braces are doubled
        arr = await query[list[int]](t"SELECT '{{1,2,3}}'::int[]").one(conn)
        assert arr == [1, 2, 3]

        # bind the whole pattern, never an interpolation inside a literal
        pattern = "al%"
        like = await query[str](t"SELECT name FROM users WHERE name LIKE {pattern}").all(conn)
        assert like == ["alice"]

        # cast a parameter whose type the server cannot infer
        missing = None
        none_ok = await query[bool](t"SELECT {missing}::int IS NULL").one(conn)
        assert none_ok

        # dynamic identifiers and nested templates are for query_unchecked only
        table, column = "users", "name"
        cond = t"WHERE id > {0}"
        names = await query_unchecked[str](t"SELECT {column:ident} FROM {table:ident} {cond} ORDER BY 1").all(conn)
        assert names == ["alice", "bob", "carol"]


asyncio.run(main())

In query_unchecked, a nested Template is flattened and its values stay bound, and {name:ident} emits a quoted identifier (a str, or a tuple[str, ...] joined with dots). There is no raw-SQL escape.