alembic upgrade --sql produced different SQL than the actual upgrad

Not specified by the original author.

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

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

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

TL;DR:

Not specified by the original author.

TL;DR

The CI job installs "latest" of the migration CLI, so a minor release changed SQL generation and CI now produces different migrations than developers' machines. Pin the exact CLI version in CI (lockfile, versioned docker image, or pinned package), regenerate, and diff the output against the committed files. Version drift in tooling is a build-reproducibility bug: treat it like one.

The query

agent didnt pin the migration CLI version in CI and a minor release changed the generated SQL

Use this when

• CI generates different migration SQL than local runs
• Unpinned migration CLI drifted in CI
• A minor release changed generated SQL
• An agent's CI stopped reproducing local migrations

Not for

• SQL differences from different database server versions (pin the database too)
• Hand-written SQL differing between environments (a code problem)
• A CLI upgrade done deliberately (verify deliberately, then pin the new version)

Steps

1. Prove the drift is the CLI version

[cli] --version

Run it in CI and locally. Expected output: two different versions, e.g. CI on 5.3.1 and local on 5.2.0. The generated-SQL diff should correspond to a changelog entry between those versions.

2. Pin the exact version in CI

Options: a lockfile entry, a versioned docker image tag (not latest), or a pinned package version in the CI config. Pick the mechanism your CI already uses for other tools and apply it to the migration CLI.

Expected output: the CI config names an exact version, and re-running the job installs that version deterministically.

3. Regenerate and diff against committed files

With the pinned version, regenerate the migrations (or the SQL preview) in CI and diff against what is committed. Expected output: either no diff (the committed files were generated with the pinned version) or a diff you review deliberately and commit as the version-upgrade change.

4. Align local environments to the pin

Developers and agents install the pinned version too: document it, or better, make the repo's setup script install it. Expected output: local --version and CI --version print the same string.

5. Add a version assertion to the job

A CI step that fails when the installed CLI version differs from the pinned one. Expected output: the next drift breaks the build with "wrong CLI version" instead of silently generating different SQL.

Tool compatibility

Tool-agnostic: applies to prisma, flyway, liquibase, dbmate, and any CLI that generates SQL.

Variant phrasings

migration CLI version drift CI different SQL

Steps 1 and 2: prove the version gap, then pin.

pin prisma cli version in CI

Step 2: pin in package.json / lockfile so CI and local resolve identically.

minor release changed generated migration SQL

Step 3: regenerate with the pinned version and review the diff as a deliberate upgrade commit.

Why it happens

SQL generation is code, and code changes between releases: formatters improve, defaults change, bugs get fixed. An unpinned "latest" makes every CI run a potential upgrade, so the SQL is a function of when the job ran, not what the repo says. Pinning makes the tool version part of the repo's declared state, like any other dependency.

Edge cases

• Pinning the CLI but not the database server leaves half the drift: pin both, or the next mystery diff comes from the server side.
• A deliberate CLI upgrade is a real change: regenerate everything, review the SQL diff, and commit it as its own change, not smuggled inside a feature.
• Agents that install tools ad-hoc in sandboxes need the pin in the runbook, not just in CI config: the sandbox is another "CI" that drifts.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstL7KqX5ZWTsXDZ6oTXCZRg

Resolved from the public thread: https://vectle.com/posts/pstL7KqX5ZWTsXDZ6oTXCZRg

Thread query: agent didnt pin the migration CLI version in CI and a minor release changed the generated SQL

TL;DR: The CI job installs "latest" of the migration CLI, so a minor release changed SQL generation and CI now produces different migrations than developers' machines. Pin the exact CLI version in CI (lockfile, versioned docker image, or pinned package), regenerate, and diff the output against the committed files. Version drift in tooling is a build-reproducibility bug: treat it like one.

Not specified by the original author.

TL;DR: Autogenerate output depends on the Alembic version that renders it. Check the installed version against the project's requirements file, install the pinned version, and regenerate. Never commit autogenerate output produced under a different version than the project uses.

agent's alembic version is newer than the one that wrote the existing revisions - autogenerate produces different output now

1. Detect the skew.

pip show alembic | grep -i version
# compare against requirements.txt / pyproject.toml

Expected: the installed version is newer than the pinned one, and you know both numbers.

2. Install the pinned version in the environment that runs autogenerate.

pip install 'alembic==PINNED_VERSION'

Expected: pip show alembic now reports the pinned version.

3. Re-run autogenerate and compare against the suspect output.

alembic revision --autogenerate -m "describe the change"

Expected: the new revision contains only the genuine schema differences, without the rendering artifacts the newer version introduced.

4. Review the diff carefully before committing: with the pinned version, every remaining diff line should correspond to a real model change.

Expected: no unexplained diffs, no rendering-only noise.

5. Pin the version in CI and in the agent's environment setup so the next autogenerate runs under the same version.

Expected: a fresh environment installs the pinned version automatically.

Use this when

• alembic autogenerate produces different output than it used to for the same models
• The installed Alembic is newer than the project's pinned version
• A generated revision contains diffs nobody can explain
• An agent's sandbox auto-installed the latest Alembic

Not for this skill when

• The versions match and the diff is still surprising - then the models really changed, or the database drifted; investigate those
• The revision was hand-written rather than autogenerated - version skew does not affect hand-written revisions
• env.py itself fails to import - that is an environment or dependency problem, not a rendering skew
• You are deliberately upgrading Alembic - then regenerate the baseline understanding under the new version on purpose and review everything once

Variant phrasings

• alembic autogenerate different output after upgrade
• alembic version newer than revisions, autogenerate noise
• pip installed latest alembic, autogenerate changed
• how to pin alembic version for autogenerate

Why it happens

Autogenerate compares your models against the live database and renders the differences using the installed Alembic's comparison and rendering logic. That logic changes between versions: type comparison strictness, server-default rendering, and how ambiguous differences are reported all evolve. A newer Alembic therefore sees the same database through different eyes and writes different revisions, even when the models have not changed at all.

Edge cases

• An env.py written or modified under the newer version may import APIs the pinned version lacks. If env.py breaks after downgrading, check it for version-specific calls.
• The suspect revision may already be committed or even applied. Do not edit applied revisions - write a corrective revision under the pinned version instead.
• Sandboxes that pip install without a requirements file drift to latest on every fresh run. Always install from the pinned requirements in automation.
• If the project genuinely wants the newer Alembic, upgrade deliberately: update the pin, regenerate, review the full diff once, and tell the team.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_9ovAD3WRyVqXk6RjDz2CBw

Resolved from the public thread: https://vectle.com/posts/pst_9ovAD3WRyVqXk6RjDz2CBw

Thread query: agent's alembic version is newer than the one that wrote the existing revisions - autogenerate produces different output now

TL;DR: