postgres_migrator VS pgpkg

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 (Go)

https://github.com/bikeshedder/tusker (Python but being ported to Rust)

https://github.com/tyrchen/renovate (Rust)

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

Forr postgres, how does the schema diffing aspect compare to migra?

https://github.com/djrobstep/migra

I'm asking because, although migra is excellent and there are multiple migrations tools based on it (at least https://github.com/bikeshedder/tusker and https://github.com/blainehansen/postgres_migrator), issues are piling up but development seem to be slowing down

Is this similar to migra? There's a tool written in Rust that calls it, postgres_migrator (there's also tusker)

The best solution I've ever seen is this Rust library https://github.com/cornucopia-rs/cornucopia

You write plain SQL for you schema (just a schema.sql is enough) and plain SQL functions for your queries. Then it generates Rust types and Rust functions from from that. If you don't use Rust, maybe there's a library like that for your favorite language.

Optionally, pair it with https://github.com/bikeshedder/tusker or https://github.com/blainehansen/postgres_migrator (both are based off https://github.com/djrobstep/migra) to generate migrations by diffing your schema.sql files, and https://github.com/rust-db/refinery to perform those migrations.

Now, if you have simple crud needs, you should probably use https://postgrest.org/en/stable/ and not an ORM. There are packages like https://www.npmjs.com/package/@supabase/postgrest-js (for JS / typescript) and probably for other languages too.

If you insist on an ORM, the best of the bunch is prisma https://www.prisma.io/ - outside of the typescript/javascript ecosystem it has ports for some other languages (with varying degrees of completion), the one I know about is the Rust one https://prisma.brendonovich.dev/introduction

You are absolutely correct; my previous business ended up ejecting almost all database logic from Java ORMs, and moved almost everything to SQL and stored procedures. Doing so resulted in a significant performance and productivity increase, relative to all the layers of nonsense we used to jump through.

One of the difficulties of doing this was that the tooling isn’t great. We had to write our own tools to make it work, but the benefits of going back to SQL were more than worth it.

(Many years later I made an open source version of that tooling, https://github.com/pgpkg/pgpkg)

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. It’s a complete work in progress, not production ready, probably has bugs, but I use it daily. It lets me use Postgres as my main development environment.

It also managea migrations for tables, types, and other stuff, if fully atomic, and lets you write unit tests in SQL (which run inside save points so they don’t affect the database and can run during production deployments). It’s sort of my Swiss Army knife of plpgsql development.

[0] https://github.com/pgpkg/pgpkg

I use static Svelte(Kit) + TypeScript + Tailwind on the front end, Go and Postgraphile in the middle, and PostgreSQL functions on the backend.

I did write my own PostgreSQL migration tool though, to make it easier to write SQL functions in my IDE: https://github.com/pgpkg/pgpkg