SQL as Contract vs. Python as Procedure — a Short Note on Atlas vs. Alembic

SQL as Contract vs. Python as Procedure — a Short Note on Atlas vs. Alembic

The useful question isn’t “which tool is better?” but “what is our source of truth?” If your team wants the database schema itself—its tables, constraints, and indexes—to be the contract you review and protect, you’ll likely prefer a declarative, SQL‑first approach (Atlas‑style). If your team prefers to encode change as Python steps that live next to application code, you’ll likely prefer an imperative, script‑first approach (Alembic‑style).

Declarative (state‑based) thinking starts from the destination. You write down the desired schema and let the tool compute a plan from current → desired. Review happens in the database’s language (DDL), not through an ORM’s interpretation. Because the plan is derived, it tends to be predictable: the same desired state should produce the same kind of SQL, and policy checks can block destructive changes before they ship. Your mental model becomes simple: read the schema, read the plan, approve.

Imperative (history‑based) thinking starts from the journey. You maintain a timeline of migrations (upgrade()/downgrade()) often generated from ORM metadata. This favors expressiveness—you can mix DDL with data backfills and write logic in Python. The cost is managing the timeline itself: version directories, multiple heads, merges, and the subtle gap between what the ORM infers and what the database enforces. Dry runs help, but accuracy can depend on runtime context and project‑specific conventions.

Choosing SQL DDL vs. Python (sqlmodel) is really choosing where you pay complexity.

• With SQL‑first, complexity moves into planning and policy: you invest in deterministic diffs, lint rules for unsafe changes, and small, explicit configuration. You optimize for clarity in review and predictability at apply time.
• With Python‑first, complexity moves into authoring and orchestration: you own autogenerate quirks, environment glue (env.py, .ini), and the hygiene of your migration graph, in exchange for programmable migrations close to your app.

Both models work at scale; they simply optimize for different truths. If you’re tired of debating what the ORM meant and want reviewers to reason directly about the database, declarative SQL keeps the conversation honest: the contract is the DDL. If your changes routinely involve nontrivial data moves or app‑level logic, imperative Python keeps everything in one language and one place.

A quick heuristic:

• Pick declarative/SQL‑first (Atlas) when you value predictable plans, schema‑as‑contract, and minimal config.
• Pick imperative/Python‑first (Alembic) when you value programmable data migrations, tight ORM integration, and explicit step control.

In other words: pick the mental model first. The tool follows.