Migrate Config and Reference Data Between Environments

The Story: New Codes Live Locally, Everywhere Else Is Stale

Meet Jordan, a backend engineer on an airline booking product. Lookup tables such as airports, aircraft types, and validation messages are maintained in a local database. Transactional tables—bookings, tickets, payments—live in test and production and must not be overwritten.

⏰ Monday, 11:00 AM — The Local Change

Jordan adds a new airport and two validation messages on the laptop. The UI already works against those rows. Test and production still have yesterday’s lookups:

bookings.airports_data is missing the new airport code
bookings.aircrafts_data is missing a new aircraft type
Validation messages show the raw key in production because the row was never promoted

The codes exist. They just are not in the other environments.

⏰ Monday, 2:00 PM — Why a Full Restore Is the Wrong Tool

A teammate suggests restoring the local backup onto test. That would:

Replace every table, including bookings and tickets
Wipe testers’ scenarios and production-like volumes
Be unthinkable as a path to production

Hand-written INSERT/UPDATE scripts are the other option. They miss unique keys, skip jsonb casts, and nobody wants to maintain them for every lookup table.

⏰ Monday, 3:00 PM — What “promote reference data” actually means

Jordan needs to:

Copy only the allowlisted lookup tables
Insert rows that do not exist on the target
Update (or skip) rows that already exist, matched by primary key or business unique key
Leave every other table untouched

The Solution

Use a Quemsi flow with an Upsert step. Take a snapshot (or reuse one) from the source environment, pick the reference tables from that backup, and write them into the target database. Dry-run first. This is not a restore and not “share the whole database state.”

When to Use Upsert vs a Full Restore

📘

Upsert

Lookup and config rows: airports, countries, messages, feature flags, small code tables

📦

Full restore

Replace the whole database state—see Share Database States Across Environments

🔒

Masked restore

Need production shape without PII—see Mask Columns

🚫

Not for

High-volume transactional tables. Upsert plans fail if selected source rows exceed the max-row cap (default 10,000)

Promote local lookups to test

Problem: Local has new airport and message rows. Test still shows old codes. Testers cannot exercise the new paths.

Solution: Backup local (or pick an existing zip), run Upsert against the test datasource with an allowlist such as bookings.airports_data and bookings.aircrafts_data.

Promote the same rows to production

Problem: Production auto-inserted stub messages (messageValue = messageKey). You want the real copy, not a wipe of bookings.

Solution: Same flow, target = production. Use Update existing so stubs are overwritten. Dry-run, then apply.

Skip rows that already exist

Problem: Production already has hand-tuned values you must not overwrite.

Solution: Set Existing rows to Skip existing. New keys are inserted; matching keys stay as they are.

How Upsert Works

Upsert reads selected tables from the backup (the From source) and writes them to a target datasource. The target is only the write destination—the table picker always uses the backup schema.

Allowlist required. You name the tables. There is no “all tables.”
Match key. Primary key, or the unique business key when the table has a surrogate id plus exactly one other unique constraint (for example country.code).
Insert / update / skip. Missing keys are inserted. Existing keys are updated or skipped. Unchanged rows are skipped.
Fail closed. Ambiguous uniques, nullable match columns, or a row that cannot be planned fail the run. Nothing is committed.
Dry run (default on). Plans every row and writes nothing. Turn it off only after the plan looks right.
One transaction. Apply is all-or-nothing for the selected tables.

How to Do It in Quemsi

1

Have a backup from the source environment

Use an existing snapshot, or run a backup flow against the database that already has the new rows (often local or test):

Open the Quemsi web UI
Create or run a backup flow: From (the source datasource) → optional processing → Zip → To (storage)
Tag it so you can find it later, for example ref-data-local-2026-08-31

The zip must include db-model.json and the table pages for the lookups you will promote.

2

Create an Upsert flow

Create a restore-direction flow (data name = the backup you just created):

From — StoredData, pick the storage and version (or latest)
Unzip — required so Upsert can read the archive
Upsert — select the target datasource (test or production), not the source

Do not add Drop Tables or a full To restore if you only want to promote lookups.

3

Configure tables from the backup

On the Upsert step:

Select the target datasource (the environment that will receive the rows)
Keep Dry run on for the first execution
Choose Update existing or Skip existing
Click Configure tables on agent

The picker lists tables from the From source (the zip’s db-model.json, or the live backup datasource). It does not list the target schema. Pick only the lookup tables, for example:

bookings.airports_data
bookings.aircrafts_data
        

You can also type qualified names in the combobox. At least one table is required.

4

Grant the target user catalog and DML rights

The agent builds the target model through information_schema. A database-level GRANT ALL ON DATABASE is not enough. The target role needs schema usage plus table privileges:

-- PostgreSQL example (run as a superuser on the target database)
GRANT USAGE ON SCHEMA bookings TO demowriter;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA bookings TO demowriter;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA bookings TO demowriter;
        

Without SELECT, Quemsi reports Loaded 0 tables and then fails on constraints it can still see in the catalog. Upsert also needs SELECT to find existing keys, plus INSERT and UPDATE to apply the plan.

5

Dry-run, then apply

Execute the flow with Dry run on
Read the execution log: match key, insert / update / skip counts, and the planned SQL
If the plan is wrong, change the allowlist or Existing rows and dry-run again
Turn Dry run off and run once to write

If any selected row is not upsertable, the run fails and the transaction is rolled back.

✅ What you should see

Transactional tables on the target are unchanged
New lookup keys appear; existing keys follow Update or Skip
Typed columns (for example PostgreSQL jsonb or point) write correctly from the backup strings

Best Practices

Keep the allowlist small

Name only the tables you intend to promote. Never treat Upsert as a substitute for restore. The default max-row cap is 10,000 source rows across the selected tables.

Always dry-run first on a new target

Dry run plans inserts, updates, and skips without writing. Use it when the target is production, when match keys might differ, or when you changed the allowlist.

Know the match key

Tables with a natural primary key (for example airport_code) match on that key. Tables with a surrogate id plus one unique business column match on the business column; the target generates new ids. Child foreign keys are not remapped—do not upsert parent/child graphs that rely on remapped ids.

Same database type on both sides

The backup’s source type must match the target (PostgreSQL to PostgreSQL, and so on). MongoDB is not supported for Upsert.

Next Steps

Set up Quemsi
Share a full database state across environments (when you do want a complete restore)
Mask columns before sharing production-shaped data
Create a read-only user for source backups (writers need extra grants on the target)
Explore more tutorials
Create your free account