Your ORM is fast. But is it right? Three silent data bugs in async Python stacks

#python #database #sqlite #orm

Your ORM is fast. But is it right? Three silent data bugs in async Python stacks

My last post was about making an ORM faster with a Rust engine, so let me open with an uncomfortable admission: for most services, the ORM is not your bottleneck. Your database is doing the real work, the network round-trip dwarfs your hydration loop, and if your p99 is bad it's almost certainly an index or an N+1, not the ORM.

What the ORM can be is quietly wrong. Not "throws an exception" wrong — that kind is easy. Wrong as in: your data is corrupted, no error was raised, and you find out three months later. I hit all three of the bugs below while building yara-orm, and every one of them exists right now in production apps that pass their test suites.

Trap 1: Your SQLite foreign keys are probably decorative

SQLite has supported foreign key constraints since 2009. It also ignores them by default. Enforcement is opt-in, per connection:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE author (id INTEGER PRIMARY KEY, name TEXT)")
db.execute("""CREATE TABLE book (
    id INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES author(id)
)""")

db.execute("INSERT INTO book (author_id) VALUES (42)")  # author 42 doesn't exist
# ...no error. You now have an orphaned row.

print(db.execute("PRAGMA foreign_keys").fetchone())  # (0,) — enforcement is OFF

The constraint is in the schema. The database just doesn't check it unless every single connection runs PRAGMA foreign_keys = ON first.

Why this bites ORM users specifically: your ORM happily generates the REFERENCES clause, so the schema looks protected, and ON DELETE CASCADE looks configured. Whether any of it does anything depends on whether your ORM (or you) sets the pragma on every pooled connection. SQLAlchemy, for example, is upfront that it doesn't do this for you — the docs tell you to hook connection creation and issue the pragma yourself. Django does it for you. Everything in between: go check. Seriously, run PRAGMA foreign_keys through your app's actual connection and look at the answer.

And "it's just SQLite, that's only for tests" is exactly the problem — your tests pass while enforcing nothing, then CI green-lights code that violates constraints your Postgres production DB will enforce.

In yara-orm the Rust engine enables enforcement on every SQLite connection it opens, because a foreign key you declared and didn't get is worse than no foreign key at all — it's documentation that lies.

Trap 2: The decimal that came back different

SQLite has no decimal type. So when your model says "this is money":

price = fields.DecimalField(max_digits=10, decimal_places=2)

…the ORM has to pick a physical representation, and the honest options are TEXT (exact, but you lose SUM() in plain SQL) or REAL (a float — fast, sortable, and wrong for money). Binary floats cannot represent most decimal fractions:

>>> 0.1 + 0.2
0.30000000000000004

Store prices through a REAL column and each individual value usually survives (rounding on the way out hides the error), but aggregate a few thousand of them — or compare for equality — and the drift is real and unexplainable to your accountant.

Again, the good ORMs don't hide this; SQLAlchemy literally emits a warning when you push Decimal values through SQLite. But a warning in a log nobody reads is a weak safety net for a correctness property. The test you want in your suite today:

from decimal import Decimal

await Order.create(id=1, total=Decimal("19.99"))
row = await Order.get(id=1)
assert row.total == Decimal("19.99")
assert isinstance(row.total, Decimal)   # not float!

yara-orm's contract is that a Decimal round-trips exactly, on every backend — the engine picks a lossless representation per database and refuses to route decimals through binary floats. That's not a performance feature. It's the feature the performance work is pointless without.

Trap 3: The migration that renamed your column by deleting it

You rename a field:

# before
name = fields.CharField(max_length=100)
# after
full_name = fields.CharField(max_length=100)

To you, that's a rename. To a schema-diffing migration tool, it's indistinguishable from "dropped name, added full_name" — unless the tool explicitly tries to detect renames. The naive autogenerated migration is:

ALTER TABLE author DROP COLUMN name;      -- your data leaves the building here
ALTER TABLE author ADD COLUMN full_name VARCHAR(100);

Runs clean in dev (three rows, who cares). Runs clean in staging. Runs clean in prod, too — DROP COLUMN doesn't raise just because the column held ten million customer names.

Migration tools across the Python ecosystem handle this with everything from silent drop-and-add to interactive "did you rename this?" prompts, and the behavior changes between versions. Which means the rule is: read the SQL of every autogenerated migration before it touches data you can't regenerate. Alembic's docs say it, Aerich's issue tracker testifies to it, and anyone who's been paged for it will say it louder.

yara-orm's migration engine does rename detection out of the box (same-type column disappearing and appearing in one diff is flagged as a rename, not a drop), and generates partial indexes without hand-written SQL. But I'll say it about my own tool too: read the generated SQL anyway. A migration is a git push --force for your data.

The theme

None of these are exotic. They share one shape: the declaration and the behavior are two different things. The schema declares a foreign key that isn't checked. The model declares a decimal that's stored as a float. The code declares a rename that executes as a drop. Every one is invisible in a green test suite unless you test the property, not the API.

If you take nothing else away, steal these three checks:

PRAGMA foreign_keys through your app's real connection — expect 1.
Round-trip Decimal("19.99") and assert type and value.
cat the SQL of every autogenerated migration before it ships.

Where yara-orm fits (and honest caveats)

I built yara-orm as a Tortoise-style async ORM — Django-ish models, chainable querysets — with the per-query hot path in Rust, across PostgreSQL, MySQL, MariaDB, SQLite, Oracle, and SQL Server. It's genuinely fast (2–9× vs. popular pure-Python ORMs on common ops in our PostgreSQL benchmarks — methodology here, scrutiny welcome). But speed is the headline, not the reason: the three guarantees above — enforced SQLite FKs, exact decimal round-trips, rename-aware migrations — are the parts I'd actually miss.

Caveats, because every tool has them: it's younger and smaller than SQLAlchemy's ecosystem, the async model differs from sync ORMs in ways that matter for session/transaction habits, and if your workload is single-row point queries you're near the network round-trip floor where no ORM's speed matters.