>What actually breaks when you move a FastAPI app from SQLite to PostgreSQL

SQLite is a genuinely good default for a small FastAPI backend — zero infrastructure, fast for a single-writer workload, trivial to back up as one file. The trap is that SQLAlchemy makes it easy to write code that looks database-agnostic and isn't. Here's exactly what broke when a backend of mine moved from SQLite to PostgreSQL, because I hit every one of these in production first and then had to explain why login was returning a 500.

1. server_default=sa.text("now()") is not portable

now() is a PostgreSQL function. Written directly as a column default, SQLAlchemy compiles it into the DDL literally — so the SQLite table ends up with DEFAULT (now()), and SQLite has no such function. Every insert that relies on that default raises OperationalError: unknown function: now(). The fix is server_default=sa.func.now(), which SQLAlchemy compiles per dialect — CURRENT_TIMESTAMP on SQLite, now() on PostgreSQL. Same source line, correct SQL on both.

2. greatest() only exists on PostgreSQL

A like-counter that clamps at zero with func.greatest(0, BlogPost.likes - 1) works perfectly against Postgres and throws no such function: greatest against SQLite. A CASE expression does the same job and is portable:

from sqlalchemy import case
clamped = case((BlogPost.likes + delta < 0, 0), else_=BlogPost.likes + delta)

3. CAST(column AS DATE) behaves differently per dialect

Grouping page views by day with cast(PageView.viewed_at, Date) is a real cast on PostgreSQL, but on SQLite it's effectively a no-op — the driver hands back the raw timestamp string, and SQLAlchemy's date processor then fails trying to parse it as a plain date. SQLite needs func.date(column) instead, which returns a 'YYYY-MM-DD' string directly. The fix has to branch on the dialect name at runtime, because there's no single expression that's simultaneously correct on both.

4. JSON containment isn't one thing

Filtering blog posts by tag with SQLAlchemy's JSON.contains() compiles to a working @> query on PostgreSQL — and to nothing that matches anything on SQLite, because SQLite's json_each-based containment isn't wired up the same way through the ORM. The fix that survives both: keep a denormalized tags_text column formatted as |tag|tag|, synced on every write via an ORM event listener, and match it with a plain LIKE '%|tag|%'. Less elegant than native JSON operators, portable by construction.

The pattern behind all four bugs

Every one of these compiled without error, passed a quick manual test against whichever database was open in a terminal that day, and only failed once real production traffic hit the code path that used the default, the aggregate, the cast, or the containment check. That's the actual danger of "SQL that looks portable" — it doesn't fail at write time, it fails at the specific runtime path nobody re-tested after switching databases.

What would have caught this earlier

Tests that run against the real, migrated schema — via Alembic, not metadata.create_all() — on whichever database staging actually uses. A schema built from ORM metadata never re-creates DDL bugs baked into a migration file; only running the migration does. That single change turned four production incidents into four failing tests.

Takeaway

"It works locally" only means it works against whatever database is open in your terminal. If production runs a different engine than local development, the SQL that reads as portable is exactly the SQL worth testing against both.