refinery VS pgroll

Great write up! At PeerDB, we’ve been using refinery https://github.com/rust-db/refinery to handle database migrations of our catalog Postgres database from Rust. It is easy, typesafe and gets the job done. Thought this would be useful for users building apps with Rust!

I don't think migrations need to be tied to ORM. We have refinery that allows you to write migrations in rust. I, personally, didn't like and prefer writing them in SQL - why learn how to migrations in X when you already know the SQL dialect that you're using.

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

What I personally use here is Refinery, but it's within the Rust ecosystem. It lets you write your migrations in pure SQL - which is what I prefer - but still requires about 5 lines of boilerplate Rust code.

Example refinery migrations

I thought I recognized xataio - they submitted pgroll a few months back https://news.ycombinator.com/item?id=37752366 (https://github.com/xataio/pgroll - Apache 2)

At the start of October we released pgroll, an open source tool for zero-downtime, reversible schema migrations for Postgres.

If you have any suggestions or questions, please open an issue in our GitHub repo, reach out to us on Discord or follow us on X / Twitter. We'd love to hear from you and keep you up to date with the latest progress on pgroll.

Any pgroll operations[0] that require a change to an existing column, such as adding a constraint, will create a new copy of the column and backfill it using 'up' SQL defined in the migration and apply the change to that new column.

There are no operations that will modify the data of an existing column in-place, as this would violate the invariant that the old schema must remain usable alongside the new one.

[0] - https://github.com/xataio/pgroll/tree/main/docs#operations-r...

This is a fantastic article! It shows that even simple migrations (like adding or removing a column) can be quite tricky to deploy in concert with the application deployement.

We (at Xata) have tried for a while to come up with a generic schema migration system for PostgreSQL that makes this easier. We ended up using views and temporary columns in such a way that we can provide both the "old" and the "new" schema simultaneously. Up/down triggers convert newly inserted data from old to new and the other way around. This also has the advantage the it can do rollbacks instantly by just dropping the "new" view.

We were just planning to announce this as an open source project this week, but actually it is already public, so if you are curious: https://github.com/xataio/pgroll