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

[marcc]

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?

[hartzell]

I've used sqitch (https://sqitch.org/) in the past, happily.

Noticed that it released its 1.0 a couple of days ago (https://github.com/sqitchers/sqitch).

[n42 → hartzell]

This looks fantastic, thank you for sharing.

[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 → nickserv]

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 → marcc]

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.

[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 → 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 → fowl2]

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.

[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.

[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.

[stephenr]

If i can migrate the data both ways meaningfully, I just put the sql to do so in the Schema migration .sql files.