ban-data-in-migration

What triggers it

Any InsertStmt, UpdateStmt, or DeleteStmt in a migration file where at least one other statement in ctx.allStatements is DDL (per the isDDL helper).

What does not

DML in a file that contains no DDL at all — a migration that's purely a data backfill is left alone, since there's nothing to separate it from.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

None named for the DML itself — the concern is that it runs inside the same file, and often the same transaction, as DDL holding a much stronger lock, so the DML's own runtime adds directly to how long that lock is held.

Why it matters

Mixing DDL and DML in the same migration makes rollback harder, increases lock duration, and violates separation of concerns. Data migrations should be in separate files with explicit rollback strategies.

The operation, and the mitigation

CREATE TABLE settings (key TEXT, value TEXT);
INSERT INTO settings VALUES ('version', '1.0');

-- migrations/003_schema.sql (DDL only)
CREATE TABLE settings (key TEXT, value TEXT);

-- migrations/004_seed.sql (DML only)
INSERT INTO settings VALUES ('version', '1.0');

This operation is irreversible, so there is no syntax that makes it safe. The second block is what care looks like — and MigrationPilot still flags it. The mitigation is process: confirm nothing reads the object, keep a way back, and do it in a window where you can watch.

What it assumes

Assumes any DML riding alongside DDL is worth splitting out, whether it's a large backfill or a single-row seed insert — the rule can't distinguish the two, so a trivial INSERT next to a fast ALTER TABLE gets flagged the same as an unbounded UPDATE.

What backs this rule

Every rule is a claim about PostgreSQL, so it carries what the claim rests on: a handbook chapter that cites the manual, the incidents that put it there, and the version it was last checked against.

What the CLI prints

⚠ [MP080] WARNING (line 2)
  INSERT on "settings" in a DDL migration file. Separate schema changes and data changes into different migration files.

  Safe alternative:
  -- Move data manipulation to a separate migration file:
  -- migrations/003_schema_change.sql (DDL only)
  -- migrations/004_data_backfill.sql  (DML only, with batching)

  Why: Mixing DDL (schema changes) and DML (data changes) in the same migration makes rollback harder, increases lock duration, and violates separation of concerns. If the DDL portion fails, the data changes may have already been applied. Data migrations should be in separate files with explicit rollback strategies and batching for large datasets.
  Docs: https://migrationpilot.dev/rules/mp080

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP080
CREATE TABLE settings (key TEXT, value TEXT);

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP080: false

# or keep it, and downgrade it
rules:
  MP080:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.