GitHub

SQL

Parameterized statements, batched lookups, transactions and migrations, with SQLite built in.

SqlClient is a service: call it with SQL and it gives back a statement, an effect that runs on a connection from the client and succeeds with the rows as dicts. sqlite.layer provides one on the standard library's sqlite3. For PostgreSQL, install the pyfect-dev[pg] extra and provide pyfect.sql.pg.layer instead; the code that uses SqlClient stays the same (see PostgreSQL). MySQL works the same way with the pyfect-dev[mysql] extra and pyfect.sql.mysql.layer (see MySQL), and SQL Server with the pyfect-dev[mssql] extra and pyfect.sql.mssql.layer (see SQL Server). libSQL and Turso use pyfect.sql.libsql.layer (see libSQL and Turso), and ClickHouse pyfect.sql.clickhouse.layer (see ClickHouse). Every failure is a SqlError whose reason says what went wrong (UniqueViolation, SqlSyntaxError, LockTimeoutError, ...), and str(error) shows the message and the driver's cause.

Safe statements

Placeholders are str.format fields ({}, {0}, {name}), and their values are always bound parameters. The template is typed LiteralString, so pyright rejects SQL text built at runtime: sql(f"... WHERE name = '{name}'") is a type error. Text that has to be built at runtime goes through sql.unsafe(text, params), which is easy to search for. On Python 3.14 the template can also be a t-string: sql(t"SELECT * FROM users WHERE id = {user_id}").

statements.py
import pyfect as fx
from pyfect.sql import SqlClient, sqlite


@fx.fn
def program():
    sql = yield from SqlClient()
    yield from sql("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, role TEXT)")

    # Values are always bound parameters, never pasted into the text.
    yield from sql(
        "INSERT INTO users {}",
        sql.insert([{"name": "Ada", "role": "admin"}, {"name": "Linus", "role": "dev"}]),
    )
    name = "Robert'); DROP TABLE users;--"
    yield from sql("INSERT INTO users (name, role) VALUES ({}, {})", name, "dev")

    devs = sql("SELECT name FROM users WHERE role = {role} ORDER BY id", role="dev")
    print(devs.compile())
    print((yield from devs))

    # Helpers build fragments: lists, column sets, conditions.
    some = yield from sql("SELECT id, name FROM users WHERE {}", sql.in_("id", [1, 3]))
    print(some)


database = sqlite.layer(sqlite.SqliteClientConfig(filename=":memory:"))
fx.run_main(program().pipe(fx.provide(database)))

Helpers build the parts that change shape: sql.insert for one or many rows, sql.in_ for lists, sql.update for SET clauses, sql.and_ / sql.or_ for conditions and sql.identifier for an escaped name. statement.compile() shows the text and parameters the driver receives.

A resolver that batches lookups

Code that loads one user at a time, from many fibers, makes one query per user. A resolver collects the requests made together and runs one query for all of them: find_by_id matches the rows back to requests by id, each request gets its own result, and duplicates are asked for once. ordered and grouped cover queries whose results come back in request order or in groups, and void covers writes.

ids 3, 1rows, matched by iduser_by_id(3)find_by_id resolveruser_by_id(1)user_by_id(3)SELECT ... WHERE id IN (3, 1)
ids 3, 1rows, matched by iduser_by_id(3)find_by_id resolveruser_by_id(1)user_by_id(3)SELECT ... WHERE id IN (3, 1)
batched.py
from collections.abc import Sequence
from typing import TypedDict

import pyfect as fx
from pyfect import schema as S
from pyfect.sql import SqlClient, sqlite
from pyfect.sql import resolver as R


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


@fx.fn
def program():
    sql = yield from SqlClient()
    yield from sql("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
    yield from sql(
        "INSERT INTO users {}", sql.insert([{"name": n} for n in ["Ada", "Linus", "Grace"]])
    )

    def select(ids: Sequence[int]):
        print("query for", list(ids))
        return sql("SELECT * FROM users WHERE id IN {}", sql.in_(ids))

    # One request per id; requests made together run as one query.
    user_by_id = R.request(
        R.find_by_id(
            id=S.Int,
            result=S.Struct(User),
            result_id=lambda user, _row: user["id"],
            execute=select,
        )
    )

    users = yield from fx.all(user_by_id(3), user_by_id(1), user_by_id(3), concurrency="unbounded")
    print(users)

    # An id with no row fails that request alone.
    missing = yield from user_by_id(9).flip()
    print(type(missing).__name__)


database = sqlite.layer(sqlite.SqliteClientConfig(filename=":memory:"))
fx.run_main(program().pipe(fx.provide(database)))

Requests and results are decoded with schemas, so a row of the wrong shape fails with a SchemaError. Requests made inside a transaction are batched with requests from the same transaction only.

One resolver per tenant

execute runs once for the whole batch, with the services of the request that came first, so a SqlClient() read inside it is that request's client. Give each tenant its own resolver over its own client, made once with the tenant's client (in the tenant's layer, for example) so its requests still batch. When the tenants share one database, put the tenant in the request instead and filter on it in execute. RequestResolver.grouped is no way out: its key replaces the transaction key, so it would batch requests of different transactions together.

tenants.py
from collections.abc import Sequence
from typing import TypedDict

import pyfect as fx
from pyfect import reactivity
from pyfect import schema as S
from pyfect.sql import SqlClient, SqlClientService, sqlite
from pyfect.sql import resolver as R


class Account(TypedDict):
    id: int
    owner: str


def tenant_database(owner: str):
    """A tenant's own database, holding its account 1."""

    @fx.fn
    def make():
        sql = yield from sqlite.make(sqlite.SqliteClientConfig(filename=":memory:"))
        yield from sql("CREATE TABLE accounts (id INTEGER PRIMARY KEY, owner TEXT)")
        yield from sql("INSERT INTO accounts {}", sql.insert({"id": 1, "owner": owner}))
        return sql

    return make()


@fx.fn
def select_from_context(ids: Sequence[int]):
    sql = yield from SqlClient()  # the client of the batch's first request
    return (yield from sql("SELECT * FROM accounts WHERE id IN {}", sql.in_(ids)))


# Unsafe across tenants: one resolver, the client read from each request's context.
shared_account = R.request(
    R.find_by_id(
        id=S.Int,
        result=S.Struct(Account),
        result_id=lambda account, _row: account["id"],
        execute=select_from_context,
    )
)


# Safe: a resolver per tenant, over that tenant's client.
def account_lookup(sql: SqlClientService):
    return R.request(
        R.find_by_id(
            id=S.Int,
            result=S.Struct(Account),
            result_id=lambda account, _row: account["id"],
            execute=lambda ids: sql("SELECT * FROM accounts WHERE id IN {}", sql.in_(ids)),
        )
    )


@fx.fn
def program():
    a = yield from tenant_database("tenant a")
    b = yield from tenant_database("tenant b")

    # Requests made together share a batch, whatever client each provides.
    got = yield from fx.all(
        shared_account(1).pipe(fx.provide_service(SqlClient, a)),
        shared_account(1).pipe(fx.provide_service(SqlClient, b)),
        concurrency="unbounded",
    )
    print("shared:", [account["owner"] for account in got])

    account_of_a, account_of_b = account_lookup(a), account_lookup(b)
    got = yield from fx.all(account_of_a(1), account_of_b(1), concurrency="unbounded")
    print("per tenant:", [account["owner"] for account in got])


fx.run_main(fx.scoped(program()).pipe(fx.provide(reactivity.layer)))

Transactions

sql.with_transaction(effect) runs every statement of effect on one connection. It commits when the effect succeeds and rolls back when it fails, dies or is interrupted, so an error raised half way through undoes the half that ran. A rollback that fails itself, as on a lost connection, is a defect, as in Effect, so the transaction then dies and catch_tag(SqlError) doesn't see it. Nested, it uses a savepoint. sql.after_commit(effect) runs effect only once the outermost transaction commits.

The SQLite client has one connection, as in Effect, so a transaction holds it from start to end and every other query on that client waits for it. Keep slow work, such as reading an upload or calling another service, outside with_transaction: read or spool the input first, then open the transaction to write it.

transfer.py
from dataclasses import dataclass

import pyfect as fx
from pyfect.sql import SqlClient, sqlite


@dataclass
class InsufficientFunds(fx.TaggedError):
    account: str


@fx.fn
def transfer(source: str, target: str, amount: int):
    sql = yield from SqlClient()
    yield from sql("UPDATE accounts SET balance = balance - {} WHERE name = {}", amount, source)
    yield from sql("UPDATE accounts SET balance = balance + {} WHERE name = {}", amount, target)
    overdrawn = yield from sql("SELECT 1 FROM accounts WHERE name = {} AND balance < 0", source)
    if overdrawn:
        yield from InsufficientFunds(source)  # rolls back both updates


@fx.fn
def program():
    sql = yield from SqlClient()
    yield from sql("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER)")
    yield from sql(
        "INSERT INTO accounts {}",
        sql.insert([{"name": "ada", "balance": 100}, {"name": "bob", "balance": 0}]),
    )

    # Commits when the effect succeeds, rolls back when it fails or is interrupted.
    yield from sql.with_transaction(transfer("ada", "bob", 30))
    error = yield from sql.with_transaction(transfer("ada", "bob", 500)).flip()
    print(repr(error))

    print((yield from sql("SELECT * FROM accounts ORDER BY name")))


database = sqlite.layer(sqlite.SqliteClientConfig(filename=":memory:"))
fx.run_main(program().pipe(fx.provide(database)))

Migrations

A migration is an effect that uses SqlClient. migrator.run creates the effect_sql_migrations table if needed, runs the migrations newer than the last one recorded, and succeeds with the ids and names it ran. The new migrations run in one transaction, so if one fails none of them stay applied, and the error is a MigrationError. Two processes starting at once don't run them twice.

migrations.py
import pyfect as fx
from pyfect.sql import SqlClient, sqlite
from pyfect.sql.sqlite import migrator


@fx.fn
def create_users():
    sql = yield from SqlClient()
    yield from sql("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)")


@fx.fn
def add_email():
    sql = yield from SqlClient()
    yield from sql("ALTER TABLE users ADD COLUMN email TEXT")


# Keys are "<id>_<name>". New migrations run in id order, all in one transaction.
loader = migrator.from_record({"1_create_users": create_users(), "2_add_email": add_email()})


@fx.fn
def program():
    sql = yield from SqlClient()
    print((yield from migrator.run(loader=loader)))
    print((yield from migrator.run(loader=loader)))  # already applied: nothing to do
    print((yield from sql("SELECT migration_id, name FROM effect_sql_migrations")))


database = sqlite.layer(sqlite.SqliteClientConfig(filename=":memory:"))
fx.run_main(program().pipe(fx.provide(database)))

migrator.from_record takes migrations keyed <id>_<name> ("1_create_users") and, as Effect's fromRecord, skips any other key, so a misnamed migration never runs: check the ids migrator.run returns in a test. migrator_file_system.from_file_system(directory) (in pyfect.sql.migrator_file_system, re-exported by each driver's migrator) loads <id>_<name>.py modules instead, each with its migration as default. migrator.layer(loader=...) runs the migrations when the layer is built, so the app's layer can migrate before anything else uses the database.

Several databases in one program

Each driver's layer provides two services: its own tag (mysql.MysqlClient, libsql.LibsqlClient, ...) and SqlClient. Merge two layers and the type checker has nothing to complain about, since both provide SqlClient, but only the last one's stays: as in Effect, layer.merge(a, b) gives b's client. Code that reads SqlClient then runs against whichever came last. Once a program has two databases, read each through its driver's tag, and give each migrator its own client with provide_merge.

several_databases.py
import pyfect as fx
from pyfect import layer
from pyfect.sql import SqlClient, libsql, sqlite

# Both layers provide SqlClient as well as their own tag.
buffer = libsql.layer(libsql.LibsqlClientConfig(url=":memory:"))
store = sqlite.layer(sqlite.SqliteClientConfig(filename=":memory:"))


@fx.fn
def program():
    # The driver tags always name the database you meant.
    in_buffer = yield from libsql.LibsqlClient()
    in_store = yield from sqlite.SqliteClient()
    yield from in_buffer("CREATE TABLE hits (id INTEGER)")
    yield from in_store("CREATE TABLE sites (id INTEGER)")

    # SqlClient is the last layer's, here the SQLite store: no error, just another database.
    either = yield from SqlClient()
    tables = yield from either("SELECT name FROM sqlite_master")
    print(tables)


fx.run_main(program().pipe(fx.provide(layer.merge(buffer, store))))

SQLite in tests

An in-memory database is fast enough to create for every test. Here the test layer runs the migrations on a fresh one, and testing.layer_fixture(..., scope="function") builds it anew for each test, so no test sees another's rows.

test_users.py
import pyfect as fx
from pyfect import layer, testing
from pyfect.sql import SqlClient, SqlError, sqlite
from pyfect.sql.error import UniqueViolation
from pyfect.sql.sqlite import migrator


@fx.fn
def create_users():
    sql = yield from SqlClient()
    yield from sql("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE)")


@fx.fn
def add_user(name: str):
    sql = yield from SqlClient()
    [row] = yield from sql("INSERT INTO users (name) VALUES ({}) RETURNING id", name)
    return row["id"]


# A fresh in-memory database for each test, migrated before the test starts.
database = migrator.layer(loader=migrator.from_record({"1_create_users": create_users()})).pipe(
    layer.provide_merge(sqlite.layer(sqlite.SqliteClientConfig(filename=":memory:")))
)
db = testing.layer_fixture(database, scope="function")


@testing.effect
def test_adds_users(db: testing.Services[SqlClient]):
    assert (yield from add_user("Ada")) == 1
    assert (yield from add_user("Linus")) == 2


@testing.effect
def test_each_test_starts_empty(db: testing.Services[SqlClient]):
    sql = yield from SqlClient()
    assert (yield from sql("SELECT * FROM users")) == []


@testing.effect
def test_names_are_unique(db: testing.Services[SqlClient]):
    yield from add_user("Ada")
    error = yield from add_user("Ada").flip()
    assert isinstance(error, SqlError)
    assert isinstance(error.reason, UniqueViolation)

To read and write rows as classes, with repositories and batched resolvers made from them, see Models.

Reference: pyfect.sql, pyfect.sql.sqlite, pyfect.sql.resolver, pyfect.sql.migrator.