I hand code my migrations for Postgres. When starting a project a create a base.sql file. Then for first database change I create db/000001-<name of change>.sql, db/00002 [...] and so forth. When I want to create a new database I just run psql<base.sql followed by psql<db/*.sql.
Very simple but extremely effective. I don’t think any tool can handle migrations better than hand coded sql. Sometimes I use PL/PgSQL to handle procedural data migrations, which cannot be solved using normal sql.
If you think any tool can't handle this better than you are woefully misinformed.
I was doing this method back in 2006, let me assure you that the automated tools available today make this look like amateur hour.
Automatic schema transactions, automatic up/down scripts, automatic detection that a schema change hasn't been committed. Automatic change tracking table in the db. Ability to seed a database with test data.
With a single command I can go back to any arbitrary point of changes, switch branch, work on a bug fix, switch back to dev and the next time I fire up the Dev site it'll automatically redeploy the new changes.
Just streets ahead of hand coded scripts. Yes, there's a learning curve, yes you have to pay attention to your design still, but it is so good.
I was skeptical at first, but honestly I'm never going back and I don't miss it. I'm using Entity Framework Migrations, I assume other languages have similar/better tools.
EF Migrations works pretty well if you use the database as a dumb object store. If you need anything remotely advanced than that, eg functions, triggers, custom types or materialised views, then it doesn’t gel at all. Raw sql is the only way to go.
To me, the database is an API, not just s dry store for OO.
I keep a simple table with the latest version run and a time stamp. Then I check in each file whether its version# (in the filename) is less or greater than what’s in the table. It’s easy because I can do it in simple sql.
