4 ms·

> without migration scripts, like editing source code. Just a quick “deploy” and the logic is updated. This is 100% the key to sanity with managing database st

> without migration scripts, like editing source code. Just a quick “deploy” and the logic is updated. This is 100% the key to sanity with managing database stored procedures and functions -- ability to manage them in Git like normal code and deploy them like code. In contrast, the workflow from traditional imperative database "migration" tools is just super awkward for developing and maintaining any non-trivial number of SQL stored programs (procs, funcs, triggers, views, etc). I wrote a blog post about this a few months ago, and although my product is aimed at MySQL and MariaDB, many of the concepts discussed apply to any relational DB: https://www.skeema.io/blog/2023/10/24/stored-proc-deployment/ https://www.skeema.io/blog/2023/10/24/stored-proc-deployment...

doctor_eval 3y ago
Hey - Skeema looks very cool. I haven’t used MySQL for decades but I can see it’s solving much the same problems as pgpkg. I did decide that declarative tables were too hard when I wrote the first predecessor of pgpkg in bash 10+ years ago - but maybe I should reconsider now that I’m working in Go! The thing is, it’s a bit of a rabbit hole, there isn’t much tooling around this stuff despite stored functions being so, so much easier for writing database logic than anything else. I feel the industry has wasted an enormous amount of time on ORMs and other nonsense when stored procedures have been under our noses the whole time.
- evanelias 3y ago
  Thanks! Yeah definitely agree that building out declarative table management for Postgres would be a major effort. A few open source projects I've seen in that area include: https://github.com/sqldef/sqldef https://github.com/sqldef/sqldef (Go) https://github.com/bikeshedder/tusker https://github.com/bikeshedder/tusker (Python but being ported to Rust) https://github.com/tyrchen/renovate https://github.com/tyrchen/renovate (Rust) https://github.com/blainehansen/postgres_migrator https://github.com/blainehansen/postgres_migrator (Rust) Some of these are based on parsing SQL, and others are based on running the CREATEs in a temporary location and introspecting the result. The schema export side can be especially tricky for Postgres, since it lacks a built-in equivalent to MySQL's SHOW CREATE TABLE. So most of these declarative pg tools shell out to pg_dump, or require the user to do so. But sqldef actually implements CREATE TABLE dumping in pure Golang if I recall correctly, which is pretty cool. There's also the question of implementing the table diff logic from scratch, vs shelling out to another tool or using a library. For the latter path, there's a nice blog post from Supabase about how they evaluated the various options: https://supabase.com/blog/supabase-cli#choosing-the-best-diff-tool https://supabase.com/blog/supabase-cli#choosing-the-best-dif...
  - doctor_eval 3y ago
    I looked at that supabase blog but the problem in my experience is that a refactor is not just about changing the database tables, but also rearranging the data within those tables - the data in a production database is obviously the most important thing. So I think the schema diff is actually the easy part. And for that, I wouldn’t use text tools like “show create tables”, I’d use the PG catalog tables directly. The problem I had was that I could never come up with a declarative scheme that would allow reliable data transformation for all databases over the long term. For example - if I have a table “lookup” with two columns “key” and “name”, over a period of years we might see a transformation like this: alter table lookup rename column name to description; […later…] alter table lookup add column name text default description; […later…] alter table lookup drop column description; Assuming “name” and “description” are modified between upgrades, then running these updates over time would result in a different transformation than if you just apply the latest definitions to an old database table. I couldn’t ever come up with a solution to this that’s simpler than a sequence of migration scripts, which is always repeatable. I haven’t had a look at Skeema yet but am curious how you deal with this?
    - evanelias 3y ago
      > So I think the schema diff is actually the easy part. And for that, I wouldn’t use text tools like “show create tables”, I’d use the PG catalog tables directly. re: SHOW CREATE TABLE, I was referring to the export logic, not diff logic. In other words, when a new user adopts a declarative schema management tool, they need some way of dumping their existing database schema to the filesystem as a set of CREATE statements. Most pg tools seem to just leverage pg_dump for this, but in my opinion that's not great since it's an external dependency. And then ideally there's also a way to re-sync the filesystem in the future as needed, pulling the latest definitions from a given DB server. This is also an export, but it should be smart enough to only overwrite CREATE statements where the corresponding object has actually changed, to avoid stomping on formatting or inline comments. The ability to do a "pull" operation is very useful in development workflows: engineers can make DDL changes to a dev DB directly while developing a feature, and then pull those changes into the filesystem to turn them into a git commit / pull request. It's also sometimes useful in production workflows, in case someone had to make an "out of band" emergency hotfix directly to the prod DB, outside of the schema management tool. > alter table lookup rename column name to description > I haven’t had a look at Skeema yet but am curious how you deal with this? Skeema just doesn't support renames directly at all. In practice this actually works out fine, since renames are hugely problematic in production databases anyway due to deploy-order concerns: there's no way to deploy an application change at the same exact moment as the RENAME is executed in SQL, so it cannot be performed "online". Best practice with schema changes is for applications to be able to work fine with both the old and new schema, and renames typically break this rule. (Well, unless you do a convoluted multi-step dance with view-swapping, for DBMS that support transactional DDL... this is doable in pg, but not in all other DBs.) In Skeema, if you really need to do a rename, you can do it out-of-band (outside Skeema) on all environments (prod/stage/dev/etc), and then use `skeema pull` to update the filesystem definition to match. Or for new tables that aren't populated in prod yet, happily a rename is entirely equivalent to drop-then-re-add. So this case is trivial, and Skeema can be configured to allow destructive changes only on empty tables.

doctor_eval 3y ago

Hey - Skeema looks very cool. I haven’t used MySQL for decades but I can see it’s solving much the same problems as pgpkg. I did decide that declarative tables were too hard when I wrote the first predecessor of pgpkg in bash 10+ years ago - but maybe I should reconsider now that I’m working in Go! The thing is, it’s a bit of a rabbit hole, there isn’t much tooling around this stuff despite stored functions being so, so much easier for writing database logic than anything else. I feel the industry has wasted an enormous amount of time on ORMs and other nonsense when stored procedures have been under our noses the whole time.

evanelias 3y ago
Thanks! Yeah definitely agree that building out declarative table management for Postgres would be a major effort. A few open source projects I've seen in that area include: https://github.com/sqldef/sqldef https://github.com/sqldef/sqldef (Go) https://github.com/bikeshedder/tusker https://github.com/bikeshedder/tusker (Python but being ported to Rust) https://github.com/tyrchen/renovate https://github.com/tyrchen/renovate (Rust) https://github.com/blainehansen/postgres_migrator https://github.com/blainehansen/postgres_migrator (Rust) Some of these are based on parsing SQL, and others are based on running the CREATEs in a temporary location and introspecting the result. The schema export side can be especially tricky for Postgres, since it lacks a built-in equivalent to MySQL's SHOW CREATE TABLE. So most of these declarative pg tools shell out to pg_dump, or require the user to do so. But sqldef actually implements CREATE TABLE dumping in pure Golang if I recall correctly, which is pretty cool. There's also the question of implementing the table diff logic from scratch, vs shelling out to another tool or using a library. For the latter path, there's a nice blog post from Supabase about how they evaluated the various options: https://supabase.com/blog/supabase-cli#choosing-the-best-diff-tool https://supabase.com/blog/supabase-cli#choosing-the-best-dif...
- doctor_eval 3y ago
  I looked at that supabase blog but the problem in my experience is that a refactor is not just about changing the database tables, but also rearranging the data within those tables - the data in a production database is obviously the most important thing. So I think the schema diff is actually the easy part. And for that, I wouldn’t use text tools like “show create tables”, I’d use the PG catalog tables directly. The problem I had was that I could never come up with a declarative scheme that would allow reliable data transformation for all databases over the long term. For example - if I have a table “lookup” with two columns “key” and “name”, over a period of years we might see a transformation like this: alter table lookup rename column name to description; […later…] alter table lookup add column name text default description; […later…] alter table lookup drop column description; Assuming “name” and “description” are modified between upgrades, then running these updates over time would result in a different transformation than if you just apply the latest definitions to an old database table. I couldn’t ever come up with a solution to this that’s simpler than a sequence of migration scripts, which is always repeatable. I haven’t had a look at Skeema yet but am curious how you deal with this?
  - evanelias 3y ago
    > So I think the schema diff is actually the easy part. And for that, I wouldn’t use text tools like “show create tables”, I’d use the PG catalog tables directly. re: SHOW CREATE TABLE, I was referring to the export logic, not diff logic. In other words, when a new user adopts a declarative schema management tool, they need some way of dumping their existing database schema to the filesystem as a set of CREATE statements. Most pg tools seem to just leverage pg_dump for this, but in my opinion that's not great since it's an external dependency. And then ideally there's also a way to re-sync the filesystem in the future as needed, pulling the latest definitions from a given DB server. This is also an export, but it should be smart enough to only overwrite CREATE statements where the corresponding object has actually changed, to avoid stomping on formatting or inline comments. The ability to do a "pull" operation is very useful in development workflows: engineers can make DDL changes to a dev DB directly while developing a feature, and then pull those changes into the filesystem to turn them into a git commit / pull request. It's also sometimes useful in production workflows, in case someone had to make an "out of band" emergency hotfix directly to the prod DB, outside of the schema management tool. > alter table lookup rename column name to description > I haven’t had a look at Skeema yet but am curious how you deal with this? Skeema just doesn't support renames directly at all. In practice this actually works out fine, since renames are hugely problematic in production databases anyway due to deploy-order concerns: there's no way to deploy an application change at the same exact moment as the RENAME is executed in SQL, so it cannot be performed "online". Best practice with schema changes is for applications to be able to work fine with both the old and new schema, and renames typically break this rule. (Well, unless you do a convoluted multi-step dance with view-swapping, for DBMS that support transactional DDL... this is doable in pg, but not in all other DBs.) In Skeema, if you really need to do a rename, you can do it out-of-band (outside Skeema) on all environments (prod/stage/dev/etc), and then use `skeema pull` to update the filesystem definition to match. Or for new tables that aren't populated in prod yet, happily a rename is entirely equivalent to drop-then-re-add. So this case is trivial, and Skeema can be configured to allow destructive changes only on empty tables.