Golang: xo + sqlx

When working with databases in Golang, it's common to map tables to structs.

However, since the database structure also needs to be updated over time as needed, manually keeping the code in sync with the current structure is surprisingly tedious (and, more than anything, error-prone).

So, let's try to simplify this process by using xo, which can generate code that matches the database, together with sqlx, which bills itself as an extension of the standard database/sql library.

• xo
• sqlx

We'll assume the following environment has already been set up.

PostgreSQL

Version 9.6, with the users table already created using the following DDL.

CREATE TABLE users (
    user_id bigserial PRIMARY KEY,
    email varchar(100) NOT NULL,
    created_at timestamp NOT NULL,
    updated_at timestamp NOT NULL
);

Golang

Golang version 1.12, with xo already installed.

About xo

Following the README, if you run the command below, files will be generated under models.

# generate code for a postgres schema
$ xo pgsql://user:pass@host/dbname -o models

The users table mentioned above is output as models/user.xo.go, as shown below.

// Package models contains the types for schema 'public'.
package models

// Code generated by xo. DO NOT EDIT.

import (
"errors"
"time"
)

// User represents a row from 'public.users'.
type User struct {
UserID    int64     `json:"user_id"`    // user_id
Email     string    `json:"email"`      // email
CreatedAt time.Time `json:"created_at"` // created_at
UpdatedAt time.Time `json:"updated_at"` // updated_at

// xo fields
_exists, _deleted bool
}

// omitted below

By updating the model definitions with xo alongside your database migrations, you can prevent the database structure and your code from drifting apart.

About sqlx

I'll leave the basic usage of sqlx to its README, but the following page has documentation on struct tags.

• Illustrated guide to SQLX

You can use the db struct tag to specify which column name maps to each struct field, or set a new default mapping with db.MapperFunc().

It doesn't appear to be strictly required, but it's preferable to explicitly specify columns with tags.

xo + sqlx

Since the structs output by xo only have a json tag, let's add a db tag as well so they work with sqlx.

To customize the output, as described in the README, you need to prepare a template file.

• Custom Template Quickstart

Once you copy the base templates into templates, you can edit the parts you need and use those templates.

# change to working project directory
$ cd $GOPATH/src/path/to/my/project

# create a template directory
$ mkdir -p templates

# copy xo templates for postgres
$ cp "$GOPATH/src/github.com/xo/xo/templates/*" templates/

# remove xo binary data
$ rm templates/*.go

This time, since we want to add a tag to the PostgreSQL output, we'll edit postgres.type.go.tpl.

• https://github.com/xo/xo/blob/master/templates/postgres.type.go.tpl

We'll edit the part on line 10 where the tag is written, adding db:"{{ .Col.ColumnName }}" there.

- 	{{ .Name }} {{ retype .Type }} `json:"{{ .Col.ColumnName }}"` // {{ .Col.ColumnName }}
+	{{ .Name }} {{ retype .Type }} `json:"{{ .Col.ColumnName }}" db:"{{ .Col.ColumnName }}"` // {{ .Col.ColumnName }}

By specifying the template as an option, you can output a customized model definition.

$ xo pgsql://user:pass@localhost/dbname -o models --template-path templates/

With this, the resulting user.xo.go now looks like this, with db added to the tags.

// Package models contains the types for schema 'public'.
package models

// Code generated by xo. DO NOT EDIT.

import (
	"errors"
	"time"
)

// User represents a row from 'public.users'.
type User struct {
	UserID    int64     `json:"user_id" db:"user_id"`       // user_id
	Email     string    `json:"email" db:"email"`           // email
	CreatedAt time.Time `json:"created_at" db:"created_at"` // created_at
	UpdatedAt time.Time `json:"updated_at" db:"updated_at"` // updated_at

	// xo fields
	_exists, _deleted bool
}

// omitted below

With this, we've been able to make the model definitions generated by xo work with sqlx.

Next, I'd like to put together some notes on managing this alongside migrations.