Skip to content

Quickstart

This page takes one file from plain SQL strings to typed, checked queries. Install sqlty first (Installation).

1. Mark queries with --sql

Start each query string with --sql and a newline, and cast the columns whose types you want:

# queries.py
user_query = "--sql\nSELECT id::INTEGER, name::TEXT FROM users"
count_query = "--sql\nSELECT COUNT(*)::INTEGER FROM users"

Writing queries covers the prefix, casts and the type mapping. With a schema file you can drop the casts.

2. Rewrite

sqlty rewrite .

sqlty rewrite wraps each query in SQL[T](...) with the inferred row type and adds the import. queries.py afterwards:

user_query = "--sql\nSELECT id::INTEGER, name::TEXT FROM users"
count_query = "--sql\nSELECT COUNT(*)::INTEGER FROM users"
# rewritten-file: from sqlty import SQL
# rewritten-file: user_query = SQL[tuple[int, str]]("--sql\nSELECT id::INTEGER, name::TEXT FROM users")
# rewritten-file: count_query = SQL[tuple[int]]("--sql\nSELECT COUNT(*)::INTEGER FROM users")

SQL[T] is a str subclass, so the queries still work anywhere a string does. Editors and type checkers now see T.

3. Check

sqlty check .

sqlty check prints nothing when everything matches. Edit the first query to select email::TEXT as well, and it reports the drift:

queries.py:2: [narrow-type] SQL[tuple[int, str]] should be SQL[tuple[int, str, str]]
Found 1 error(s)

Errors, such as this drift or a row type mismatch, exit with 1; warnings leave the exit code at 0. The rules reference lists every diagnostic.

sqlty check --fix . updates the stale type in place, then reports anything left that needs a decision.

4. Name the columns

Replace tuple[...] with a TypedDict, NamedTuple, dataclass or Pydantic model, and sqlty check validates field count, types and names:

from typing import TypedDict
from sqlty import SQL

class User(TypedDict):
    id: int
    name: str

user_query = SQL[User]("--sql\nSELECT id::INTEGER, name::TEXT FROM users")
# error:

The bare # error: line means sqlty check reports nothing for this file. See Row types.

5. Edit queries

sqlty rewrite does not touch existing wrappers. After you change a query:

  • Edit in place. Change the SQL inside SQL[T](...) and let sqlty check report a stale T, as in step 3. Use this for files with row types like User.
  • Strip and rewrite. For files that only have SQL[tuple[...]], run sqlty strip, edit, and sqlty rewrite to regenerate every type.

Warning

sqlty strip also removes hand-written row types such as SQL[User]. See Editing queries.

6. Run it before every commit

Add sqlty rewrite and sqlty check as pre-commit hooks. CI and pre-commit also shows how to fail a build on unwrapped or stale queries.

Next steps