What I'd love to see is state-based migrations similar to what Prisma offers - but that can handle, views, functions, and complex logic that references other things - and have it be smart enough to change those as well. Or at least walk you through any dependent changes. I'd pay for that.
For things like stored procs, triggers, and views, there's a lot of vendor-specific (e.g. Postgres vs MySQL vs SQL Server) edge cases in syntax, introspection, and operational best practices. That's true of tables too of course, but at least the introspection part tends to be fully functional for tables in all major database systems. For other object types, introspection can be half-baked and things can get painful in general. It's much harder to design a generic declarative tool which works across multiple DBs without making sacrifices in expressiveness, safety, and ergonomics.
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...
I was referring to blog posts, for example [1], in which you admittedly use both terms in the text, but you focus more on "state-based" for example in the headline and image. You also erroneously conflate declarative migrations with somehow involving `mysqldump` for some reason? This is what I mean when I say posts like this feel like they're designed to make declarative migrations seem strange and foreign.
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.
We stated the reality at the time of writing that migration-based approach is more common. I think that still holds true today. Meanwhile, there are more solutions introducing the state-based solution (including Bytebase itself).
>> 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.
> We stated the reality at the time of writing that migration-based approach is more common.
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.
If you’re seriously willing to pay money for this, send me an email — I’m considering implementing this in my pgmigrate tool but not sure if it’s worth the development time. I have all the groundwork done, probably achievable in about 10 hours of concerted effort.
I’ve looked everywhere for this in NodeJS & adjacent stacks; almost all migration tools seem to focus on tables, columns and rows. None seem to deal with views, functions, triggers.
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?
Thanks! I’ll give it a look (their docs are offline atm)
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.
The migra maintainer has abandoned it for greener pastures. If you’re interested in sponsoring an alternative, send me an email. My pgmigrate tool has all the groundwork necessary to make this possible but I have held off implementing this because I am not personally interested in using it.
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.
