Test data management

Test data (also called fixture or fake data) is the sample rows loaded into a development or test database so the application has something to show and exercise. It is dev/test state, not migration history — yet it lives in the same Liquibase changelog as the schema, and that creates a tension this page resolves.

The problem

Fixtures are typically loaded with loadData change sets under a dedicated context (JHipster calls it faker) that is only active under the dev profile, and they are `<include>`d last in the master changelog so they act on the final schema.

As the schema evolves, fixture data goes stale and must be amended. But a fixture change set is still a change set, so the immutability rule applies (see Change set immutability): editing the CSV or the change set changes its checksum and aborts the next start-up — and because validation is global, a single stale fixture can block unrelated schema changes from applying.

Developers who do not know this edit the fixtures in place, and the shared dev environment stops starting. That is the failure mode this pattern eliminates.

The pattern: fixtures are re-runnable upserts

Make every fixture loader idempotent and re-runnable so that editing it is safe and expected rather than forbidden:

Convert loadData (a blind INSERT) to loadUpdateData with a primaryKey, which upserts: match on the key and update, or insert if absent.
Add runOnChange="true" so a checksum change triggers a re-run instead of a validation failure.

<changeSet id="FAKE-010-user" author="me" context="faker" runOnChange="true">
    <loadUpdateData file="liquibase/fake-data/user.csv" separator=";"
                    tableName="user" primaryKey="id">
        <column name="id" type="numeric"/>
        <!-- one <column> per CSV column; the primaryKey column MUST be loaded -->
    </loadUpdateData>
</changeSet>

The consequences — and the whole point:

Editing a fixture CSV is safe. The checksum change makes Liquibase re-run the loader as an upsert: existing rows are updated, new rows inserted, no duplicates, no validation failure.
A stale fixture can never block a schema change again, because it re-runs instead of aborting validation.

Use a composite key where the table has no single surrogate id, for example primaryKey="user_id,meta_key" for a key/value metadata table. The primary-key column(s) must be present in the loaded <column> list so Liquibase has a value to match on.

This is a deliberate exception to immutability, justified only because fixture data is dev-only and disposable. Never copy runOnChange onto real schema DDL — schema history must stay immutable.

What stays immutable even among fixtures

A <sql> change set containing INSERT is not idempotent — a re-run duplicates rows or collides on the primary key — so it must not be marked runOnChange. Treat such change sets as immutable: amend them with a new delta change set, never by editing the existing one. Pure <update> change sets are idempotent and may carry runOnChange="true".

How to amend fixtures — decision guide

You want to… Do this

You want to…	Do this
Change column values on existing fixture rows	Edit the CSV in place. The `runOnChange` loader re-runs and upserts.
Add rows to an existing fixture table	Append rows to the CSV with fresh ids. The loader upserts them in.
Add a column the loader should populate	Add the column to the CSV and a matching `<column>` to the loader change set (it is `runOnChange`, so it just re-runs).
Add a brand-new fixture table	Append a new `loadUpdateData` + `runOnChange` change set (own CSV), placed in foreign-key order after its parents. Then add a matching delete — see below.
A one-off correction an upsert cannot express (delete specific rows, restructure)	Append a new delta change set (`<delete>` / `<sql>`).

Change column values on existing fixture rows

Edit the CSV in place. The runOnChange loader re-runs and upserts.

Add rows to an existing fixture table

Append rows to the CSV with fresh ids. The loader upserts them in.

Add a column the loader should populate

Add the column to the CSV and a matching <column> to the loader change set (it is runOnChange, so it just re-runs).

Add a brand-new fixture table

Append a new loadUpdateData + runOnChange change set (own CSV), placed in foreign-key order after its parents. Then add a matching delete — see below.

A one-off correction an upsert cannot express (delete specific rows, restructure)

Append a new delta change set (<delete> / <sql>).

The delete script and its ordering contract

Fixture sets usually ship with a companion delete changelog that wipes all fixture rows, `<include>`d before the loaders so a fresh database starts clean and a deliberate reset re-wipes before reloading.

Because the delete script runs before the loaders and does not normally re-run, on a normal boot it operates on an empty or freshly-seeded database — so its foreign-key ordering is never exercised until someone triggers a real reset. Ordering bugs therefore lie dormant for a long time and surface only when the reset is first used. Review it deliberately.

When you add or remove a fixture table, keep the delete script in sync:

Completeness — every loaded table must have a matching <delete>.
Reverse foreign-key order — a table must be deleted before the tables it references (its parents) and after the tables that reference it (its children). Fixture foreign keys are usually plain (no ON DELETE CASCADE), so order is load-bearing.
Protect permanent seeds — tables that also hold permanent (non-fixture) seed rows must not be wiped wholesale. Guard them with a WHERE clause that spares the seeded id range, or omit the table from the delete entirely if it holds only permanent seeds.
Break circular foreign keys — when two tables reference each other (for example a period table whose default_*_id points at a criteria table that points back at the period), neither can be deleted while the other references it. Null the optional back-reference first, then delete both:
```
<changeSet id="..." author="me" context="faker">
    <sql>UPDATE membership_period SET default_criteria_id = NULL;</sql>
</changeSet>
```

Verify completeness after editing — every loaded table should also be deleted (empty output means good):

cd src/main/resources/liquibase/changelog
comm -23 \
  <(grep -oE 'tableName="[^"]+"' fake-data*.xml | grep -oE '"[^"]+"' | sort -u) \
  <(grep -oE 'tableName="[^"]+"' delete-fake-data.xml | grep -oE '"[^"]+"' | sort -u)

Resetting and reloading all fixtures

Simplest, recommended — drop and rebuild the dev database. Run mvn -pl <db-module> liquibase:dropAll (or drop the dev schema), then restart with the fixture context active. Everything re-runs cleanly with fresh checksums, including permanent seeds.

Surgical reset without dropping the schema — re-run the deletes and then the loaders by removing their DATABASECHANGELOG rows, then restart:

-- the delete change sets
DELETE FROM DATABASECHANGELOG
 WHERE ID LIKE '%-delete' OR ID LIKE '%-data' OR ID LIKE '%-metadelete';
-- the loaders
DELETE FROM DATABASECHANGELOG WHERE ID LIKE 'FAKE-%';

Remove both sets. Drop only the delete rows and you wipe without reloading.

Migrating an existing fixture set to this pattern

The conversion changes the checksum of every loader (and reorders the delete script), so the stored checksums no longer match. The loaders self-heal because they are now runOnChange, but a reordered run-once delete script does not — so a one-time reset of each developer’s dev database accompanies the migration (liquibase:dropAll or drop the schema). After that, no future drops are needed for routine fixture edits: editing a CSV simply re-runs the upsert.