Fully typed deep relationship navigation in Python. No codegen. No type-checker plugin.
Docs (English and Spanish): https://velezanthony.github.io/snake-orm/
Truck.maker.nation.name == "España" # SnakeExpr[str] -> SnakeConditionSELECT t0."id", t0."model", t0."maker_id" FROM "public"."trucks" AS t0
JOIN "public"."makers" AS t1 ON t0."maker_id" = t1."id"
JOIN "public"."nations" AS t2 ON t1."nation_id" = t2."id"
WHERE t2."name" = %sMypy checks it. Pyright checks it. Pylance autocompletes it. The Django equivalent,
filter(maker__nation__name="España"), is a string: no autocomplete, no check, and renaming
nation fails in production.
Requires Python 3.11+. SQLite ships with the standard library, so nothing needs to be running to start.
pip install snake-orm==0.1.0b1 # or: pip install --pre snake-ormThe distribution is snake-orm and the package is snakeorm: import snakeorm.
The version is a beta, and the pin is the point: a preliminary is not picked up by a plain
pip install snake-orm, so nobody upgrades into it by accident while the API is still moving.
From a checkout, to work on the ORM itself:
uv sync --all-extras --all-groups
uv run pytest # suite
uv run mypy . # must pass
uv run ruff check . # must passInstallation → first model → migrations.
@snake_model(table="makers")
class Maker(SnakeModel):
id: SnakeColumn[int] = snake_auto()
name: SnakeColumn[str] = snake_str(unique=True)
nation_id: SnakeColumn[int] = snake_int()
nation: SnakeToOne[Nation] = snake_to_one(nation_id)
trucks: SnakeToMany[Truck] = snake_to_many("maker")
snake_link() # once, after importing every modelThe type always comes from the annotation. snake_column() only adds SQL information.
uv run snakeorm makemigrations --models myapp.models --name initial
uv run snakeorm migrate --models myapp.models --dsn "host=... dbname=..."
uv run snakeorm rollback --models myapp.models --dsn "..."Recursive descriptors whose __get__ returns a different type depending on the access:
user.car.name # instance -> the value -> str
User.car.brand.name == "x" # class -> a SQL expression -> SnakeConditionUser.car returns type[Car], so .brand.name re-triggers the class overload of Car's
descriptors. @dataclass_transform on the decorator types __init__. It is the manual equivalent
of TypeScript's mapped types.
The type system is the single source of truth: the class is compiled once into an immutable metadata graph, and the runtime never reflects on it again.
User.objects.annotate(num_posts=Count("posts"))
user.num_posts # -> Anyannotate returns "a User plus a num_posts: int". That is an intersection type. TypeScript
has it; Python does not. So there are three paths, and only three:
| Path | Dynamic names | Real typing | IntelliSense |
|---|---|---|---|
__getattr__ -> Any |
✅ | ❌ | ❌ |
| Declared names | ❌ | ✅ | ✅ |
| Type-checker plugin | ✅ | ✅ | partial |
Django took the first and patched it with the third (django-stubs). SnakeORM forbids the third by
thesis and takes the second, with a typed escape hatch.
Any is not typing, it is switching the checker off:
def __getattr__(self, name: str) -> Any: ...
u.agg.count_children * 2 # mypy: 0 errors
other: str = u.agg.count_children # mypy: 0 errors <- same value, as a strobject keeps the dynamic name and wakes the checker up:
def __getattr__(self, name: str) -> object: ...
u.agg.count_children * 2 # error: unsupported operand types for *
count: int = cast("int", u.agg.count_children) # explicit, and signed with your nameSnakeQuery(Nation).filter(Nation.makers.name == "SEAT")
# error: "SnakeCollection[Maker]" has no attribute "name" [attr-defined]In Django that compiles, runs and silently duplicates rows — which is what .distinct() is for. A
to-many exposes collection operations, not the child's columns:
q.filter(Nation.makers.any(Maker.name == "SEAT")) # any? -> correlated EXISTS
q.filter(~Nation.makers.any()) # none? -> NOT EXISTS
q.filter(Nation.makers.count() > 3) # how many? -> scalar subquery
q.include(Nation.makers) # load them -> select-in, 2 queries
q.include(SnakePrefetch(Nation.makers).then(Maker.trucks))
# one query per LEVEL (root + makers + trucks = 3), never one per parentOne row per parent. DISTINCT is never needed.
-- Nation.makers.any(Maker.trucks.any(Truck.model == "Ibiza"))
SELECT "id", "name" FROM "public"."nations" WHERE EXISTS (
SELECT 1 FROM "public"."makers" AS e0 WHERE e0."nation_id" = "nations"."id" AND EXISTS (
SELECT 1 FROM "public"."trucks" AS e1 WHERE e1."maker_id" = e0."id" AND e1."model" = %s))Implicit when the answer is unique. Explicit when there is more than one correct answer.
A to-one never changes the row count, so the JOIN is inferred. A to-many does, so the ORM does not
guess.
No silent N+1. An unloaded relation raises instead of querying:
truck.maker
# SnakeRelationshipNotLoaded: Relation 'maker' was not loaded.
# Use .include(Truck.maker) in the query.No F(). Class access already is an expression:
session.update_where(query, [(Counter.views, Counter.views + 1)])
# UPDATE "counters" SET "views" = ("views" + %s) WHERE ...Pairs, not a dict: SnakeExpr is unhashable because its __eq__ returns a SnakeCondition.
Many-to-many crosses a real model, never an implicit table:
tags: SnakeToMany["Tag"] = snake_to_many_through(through="PostTag", via="post", to="tag")The bridge is an ordinary model, so extra columns on it are ordinary fields from day one.
A bulk UPDATE/DELETE uses the filter and nothing else. With no WHERE it is refused, and
so is any other knob you set on the same query — limit(), order_by(), only(). Dropping what
you asked for would answer a different question without saying so: select the rows first, then
write by primary key.
annotate() counts with a correlated subquery, not a LEFT JOIN — a parent with no children
comes out 0 instead of disappearing.
A view is a read-only model, and navigable. @snake_view maps a VIEW with typed columns,
navigable both ways; session.add/update/delete reject it in the type, because writing requires
a SnakeModel. CreateView/AlterView/DropView live in the migrations.
Migration history is .py, because python_type is a Python type. JSON would need a
name↔type registry: a second type system, parallel to Python's and worse.
Declare the result class — you choose the name, Python chooses the type:
@snake_result
class RealmStats(SnakeResult[Realm]):
realm: Realm
forge_count: int
rows = session.annotate(query, RealmStats, forge_count=Realm.forges.count())
rows[0].forge_count # int, with IntelliSense
rows[0].realm.name # str, navigation intactFor genuinely dynamic names, the escape hatch is explicit:
count = cast("int", realm.aggregate.forge_count) # object -> the cast is mandatoryWithout the cast it does not compile. Without annotating, it raises SnakeAggregateNotLoaded naming
the aggregates it does have.
| Queries | filter · order/limit/offset · group by/having · aggregates · annotate · explicit joins · include (to-one and to-many) · deep navigation · .any() · correlated subqueries · composite IN · only/defer · iterate (server cursor) · for_update · raw |
| SQL | window functions with frame · UNION/INTERSECT/EXCEPT · WITH RECURSIVE · CASE/COALESCE/NULLIF · text, date and math functions · json_get · ILIKE |
| Writes | insert/update/delete · upsert · bulk · RETURNING · savepoints · isolation levels · retry on transient conflict · refresh |
| Schema | composite PK and FK · polymorphic inheritance · views · triggers · indexes (partial, functional, GIN/GIST/BRIN) · checks · comments · enums · custom converters |
| Engines | PostgreSQL · MySQL/MariaDB · SQLite, all first class · Cap catalogue (Full/Degraded/Nope) · sync and async drivers · pool with pre_ping/recycle/timeout · statement timeout · EXPLAIN |
| Migrations | autodetected diff · atomic runner · RebuildTable for SQLite · RunPython with reverse · squash · cross-app dependencies · drift detection against the live database |
| Tooling | introspection and scaffold for the three engines · debug panel (ssr, envelope, timing, sidecar, otel) · index advisor · WSGI/ASGI/Django contrib · CLI |
Row by row, with links to the code, the test and the page: feature index.
Python class → Model Compiler → immutable metadata graph
↓
SQL · migrations · query · session · CLI
decorators/ metadata/ compiler/ registry/ linker/
query/ expressions/ sql/ dialects/ drivers/ session/ migration/ cli/
Two axes that never mix: the dialect decides how SQL is written (placeholders, quoting,
RETURNING, ON CONFLICT); the driver decides how it is executed. Models and graph are
engine-agnostic — anything Postgres-specific reaching the model is a bug.
SQL is always parameterised: emission returns (sql, params) and values never enter the string.
That kills injection, and it is what makes multi-engine possible.
Async reuses the whole core: SQL generation does not execute, so it has no colour.
Details in architecture; how to work here in CONTRIBUTING.
In test/typing/:
cases_positive.py— what must type, withassert_type.cases_negative.py— what must not compile, each line carrying its# EXPECT: <code>.- The runner requires mypy to report exactly those errors on exactly those lines, and pyright to reject the same ones.
Break Truck.maker.nation.name and the suite fails.
- Identity map and unit of work. Two queries to the same row return two objects. Writes are explicit; nothing is flushed behind your back.
- Lazy loading. Touching an unloaded relation raises. This is what makes N+1 impossible by default.
- Joined-table inheritance. Single table with a discriminator covers the polymorphism, and its
price is one rule: a child's own columns must allow
NULL. - Model default ordering. A hidden
ORDER BYyou did not write.
storage=NATIVEinsnake_enum(PostgresCREATE TYPE) is not built:ALTER TYPE ... ADD VALUEhas no inverse, so itsdown_sqlwould be a lie. The defaultCHECKis reversible.- CHECKs are declared outside the class body (
snake_checks(User, ...)). Inside it,__set_name__has not run and the column does not know its own name. - An expression does not carry its owning model in the type:
Maker.idandTruck.idare twins to the checker. Encoding the owner would break deep navigation and condition composition; where it matters (aggregates,.any()) it is validated at runtime. - No lazy loading, full-text search, JSON containment operators or array operators with a typed API.
The complete, current list is known limits — part of the contract, not a list of apologies.
Not published on PyPI. The distribution is named snake-orm, the import name is
snakeorm; both are explained in release.
Everything above is implemented and tested against real PostgreSQL, MySQL/MariaDB and SQLite. It has not run in production yet, and that is the one thing a repository cannot give itself.