3 ms·

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 th

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.

t1mmen 3y ago
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?
- peter_l_downs 3y ago
  I believe Migra can generate those changes for you via diffing, not sure how well it handles dependent views/functions/etc
  - t1mmen 3y ago
    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.
    - peter_l_downs 3y ago
      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.
      - t1mmen 3y ago
        Oh, that’s unfortunate. 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.
evanelias 3y ago
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.
Guillaume86 3y ago
SSDT for mssql can do it, I have my schema as SQL files in the repo, the tooling can diff and migrate between schema versions.

t1mmen 3y ago

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?

peter_l_downs 3y ago
I believe Migra can generate those changes for you via diffing, not sure how well it handles dependent views/functions/etc
- t1mmen 3y ago
  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.
  - peter_l_downs 3y ago
    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.
    - t1mmen 3y ago
      Oh, that’s unfortunate. 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.

evanelias 3y ago

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.

Guillaume86 3y ago

SSDT for mssql can do it, I have my schema as SQL files in the repo, the tooling can diff and migrate between schema versions.