App Development

Go Generate Your Database Code

Use go:generate to quickly build domain methods on top of sqlx

I find Golang to be an attractive option for both API server development and cross-platform mobile development. It’s a lovable beast of a language that puts pragmatism on a pedestal, arguably at the expense of desirable features (ahem, generics). In the vein of pragmatic solutions, Go version 1.4 added native support for code generation via go generate. I recently explored how to use generation to speed up development of database code.

First, let’s look at the situation without using code generation.

Starting with** database/sql**

Let’s set up our database with an accounts table that holds the name of an account, keyed by a unique id. I’ve been using Postgres for server work, so here’s an example using its SERIAL type:

CREATE TABLE accounts (
  name VARCHAR(255)

In our code, we’ll build a struct that will hold the equivalent data:

type Account struct {
    ID int64
    Name string

Now, using Go’s database/sql package we could fetch an account like this:

func main() {
    db, _ := sql.Open("postgres", "dbname=demo-db sslmode=disable")
    var account Account
    AccountByID(db, &account, 1)
    fmt.Printf("Account: %v", account)

func AccountByID(db *sql.DB, account *Account, id int64) error {
    row := db.QueryRow("SELECT id, name FROM accounts WHERE id = $1", id)
    return row.Scan(&account.ID, &account.Name)

The standard library is an excellent foundation, but column scanning quickly gets tedious when you are working with many structs, each with many fields.

Continuing with jmoiron/sqlx

The jmoiron/sqlx library adds extensions on top of database/sql that make it a bit more convenient to work with. Rewriting the above access with sqlx we can replace the column scanning with the sqlx struct helper:

func AccountByID(db *sqlx.DB, account *Account, id int64) error {
    return db.Get(account, "SELECT id, name FROM accounts WHERE id = $1", id)

That’s great! But extending it to production usage…

Production needs

I want the following features for every database query:

  • Extraction of the SQL queries out of my Go code for easier maintenance.
  • Error wrapping inside my error methods, so that I have greater context when inspecting errors than just error in SQL near (.
  • An interface to hide my data access implementations for greater testability.
  • A mock implementation of data access.
  • Access to read-only methods both directly as well as through a transaction, but the ability to choose write actions that should only be accessible through a transaction.


That means that for something like the above AccountByID query, I end up with all of the following code (modified to show my real usage):

SQL File

SELECT id, name FROM accounts WHERE id = $1 LIMIT 1; 


type Reader interface {
  AccountByID(account interface{}, id int64) error

This interface is then included in both my DB and TX interfaces, so that both can read accounts by ID.


func (db *RDBMS) AccountByID(instance interface{}, id int64) error {
    return selectOne(db, "accounts/first_by_id.sql", instance, id)

func (tx *TX) AccountByID(instance interface{}, id int64) error {
    return selectOne(tx, "accounts/first_by_id.sql", instance, id)

Those methods rely on a helper method selectOne that will handle looking up the SQL from the file, calling the appropriate Get on sqlx.DB or sqlx.TX,and wrap any errors that occur to give me context into which query caused the trouble.

Mock Implementation

func (db *MockDB) AccountByID(instance interface{}, id int64) error {
  instance = db.Thing
  return db.Error

The mock object holds some general purpose values that I can set up in my test suite to create the result I want to replicate without relying on hitting a database in other logic tests.

That’s a lot of repetition for one method! And as my domain model grows, it gets more and more cumbersome to add methods and keep them up to date. Now let’s simplify our maintenance needs by using code generation to build the repetitive signatures.

Using code generation

Although the method signatures for two find queries may be different (e.g., AccountByID compared to a hypothetical AccountByName) they contain the same basic implementations. The only unique parts of the query are the method name and the parameters.

Go 1.4 introduced go generate, which extracts commands from //go:generate comments and runs them. I decided to use this to parse out custom commands and write a code generator to generate the unique parts of the code.

I thought about where I’d like to define the method signature and how to generate all of the necessary code from it. I settled on adding the additional information to the SQL file as comments, and then parsing them out to generate the required code.

Here’s the example for the AccountByID query:

-- !selectOne AccountByID
-- $1: id int64

SELECT id, name FROM accounts WHERE id = $1 LIMIT 1;
  • The custom command !selectOne tells my custom code generator that I want it to generate code that is selecting a single instance from the database.
  • After the command, I specify the name of the method that I’d like to be generated.
  • Finally, I go through the parameters that the query accepts, and the name and type that should be used for each.

This has a wonderful effect. Now my queries sit side by side with information about the name and type of each parameter.

Generating code from that file requires the generator to know how to parse the files and read comments, and have information about the expected format of code. But that’s a mere matter of programming! That I can do.

The generator I wrote implements the following custom commands:

  • !exists - Assumes a COUNT query to return a bool if the count is > 0
  • !selectOne - Scans a single instance from the database
  • !selectAll - Scans a slice of instances from the database
  • !insert - Inserts an instance into the database, returning a new int64 identifier
  • !updateOne - Updates an instance in the database, checking that exactly one row was affected
  • !deleteOne - Deletes an instance from the database, checking that exactly one row was affected
  • !exec - Execute arbitrary SQL

The same generator command will generate different code depending on the flags passed to it. That one annotated SQL file can be used to generate the interface definition:

//go:generate rootx-gen -mode interface -readType "Reader\_Gen" ...

The concrete real implementation:

//go:generate rootx-gen -mode code -readType "(db \*RDBMS)" ...

As well as the mock implementation:

//go:generate rootx-gen -mode mock -readType "(db \*MockDB)" ...

Presto! A single annotated SQL file now generates all the methods I need. Adding more queries is as simple as writing the query and labeling its components.

An important thing to note is that without the special commands in the comments, nothing is generated. That means I can write all of the database code I want by hand, and then use these generators only for cases where they are most convenient. It’s not the generator’s way or the highway.

Links to code

I’ve published the code to do the above in two repositories:

  • Acorn handles general code generation tasks, such as parsing files looking for special comments.
  • Rootx uses Acorn to implement the database access methods discussed above, built on top of sqlx. Full usage instructions are available on the command line via rootx-gen -h.

Additionally, in the examples above, I embedded my SQL files in my source code with go-bindata.

And one more thing…

Following the instructions here, it’s easy to start using this tooling to add cross-platform data access and business logic to your mobile apps.

I set up a sample project called Timestamp that stores and retrieves timestamps from an SQLite database.

You can see how to use Rootx in action by inspecting the contents of the db directory, specifically:

  • The sql subfolder holds the queries
  • db.go has the go:generate directives
  • db_gen.go has the generated code

Here’s what the built app looks like in action from a command line application written in pure Go, and from an iOS application using the same Go code but wrapped in a layer of Swift. blog-post-image go-generate-database-code IT

Moving from Monolith to Microservices Architecture

When a client decides to move from a monolith platform to microservice architecture,...

Read the article