Skip to main content
Preslav Rachev
  1. My Writings/

Things to Consider When Going With sqlc

·4 mins

credits: casparrubin @ unsplash

Newcomers to Go may often get surprised when faced with the task of using a database. While the standard library provides excellent support for database access, it is often at the expense of writing verbose boilerplate code. This is why many Go developers I know of got excited when sqlc came out. sqlc is a code generator that would turn SQL code into statically-typed Go code. To parse the SQL into Go code, sqlc has to validate it on a structural level, which is yet another big plus.
Compile SQL to type-safe Go

Thus on paper, sqlc seems like the best of both worlds. sqlc saves people time (and potential errors) when writing database-related Go code. At the same time, even though generated, the code looks similar to what one would have had to do anyway. Indeed, very verbose but readable, and once generated, it has zero external dependencies. Sounds fantastic, right?

Well, sort of. And here are my issues with it:

First, the fact that you are now using a tool to generate the boilerplate code away does not address the fact that you are dealing with low-level SQL abstractions. If you took the sqlc-generated code and used it across your application, you’d be leaking those around. Thus, get prepared for some mapping back and forth. This means more boilerplate Go code, but you’d have to write it manually this time. Or you might as well call it a YOLO and live with the fact that you will never move away from that particular database type (fair enough).

sqlc is smart but not as smart as you think. For instance, while it may structurally validate your SQL code, don’t expect it by any means to be able to help you with refactoring your SQL code, for instance. Suppose you change the name of a column in one of your tables. Re-generating the code with sqlc might point out some problem spots, but I have had way too many queries, which it simply assumed were perfectly fine. So, in the end, refactoring is left to you, your search and replace tool of choice, and (hopefully) many, many tests. Unlike an Object-Relational Mapper (ORM), where you need to change a single place, with vanilla SQL, you must go over every query.

Related to the previous point, sqlc is not always able to pick the exact data type you want to use in your Go code. Or do you want to use a particular name for one of your query parameters? For these purposes, the sqlc community has developed a few sqlc-specific decorators like sqlc.arg(), sqlc.narg(), etc. Those are helpful and useful, no doubt. However, once you have added them to your SQL code, I think it stops being pure SQL code. You can’t copy and paste it into a database tool without a few modifications.

-- name: Feed_UpdateNextRetry :exec
UPDATE feeds
SET next_retry_at = sqlc.arg(next_retry)::timestamp
WHERE id = $1;

Not every SQL query can simply be set in stone. In my developer experience, I have had quite too many situations where the same function call would result in entirely different SQL queries depending on the logic. This is one particular advantage of constructing SQL on the fly that sqlc cannot address. If you have such dynamic queries and still want to use sqlc, you have to accept that one big dynamic query would turn into multiple smaller ones that you have to stitch together manually. Either that, or you’d have to end up with numerous, almost identical versions of the same SQL you’d assign to different functions. Good luck refactoring those.

Last but not least, you have to ask yourself what you are building. If your application needs a few very particular SQL queries, then sqlc is a great fit. Suppose you are making a more traditional, CRUD-style application, however. In that case, you’d quickly notice that writing every single SQL query by hand and then getting sqlc to turn it into Go code will soon prove to be an unmaintainable mess. Every single function ends up becoming a method attached to the same Queries instance, which introduces the need for proper naming and some sort of manual name-spacing. As much as the Go community likes to stay away from ORMs, in that case, I’d very much go for one rather than complicate my life by trying to stay “idiomatic.”

As always, don’t blindly go for a tool or a library. Do a test, whether it works for your particular use case. That applies to using Go, too, by the way.

So, there you go. If you’d like to share your personal experience with sqlc, you are very much welcome to do in the comments section below.

Have something to say? Join the discussion below 👇

Want to explore instead? Fly with the time capsule 🛸