
Ask HN: How do you handle data migrations in SQL databases? - marcc
There was a recent discussion about handling schema migrations (https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=19880334). Following up on this, if you deploy a schema migration, sometimes there&#x27;s a data migration that needs to follow. For example, I recently wanted to &quot;promote&quot; a field from a JSON column in a postgres database to a SQL table column. It&#x27;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/](https://sqitch.org/)) in the past,
happily.

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

~~~
n42
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
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.

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

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

