Cygnet is a small, fierce, PostgreSQL-only ORM for async Python, and its whole personality is that it refuses to hide the SQL. Most ORMs exist so you never have to think about the query; Cygnet exists for the opposite kind of person, the one who already knows what SQL they want and would just like some help writing it without hand-numbering the bind parameters. (It’s named for a young swan. If you have ever been near a swan, you know “small but fierce” is the correct register.)
The convention that organizes the whole API is one you’ll internalize in about thirty seconds: SQL keywords are uppercase methods, Python utilities are lowercase. SELECT, FROM, WHERE, JOIN, and ON_CONFLICT_DO_UPDATE are uppercase because they are SQL. cygnet.save, cygnet.get, cygnet.op, and cygnet.lit are lowercase because they are Python. There is no base class to inherit, no metaclass, and no implicit query firing off when you touch an attribute. You define a plain dataclass, wrap it once, and write queries that read like the SQL they become.
1 import dataclasses 2 from typing import Annotated 3 import cygnet 4 5 @dataclasses.dataclass 6 class Account: 7 id: Annotated[int, cygnet.DBKey] 8 name: str 9 email: str 10 11 AccountTable = cygnet.Table(Account) 12 13 # Reads like SQL, comes back as Account objects: 14 accounts = await cygnet.SELECT(db).FROM(AccountTable).WHERE( 15 (AccountTable.name == "Fred") & (AccountTable.id > 10) 16 )
When you want to know exactly what hit the database, .sql() tells you, with no round trip:
1 sql, params = ( 2 cygnet.SELECT(db, AccountTable.name).FROM(AccountTable) 3 .WHERE(AccountTable.id == 1).sql() 4 ) 5 # "SELECT accounts.name FROM accounts WHERE (accounts.id = $1)", [1]
There is no query DSL behind the chaining. Every verb builds a small tree of renderable nodes that emit parameterised PostgreSQL in one left-to-right pass over a single shared parameter list. Values are always parameters; you never interpolate user data into the string.
PostgreSQL-only, on purpose
Portable ORMs pay for portability in lowest-common-denominator SQL: whatever the weakest supported database can do, plus leaky workarounds for the rest. Cygnet declines the trade. It targets one database, so the things Postgres is good at are first-class verbs instead of extensions you bolt on: JSONB and array operators, full-text search, LATERAL joins, ordinary and recursive CTEs, window functions, ON CONFLICT upserts, RETURNING, FOR UPDATE SKIP LOCKED for queue workers, and a .stream() that pulls rows through a server-side cursor instead of buffering the whole result into memory. None of that has to be smuggled past a portability layer, because there isn’t one.
It fails loudly, on purpose
The other thing you’ll notice is that Cygnet is built to refuse the quiet mistake. The headline rail: UPDATE and DELETE require an explicit .WHERE(). Forget it and you get a ValueError, not a full-table mutation. The check fires at render time and at .sql() time, so you can’t inspect your way around it. When you really do mean every row, you have to say so:
1 await cygnet.DELETE(db).FROM(LogTable).WHERE(cygnet.all)
The same instinct runs through the rest. A typo’d field name in a SET or INSERT raises rather than silently emitting a no-op. An empty SET clause raises. An application-assigned key left None at insert raises. Comparing a column to None renders IS NULL instead of = $1, so a value that turns out to be None at runtime does the obvious thing rather than quietly matching zero rows. The worldview is consistent: the database should tell you loudly when you’ve done something dumb, and the library’s job is to make sure it gets the chance.
The payoff of being an AST
Because a query is a tree of renderables over one shared parameter list, the pieces compose without special cases. A SELECT builder is itself a renderable, so it drops into any expression position as a subquery with no wrapper method; EXISTS, IN (SELECT …), and scalar subqueries all just work, and the $N numbering threads correctly through the inner-then-outer pieces because everything appends to the same list in document order. Extending the query surface doesn’t mean touching internals, either: cygnet.op, cygnet.ops, and cygnet.lit reach any operator or raw fragment that Postgres has and Cygnet hasn’t gotten around to wrapping. Those are trusted strings, stated plainly: lit() and operator names are not parameterised, so you never build them from user input.
The connection is yours to manage. Cygnet’s core imports no database driver at all; the db object is a small duck-typed protocol, and the psycopg3 reference adapter is an optional extra. pip install cygnet-orm is driver-free; pip install 'cygnet-orm[psycopg]' adds the reference adapter; or you implement the protocol yourself and Cygnet never pulls psycopg into your tree. Python 3.12 or newer, PostgreSQL 14 or newer, MIT licensed.