Re: VCS, I’ve found that with a small amount of setup, tools like Liquibase[0] allow you to maintain functions and views (with “CREATE OR REPLACE” or equivalent) as SQL files in a file system with full VCS, diffing, etc. just like any other code.
You still need to sequentially define your changes (migrations) which isn't exactly the VCS experience people are used to. If all changes to your db require new file entries, rarely do you need to diff let alone bisect to find out the history. It does make it harder to find out the current shape of your app though with all the ALTER statements to a single table strewn about multiple files. I wonder if it's feasible to write a tool that allows you to generate sequential migrations from DDL organized like tradition code with modules and items dealing with the same domain in the same file after diffing it with the sequential migrations already in the codebase.
No you don't have to structure your project that way. You can have all your logic (views/functions) stay in a single file per, and use git like usual with the standard diff you are used to. Your changelog xml just needs to be setup for it.
So, after digging about in the docs, I found the Best Practices[0] page which lays out what you describe. I was disappointed to find it is just using idempotent creation statements for procedures and views and putting them all in files that are re-run on each migration step-along with each new roll forward migration-so that new versions of these logic objects replace any old ones. This is not exactly something that liquidbase provides, should be easily replicatable, and I was hoping it'd do diffs to provide similar experience on tables as well.
After some web searching, I came across a project[1] that's trying to do what I describe but it appears to be dead. I'm surprised that migration tools (that I looked through) don't already support such a basic approach that I suspect I'm missing something obvious. Some fundamental block in how SQL works or maybe it doesn't work that well in practice for, in concept, it sounds easy to script using what they call a shadow db and `pg_diff`.
[0] https://www.liquibase.org/