postgres_migrator VS cornucopia

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

There are multiple queries each separated by ; and on top of each query, there's a comment giving a name to the query (it's more like a header)

I think the only thing that would require specific support in postgres_lsp is using the :parameter_name syntax for prepared statements [1] (in vanilla Postgres would be something like $1 or $2, but in Cornucopia it is named to aid readability). But, if postgres_lsp is forgiging enough to not choke on that, then it seems completely fit for this use case.

[0] https://github.com/cornucopia-rs/cornucopia

[1] https://cornucopia-rs.netlify.app/book/writing_queries/writi...

Some examples for anyone else reading:

https://github.com/kyleconroy/sqlc

https://github.com/cornucopia-rs/cornucopia

This is my preferred method of interacting with databases now.

Very flexible.

I like Cornucopia. It’s a SQL-first approach, so I don’t have to worry about an ORM generating pathological queries. It’s also basically zero cost compared to directly using rust-postgres and supports both sync and async. I also like that my SQL queries end up separate from my Rust code, so it’s easy to update all the relevant queries when the schema changes.

Also, I moved away from SQLx due to slow compile times and now use https://github.com/cornucopia-rs/cornucopia

While we're on the subject of ORM's I really like the https://github.com/cornucopia-rs/cornucopia way of doing things.

Basically write SQL in a file and code generate a function that runs the SQL for you and puts it into a struct (this one is for rust)

I think there's a library to do the same thing with typescript.

For me, the best way to talk to the database is with SQL and I don't have to learn an ORMs way of doing it.

You can take a look at https://github.com/cornucopia-rs/cornucopia which is a thin codegen layer on top of tokio-postgres for ease of use.

I think we have that https://github.com/cornucopia-rs/cornucopia

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

Does Cornucopia satisfy this requirement?