Go and MySQL

I’ve been using Gorm for a while and wanted to see what it would look like to use the standard database/sql package instead.

My main concern was development time since Gorm makes it simple to create the structures needed for simple queries. Basically a lot less typing.

My exploration yielded a pattern that I’ve enjoyed so far. Also, my (not yet very thorough) benchmarks indicate that removing Gorm’s heavy reflection from the program yielded better application performance for large queries.

And I found it easier to do the tricks below since each interface satisfier can tailor their handling of the query, values, and results directly.

Some code:

// libs/db/db.go
package db

import "database/sql"

var db *sql.DB

// Register called from main.go with an instantiated db connection using the std lib's
// sql interface only (with appropriate driver).
func Register(in *sql.DB) {
	if in == nil {
		panic("db.Register was given a nil *sql.DB")
	}

	db = in
}

type HasQuery interface {
	Query() string
}

type HasValues interface {
	Values() []interface{}
}

type NeedsRows interface {
	UseEach(rows *sql.Rows) error
	SetTotal(count int64)
}

Other interfaces: NeedsInsertID, NeedsRow for single row queries, and NeedsAffectedCount for checking update queries in case I know exactly how many rows should be updating and rolling the transaction back if I get an unexpected result.

Next would be interface combinations that allow structs to use directly. I liked this because it seemed readable in a composed way to me.

// libs/db/list.go
type Lister interface {
	HasQuery
	HasValues
	NeedsRows
}

func List(model Lister) error {
	tx, err := db.Begin()

	if err != nil {
		return err
	}

	rows, err := tx.Query(model.Query(), model.Values()...)

	if err != nil {
		tx.Rollback()
		return err
	}

	defer rows.Close()

	err = model.UseEach(rows)

	if err != nil {
		return err
	}

	// THE COOL PART!
	//
	// Gets either the current row count (not very useful) or the value created
	// by SQL_CALC_FOUND_ROWS (very fast in latest mysql/mariadb).
	// See UserList.Query comments!
	var count int64
	err = tx.QueryRow("SELECT FOUND_ROWS() as Count").Scan(&count)

	if err != nil {
		tx.Rollback()
		return err
	}

	model.SetTotal(count)

	return tx.Commit()
}

Other funcs/interface combos using appropriate base interfaces just like Lister:

• Finder & Find(model Finder) error
• Inserter & Insert(model Inserter) error
• Joiner & Join(model Joiner) error (take two parent IDs and put them in their join table; handle a duplicate entry as a success - idempotent)
• Deleter & Delete(model Deleter) error
• Updater & Update(model Updater) error

Now for a satisfier:

// app/users/list.go
import "database/sql"

type User struct {
	ID   int
	Name string
}

type UserList struct {
	Users  []User
	Limit  int
	Offset int
	Total  int
}

// Satisfy db.Lister
func (list *UserList) Query() string {
	// SQL_CALC_FOUND_ROWS gives you what the Count(...)
	// would be if you left off LIMIT and OFFSET. So if the db has
	// 20 records and you set 'LIMIT 5 OFFSET 0' the total would be
	// 20. Been a long time since I've used this and I seem to
	// remember it being slow.
	return "SELECT SQL_CALC_FOUND_ROWS id, name FROM users LIMIT ? OFFSET ?"
}

// Satisfy db.Lister
func (list *UserList) Values() []interface{} {
	return []interface{}{
		list.Limit,
		list.Offset,
	}
}

// Satisfy db.Lister; import returned rows
func (list *UserList) UseEach(rows *sql.Rows) error {
	var errs errBag // simple k/v collection system not in this example

	for rows.Next() {
		var user User

		err := rows.Scan(&user.ID, &user.Name)

		if err != nil {
			errs.Append(err.Error()) // key: next index; value: err string 
			continue
		}

		list.Users = append(list.Users, user)
	}

	return errs.AsError() // nil if empty
}

// Satisfy db.Lister
func (list *UserList) SetTotal(count int64) {
	list.Total = int(count)
}

// List takes an offset and limit then requests rows from the database. This
// is one of the main functions of the 'users' package.
func List(limit, offset int) (model UserList, err error) {
	// skipping limit > 0 <= 100 and offset >= 0 validation/bounding in this example

	model.Limit = limit
	model.Offset = offset

	err = db.List(&model)

	return model, err
}

These interfaces still present a lot of typing for each satisfier. I might look into code generation for this at some point. Right now my IDE scaffolds this stuff easily enough.

IDE example:

Comments/Questions?