Embedding SQL queries in Go
Go 1.16 introduced the embed package to the standard library. This library allows us to embed static content within your compiled executable program. This is amazing, because we only have to ship one binary, even when we want to include an HTML page, for example.1
One application for embedding that I personally find very exciting, is SQL. Call me old fashioned, but I love me some plain old SQL queries. I think they’re easier to read, reason about, debug, and maintain than anything an ORM or SQL statement builder library can provide.
Without embedding, we could use string literals to represent queries:
package store const getAsset = ` SELECT a.id, a.publication_reference, a.purpose, mt.media_type FROM assets a JOIN media_types mt ON a.media_type_id = mt.id WHERE publication_reference = $1; `
Unfortunately, many editors will not recognize the contents of the
string as SQL code. As such, the string will be shown as a string, without any
syntax highlighting. Gofmt will not format the query either. On top of that, to
make the query line out nicely, we may want to add some padding to the query.
How much padding? The answer will depend on your team’s styling conventions, or
even on the opinion of the individual writing the code.
Wouldn’t it be nice to move these queries to their own files? I’d like to think so, and Go provides us a great way to do just that. This is how I currently embed SQL queries:
-- path/to/project/store/sql/get_asset.sql SELECT a.id, a.publication_reference, a.purpose, mt.media_type FROM assets a JOIN media_types mt ON a.media_type_id = mt.id WHERE publication_reference = $1;
// path/to/project/store/queries.go package store import _ "embed" //go:embed sql/get_asset.sql var getAsset string
The embedded file in the
//go:embed directive is always relative to the
location of the source file that contains the directive. In other words, if our
embedding Go file resides at
store/queries.go and we have a directive such as
//go:embed sql/get_asset.sql, the Go compiler will try to read a file at
Embedding SQL queries has a couple of benefits. The first, and perhaps most
obvious, is that storing SQL queries in
.sql files allows your editor to
apply syntax highlighting, formatting2, and all of that other cool stuff
editors do when they know the type of file they’re editing. When a query is
hidden in a string in a
.go file, there’s no easy way for the editor to know
that the string is in fact an SQL query.
Another emergent benefit of embedding is that it becomes very easy to find out
which tables and columns are used where. Recently, I was cleaning up some dead
code. There was one column,
purpose, which had served its purpose (pun
intended). Its value could easily be derived, so it was due for removal.
If my queries wouldn’t have been stored in
.sql files, I would have had to
grep the entire codebase for
purpose, and I would have had to deal with a lot
of noise from the surrounding Go code, which was still using the concept of
Instead, since my queries were stored in
.sql files, finding all usages of
this column was as easy as:
find . -name '*.sql' | xargs grep purpose
As with anything in the field of software engineering, embedding SQL files is not a silver bullet. Sometimes you need to dynamically construct queries. Sometimes your queries are so simple, it doesn’t make sense to put them in separate files.
It has, however, made our code base more consistent. Separating Go from SQL is already paying dividends.