Tests with Hardcoded IDs That Collide Against a Shared Database

Quick answer

Tests that insert rows with hardcoded primary keys, like a fixed customer id of 1001, pass on an empty database and fail the moment they run against a shared or already-seeded one. The failure is a unique-constraint violation, and it often appears nondeterministic because it depends on what another test or another developer left behind. Stop choosing ids in the test at all. Let the database assign keys, or reserve an isolated schema per test run, and refer to the created row through the value the system returns rather than a constant you invented.

Why the same test changes result by environment

Consider a test that assumes a clean table and a well-known id.

INSERT INTO customers (id, email, plan)
VALUES (1001, 'case-1001@example.com', 'free');

On a local empty database this works. On a shared CI database, a previous run may have already inserted id 1001 and not rolled it back, so the second execution fails with a duplicate key error. Worse, if the test instead does an upsert or silently ignores the conflict, it updates someone else's row and then asserts against the wrong state, producing a failure that points at unrelated code.

Let the system hand you the id

The test should create the row and then use whatever identifier came back. This removes the assumption entirely and works regardless of existing data.

const created = await db.query(
  "INSERT INTO customers (email, plan) VALUES ($1, $2) RETURNING id",
  [`run-${process.env.RUN_ID}-c@example.com`, "free"]
);
const customerId = created.rows[0].id;

const fetched = await db.query(
  "SELECT plan FROM customers WHERE id = $1",
  [customerId]
);
assert.equal(fetched.rows[0].plan, "free");

For natural unique keys such as email, generate the value from a run or test identifier so repeated executions never collide. A timestamp alone is not enough if two tests can run in the same millisecond; combine a unique run id with a per-test suffix.

Prefer real isolation over careful naming

Even dynamic ids do not protect you from tests that interfere through shared state such as configuration rows or global counters. The strongest design is to give each test run its own schema or database and run inside a transaction that rolls back at the end, so the shared environment is never mutated.

1. Open a transaction at the start of the test and register rollback in teardown, so inserted rows never persist.
2. Where rollback is not possible, use a schema created per run and dropped afterward.
3. Seed reference data explicitly inside that isolated scope rather than relying on whatever the shared database happens to contain.
4. Assert against returned identifiers and queried rows, never against a constant id that the test did not create.

How to detect an existing offender

Run the suite twice in a row against the same database without cleaning it. Tests that only pass on the first execution are the ones depending on an empty world or a fixed id. You can also run the tests in parallel; collisions that were hidden by strict ordering surface immediately when two tests try to claim the same constant. Treat the resulting failures as evidence of an isolation problem, not a reason to force serial execution.

Watch for shared sequences and reset assumptions

A subtler version of the same defect appears with auto-increment sequences rather than literal ids. A test that asserts the new row has id equal to the next expected sequence value, such as expecting exactly 5002 after the last test, passes only while no other process consumes a value from that sequence. On a shared database the sequence is global, so a background job or another suite can advance it and invalidate the assertion even though the code is correct. The rule is the same as for hardcoded ids: never assert a specific generated key. Assert properties of the row that matter to behavior, such as the plan and the email, and treat the identifier as an opaque value you read back. If you must verify that an id was generated, assert that it is present and unique rather than equal to a predicted number, so global sequence drift cannot break a correct implementation.

When a fixed id is legitimate

Reference data that is genuinely immutable and part of the contract, such as a fixed enum table seeded by migrations, can be referenced by a stable id. The distinction is whether the test creates the row or merely reads a row the system guarantees. If the test inserts it, the id should be assigned; if the platform provisions it as a permanent fixture, reading its known id is safe.

FAQ

Why does the test fail only on Tuesdays or after a retry?

Because the failure depends on leftover data, not the day itself. A scheduled job, a retried pipeline, or a teammate's run leaves id 1001 in the table, and the next insert collides. The timing looks random until you realize the shared database is the hidden input.

Is wrapping inserts in a transaction enough?

It is the best default when your database and driver support nested or per-test transactions. If code under test commits independently or uses a separate connection, rollback will not contain it, and you need a dedicated schema instead.

Should I just truncate the table before each test?

Truncation is destructive on a shared database and serializes the whole suite. It also hides the real isolation problem. Use it only inside an ephemeral database that no other process depends on.

What if the API requires me to send a client-generated id?

Generate a unique id with a proper uuid rather than a small fixed integer. The requirement to supply a key does not justify reusing the same constant across runs; a uuid gives uniqueness without coordination.

This is part of the The API Testing Guide — start there for the full picture of test isolation and shared-state failures.