pg-osc VS postgres_migrator

Great to see more innovation in this space! How does this compare to?

https://github.com/shayonj/pg-osc

Postgres has some less robust tooling, at least both of these carry the "experimental" tag:

https://news.ycombinator.com/item?id=29825520

https://github.com/shayonj/pg-osc

But, still, they do it for you.

You can also try it out, Github: https://github.com/shayonj/pg-osc

This is very cool! I no longer work on Rails on daily basis, but would have loved to have this.

I built something similar called `pg-osc` / `pg-online-schema-change` for PostgreSQL workloads. It does zero downtime migrations using triggers and shadow table.

Github: https://github.com/shayonj/pg-osc.

Blog: https://www.shayon.dev/post/2022/47/pg-osc-zero-downtime-sch...

Also love that this is written in Ruby :). Going to check it out on some side projects.

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