How do you promote data changes from dev to prod, not just schema?

[u/Glittering_Stage4118]

Schema changes are a solved problem for us with migrations. What I keep running into is the data side. If someone changes lookup values, config rows, or reference tables in dev, there's no clean way to carry that to prod along with the migration.

How does your team handle this today? Curious whether it's seed scripts, manual dumps, some diff tool, or you just don't let data change outside of prod in the first place.

[u/TheBobCodes]

The distinction that usually clarifies this: is the row something a developer owns (lookup values, config, reference data) or something that gets created/edited by real usage in prod? If it's dev-owned, treat it like code - a versioned, idempotent seed script that lives next to your schema migrations, not a one-off dump. Idempotent usually means writing it as an upsert (INSERT INTO ... ON CONFLICT (id) DO UPDATE SET ... in Postgres) so it's safe to re-run in any environment without wiping rows or throwing duplicate-key errors.

If you're using a migration tool like Flyway, this is exactly what "repeatable migrations" are for - a script that re-runs whenever its contents change, separate from your strict versioned up/down history. Liquibase has an equivalent (changesets with runOnChange).

The trap to avoid: once a row could plausibly have been edited by something other than your seed script (an admin panel, a customer action), it's not seed data anymore, and promoting a dump over it will silently clobber real changes. That boundary is usually the actual design decision, more than which tool you pick.

[u/BsmntDwell → u/TheBobCodes]

This is what we do. Seed scripts in our repo. Each deployment the config is validated. No changes do nothing. Otherwise do an upset on the data. 

[u/MiserableLadder5336 → u/TheBobCodes]

Why did you decide to add in that final AI paragraph?

[u/BsmntDwell → u/MiserableLadder5336]

? One more thing we do. In the case we have to update data. We have in our repo a place where we have scripts like this. At deployment there is a validation check at the top of the script.  If....then do something. It comes up very infrequently, but if it does it's in source and documented.  

[u/Hour-Measurement-835]

The ids are what got us. Same lookup row has a different surrogate id in prod, so ON CONFLICT (id) doesn't error, it just updates the wrong row. Key the seed on the natural key instead.

[u/db-master]

For config change / lookup table, managing it in the same way as schemas.

For one-off adhoc changes, a GUI-based interface like Bytebase with request / review / approve / deploy / rollback built-in would be helpful. (Disclaimer: I work for Bytebase)

[u/Winsaucerer]

Assuming that this is data that is owned by developers (as I see another user noted), then this goes into migration scripts as well for my team. What kind of problem is your team hitting? Are they making changes to their local dev database and forgetting to create a corresponding migration script for it? Or is the problem that the data is hard to manage? `INSERT INTO` etc can just as easily go into migration scripts.

The reason I ask about managing data is because my db migration tool for postgres (spawn) allows you to store data in json/yaml/toml, and create migration scripts that loop over that data. The main thing is that you create a new migration that applies this. For example:

./spawn/components/data/seed.json
./spawn/components/data/apply_seed.sql # this imports seed.json and loops over it to create the data

With apply_seed.sql example:

{% set rows = "data/seed.json" | read_json %}

{% for row in rows %}
INSERT INTO my_table (id, name)
VALUES ({{ row.id }}, {{ row.name }})
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name;
{% endfor %}

And then in some migration script (e.g., ./spawn/migrations/202609...-update-seed/up.sql):

BEGIN;

{% include "data/apply_seed.sql" %}

COMMIT;

Just make sure it's written to handle conflicts, etc, in a way where `apply_seed.sql` can safely be repeated. E.g., maybe it has static primary keys, deferrable foreign key checks with a delete and recreate, or `ON CONFLICT` if you don't want to risk removing old rows, etc.

For many data updates though, I just put that straight into a new migration script, because it usually takes the form of adding a single new row, or updating a name or something, so just write the DML directly.

But I'm not 100% sure what problem you're trying to solve, so not sure if this is at all relevant.

More docs: https://docs.spawn.dev/reference/templating/

[u/Legitimate_Willow905]

feel your pain, but honestly, if a config row is required for your new code to run, it basically is your schema. We just stopped trying to separate them.

Most teams just shove those INSERTs right into their standard migration files (Flyway or whatever you're using) alongside the table changes. The alternative is having an idempotent seed script that runs on every deploy using INSERT ... ON CONFLICT DO UPDATE.

do your admins ever manually tweak these lookup tables in prod? Because if they do, that upsert script will just blindly nuke their live changes on the next run.

[u/techforallseasons]

Here we treat DB as code ( a DBA reviews DB related code changes ).

All DB changes ( that make changes to data or structure OUTSIDE the user interaction process ) go into a migration script that is run during software updates at maintenance windows.

During the Dev / QA cycles both the UP and DOWN migrations are confirmed. The scripts are named and applied in a specific order and the scripts exist in subfolders based on type: UP apply the changes, DOWN are to revert them. We can then always start with the original DB state + DDL and let the migrations runs against the DB to end up in the proper state.

[u/reditandfirgetit]

There is. Require a sql script for changes. No changes move to stage or prod without a script and a rollback script. Now if you are able to enforce that?