ANN: pGenie – a SQL-first code generator for PostgreSQL: no DSLs, no ORMs, no hand-rolled codecs

Yes.

Following is a comparison which I’ve originally posted on Reddit and Lobsters.

The main differences are:

1. Analysis engine. pGenie is designed from ground up to rely on the Postgres server. That’s why it sees everything the way Postgres itself does. sqlc made an initial mistake of going with a custom emulator, which as I understand led to an endless stream of bugs caused by the emulator being out of sync with the interpreter of Postgres. So pGenie supports any Postgres query of any complexity, sqlc likely does not. Lately they’ve been attempting to change this strategy and as I understand they have a mixture of both strategies now.

2. pGenie is focused on Postgres only. This lets us represent all features of Postgres in the internal models. That’s why we support composite types and multiranges for instance, which sqlc does not. Targeting multiple databases inevitably requires you to limit the internal models to the least common denominator which leads to the reduction of supported features of the DB. sqlc targets multiple DBs.

3. Signature files. pGenie generates signature files which are then used as the source of truth about what types your queries expect. This is essential for preventing schema drift, when you create a migration that changes a column’s type thus silently breaking your queries. They are also used for fine-tuning the derived types (e.g., enforcing non-null on parameters, where Postgres allows nulls). sqlc does not have such a concept.

4. Index management. pGenie performs analysis on the indexes used by the queries and generates recommendations on how to optimize them. E.g., delete the unused ones or add ones on columns that cause seq-scans. It can actually generate such migrations for you. sqlc does not touch this area.

However it must be noted that sqlc is a large project now and there is a large community around it and that inevitably has impact in areas like the amount of available resources, tutorials, and community support.

There’s also a historical point. This project was originally developed when sqlc wasn’t on the radar. I was experimenting with a different strategy for the distribution of pGenie (SaaS), which did not succeed. Now I’ve pivoted on the strategy to Open Source + Consulting. E.g., here’s the initial announcement on Reddit dated 4 years ago.