So most likely you're going to want a Postgres-specific tool for this, but I'm not sure one exists yet that handles everything you're looking for here.
I'm the author of a product called Skeema which does handle all this (tables, procs/funcs, views, triggers) for MySQL and MariaDB, and in my opinion this is an area where MySQL/MariaDB's relative simplicity -- in things like e.g. lack of transactional DDL -- actually makes this problem easier to solve there. For example Skeema explicitly doesn't handle data migrations because you can't atomically combine schema changes and data changes in MySQL/MariaDB in the first place.
btw when describing/searching for this, I always say "declarative" and never "state-based". "Declarative" is consistent with terminology used by other infra-as-code such as Terraform and Kubernetes. The main places I see calling it "state-based" are marketing blog posts from commercial schema management tools using an imperative migration approach (Liquibase, Bytebase, etc). To me it feels like they say "state-based" in order to make the declarative competition seem more strange/foreign...
Our initial launch does use "state-based", but we later change it to "declarative". You can check the product here https://demo.bytebase.com/project/blog-102#gitops
Of course, it's always a bit confusing to have multiple ways to reference the same thing. So be it.
I realize this post is two years old, and you're understandably not going to mention a competing product like mine. But it feels like a disingenuous strawman argument to claim that declarative schema management requires `mysqldump`, considering that Skeema (my product) and SQLDef (another declarative tool) were both released in 2016 and are both widely used.
[1] https://www.bytebase.com/blog/database-version-control-state...
>> it feels like a disingenuous strawman argument to claim that declarative schema management requires `mysqldump`
The article explicitly said it's a hybrid approach and never intended to claim this way. Otherwise, it would be an obvious mis-statement.
Yes, and I have not disputed this at any point here.
> The article explicitly said it's a hybrid approach and never intended to claim this way. Otherwise, it would be an obvious mis-statement.
Your blog post said "State-based approach stores the desired end state of the entire schema in the code repository. For MySQL, it means to store the schema dump created by mysqldump." I think that is indeed an obvious mis-statement. It would give a casual unfamiliar reader the impression that declarative schema management somehow involves/requires mysqldump.
I only got back into Postgres this year, after almost a decade away from SQL. It’s kind of bizarre to me that the migration tooling is still at the stage where a 1 line change to eg a Postgres function requires a the whole function to be dropped and re-created?
I understand this is needed at the db level, but surely a “definition” that generates the final migration is doable; it would make such a huge difference in code reviews and to understand how a function/etc changed over time.
Am I just looking in the wrong place? Does this exist? If not, how come? Is it really that hard to do?
DrizzleKit and several others do this for table changes, but nothing I’ve found (possibly excluding Flyway and other Java options) do views/functions/etc.
I’m not too keen on sponsoring an alternative when the dev doesn’t particularly care about the feature — it didn’t work out last I tried something similar. But, perhaps there’s another path: Supabase.
They are in (imo) desperate need of better migration tooling, and they sponsor several open source projects that boost their over-all offering.
AFAIK, they haven’t done much in the migration space yet (aside from their alpha db branching feature), so I expect they’ll co-opt an open source solution at some point soon. May be worth pinging them? Seems it could be a win-win there.