Skip to content
plainsql

plainsql

A tool for generating typed code from SQL queries. Best illustrated with an example:

  1. Write a SQL query, add a comment annotation:

    -- plainsql: query GetUser returns one
    SELECT * FROM users WHERE id = $1;

  2. Run plainsql generate

  3. Generated code:

    // The generated code
    func (q *Queries) GetUser(ctx context.Context, id int64) (User, error)
    

    // Use it in your code
    user, err := queries.GetUser(ctx, 1)

But why?

So the SQL stays “plain sql” without macros or generator-only syntax leaking into the queries. The extra information needed for code generation lives as comment annotations.

And because everything nowadays needs an AI angle. My thesis is that LLMs are pretty damn good at SQL, and if we keep them close to the raw queries it removes layers of indirection for them to work through. There is no ORM or query builder in the middle, and the database schema gives them a concrete way to catch mistakes.

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 the same, write SQL queries you want the database to run and then generate type-safe application code needed to call it.

There are serious pros to this approach:

  • Real SQL queries!
    • I will never forget the beast of an analytics 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 final query looked like.
  • No ORM or query-builder abstraction.
  • Queries evolve with the schema.
    • If a migration breaks something, code generation fails at build time.
    • Likewise, a query that doesn’t match the schema will also fail to build.

Again, give credit where credit is due. sqlc proved this model works and it made writing SQL a practical alternative to ORMs. So I do think this is the right direction.

But, there’s a bit of tension in those query files (and this is true for any project that tackles this problem), it attempts to describe two different things at once:

  1. The SQL statements the database executes.
  2. The type-safe code the generator should create.

SQL describes the query itself exceptionally well, haha, you already knew that. But it doesn’t 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 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 as “plain” as possible. Hence the name.

Comment annotations have worked surprisingly well in goose (a migration tool). Devs and coding agents quickly get over having to annotate their migration files with -- +goose up.

Initial support will focus on Postgres and Go, but I’d like to expand to support SQLite and potentially other languages.

That’s all for now, here’s some relevant links: