30 ms·

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,

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...

cpursley 3y ago
I’m 100% in on Postgres but what you describe sounds awesome.
- peter_l_downs 3y ago
  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.
tianzhou 3y ago
One of Bytebase authors here, we use "state-based" and "declarative" https://www.bytebase.com/blog/database-version-control-state-based-vs-migration-based/ https://www.bytebase.com/blog/database-version-control-state... interchangeably 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 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.
- evanelias 3y ago
  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. [1] https://www.bytebase.com/blog/database-version-control-state-based-vs-migration-based/ https://www.bytebase.com/blog/database-version-control-state...
  - tianzhou 3y ago
    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.
    - evanelias 3y ago
      > 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.

cpursley 3y ago

I’m 100% in on Postgres but what you describe sounds awesome.

peter_l_downs 3y ago
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.

tianzhou 3y ago

One of Bytebase authors here, we use "state-based" and "declarative" https://www.bytebase.com/blog/database-version-control-state-based-vs-migration-based/ https://www.bytebase.com/blog/database-version-control-state... interchangeably 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 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.

evanelias 3y ago
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. [1] https://www.bytebase.com/blog/database-version-control-state-based-vs-migration-based/ https://www.bytebase.com/blog/database-version-control-state...
- tianzhou 3y ago
  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.
  - evanelias 3y ago
    > 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.