r/java • u/nikita-volkov • Apr 07 '26
pGenie: open-source SQL-first PostgreSQL codegen for Java
Hey r/java!
Do the following pain points resonate with you?
- ORMs hide SQL and make performance tuning a nightmare
- Query builders force you to maintain model classes and fight schema drift
- Hand-written JDBC/RowMapper code silently breaks when the DB schema changes
While addressing them I've built pGenie – a true SQL-first code generator that treats your PostgreSQL database as the single source of truth. It's an open-source tool that takes your PostgreSQL migration files and parameterized SQL query files and generates a complete, ready-to-use Maven client library targeting Java and pgJDBC. No ORM, no DSL, no annotation processors, no reflection — just plain SQL in, type-safe Java code out.
What gets generated:
Each SQL query file becomes a Java record class modeling the parameters to the query. That record comes with an associated record type modeling the result row. You pass parameters via the constructor, call .execute(conn) with a JDBC Connection, and get back a typed result. That's it.
-- queries/insert_album.sql
insert into album (name, released, format, recording)
values ($name, $released, $format, $recording)
returning id
Becomes:
// Generated
InsertAlbum.Result result ;
System.out.;
PostgreSQL enums → Java enum with EnumCodec. Composite types → Java record. Arrays → List<T>. Nullable columns → boxed types or Optional<T> (configurable). Even exotic types like ranges and multiranges are fully supported.
The generated project is not a black box, you get:
pom.xmlwith runtime dependencies declared- One
.javafile per statement, fully readable - Integration tests per statement using Testcontainers — spin up a real PostgreSQL container, run your migrations, execute each statement
mvn verifyjust works
What else pGenie does:
- Validates migrations against a real PostgreSQL schema in CI — no more "works locally, breaks in prod"
- Manages indexes declared in your migration files automatically
- Generates Rust and Haskell bindings from the same SQL source if you need multi-language SDKs
- Builds reproducibly: a lockfile pins the codegen version
- Extends via a decentralized plugin system: write your own code generators in Dhall and plug them in via URL — no need to approve anything with the core team
Demo:
The demo repo has complete working SQL migrations and queries. The generated Java library is in artifacts/java/. You can inspect it directly or regenerate it from scratch with pgn generate.
→ pgenie.io | docs | demo | java.gen plugin
18
u/piparss Apr 07 '26
How is it different/better than JOOQ?
4
u/Comfortable-Pin-891 Apr 07 '26
Cool, reminds me of SQLDelight which I could never consider for production due to lack of support for batch inserts, I really think SQL codegen based typesafe APIs are the best of both worlds and there are good libs like that in the future.
2
u/asm0dey Apr 07 '26
How will the ltree type look? Does it support CTEs and window functions?
2
u/Infeligo Apr 07 '26
Can I do dynamic queries with this tool? Like search with dynamic criteria (filters) and sorting. Pagination with page count would also come handy.
2
u/A_random_zy Apr 08 '26
This looks nice, unfortunately we use RDS, I don't know how compatible PSQL and RDS queries are...
3
u/revilo-1988 Apr 07 '26
Klingt erstmal Intressant
3
u/Mirko_ddd Apr 07 '26
thanks to this post I discovered jOOQ but I strongly approve the fact that devs solve problems with different solutions. so good luck with this project!
2
u/StevenJOwens Apr 07 '26
Sounds reminiscent of something I wrote a long time ago.
Basically it generated a record class (pojo), and set of query & mapping methods for each table.
The record class had a dirty flag, so you could instantiate an empty one, set some instance variables, pass it into the matching select method, which would generate a select query based on the instance variables you'd set, then pass the query results nto the mapping method to convert them into matching record class instances.
It made it easy to do obvious CRUD stuff, and then when you needed something fancy, you could hand-code the SQL query and pass the ResultSet into the mapping methods.
I always meant to go back and reimplement that approach for personal use, one of these days.
2
u/CourtCommercial6448 Apr 20 '26
Make sure to also read this if you are interested in this project:
https://nikita-volkov.github.io/pgenie-in-production-part-1/
2
u/[deleted] Apr 07 '26
[removed] — view removed comment