So recently, against my best judgement, I’ve started working on a new project. The classic “how hard can it be?” kind of thing. What that actually is is not important right now. More important are the tech choices and how I shifted my opinion on generated code.

The tech stack I went with is what I usually pick; go for backend, pgx and sqlc for database access, htmx for frontend matters. I found managing Go’s template/html always a bit fiddly and wanted a better way of dealing with nested components and being able to render them individually. I have seen templ recommended many times and because it was a new project, I gave it a try. Initially I felt having yet another tool that generates go was weird, but after a few components I was sold.

An Old Problem That Didn’t Need Solving

And then I encountered a problem I’ve been trying to solve nicely for a while. See, in databases you can paginate through datasets very easily via limit <count> offset <num> which translates very nicely into page numbers and is overall straightforward to implement. But it’s also not great for performance for reasons that are better explained in Use the index, Luke. The better, or faster way to paginate is via keyset pagination. In keyset pagination we build queries and paginate them by limiting the amount of rows the database has to look at via where conditions. Usually, a sort column and the primary key are sufficient to efficiently paginate and access rows. This comes at a cost though; queries need to include the specific where conditions based on the columns sorted by and which direction the data is to be sorted by and we’re accessing the first page (which doesn’t require the where conditions at all) or subsequent pages. For example:

select * from books order by title asc, id asc limit 50; -- First page
select * from books where (title, id) > ('Database Internals', 437) order by title asc, id asc limit 50; -- subsequent pages
select * from books order by title desc, id desc limit 50; -- First page, but descending order
select * from books where (title, id) < ('Database Internals', 437) order by title desc, id desc limit 50; -- subsequent pages, descending

Now, that’s already four queries. And you could probably get by by using max values to get rid of the extra case for the first page, but I never felt strongly enough to try it. In either case, it would still be two queries. And once you add sorting by different columns, the number just keeps going up. Now you might ask, do you really need the most optimized pagination queries? And you’re absolutely right, I don’t, but this is my toy project and I do what I want here. And I want fast.

ORMs

At this point most people reach for a query builder or an ORM. I don’t hate ORMs, but I have feelings that are grounded in the fact that they are code that allow the generation of any possible query at runtime. Or in other words, the state space that they open up for SQL queries is enormous, making query planning and analysis harder and the queries that hit your database are sometimes a surprise. And you really don’t want to surprise your query planner. Performance is an interesting thing at any end of the spectrum: if you deal with a lot of data, design decisions make or break your systems. This is true on huge expensive systems and on tiny single board computers.

On that note, most ORMs rely on Go’s empty interfaces as a crutch to deal with the variety of types which in turn requires the compiler to use pointers which are nullable and the use of reflection to figure out what it’s even dealing with. I suspect that all these things do have an impact on performance but I have no numbers to back this up and it might just be negligible. But it also means you’re giving compile-time known types and I’m a big fan of those.

Generate queries for sqlc

My choice to deal with database access is sqlc. It’s so nice to just write SQL queries and have it generate structs and all the binding code for you. No need to deal with a DSL layered on top of constructs in a different language that almost always are more convoluted than the language they’re translated into. So all in all sqlc is fantastic. Except this one flaw. It doesn’t do any dynamic queries. So in order to deal with pagination, I’d have to write every single permutation of these queries myself which is totally doable, but two important factors made me reconsider:

• I don’t wanna
• Pagination is more than just the queries

I had the realization that building this queries is a generative problem. If I can describe how these queries should be built, then I could just build these queries, write them into sqlc’s queries file, and have sqlc do the rest. I don’t know why I didn’t think of this earlier, and I’m quite certain many other people have had this thought before, but nonetheless I was quite happy with this idea.

In an afternoon I cooked up a prototype. Just a small go main that reads some metadata and then spits out all the necessary queries to paginate an entity; two queries per sort column per sort direction, complete with sqlc comments so I just added the generated queries file to the sqlc configuration. And it worked.

Pagination

But that was only half the battle. Pagination requires a lot more work. Starting from the top, pagination is usually triggered in webapps via query params. That means we require functionality to safely parse order direction, order by column, limits, etc from the query string into a structure that holds all this data. Then we have to take this structure and based on its values pick the correct SQL query, bind the values to the parameters, and execute the query. And finally, we have to determine the values for the next page from the results.

That’s a lot of machinery; but like the queries, it follows a pattern. And then came the real epiphany: now that I had code that understood the pagination and the entities involved, I figured I could also generate Go code to support this! After getting over the feeling of … disgust?… I threw together some more code that now generates all the moving pieces needed:

• Paginator types that encapsulate the values need on what to order by and in which direction
• Total functions that parse query params into paginators with safe defaults
• The Paginated type to describe a set of rows and the paginator to get the next set
• One function per entity that takes a paginator and invokes the correct queries
• A handful of useful helpers and utilities

A rocket powered crutch?

It was strange to write code that generates code because it feels like an affront to clean design. If you look closely it’s a lot of very similar code. It smells of duplication. Granted, Go doesn’t give us a lot of tools for metaprogramming, but still, for a moment it felt like I failed at the strangely romanticized idea of coming up with the perfect design. It felt like a was reverting to a crutch because I couldn’t figure out a better solution. And then I realized it was more of a jetpack than a crutch because I could very quickly iterate on a core concept and then have the program update quite a lot of code for me. I had found an “abstraction” that works around Go’s limitations and it’s fun to use, plus the code has some very nice properties:

• No pointers. Everything is known at compile time.
• All types are known at compile time. No need for interface{} anywhere! No need for type assertions or type checks.
• Queries are known beforehand. They can be inspected and checked in, no more runtime surprises.

I don’t know why I looked down on code generation for so long. Now I regret that we don’t have better tools for generating go code because it’s a tool that I will happily use again. This approach is not without issues though. Generating Go code via Go templates is not exactly fun, though it’s fast enough that I can iterate without getting slowed down too much. The other is this nagging feeling that I could have been done 23 paragraphs ago with a simple limit/offset query, but here we are.

sqlc-gen

If you’re interested in the actual generator, you can find it on sr.ht: https://git.sr.ht/~ilikeorangutans/sqlc-gen . I’ve written some initial documentation but and it’s usable. Maybe you’ll use it? Let me know.