alembic upgrade --sql produced different SQL than the actual upgrade ran - the agent cant trust the preview

TL;DR: Treat --sql as an approximation, not a transcript. Offline rendering cannot know the server version, server-side defaults, or anything the migration computes in Python. For the real SQL, run the upgrade against a staging copy of the database with SQL echo logging on and capture the log - that is ground truth.

alembic upgrade --sql produced different SQL than the actual upgrade ran - the agent cant trust the preview

1. Diff the --sql output against what the real upgrade did, and categorize the differences: rendering differences (quoting, default rendering), batch-mode differences, and statements that only exist in one version.

Expected: the differences fall into recognizable categories rather than looking random.

2. Set up a staging copy of the target database at the same server version, and run the upgrade there with echo enabled.

# in env.py or via the alembic context: configure echo / logging of SQL
alembic upgrade head   # against the staging database, with SQL echo on

Expected: the log shows every statement the upgrade actually executed, in order.

3. Compare the echoed log against the --sql output. The echoed statements are what production will see.

Expected: you can point at each --sql discrepancy and explain it (offline default rendering, batch mode emitting different statements, Python-side data logic emitting nothing in --sql).

4. Use the echoed log as the review artifact going forward, and keep --sql for rough shape only.

Expected: reviewers see the true statement list, including anything --sql could not render.

Use this when

• alembic upgrade --sql output does not match the real upgrade
• You need an accurate SQL review artifact for a migration
• Offline SQL and online behavior diverge on defaults or batch mode
• A migration does Python-side data work that --sql cannot show

Not for this skill when

• The SQL matches but the migration still fails - that is a runtime problem (locks, constraints, data), not a rendering problem
• You need the SQL without any database at all - then --sql is the only option; accept its limits and document them
• The divergence is in data migration results rather than DDL - echo logging shows statements, not row outcomes; verify data separately
• You are comparing --sql across alembic versions - version skew in rendering is a separate issue

Variant phrasings

• alembic upgrade --sql different from actual upgrade
• alembic offline sql does not match real migration
• cant trust alembic --sql preview
• alembic echo vs --sql differences

Why it happens

upgrade --sql renders SQL offline: no connection, no server version, no knowledge of server-side defaults, and no execution of the Python code inside data migrations. Batch mode, dialect quirks, and anything the migration computes at runtime (conditional DDL, data-driven statements) either render differently or not at all. The real upgrade negotiates all of that with the live server, so its statement stream is the only accurate record.

Edge cases

• Data migrations (op.execute with Python logic, bulk updates driven by queries) are invisible to --sql by design. Only a staging run with echo shows them.
• The staging database must match production's server version and configuration, or the echo log inherits the same rendering skew you are trying to escape.
• Echo logging shows statements, not their effects. A statement can log cleanly and still fail on production data - keep a data-verification step.
• If reviewers only ever see --sql, they will approve migrations whose real SQL they never saw. Make the echo log the standard review artifact.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_-JYWkZKN3IENwxgjbS1YRA