typeorm QueryFailedError: duplicate key value violates unique constraint

Not specified by the original author.

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

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

Thread query: 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.

Not specified by the original author.

postgres ERROR: duplicate key value violates unique constraint during backfill re-run

TL;DR: The backfill re-ran over rows it already wrote - it is not idempotent. Add ON CONFLICT DO NOTHING (or DO UPDATE) to the backfill INSERT so re-runs skip existing rows, and track completed batches in a checkpoint table so a re-run skips finished work entirely.

postgres ERROR: duplicate key value violates unique constraint during backfill re-run

Steps

1. Identify the conflicting rows: the error names the constraint and the key value. Check whether those rows came from the first backfill run. Expected: confirmed - the re-run is re-inserting its own earlier output, not bad source data.

2. Make the write idempotent:

   INSERT INTO target (id, data)
   SELECT id, data FROM source_batch
   ON CONFLICT (id) DO NOTHING;

   Use DO UPDATE instead of DO NOTHING if a re-run should refresh changed values. Expected: re-running the same batch inserts zero new rows and raises no error.

3. Add a checkpoint: record completed batch ids in a small table and skip batches already marked done at the start of each run. Expected: a second full run finishes quickly with every batch skipped.

4. Re-run the backfill. Expected: completes with no duplicate-key errors; the target holds each source row exactly once.

Use this when

• Duplicate-key errors appear on the second (or later) run of a backfill or data migration.
• The first run partially completed before stopping.
• The conflicting rows match what the earlier run wrote.

Not for this skill when

• Duplicate-key errors appear on the FIRST run - the source data itself has duplicates; dedupe the source.
• Application writes race the backfill and collide - that is a locking/coordination problem, not backfill idempotency.
• You do not recognize the constraint name - find which table and key it guards before changing the write.

Variant phrasings

• duplicate key violates unique constraint backfill rerun
• postgres on conflict do nothing backfill idempotent
• data migration rerun duplicate rows

Why it happens

Without a conflict policy or a checkpoint, every re-run replays the same INSERT statements from the beginning. The unique constraint correctly refuses the second copy of each row - the error is the backfill's lack of idempotency, not corrupt data.

Edge cases

• DO NOTHING silently skips rows that changed upstream since the first run - use DO UPDATE when freshness matters.
• The checkpoint table needs its own transaction discipline: a checkpoint row that never commits recreates the double-run bug you just fixed.
• For very large backfills, keep each ON CONFLICT statement to one batch so a single statement never grows unbounded.

Provenance

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

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

Thread query: postgres ERROR: duplicate key value violates unique constraint during backfill re-run

TL;DR:

Not specified by the original author.

TL;DR

The fix The root cause is a UID/GID mismatch on the bind-mounted machinekey` folder: the container runs as UID 1000, but the host folder is often created owned by root, so setup cannot write its files and the migration fails. Fix it with: 3. chown 1000:1000 machinekey 4. then restart the stack. If the database migration state is already broken, delete the Postgres volume first so migrations start clean, then bring the stack up again.

When to use

You are seeing this: Self-hosted ZITADEL deployed with Docker randomly throws migration exception on configuring step: msg="migration failed" caller="[HOME]/..." error="ID=V3-DKcYh Message=Errors.Instance.Domain.AlreadyExists... Use this skill when you run into "duplicate key value violates unique constraint "uniqueconstraintspkey" on configuring step".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Steps

1. Self-hosted ZITADEL deployed with Docker randomly throws migration exception on configuring step:

`msg="migration failed" caller="[HOME]/..." error="ID=V3-DKcYh Message=Errors.Instance.Domain.AlreadyExists...

2. The fix

The root cause is a UID/GID mismatch on the bind-mounted machinekey folder: the container runs as UID 1000, but the host folder is often created owned by root, so setup cannot write its files and the migration fails. Fix it with:

3. chown 1000:1000 machinekey
4. then restart the stack. If the database migration state is already broken, delete the Postgres volume first so migrations start clean, then bring the stack up again. The maintainer independently reproduced the UID/GID mismatch, confirming this is the cause.

Why this happens

The fix The root cause is a UID/GID mismatch on the bind-mounted machinekey` folder: the container runs as UID 1000, but the host folder is often created owned by root, so

In gh:zitadel/zitadel#8910 the issue was: Issue zitadel/zitadel#8910 (closed, 25 comments): ### Preflight Checklist

• [x] I could not find a solution in the documentation, the existing issues or discussions
• [x] I have joined the ZITADEL chat

Environment

Self-hosted

Version

2.64.1

Database

PostgreSQL

Database Version

16-alpine

Describe the problem caused by this bug

Self-hosted ZITADEL deployed with Docker randomly throws migration exception on configuring step: msg="migration failed" caller="[HOME]/..." error="ID=V3-DKcYh Message=Errors.Instance.Domain.AlreadyExists... The verified answer: The root cause is a UID/GID mismatch on the bind-mounted machinekey` folder: the container runs as UID 1000, but the host folder is often created owned by root, so setup cannot write its files and the migration fails. Fix it with:

chown 1000:1000 machinekey

then restart the stack. If the database migration state is already broken, delete the Postgres volume first so migrations start clean, then bring the stack up again. The maintainer independently reproduced the UID/GID mismatch, confirming this is the cause. Source: https://github.com/zitadel/zitadel/issues/8910