The Other 10%

So we’re now left with the problem that raw SQL is kinda verbose and hard to re-use. We don’t really have a great answer, but we have started organizing our Repo files in a way that helps.

Each one of our repos lives in a file by itself. Those files have the repo interface up top, followed by a bunch of consts defining the SQL queries we’re gonna need, and then a struct that implements the interface.

The file that the CategoryRepo from above lives in looks something like this:

We tend to write a bunch of very similar get methods that all have to do the same post-query transformations on the data we fetch from Postgres, so we usually end up writing a getOne and a getSlice func to help with that. Those funcs do all the fetching of related entities (by calling other repos) and setting computed fields.

They tend to look a little bit like this:

Which is, honestly, pretty great. It gives us one place to look for N+1 queries, missing fields, and all that fun stuff that shows up when we’re looking at bugs.

Ok Where Are We?

The Bad Parts

While these funcs are looking great, the SQL up top is a little bit of a sadder story. The more we’re adding, the noisier the file gets. We’ve been composing consts (and only ever consts, no runtime SQL changes here) to reduce the noise.

Episodes are pretty chill, but we have quite a few different ways of fetching Users and things in UserRepo can get pretty crazy once you start adding in bulk selects.

There’s a lot of noise there. Preparing all our statements in new-struct funcs means that finding the query for a statement generally involves doing a go-to-definition on a statement, finding it in the new-repo func, and then doing a go-to-definition on a constant just to get back to our composed consts. Naming conventions for the consts and statements help a lot, but it’s still a little gnarly. We’ve considered moving the queries into their own file whatever_queries.go but haven’t tried that yet.

Unsurprisingly, relationships between structs/tables are hard. We’re currently enforcing that a EpisodeRepo is the only repo that is allowed to return an Episode and a UserRepo is the only repo allowed to return a User . We end up with Repo implementations that that have other Repos as fields, and where the referenced repo knows about a join table.

Whew that’s gnarly prose. Here’s an example: an Episode has multiple Authors and a UserRepo has to know about our episode_authors join table:

It works well enough that we avoid having any real fun N+1 query situations. It does mean that every time we want to grab an Episode we’re fetching every author for it. This is fine for now, and we can get away with it at our current data volume, but that may not always be the case.

Finally, since we’re writing our own SQL queries for all those Get methods, we write tests for all of these funcs that would come for free with an ORM. That definitely feels like it should count towards our lines of boilerplate-per-Repo.

The Good Parts

While there’s still an amount of boilerplate in this code, the end result is still readable. It hasn’t been a crazy thing for us to drop in and change our Repos pretty significantly. This has held true when our repos have two dozen methods, which is crazy. The most painful part has been the up-front creation of new Repos. Trading a few minutes of up-front tedium for readability and maintainability seems like a very Go tradeoff, and we’re generally happy to optimize for operator happiness over developer happiness. So, we’re fine with that.

Not having to guess what an ORM is doing is another operator happiness thing — since we’re writing queries by hand, we have to think up front about what indexes a query is going to use, whether or not a join is faster than two queries, or if a SELECT FOR UPDATE is locking rows that it doesn’t need to. Running EXPLAIN ANALYZE on a query and also having code review cover SQL is amazing, and all that explicit thinking about SQL really sets us up to think about things like “transactions” and “data integrity”. Ultimately, we’re writing better SQL than we would be otherwise because we have to think about it.

And last but not least, it means that we’ve got full access to the dope features in newer versions of Postgres. Sometimes this is maybe a little too much Shiny New Toy, but jsonb column types and INSERT… ON CONFLICT DO NOTHING have already been awesome. Given that we’re doing code-review for SQL, it’s not crazy to use those things and there’s no weird impedance mismatch between an ORM’s abstraction and those features.

THE END

So that’s where we ended up. We’re happy enough with our current situation, but still keeping our ears open for the next cool thing to come along. I occasionally space out on the train and think about what something like a Go take on HugSQL would look like, so if you’re working on that DEFINITELY let me know.

If you’ve got questions or think we’re absolutely insane or had a similar experience, leave a comment or a note and I’ll do my best to reply.

✌️