v0.6.1

What's Changed

• Add CI workflow for lint and tests on every PR by @varjoranta in #3
• Fix iter_rows_keyset infinite loop on NULL by column (#1) by @varjoranta in #2
• Fix iter_rows_keyset NULL hang and silent row loss on ties by @varjoranta in #4
• Bump version 0.6.0 -> 0.6.1 by @varjoranta in #5

New Contributors

• @varjoranta made their first contribution in #3

Full Changelog: v0.6.0...v0.6.1

v0.6.0

Full Changelog: v0.5.0...v0.6.0

v0.5.0

Full Changelog: v0.4.0...v0.5.0

v0.4.0 - upsert, Inc/Now sentinels, IN filters, int rowcount

v0.4.0 of etchdb. Available on PyPI: https://pypi.org/project/etchdb/0.4.0/.

pip install etchdb[asyncpg]    # or [psycopg], [sqlite], [all]

What's new

on_conflict="upsert" on db.insert / db.insert_many

returned = await db.insert(
    User(id=1, name="alice", email="a@x"),
    on_conflict="upsert",
)
await db.insert_many(rows, on_conflict="upsert")

Conflict target is the row's __pk__; the SET clause writes every non-PK field via excluded.col, so the existing row is overwritten with the new values. Works on Postgres and SQLite 3.24+ (RETURNING on the upsert path also requires SQLite 3.35+).

Every PK field must be set on the row. Otherwise the auto-allocated PK can never collide, and upsert would silently degrade to a plain insert; rejecting the shape up front prevents the footgun.

Inc / Now column-expression sentinels for UPDATE SET

from etchdb import Inc, Now

await db.update(Counter.patch(id=1, n=Inc()))            # n = n + 1, atomic
await db.update(Counter.patch(id=1, n=Inc(by=-3)))       # decrement
await db.update(Article.patch(id=1, updated_at=Now()))   # CURRENT_TIMESTAMP

Inc() renders as column = column + $N so the increment is atomic at the DB level - no read-modify-write race. Now() renders as column = CURRENT_TIMESTAMP, picked inside the same statement that writes the row.

Use via Row.patch(...) since the sentinels do not satisfy Pydantic validation for int / datetime fields. Inc / Now are accepted by db.update only; db.insert and db.insert_many reject them, including under on_conflict="upsert". For create-or-increment patterns, raw SQL with INSERT ... ON CONFLICT (key) DO UPDATE SET count = table.count + 1 stays the canonical answer.

List / tuple filters produce IN (...)

recent = await db.query(User, id=[1, 5, 7])
guarded = await db.update(row, where={"tenant_id": [1, 2]})

Same shape works on query, update(where=), and delete(where=). Empty list short-circuits to [] / None / no-op without round-tripping. None inside a list raises (ambiguous with NULL - drop to raw SQL for IN (...) OR IS NULL).

db.get rejects list filters with a clear pointer to db.query. Reaching for IN (...) LIMIT 1 from the single-row verb silently returned "first match", which mixed two intents.

db.execute returns int rowcount, normalised across drivers

n = await db.execute("DELETE FROM events WHERE id < $1", cutoff)

asyncpg previously returned a status string ("UPDATE 5"); psycopg returned the cursor object; aiosqlite discarded the cursor entirely. All three now return the affected-row count for DML (INSERT / UPDATE / DELETE). DDL / SELECT / COPY return -1 consistently across backends - the same sentinel psycopg and sqlite3 already use natively.

SELECT through execute is explicitly not a count contract: use fetch / fetchrow / fetchval for SELECT and read the count off the result.

compose covers insert_many and delete_many

Every public typed op is now inspectable through the same path:

q = sql.compose("insert_many", rows, placeholder=pg, on_conflict="upsert")
q = sql.compose("delete_many", User, [1, 2, 3], placeholder=pg)

The Op Literal is public.

Compatibility

db.execute previously returned a driver-specific shape. Code that ignored the return value continues to work; code that read or parsed it will need to adjust.

Empty list filters used to raise; they now short-circuit to "no rows". Existing if not ids: return [] guards can be dropped but don't have to be.

db.get(model, field=[...]) used to silently return "first match"; it now raises. Any caller relying on the silent first-match behavior surfaces its intent.

Acknowledgements

Most of this release came from spegl.ing pattern research and from applying v0.3 against working code. The Inc / Now design and the empty-list short-circuit were both surfaced by adoption agents working against the unreleased v0.4 - friction first, then API. Codex reviewed every release before tagging.

v0.3.0 - typed exceptions, JSONB by default, iter_rows, bulk ops, IS NULL filters

v0.3.0 of etchdb. Available on PyPI: https://pypi.org/project/etchdb/0.3.0/.

pip install etchdb[asyncpg]    # or [psycopg], [sqlite], [all]

What's new

Typed exception family

Driver exceptions now map to a small etchdb family so application code catches the same type regardless of the backend. The original driver exception is preserved as __cause__.

from etchdb import (
    EtchdbError,            # base for everything etchdb raises
    IntegrityError,         # unique / FK / NOT NULL / check violation
    UndefinedTableError,    # table referenced by a query does not exist
    UndefinedColumnError,   # column referenced by a query does not exist
    OperationalError,       # connection-level / driver-level failure
)

try:
    await db.insert(User(id=1, name="Alice"))
except IntegrityError as e:
    log.warning("constraint violation: %s", e)

except EtchdbError catches every member of the family at once.

JSONB codec on by default for from_url

Both Postgres adapters now register a JSONB codec on every connection. dict and list parameters round-trip with UUID, datetime, Enum, and Pydantic BaseModel values handled transparently; JSONB columns come back as Python objects directly - no json.loads at the call site, no manual json.dumps to insert.

For pool-init concerns beyond that (pgvector tuning, custom ENUM codec, custom min/max sizes), construct the pool yourself and pass it via from_pool - the BYO escape hatch stays minimal.

db.iter_rows async paginator

async for user in db.iter_rows(User, batch_size=500):
    process(user)

Stable PK-ordered pagination by default. Uses offset pagination, so cost scales O(N^2) on huge tables; the docstring points at raw keyset queries for full table scans. batch_size must be positive (the earlier shape could loop forever on SQLite's quirky LIMIT -1).

Bulk inserts and deletes with parameter-limit chunking

await db.insert_many([User(name=n) for n in names], on_conflict="ignore")
await db.delete_many(User, [1, 2, 3])
await db.delete_many(UserRole, [{"user_id": 1, "role_id": 2}, ...])  # composite PK

Long batches automatically chunk at the driver's parameter limit (Postgres caps at 32767, SQLite at 32766 since 3.32) so a single call covers thousands of rows. on_conflict="ignore" appends ON CONFLICT DO NOTHING on Postgres and SQLite (3.24+); for richer conflict handling, drop to raw SQL.

Mixed Row subclasses and unknown on_conflict strings are rejected up front so dropped columns and silent typos can't masquerade as "works fine".

IS NULL via field=None

A correctness fix. db.get(User, deleted_at=None) previously emitted WHERE deleted_at = $1 with $1 bound to NULL, which silently matches no rows. After this release it emits WHERE deleted_at IS NULL and returns the rows you asked for.

The change applies to filters in get, query, update(where=...), and delete(where=...). SET clauses still emit field = $N so column-to-NULL writes via update are unchanged.

Compatibility

The IS NULL change is technically a behavior change but it's a correctness fix - any code relying on the previous behavior was silently returning no rows. Everything else is additive. v0.2 code continues to work.

Acknowledgements

Most of this release came from real-world adoption feedback - using etchdb against production-shape backends surfaced ergonomic gaps and one outright correctness bug (the IS NULL one) that an isolated design pass missed. Adoption is the best test.

v0.2.0 - psycopg adapter + ergonomic improvements

v0.2.0 of etchdb. Available on PyPI: https://pypi.org/project/etchdb/0.2.0/.

pip install etchdb[asyncpg]    # or [psycopg], [sqlite], [all]

What's new

psycopg3 adapter

postgresql+psycopg:// URLs now resolve to PsycopgAdapter, joining asyncpg and aiosqlite as a third supported driver. Uses psycopg.AsyncRawCursor so raw SQL keeps libpq-native $1, $2, ... placeholders - the same syntax the asyncpg adapter accepts. Drop-in for projects that already standardise on psycopg3.

db = await DB.from_url("postgresql+psycopg://user@host/db")

The integration suite now parametrizes every test across SQLite + asyncpg + psycopg.

Atomic scoping with where=

db.update(row, where={...}) and db.delete(row, where={...}) AND extra equality filters onto the PK in a single statement. Multi-tenant scoping ("update this row only if it belongs to this user") becomes atomic - no fetch-then-update window.

await db.update(
    Note.patch(id=note_id, status="archived"),
    where={"user_id": current_user_id},
)

The where= parameter accepts any Mapping[str, Any]. PK fields in where= are rejected with a clear error since they're already in the WHERE clause.

Row.patch(...) for partial updates without lying schema

Previously, partial updates against a model with NOT NULL columns required making every field Optional on the Pydantic side - the model would lie about the schema. Row.patch(**fields) builds a partial Row via model_construct, skipping validation:

class Note(Row):
    __table__ = "notes"
    id: int | None = None
    body: str          # honestly NOT NULL
    status: str        # honestly NOT NULL

await db.update(Note.patch(id=1, status="archived"))   # works

Documented as not-a-general-constructor: only for the partial-update / partial-delete row-identity shapes.

Adapter-free SQL inspection: etchdb.sql.compose

The compose inspector is now also available at module level, so SQL can be inspected in tests and scripts without instantiating an adapter or DB:

from etchdb import sql

q = sql.compose("get", User, id=1, placeholder=lambda i: f"${i+1}")
# SqlQuery(sql='SELECT id, name FROM users WHERE id = $1 LIMIT 1', params=[1])

DB.compose is now a thin wrapper that fills in placeholder from the live adapter.

README: BYO-pool + ENUM codecs

The README now shows how to register asyncpg type codecs (e.g., for Postgres ENUMs) by passing your own init=-configured pool to DB(AsyncpgAdapter.from_pool(my_pool)). The minimal from_url constructor stays minimal; pool-init concerns live in user code via the BYO escape hatch.

Compatibility

No breaking changes. All v0.1 code continues to work.

Acknowledgements

Several improvements in this release came from real-world adoption feedback - using etchdb against an actual production-shape backend surfaced ergonomic gaps that an isolated design pass missed. Adoption is the best test.

v0.1.0 - first PyPI release

First public release of etchdb. Available on PyPI: https://pypi.org/project/etchdb/0.1.0/.

pip install etchdb[asyncpg]    # or [psycopg], [sqlite], [all]

Highlights

• Two user-facing types: DB and Row(BaseModel). Subclass Row, set __table__, you have a typed table.
• Typed CRUD: db.get / query / insert / update / delete over Pydantic Row classes.
• model_fields_set-aware insert and update (the core ergonomic):
  • db.insert(User(name="Alice")) lets the DB allocate a SERIAL or INTEGER PRIMARY KEY id.
  • db.update(User(id=1, name="New")) only emits the columns you set; the email column is preserved instead of being clobbered.
  • Explicit None counts as set, distinct from defaulted.
  • update and delete raise loudly if any PK field is unset (no silent no-match).
• Raw SQL passthrough mirroring asyncpg's vocabulary: execute / fetch / fetchrow / fetchval.
• Typed-result raw SQL for joins: db.fetch_models(User, "SELECT u.* FROM ...", since).
• Inspectable SQL: db.compose("get", User, id=1) returns a SqlQuery so you can preview the SQL+params without hitting the DB.
• Drivers: asyncpg and aiosqlite. psycopg URL is recognised but raises NotImplementedError for now.
• Lazy driver imports: from etchdb import DB, Row works in a venv with no driver installed; etchdb.asyncpg and etchdb.aiosqlite raise a clear ImportError naming the right extra.
• Transactions via async with db.transaction() as tx: yielding a DB bound to the connection.

Requirements

• Python >= 3.12.
• Pydantic v2 only.

Status

Alpha. API may tighten between alpha releases. Use raw SQL whenever the typed surface doesn't fit yet.

Migrations

Out of scope for v0.1; a small forward-only file-based helper is planned. Use any external tool (Alembic, dbmate, hand-rolled db.execute) in the meantime - etchdb owns no schema state.