migra VS tusker

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

We’ve extended the CLI migration feature and added Dashboard support. Database migrations give you a way to update your database using version-controlled SQL files. We’ve built a lot of tooling around our migrations, including reparation, migration cleanup using the squash command, and diffing (using migra) to generate a new migration or to detect schema drift.

I've had a lot of success with: https://github.com/djrobstep/migra

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

If you wish to auto-generate migrations, there are declarative schema change tools available for most relational databases. I'm the creator of Skeema [1] which provides them for MySQL, but there are options for other DBs too [2][3][4].

Prisma's migration system actually partially copied Skeema's design, while giving credit in a rather odd fashion which really rubbed me the wrong way: "The workflow of working with temporary databases and introspecting it to determine differences between schemas seems to be pretty common, this is for example what skeema does." [5]

While I doubt I was the first person to ever use that technique, I absolutely didn't copy it from anywhere, and it was never "pretty common". I'm not aware of any other older schema change systems that work this way.

[1] https://www.skeema.io

[2] https://github.com/djrobstep/migra

[3] https://github.com/k0kubun/sqldef

[4] https://david.rothlis.net/declarative-schema-migration-for-s...

[5] https://github.com/prisma/prisma-engines/blob/6be410e/migrat...

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

I really liked the idea proposed in https://github.com/djrobstep/migra but haven’t used it yet.

I'd love to be able to be able to declaratively make changes directly in the table create commands instead of manually creating new migration scripts every time. I've found migra (we use PostgreSQL) and it seems to be exactly what I'm looking for. I'm curious about other people's experience and why things like Migra are the norm.

Migra should do it https://databaseci.com/docs/migra

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

Big fan of tusker (https://github.com/bikeshedder/tusker) for PostgreSQL migrations. Tusker takes a SQL-first approach; You write your schema in declarative DDL (I have my entire project in one schema.sql file) and when you edit it, tusker generates the sql code required to migrate. It uses temporary test databases to run both your declarative DDL and your step-by-step migrations to ensure they are in lock step. And it can connect to live databases and diff your schema/migrations against reality. I've never seen a better toolkit for schema evolution.

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

> Tusker actually uses Migra to power its functionality: https://github.com/bikeshedder/tusker#how-does-it-Work

What a twist! Might we ask what field you work in? Seems niche