Upsert data records in a migration

Questions / Help
,
1

In the process of migration between databases, I’m needing to do re-create some migrations that INSERT data. Let’s ignore the discussion/debate about whether or not data records should be handled inside a migration vs. as a seed – for this case, it’s not something I have control over. The problem can come up in prod where a sysadmin/devops person/process may have synced the database. The original migration included something like

execute(
      "INSERT INTO foo.bar (name) VALUES ('x'), ('y'), ('z')"
    )

That migration works fine on the new database on dev environments because we are resetting the database… but on prod, that table may already have the records in it, so we need to modify the migration here to tolerate the possibility that data might already exist (e.g. in a production context).

Is there a recommended way to do this?

2

Is there a problem with checking if entry exists before inserting? In theory you shouldn’t have any race conditions as migrations don’t run concurrently.

3

ah, it’s as easy as adding ON CONFLICT DO NOTHING to the query. Simpler than I thought.

1 Like
4

Make sure there’s a unique index on the field. Otherwise a conflict won’t happen.

5

Good point. In my simple example with “enums”, the name is the primary key.