5 ms·

I wrote something [0]. It lets you write plpgsql functions, views, triggers and casts in a way that can be re-created without migration scripts, like editing so

I wrote something [0]. It lets you write plpgsql functions, views, triggers and casts in a way that can be re-created without migration scripts, like editing source code. Just a quick “deploy” and the logic is updated. It also manages migrations for tables, types, and other stuff in a really simple way. Upgrades are fully atomic, and it lets you write unit tests in SQL - which are run after every upgrade, run inside save points so they don’t affect the database, and can run during production deployments. It’s sort of my own personal (open source) Swiss Army knife of plpgsql development. It’s a complete work in progress, not production ready, probably has bugs, and needs more and better documentation - but I use it daily. It lets me use Postgres as my main development environment. [0] https://github.com/pgpkg/pgpkg https://github.com/pgpkg/pgpkg (It also lets you import packages from other sources so you can create libraries of reusable code, within some limits)

chuckhend 3y ago
That looks like an awesome tool. I am going to try it out. Gave you a star!
- doctor_eval 3y ago
  Awesome, thanks! I update it whenever I work with it, which has ramped up in the last few weeks (added cast support a few days ago). It uses pg_analyse to parse the SQL and uses a few tricks to get everything updated. The best thing (IMO) is that there is basically no funny stuff, no filename conventions, no funny delimiters. It’s just regular Postgres SQL, and a couple of very small config files. Works perfectly with git.
evanelias 3y ago
> 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?

chuckhend 3y ago

That looks like an awesome tool. I am going to try it out. Gave you a star!

doctor_eval 3y ago
Awesome, thanks! I update it whenever I work with it, which has ramped up in the last few weeks (added cast support a few days ago). It uses pg_analyse to parse the SQL and uses a few tricks to get everything updated. The best thing (IMO) is that there is basically no funny stuff, no filename conventions, no funny delimiters. It’s just regular Postgres SQL, and a couple of very small config files. Works perfectly with git.

evanelias 3y ago

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