4 ms·

But it would be much better if Postgres (or any SQL DBMS) were a .git recipient where you push to deploy the triggers and stored procedures. There are hundreds

But it would be much better if Postgres (or any SQL DBMS) were a .git recipient where you push to deploy the triggers and stored procedures. There are hundreds of “schema upgraders”, all based on their flavor of XML, JSON, text files and their associated name comventions, precisely because the canonical SQL way of doing it is limping.

Cthulhu_ 5y ago
Why add it as a concern to a database engine when it's down to the individual use case? I mean, there's hundreds of database migration tools out there, all you need to do - as a developer, that's your job - is to add some automation to your git repository and database server's master to run a migration. Set up a cron job to pull every X minutes, set up a post-merge hook to run those migrations. Simples. Just use straight SQL files for these migrations.
necovek 5y ago
I'd rather say that there are "hundreds of schema upgraders" because there are hundreds of ORMs instead: it's usually a database migration tool (or two) per ORM. SQL DBMS come with an obvious text-only schema management tool called SQL-in-text-files. If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...), and you are good to go. Any VCS can deal with those properly. I've used plenty of saner version control systems than Git, and I wouldn't want a database to restrict me to it (even though I am usually "forced" to use Git nowadays).
- mhitza 5y ago
  > If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...) I would just suggest people use database migration tools from the start. This "autoincrement" method breaks down easily when there are multiple developers working on the same project.
  - necovek 5y ago
    Sure, though the two obvious problems are solved easily: make developers "reserve" a number ahead of time, and keep a list of applied revisions in the DB table. I was on a team doing this 15 years ago, and we did some pretty hardcore DB migrations on a large, complex database without any fuss with >30 developers working on it at the same time.
  - dllthomas 5y ago
    One thought I had is to put the schema in a flat file and put the migration on the commits (with mechanical checking that the two agree). I may've prototyped it at some point, I don't recall - I'm pretty sure I never used it in anger.

Cthulhu_ 5y ago

Why add it as a concern to a database engine when it's down to the individual use case? I mean, there's hundreds of database migration tools out there, all you need to do - as a developer, that's your job - is to add some automation to your git repository and database server's master to run a migration. Set up a cron job to pull every X minutes, set up a post-merge hook to run those migrations. Simples. Just use straight SQL files for these migrations.

necovek 5y ago

I'd rather say that there are "hundreds of schema upgraders" because there are hundreds of ORMs instead: it's usually a database migration tool (or two) per ORM. SQL DBMS come with an obvious text-only schema management tool called SQL-in-text-files. If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...), and you are good to go. Any VCS can deal with those properly. I've used plenty of saner version control systems than Git, and I wouldn't want a database to restrict me to it (even though I am usually "forced" to use Git nowadays).

mhitza 5y ago
> If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...) I would just suggest people use database migration tools from the start. This "autoincrement" method breaks down easily when there are multiple developers working on the same project.
- necovek 5y ago
  Sure, though the two obvious problems are solved easily: make developers "reserve" a number ahead of time, and keep a list of applied revisions in the DB table. I was on a team doing this 15 years ago, and we did some pretty hardcore DB migrations on a large, complex database without any fuss with >30 developers working on it at the same time.
- dllthomas 5y ago
  One thought I had is to put the schema in a flat file and put the migration on the commits (with mechanical checking that the two agree). I may've prototyped it at some point, I don't recall - I'm pretty sure I never used it in anger.