agent's migration config pointed at sqlite while production runs postgres

Not specified by the original author.

TL;DR

Sqlite and postgres are different databases with different DDL, types, and transactional behavior: a migration green on sqlite proves nothing about postgres. Reproduce the failure against real postgres (a container is fine), fix the dialect-specific statements, and add a CI job that runs the migration chain against postgres. Local-dev convenience must never be the migration test.

The query

agent's migration config pointed at sqlite while production runs postgres - the migration passed locally and failed in prod

Use this when

• Migration passed on sqlite but failed on postgres
• Local/prod database mismatch hid a migration failure
• Need postgres parity for migration testing
• An agent tested migrations only on sqlite

Not for

• Failures that reproduce on postgres locally (fix the migration directly)
• Application queries behaving differently (a query problem, not the migration)
• Intentional sqlite-only tooling (then prod must not be postgres)

Steps

1. Reproduce against real postgres

docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=[pw] postgres:16

Point the migration config at it and run the chain from zero. Expected output: the production failure reproduces locally. Common hits: ALTER COLUMN TYPE syntax, concurrent index builds, transactional DDL assumptions, type names sqlite accepted loosely.

2. Fix the dialect differences

Rewrite the failing statements in postgres dialect: explicit type casts in ALTER COLUMN ... TYPE ... USING, CREATE INDEX CONCURRENTLY outside transactions, postgres-native types. Keep the migration postgres-first; add sqlite compatibility only if local dev truly needs it.

Expected output: the chain runs clean on postgres from zero.

3. Find every other sqlite-ism in the chain

Grep the migrations for sqlite-isms: bare ALTER TABLE ... RENAME that postgres handles differently, type names like TEXT-for-everything, missing USING clauses, batch-mode assumptions from alembic's sqlite workaround.

Expected output: a list, fixed one by one, each verified on postgres.

4. Add postgres migration testing to CI

A CI job spins up postgres (service container) and runs the full migration chain from zero plus up/down on every change. Sqlite may stay for fast unit tests, but it is no longer the migration gate.

Expected output: the next dialect bug breaks the build, not production.

5. Make the agent target-aware

The agent's migration procedure starts with "what database does production run", and every migration is tested against that database family. "It passed on sqlite" stops being evidence.

Expected output: migrations are written for the production dialect from the first draft.

Tool compatibility

The sqlite-vs-postgres gap applies to alembic, django, rails, and any ORM-backed migration tool with a sqlite dev default.

Variant phrasings

migration passed sqlite failed postgres

Steps 1 and 2: reproduce on real postgres, fix the dialect.

test migrations against postgres in CI

Step 4: service-container postgres running the full chain from zero.

alembic sqlite vs postgres migration differences

Step 3: audit the chain for sqlite-isms; alembic's batch mode exists precisely because of them.

Why it happens

Sqlite prioritizes permissiveness (dynamic typing, transactional DDL, lenient ALTER) while postgres prioritizes strictness (rigid types, lock-heavy DDL, exact syntax). A migration is a program written in a dialect: running it against a different dialect tests a different program. The mismatch is invisible until production because sqlite never complains about the things postgres rejects.

Edge cases

• Alembic's batch mode for sqlite rewrites migrations at runtime: a migration that relies on batch mode may behave differently on postgres in subtle ways. Read the rendered SQL.
• Postgres version matters too: test against the production major version, not just "postgres".
• The reverse mismatch (dev on postgres, prod on mysql) has the same shape with different specifics: the rule is "test on the production family", not "postgres specifically".

Provenance

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

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

Thread query: agent's migration config pointed at sqlite while production runs postgres - the migration passed locally and failed in prod

TL;DR: Sqlite and postgres are different databases with different DDL, types, and transactional behavior: a migration green on sqlite proves nothing about postgres. Reproduce the failure against real postgres (a container is fine), fix the dialect-specific statements, and add a CI job that runs the migration chain against postgres. Local-dev convenience must never be the migration test.

Not specified by the original author.

TL;DR

The agent trusted an environment variable and migrated the wrong database. First assess exactly what ran against production (read the state table, diff the schema), then make wrong-target runs structurally impossible: a wrapper that prints the resolved host and database name and requires an explicit environment flag, separate credentials per environment, and a pre-flight check that aborts on production unless explicitly confirmed.

The query

agent ran the migration against production because DATABASE_URL pointed at prod in its shell - how to guard against wrong-target runs

Use this when

• An agent migrated production when it meant staging
• DATABASE_URL pointed at prod in the agent's shell
• Need a wrong-target guard for agent-driven migrations
• Post-incident: what ran against prod and how to prevent repeats

Not for

• A migration that failed on the intended database (fix the migration)
• Application code connecting to the wrong database at runtime (a config bug, not a migration guard)
• Intentional production migrations (those need a runbook, not a guard)

Steps

1. Assess exactly what ran against production

SELECT version_num FROM alembic_version;

(or the equivalent state table for your tool: flywayschemahistory, prismamigrations, django_migrations). Compare against what production should be at, and diff the schema against staging.

Expected output: the list of migrations that applied to prod. If the set matches what prod was supposed to get anyway, the incident is a process failure with no schema damage. If unexpected migrations applied, treat it as a production schema incident: verify the application still works against the new schema.

2. Contain the blast radius

Rotate any credentials the agent had, check for application errors against the changed schema, and decide whether to roll forward (keep the migrations, deploy matching code) or roll back. Do not roll back reflexively: a rollback that the application is not built for is a second incident.

Expected output: a deliberate decision, recorded, with the application verified healthy in its final state.

3. Build the target-verification wrapper

Every migration the agent runs goes through a wrapper script that resolves the connection string, prints the host and database name, and requires an explicit --env flag:

#!/bin/bash
# migrate.sh: refuses to run without --env, prints the target, confirms prod

The wrapper aborts when the resolved host matches the production pattern unless a --i-understand-this-is-prod flag is also passed. Expected output: running the wrapper without flags prints the target and exits; there is no silent default that can point at prod.

4. Separate credentials per environment

Give the agent's staging and development shells credentials that physically cannot reach production: different users, different hosts, network rules. An agent cannot migrate a database it cannot connect to.

Expected output: even a confused agent holding the staging credential gets "connection refused" from prod, not a migrated prod.

5. Add the pre-flight check to the agent's procedure

Before every migration step, the agent prints the target (host, database, current version) and waits for the wrapper's confirmation gate. Bake it into the runbook so no migration command is ever typed raw.

Expected output: the runbook's migration step starts with target verification, and skipping it is impossible without editing the runbook.

Tool compatibility

Tool-agnostic: the guard pattern works for alembic, flyway, prisma, django, rails, and any CLI that reads a connection string from the environment.

Variant phrasings

agent ran migration against production wrong DATABASE_URL

Steps 1 and 2: assess what applied, then decide roll-forward vs rollback deliberately.

how to prevent migrations hitting the wrong database

Steps 3 and 4: a verifying wrapper plus credentials that cannot reach prod. Either alone is insufficient.

migration ran against prod instead of staging

Step 1: read the state table on prod first. Everything else depends on knowing exactly what applied.

Why it happens

Agents inherit shell environments wholesale, and DATABASE_URL is just a string: nothing in the migration tools verifies that the string points where the operator intended. Humans catch this with context ("this terminal is the prod one"); agents have no such context unless the procedure manufactures it. The fix is making the target explicit and verified, not hoping the variable is right.

Edge cases

• A wrapper that can be bypassed with an env var is decoration: the confirmation for prod must be a separate explicit flag, not the absence of a mistake.
• CI jobs need the same guard: a CI migration job with prod credentials and a default env is the same incident on a schedule.
• After the incident, audit every other agent runbook for raw migration commands: the same pattern usually exists in more than one place.

Provenance

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

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

Thread query: agent ran the migration against production because DATABASE_URL pointed at prod in its shell - how to guard against wrong-target runs

TL;DR: The agent trusted an environment variable and migrated the wrong database. First assess exactly what ran against production (read the state table, diff the schema), then make wrong-target runs structurally impossible: a wrapper that prints the resolved host and database name and requires an explicit environment flag, separate credentials per environment, and a pre-flight check that aborts on production unless explicitly confirmed.

Not specified by the original author.

TL;DR

The dev ini pointed at (or configured for) the wrong database, so a dev-configured alembic ran against production. Assess what ran and with what settings, then separate configs per environment: one ini per environment (or one ini reading the URL from the environment), a wrapper that selects by explicit --env flag, and a guard that refuses dev-configured runs against production hosts.

The query

agent ran alembic with the dev alembic.ini against the production database

Use this when

• Alembic ran with dev alembic.ini against production
• Wrong ini file targeted the wrong database
• Need per-environment alembic configs
• An agent used the dev config on prod

Not for

• The right config failing on prod (fix the migration)
• A dev ini used on a dev database (that is its job)
• Connection-string confusion without ini involvement (guard the URL directly)

Steps

1. Assess what ran and under what settings

Read the dev ini: which sqlalchemy.url did it resolve to, and what settings differ from prod (echo, pool sizes, transaction settings)? Check the state table on the affected database to see which revisions applied.

Expected output: the exact database hit, the revisions applied, and any dev-only settings (like verbose echo or aggressive pooling) that touched prod.

2. Contain and reconcile

If unexpected revisions applied to prod, reconcile the schema and the history (inventory, hand-apply or stamp, as with any out-of-place migration). Rotate any credentials the dev config exposed. Expected output: prod's schema and history are consistent and deliberate again.

3. Separate configs per environment

Create alembic.prod.ini (and staging), or keep one ini that reads the URL from the environment and nothing else. No ini file should contain a production URL as a default a dev command can pick up accidentally.

Expected output: there is no single file that a bare alembic command can load and hit prod with.

4. Add the env-selecting wrapper

The agent runs migrations through a wrapper requiring --env, which selects the ini and prints the resolved target for confirmation. The wrapper refuses --env prod without an additional explicit confirmation flag.

Expected output: running alembic "the normal way" cannot reach prod; reaching prod requires two deliberate flags.

5. Make the dev ini unable to reach prod structurally

The dev ini's URL should resolve to a local database only (YOUR_HOST, or a socket). If the dev config cannot address prod at the network level, the wrong-ini mistake becomes a connection error, not an incident.

Expected output: the entire class of mistake degrades to "connection refused".

Tool compatibility

alembic 1.x. The per-environment config pattern applies to any ini-based tool.

Variant phrasings

alembic dev ini against production

Steps 1 and 2: assess what ran, reconcile prod.

per-environment alembic config

Step 3: one ini per environment, no prod URL as any default.

prevent alembic hitting wrong database

Steps 4 and 5: env-selecting wrapper plus a dev ini that cannot route to prod.

Why it happens

Alembic resolves its database from the ini file, and a bare alembic command loads alembic.ini from the current directory: whatever that file says is the target. When the dev ini is the default and it can address prod (via an env var someone exported, or a URL someone pasted), the tool does exactly what it was configured to do. The bug is a configuration that makes the wrong target reachable by default.

Edge cases

• Env-var-driven URLs are the usual vector: the ini reads sqlalchemy.url from DATABASE_URL, and the agent's shell had prod's. Fix the shell hygiene too, not just the ini.
• A wrapper the agent can bypass with an env var is decoration: the prod confirmation must be a separate explicit flag.
• Audit other ini-driven tools in the agent's reach (pytest configs, deploy scripts): the same default-config hazard usually exists in more than one place.

Provenance

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

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

Thread query: agent ran alembic with the dev alembic.ini against the production database

TL;DR: The dev ini pointed at (or configured for) the wrong database, so a dev-configured alembic ran against production. Assess what ran and with what settings, then separate configs per environment: one ini per environment (or one ini reading the URL from the environment), a wrapper that selects by explicit --env flag, and a guard that refuses dev-configured runs against production hosts.