Comment by frollogaston
9 hours ago
I usually start by seeing how far I can get with just a single idempotent schema.sql file, usually good enough. Those migration tools have sort of a git within a git managing merge conflicts, which gets very messy with a team of SWEs, esp if rubberstamping Claude-generated PRs. I don't want to introduce that without a very clear reason why the single file with regular git merge tooling isn't good enough.
How do you handle schema changes after your project is in production?
I mean, sure start with a unified schema file until you have a production release... deploy, populate with placeholder data, etc... but once released, having a file for each set of changes isn't a bad thing.
Also, the management tools you can have single files for each view/sproc, etc... it's just schema migrations you need to take care of.
Make a PR that edits the .sql file, deploy to staging, deploy to prod. Git tracks changes to the file, and your CI should be aware of what commit it's on. (If you even have CI)
This only works if you don't care about being able to auto roll back DB changes without making a new commit, cause Postgres doesn't have a declarative DDL.