HN Explorer

Ask HN: How do you handle data migrations in SQL databases?

21 points by marcc ↗ HN
There was a recent discussion about handling schema migrations (https://news.ycombinator.com/item?id=19880334). Following up on this, if you deploy a schema migration, sometimes there's a data migration that needs to follow. For example, I recently wanted to "promote" a field from a JSON column in a postgres database to a SQL table column. It's easy enough to add the new column, but what tools and patterns are out there for handling the data migration?

14 comments

[ 2.8 ms ] story [ 38.0 ms ] thread
[dead] test0r
I dont handle this.
nickserv
We use Django primarily so data migrations are built in.

For complex operations we use plain sql files.

Always test on staging with the live data and schema!

marcc
Agreed on testing with real data. Even then, sometimes migrations can be brittle when unexpected data is loaded from a new environment.

I'm curious how folks who aren't using an ORM handle this. We've used Goose before for schema migrations, and can add .go migrations that handle more complex data-migration tasks. It always feels more brittle, and I wonder if there's a pattern that I'm missing here. Is there a better way for non-ORM users to handle data migrations?

JamesBarney
What are the issues you are currently running into?
jolmg
In tools like rails migrations, you would just put the DML statements (INSERT, UPDATE, DELETE) right along with the DDL statements (CREATE, ALTER, DROP, etc.) in the migration file.
[dead] FatDrunknStupid
1) Write migration code. 2) Write undo-migration code. Test. Test. Probably die in Live. SQL Server et al. Great fun.
fowl2
Kinda off topic, but I always feel like this is an opportunity for databases to fix a leaky abstraction. I mean databases are basically an abstraction over the storage mechanism, so why does the choice of json/column as an api have to be so tied to the storage?
fowl2
Why can't I express my denormalization for performance reasons separately from my data model (like I do indexes) and let the database handle it?
dragonwriter
You can. The public side of the schema of objects (views exclusively in many recommendations even back to the beginning of relational DBs) visible to an app are it's logical data model, the base tables and the way the views are implemented on top of them are a separate concern which can be isolated from that model.
tnolet
I'm in the Node.js world and knex.js and its migration feature has been flawless. I run it against an Heroku Postgres DB.
gremlinsinc
I come from laravel world, but lately I've been playing with Hasura as a backend, since javascript is easy to drop in anywhere I've started using knex for my migrations. Works pretty well for my use case.
stephenr
If i can migrate the data both ways meaningfully, I just put the sql to do so in the Schema migration .sql files.