A pooled PostgreSQL client: JSON columns, streamed rows, time zones, LISTEN/NOTIFY, notifications sent on commit, and migrations.
pyfect.sql.pg runs on psycopg 3; install the pyfect-dev[pg] extra. psycopg needs the
libpq C library: slim Docker images leave it out, so install it there (apt-get install libpq5)
or add psycopg[binary], which bundles it (not built for free-threaded Python). pg.layer provides
both PgClient and SqlClient, so the code from the SQL guide runs unchanged on
PostgreSQL. PgClient adds what only PostgreSQL has: json, listen and notify.
The snippets on this page read the server's URL from DATABASE_URL:
DATABASE_URL=postgres://user:password@localhost:5432/app python connect.pyConnecting#
PgPoolConfig holds the URL (a Redacted, so it never shows up in logs), the pool's size
and timeouts, and the session settings. pg.layer(config) opens connections as statements
need them and closes them when the layer's scope closes. To read the settings from the
environment instead, use pg.layer_config({"url": fx.config.redacted("DATABASE_URL")}).
sql.pool_stats gives a PoolStats snapshot of the sessions (idle, used, pending, min,
max); a make_client client always reports its one session as used.
Statements use the same {} placeholders as every SqlClient. Three things are specific to
PostgreSQL or easy to miss:
sql.json(value)binds a value asjsonb. Reading ajsonorjsonbcolumn gives the parsed value back.sql.literal("...")is trusted SQL text, spliced in as written. Its argument is aLiteralString, so pyright rejects text built at runtime.sql.unsafe(text, params)takes text built at runtime, and its parameters go to PostgreSQL's own placeholders,$1,$2and so on, not{}.
import os
import pyfect as fx
from pyfect import redacted
from pyfect.sql import pg
# DATABASE_URL=postgres://user:password@localhost:5432/app python connect.py
config = pg.PgPoolConfig(
url=redacted.make(os.environ["DATABASE_URL"]),
max_connections=5,
idle_timeout="30 seconds",
)
@fx.fn
def program():
sql = yield from pg.PgClient()
yield from sql("CREATE TABLE events (id serial PRIMARY KEY, kind text, data jsonb)")
# sql.json binds a value as jsonb; reading the column gives back the dict.
yield from sql(
"INSERT INTO events (kind, data) VALUES ({}, {})",
"signup",
sql.json({"user": "ada", "plan": "pro"}),
)
rows = yield from sql("SELECT kind, data FROM events")
print(rows)
# sql.literal is trusted SQL text (a LiteralString), spliced in as written.
order = sql.literal("ORDER BY id DESC")
print((yield from sql("SELECT id, kind FROM events {}", order)))
# unsafe takes text built at runtime; PostgreSQL's placeholders are $1, $2, ...
column = "kind"
found = yield from sql.unsafe(f"SELECT id FROM events WHERE {column} = $1", ["signup"])
print(found)
fx.run_main(program().pipe(fx.provide(pg.layer(config))))Streaming rows#
A statement's .stream is its rows as a Stream, read from PostgreSQL as the stream is
pulled, so an export of millions of rows never holds them all in memory. The stream holds one
session until it ends. Ending it early, with take or by interrupting it (a client that
hangs up on a streamed HTTP response, for example), cancels the query in PostgreSQL.
import os
from collections.abc import Mapping
import pyfect as fx
from pyfect import redacted
from pyfect.sql import pg
config = pg.PgPoolConfig(url=redacted.make(os.environ["DATABASE_URL"]))
def number(row: Mapping[str, object]) -> int:
value = row["n"]
assert isinstance(value, int)
return value
@fx.fn
def program():
sql = yield from pg.PgClient()
# .stream gives the rows as PostgreSQL sends them, holding one session until it ends.
# (generate_series in the select list streams; in FROM it is computed whole first.)
rows = sql("SELECT generate_series(1, 1000000000) AS n").stream
multiples = rows.filter(lambda row: number(row) % 40 == 0).take(3)
print((yield from multiples.run_collect()))
# take(3) ended the stream, which cancelled the query: nothing is left running.
running = yield from sql(
"SELECT count(*) AS n FROM pg_stat_activity "
"WHERE state = 'active' AND query LIKE 'SELECT generate_series%'"
)
print(running)
# Folding a stream never holds every row in memory.
small = sql("SELECT generate_series(1, 100000) AS n").stream
print((yield from small.run_fold(lambda: 0, lambda total, row: total + number(row))))
fx.run_main(program().pipe(fx.provide(pg.layer(config))))Timestamps and time zones#
A timestamptz column comes back as an aware datetime in the session's TimeZone, which
is the server's setting unless the pool sets one. Effect's driver gives a JavaScript Date,
an instant with no zone, so decode the column the way Effect decodes a Date: with
S.DateTimeUtcFromDate, which takes an aware datetime in any zone and gives it in UTC.
S.DateTimeUtc takes only a datetime that is already in UTC, so on a server whose zone
isn't UTC it rejects every row. Setting startup_parameters={"TimeZone": "UTC"} on the pool
makes the values UTC as well.
import os
from datetime import datetime
from typing import TypedDict
import pyfect as fx
from pyfect import redacted
from pyfect import schema as S
from pyfect.sql import pg
# The session's TimeZone decides the zone timestamptz values come back in; it is the
# server's setting unless the pool sets one.
config = pg.PgPoolConfig(
url=redacted.make(os.environ["DATABASE_URL"]),
startup_parameters={"TimeZone": "America/Chicago"},
)
class Event(TypedDict):
at: datetime
EVENT = S.Struct(Event, at=S.DateTimeUtcFromDate)
@fx.fn
def program():
sql = yield from pg.PgClient()
rows = yield from sql("SELECT timestamptz '2026-11-12 18:00:00+00' AS at")
print(rows[0]["at"])
# S.DateTimeUtc only takes a datetime that is already UTC, so it rejects this one.
rejected = yield from S.decode_unknown_effect(S.DateTimeUtc)(rows[0]["at"]).flip()
print(rejected)
# S.DateTimeUtcFromDate takes the instant in any zone, as Effect's does a Date.
event = yield from S.decode_unknown_effect(EVENT)(rows[0])
print(event["at"])
fx.run_main(program().pipe(fx.provide(pg.layer(config))))Errors and traces#
A failed statement is a SqlError whose reason comes from the SQLSTATE, as in Effect's
driver: UniqueViolation carries the name of the constraint it broke, and any other
integrity violation (a foreign key, a CHECK, an exclusion constraint) is a
ConstraintError, which, as in Effect, does not. To tell such constraints apart, read the
driver's error in the reason's cause: psycopg's cause.diag.constraint_name and
cause.sqlstate.
Each statement's span records db.namespace, server.address and server.port from the
config's database (else username, else postgres), host (else localhost) and port
(else 5432), as Effect's driver does. They are not read from url, so a client configured by
URL alone reports the defaults; give database and host as well, or add the attributes you
want with span_attributes.
LISTEN and NOTIFY#
sql.listen(channel) runs LISTEN and succeeds once PostgreSQL has confirmed it. It gives a
Dequeue[Notification, SqlError] with every notification sent on the channel after that.
The listener holds its own session until its Scope closes, so listen needs a Scope:
here fx.scoped around the program. Keep that scope inside the client's layer
(fx.scoped(program).pipe(fx.provide(layer))): closing the layer waits for the pool's
sessions to come back, so with the scope outside it
(fx.scoped(program.pipe(fx.provide(layer)))) the program never ends, as in Effect. If the session fails, the queue fails with the
session's error. If more than capacity notifications (default 1024) are left untaken, the
queue fails with an error saying the listener fell behind.
sql.notify(channel, payload) sends one. Inside a transaction, wrap it in
sql.after_commit(...): it then runs only if the transaction commits, so a listener never
hears about a write that was rolled back. after_commit takes an effect that can't fail,
so handle the notify's SqlError there (here it's logged).
import os
from dataclasses import dataclass
import pyfect as fx
from pyfect import redacted
from pyfect.sql import pg
layer = pg.layer(pg.PgPoolConfig(url=redacted.make(os.environ["DATABASE_URL"])))
@dataclass
class Rejected(fx.TaggedError):
reason: str
@fx.fn
def checkpoint(job: int, row: int, *, reject: bool = False):
sql = yield from pg.PgClient()
yield from sql("UPDATE jobs SET row = {} WHERE id = {}", row, job)
# Sent only if the transaction commits; a notify can fail, after_commit's
# effect can't, so its error is logged and dropped.
yield from sql.after_commit(
sql.notify("progress", f"{job}:{row}").pipe(fx.catch_all(fx.log_error))
)
if reject:
yield from Rejected("checkpoint refused") # rolls back, nothing is sent
@fx.fn
def program():
sql = yield from pg.PgClient()
yield from sql("CREATE TABLE jobs (id int PRIMARY KEY, row int)")
yield from sql("INSERT INTO jobs VALUES (1, 0)")
# LISTEN holds a session until the scope closes; the Dequeue gets every
# notification sent on the channel from then on.
progress = yield from sql.listen("progress")
# A checkpoint that commits between the LISTEN and the read below.
yield from sql.with_transaction(checkpoint(1, 500))
# Read the current state after listening, then take only newer rows:
# the 500 notification is still queued and is older than what we read.
[state] = yield from sql("SELECT row FROM jobs WHERE id = 1")
seen = state["row"]
assert isinstance(seen, int)
print("read", seen)
error = yield from sql.with_transaction(checkpoint(1, 750, reject=True)).flip()
print(repr(error))
yield from sql.with_transaction(checkpoint(1, 1000))
while seen < 1000:
notification = yield from progress.take().timeout("5 seconds")
row = int(notification.payload.split(":")[1])
if row <= seen:
print("dropped", notification.payload)
continue
seen = row
print(notification.channel, notification.payload)
fx.run_main(fx.scoped(program()).pipe(fx.provide(layer)))Call listen before the first read of the state it reports on: a change committed between
the read and the LISTEN would otherwise go unnoticed. The other side of that order is that
the queue can then hold notifications older than the state you read: one sent between the
LISTEN and the read is delivered after it. So drop every notification that isn't newer
than what you read, by comparing a version or sequence column, as the snippet does with
row (it drops 1:500).
Migrations#
pg.migrator works like the SQLite migrator, and migrator.layer
runs the migrations when the layer is built. With schema_directory, it also writes the
schema and the migrations table to that directory with pg_dump. That's why the layer needs
the platform services, even when it doesn't dump anything.
import os
import pyfect as fx
from pyfect import platform, redacted
from pyfect.sql import SqlClient, pg
from pyfect.sql.pg import migrator
@fx.fn
def create_jobs():
sql = yield from SqlClient()
yield from sql("CREATE TABLE jobs (id serial PRIMARY KEY, name text NOT NULL)")
@fx.fn
def add_progress():
sql = yield from SqlClient()
# Braces are placeholders: a literal "{}" is written "{{}}".
yield from sql("ALTER TABLE jobs ADD COLUMN progress jsonb NOT NULL DEFAULT '{{}}'")
loader = migrator.from_record({"1_create_jobs": create_jobs(), "2_add_progress": add_progress()})
database = pg.layer(pg.PgPoolConfig(url=redacted.make(os.environ["DATABASE_URL"])))
# Migrates when built, before anything else uses the database. With
# schema_directory="db", it also writes the schema there with pg_dump.
migrated = (
migrator.layer(loader=loader)
.pipe(fx.layer.provide_merge(database))
.pipe(fx.layer.provide(platform.layer)) # pg_dump runs as a child process
)
@fx.fn
def program():
sql = yield from SqlClient()
print((yield from sql("SELECT migration_id, name FROM effect_sql_migrations")))
columns = yield from sql(
"SELECT column_name FROM information_schema.columns"
" WHERE table_name = 'jobs' ORDER BY ordinal_position"
)
print([row["column_name"] for row in columns])
fx.run_main(program().pipe(fx.provide(migrated)))Reference: pyfect.sql.pg,
pyfect.sql.pg.client,
pyfect.sql.pg.config,
pyfect.sql.pg.migrator.