
Ask HN: Declarative database migrations? - beefsack
Hi HN, does anyone have suggestions for a declarative database migration tool, ideally compatible with Postgres?<p>I&#x27;ve used a number of imperative migration systems, most of which are baked into application stacks.  They scratch an itch, but I&#x27;ve always found them a cumbersome and time consuming.<p>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&#x27;ve declared.<p>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&#x27;m having trouble finding any similar tools around though and would love to hear any recommendations.
======
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.

------
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...](http://illuminatedcomputing.com/posts/2013/03/rules-for-rails-
migrations/)

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.

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

------
pentium10
[http://www.liquibase.org/](http://www.liquibase.org/)

------
alexgaribay
Jetbrains is working on an IDE for what seems to be your use-case.

[https://www.jetbrains.com/dbe/](https://www.jetbrains.com/dbe/)

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

------
ivanceras
apgdiff

