I'll spare you the trouble of reading this whole thing. This project is similar to sqlc where you write SQL and it generates typed code. The main difference is a richer annotation system to give more control over what gets generated.
But why? So the SQL itself stays "plain sql", without macros or generator-only syntax leaking into queries sopping them from running directly against the database. The extra information needed for code generation lives in comments instead.
Alright, so here's the longer version. I've been a long-time sqlc user and for the most part it has worked okay. The idea is simple, write SQL queries you want the database to run and then generate type-safe application code needed to call it. The compiler does the heavy lifting ensuring queries match the schema, which becomes even more important in the age of AI.
There are pros to this:
- Real SQL!
- I will never forget the beast of an analyatics query a coworker wrote with a SQL-builder. There was just no way to make heads or tails of what even the general shape of the query looked like.
- Compiler catches type and schema mismatches.
- No ORM or query-builder abstraction.
Again, give credit where credit is due. sqlc proves this model works and it made writing SQL directly a practical alternative to ORMs. So I do think this is the right foundation.
But, if you squint there's a bit of tension in those query files (and this is true of any project that tackles this problem), it describes two different things at once:
- The SQL statements the database executes.
- The code the generator should create.
SQL describes the query itself exceptionally well, haha you already knew that. But it does not say what the generated code should be called, whether it returns no rows, one row, many rows, what it's argument should be called, whether it is batched, or what type should hold the result (inferred, but difficult to get right 100% of the time).
As code generators grow more capable they need more of this extra information.
That's the crux of it, encoding more information into the raw SQL queries means risking those queries no longer being able to work with editors, formatters and tools (think psql).
It's an idea that has been brewing in my head for some time, to see how far we could take a comment-based annotation system to drive code generation, while keeping the SQL queries plain.
This has worked surprisingly well in goose (a migration tool). People quickly get over having to annotate their migration files with -- +goose up.
That's it for now. I'll follow this up with a few more technical posts on where I've seen sqlc break down and how it could be approached. Likewise I'll work towards formalizing a draft of the annotations.
Stay tuned!