The pain that I've often felt when creating database migration files, is best described in this Speakerdeck: Sane Database Change Management with Sqitch.
- Paste entire function to new "up" script
- Edit the new file
- Copy the function to the new "down" script
- Three copies of the function!
And I end up with no clear diff of the function that I can easily git-blame to understand the change later in time.
I feel too that
sync-based approach to schema migrations is much better.
I stand before this new greenfield project (as the only developer). The stack I've chosen is Postgres (on AWS RDS), Node.js, Express, Apollo (GraphQL) and React (on Heroku).
I have read about sqitch and migra, but never used them. Are those the cure to the pain I've felt? Are they compatible with the stack I'm using for this new project or what other sync-based migration tool is best compatible with this stack?
My current workflow is like this. Dev and production database models are the same. A new story arrises. An existing database function needs to be altered. I create a new migration file. I copy the function that needs to be altered into the "up" and again into the "down" part of the new migration. I commit. I alter the "up" part. I commit (creates diff).
This all feels very verbose just when only a few lines in the function needed to be changed. Ideally, I have the whole schema in code in git. I alter the schema. I commit (creating a diff in git history). A tool then helps to generate the required SQL statements and makes a new up and down migration file for me. I don't mind that I need to verify the generated migration file.
