migra

I've been comparing PostgreSQL schemas for so many years now. I've been using all kinds of frameworks for ORM and code-first schema generation, but getting the actual "diff" between what I have and what I want has always been a pain. For a long time, I've relied on a customized version of migra (https://github.com/djrobstep/migra). It was the best tool of all those I've tried, but it's unfortunately deprecated and no longer maintained. I kept dreaming of building my own comparison tool - one with modern features, better performance, and the extra features I always wished I had. I've had the architecture of the tool in my head for years, but knew that I will never find the time to do the tedious introspection query logic. Last weekend I decided to finally give it a try and built an initial version with Claude. Long story short it's now st

See also: https://github.com/djrobstep/migra

Reminds me of Migra: https://github.com/djrobstep/migra

Hey, are you using some tool like the unmaintained migra https://github.com/djrobstep/migra (perhaps using this tool https://github.com/blainehansen/postgres_migrator) or pg-schema-diff https://github.com/stripe/pg-schema-diff or maybe this other unrelated pg-schema-diff https://github.com/zombodb/pg-schema-diff anything like it? Is it open source? I mean, I see you say > We then use a schema diff tool, like migra, to figure out the necessary updates to views and functions when generating the migration file. But "like migra" is very nonspecific. I guess you are not using migra itself

There are a few stand-alone declarative Postgres tools available, including Stripe's pg-schema-diff [1], Tusker [2] which wraps Migra [3], and sqldef [4] which supports multiple DBMS. For sake of completeness for users of other databases: for SQLite check out stb-tester's migrator [5], and last but not least for MySQL/MariaDB there's my tool Skeema [6]. [1] https://github.com/stripe/pg-schema-diff [2] https://github.com/bikeshedder/tusker [3] https://github.com/djrobstep/migra [4] https://github.com/sqldef/sqldef/ [5] https://david.rothlis.net/declarative-schema-migration-for-s... [6] https://github.com/skeema/skeema

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

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. From Prisma Migrate's architecture doc: "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 I'm not aware of any other older schema change systems that work this way. It was certainly never "pretty common" prior to Skeema

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 / ty

Using a relational DB doesn't necessitate the use of an ORM. This is another case of YAGNI. I rarely reach for an ORM until there's significant enough complexity to warrant it, and even then it's not a decision made lightly. Designing and maintaining a schema is not too arduous. There are tools which can produce a diff between the current and desired schema for your database, for instance Migra for Postgres (https://github.com/djrobstep/migra).

I am not very familiar with this tool, and the documentation doesn't mention this use case but it does look like you can pass specific schema(s) or exclude them: https://github.com/djrobstep/migra/blob/master/migra/migra.p... It looks like the schema do have to have the same name between the databases, which I think is sensible but again I could be wrong. This is a darker corner of the postgres tooling world that could definitely use some light, so good on the migra devs to trying this out. If you do get it working, consider sending them a PR with some documentation on your use case!

Check out migra[1] - it covers most of that. One notable omission is column-level grants though - as far as I know changes to those aren’t detected [1] https://github.com/djrobstep/migra

https://atlasgo.io/ or https://github.com/djrobstep/migra

Declarative schema management tools make this much easier. The concept is your schema repo just stores CREATE statements, and the schema management tool knows how to generate DDL to transition between the current state in your DB and the desired state in your repo. Not sure about Google, but Facebook/Meta has used this approach company-wide (via internal / non-open-source systems) for over a decade. I'm the author of declarative schema management tool skeema (https://www.skeema.io, for MySQL / MariaDB). Some other options in this space are sqldef (https://github.com/k0kubun/sqldef, for MySQL or Postgres) and migra (https://github.com/djrobstep/migra, for Postgres). In MS SQL Server, SSDT DACPACs are also somewhat similar.