HN Explorer

Ask HN: Declarative database migrations?

11 points by beefsack ↗ HN
Hi HN, does anyone have suggestions for a declarative database migration tool, ideally compatible with Postgres?

I've used a number of imperative migration systems, most of which are baked into application stacks. They scratch an itch, but I've always found them a cumbersome and time consuming.

What I really want to do is declare what schema I want, and have a tool (ideally separate from an application stack) which will look at the existing schema and create a diff to update the schema based on what I've declared.

I actually implemented a very basic tool like this for MySQL for a project I was doing a few years ago which created and altered tables and indexes, and there were some pitfalls around destructive changes but on the whole it was very pleasant to use as long as you understood the pitfalls. I'm having trouble finding any similar tools around though and would love to hear any recommendations.

9 comments

[ 4.2 ms ] story [ 28.5 ms ] thread
[deleted]
(comment deleted)
jasonkester
The best plan for this today is the same as it was 20 years ago: write your change scripts in SQL.

SQL is easy to learn, easy to read, easy to test, does exactly what you tell it to with no surprises, and drops happily into version control. No tools required because tools can only possibly complicate things.

The only reason the question you ask is in your brain at all is because of the trend 10 years ago to abstract away the database and declare it in configuration files elsewhere. There's no need to do that. The real thing is simpler, easier to comprehend, and guaranteed not to magically map your description of what you want into something different entirely.

thorin
I think you're right. Keep a text file of your current schema in a vcs. This may be generated from a modelling tool, but you need a text file you can diff and log within the vcs.

SQL isn't difficult to read, modify, test. Ensure you have dev and test servers. You must have someone in your team who is conversant with the db server(s) you target, preferably not an application programmer but someone focused on the database.

There are many schemes to manage changes, but just store a alter and backout script of each change once in production works for me, give them a number and an order to apply.

I'm managing schema for our product set of maybe 1000 db objects as well as doing some reporting, BI and some app dev and scripting. Just ensure that the team use the correct db and can rebuild easily to the trunk or release tag required and you should be fine.

ptype
You could use Sqlalchemy + alembic. This would require you to declare a schema in python. You would not be forced to use sqlalchemy in the application though.
pjungwir
I don't know of any declarative tools, although I've written some tips on using Rails migrations here:

http://illuminatedcomputing.com/posts/2013/03/rules-for-rail...

The problem with a declarative tool is managing change over time. What happens when you want to change a relationship from one-to-many to many-to-many? How does the declarative tool know how to migrate the old data? There are lots of examples where you need to say specifically how to get from point A to point B, especially once you're in production and can't just throw everything away and start over.

wvenable
The problem with this is that some transforms you can make (like renaming columns) can't be figured out from comparing two schemas.