typeorm QueryFailedError: duplicate key value violates unique constraint during migration run

TL;DR

The migration tried to insert a row that already exists, usually because it re-ran or the backfill was not idempotent. Read the constraint name from the error to find the table and columns, locate the conflicting rows, and rewrite the insert to skip existing rows (ON CONFLICT DO NOTHING or a WHERE NOT EXISTS guard). Then re-run the migration; the guarded statement makes the retry safe.

The query

typeorm QueryFailedError: duplicate key value violates unique constraint during migration run

Use this when

• typeorm migration fails with "duplicate key value violates unique constraint"
• A backfill INSERT collides with rows from a previous partial run
• Re-running a migration double-inserts
• An agent's data migration is not idempotent

Not for

• Duplicate-key errors from live application traffic (a race or logic bug, not the migration)
• Unique violations on schema creation (two migrations creating the same constraint)
• Primary-key collisions in seed scripts run by hand

Steps

1. Read the constraint from the error

The error names the constraint, e.g. duplicate key value violates unique constraint "UQusersemail". That tells you the table and the columns.

SELECT * FROM [table] WHERE [column] IN (SELECT [column] FROM [table] GROUP BY [column] HAVING COUNT(*) > 1);

Expected output: the conflicting rows. If the duplicates are exact copies from a partial first run, the fix is idempotency; if they are genuinely different rows sharing a key, the data needs deduplication first.

2. Make the insert skip existing rows

Rewrite the migration's insert to be idempotent. In raw SQL inside the migration:

INSERT INTO [table] ([columns]) SELECT [columns] FROM [source]
ON CONFLICT ([key columns]) DO NOTHING;

Or guard with WHERE NOT EXISTS. Expected output: re-running the statement inserts only the missing rows and changes nothing that already exists.

3. Handle the partial-run leftovers explicitly

If the first run inserted half the rows before failing, decide per row: keep the good rows, fix or remove the bad ones. Do not delete-and-reinsert blindly when foreign keys reference the existing rows.

Expected output: the table holds exactly one correct copy of each row, and you can state the row count before re-running.

4. Re-run the migration and verify counts

npx typeorm migration:run -d [data source file]

Then compare row counts between source and target, and re-run the duplicate check from step 1.

Expected output: the migration completes, counts match, and the duplicate query returns zero rows.

5. Make future data migrations idempotent by default

Every backfill the agent writes gets the ON CONFLICT / NOT EXISTS guard and a pre-check query that reports how many rows it will insert. A migration that can run twice safely is a migration an agent can retry without fear.

Expected output: the next partial failure resumes with a plain re-run instead of a forensic session.

Tool compatibility

typeorm 0.3.x with postgres (ON CONFLICT). For mysql, use INSERT IGNORE or ON DUPLICATE KEY UPDATE; the idempotency principle is the same.

Variant phrasings

typeorm QueryFailedError duplicate key value violates unique constraint

Steps 1 and 2: identify the constraint, then guard the insert.

typeorm migration duplicate key on re-run

Step 2 is the fix and step 5 is the prevention: the migration must tolerate being run twice.

typeorm backfill duplicate rows partial run

Step 3: reconcile the half-inserted rows by hand before re-running, so the retry starts from a known state.

Why it happens

Unique constraints reject the second insert of the same key. Migrations that insert data without an existence guard are not idempotent, so any retry, partial failure, or double-apply collides with the rows the first attempt left behind. The constraint is doing its job; the migration was not written for retries.

Edge cases

• ON CONFLICT DO NOTHING silently skips rows that differ in non-key columns: if the first run inserted wrong values, skipping perpetuates them. Reconcile values, not just keys.
• A duplicate key on a primary key during a schema-plus-data migration can mean the schema change itself ran twice: check the migration history before assuming it is a data problem.
• For very large backfills, the idempotent re-run still scans everything: combine the guard with keyset batching so retries are fast.

Provenance

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