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}").
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.
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#
Only the transaction separates batches. Requests made together outside a transaction share
one batch even when each provides a different SqlClient, and the batch runs on the first
request's client. A resolver shared by tenants with their own databases gives one tenant
another's rows. Effect's SqlResolver does the same and says so.
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.
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.
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.
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.
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.
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.