これ の2つ目のやつの API サーバーを Golang で書くので、DB 周りどうしようかなあと考えながら gorpsquirrel でサンプルコードを書いたのでメモ。

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	"github.com/Masterminds/squirrel"
	_ "github.com/go-sql-driver/mysql"

	"gopkg.in/gorp.v1"
)

type DbMap struct {
	*gorp.DbMap
}

func newDbMap() (*DbMap, error) {
	db, err := sql.Open("mysql", "root:@/test?loc=Local&parseTime=true")
	if err != nil {
		return nil, err
	}

	dbm := &gorp.DbMap{
		Db: db,
		Dialect: gorp.MySQLDialect{
			Engine:   "InnoDB",
			Encoding: "UTF8",
		},
	}

	return &DbMap{dbm}, nil
}

func (dbMap *DbMap) initDB() error {
	dbMap.addUserTable()
	if err := dbMap.DropTablesIfExists(); err != nil {
		return err
	}
	if err := dbMap.CreateTablesIfNotExists(); err != nil {
		return err
	}
	if err := dbMap.TruncateTables(); err != nil {
		return err
	}
	return nil
}

func (dbMap *DbMap) addUserTable() {
	userTableMap := dbMap.AddTableWithName(User{}, "user").SetKeys(true, "ID")
	userTableMap.ColMap("Name").SetNotNull(true)
	userTableMap.ColMap("Age").SetNotNull(true)
	userTableMap.ColMap("UpdatedAt").SetNotNull(true)
	userTableMap.ColMap("CreatedAt").SetNotNull(true)
	return
}

type User struct {
	ID        int64     `db:"id"`
	Name      string    `db:"name"`
	Age       int32     `db:"age"`
	UpdatedAt time.Time `db:"updated_at"`
	CreatedAt time.Time `db:"created_at"`
}

func newUser(name string, age int32) *User {
	return &User{
		Name: name,
		Age:  age,
	}
}

func (u *User) PreInsert(s gorp.SqlExecutor) error {
	now := time.Now()
	u.UpdatedAt = now
	u.CreatedAt = now
	return nil
}

func (u *User) PreUpdate(s gorp.SqlExecutor) error {
	u.UpdatedAt = time.Now()
	return nil
}

func (u *User) PrintValues() {
	fmt.Println(fmt.Sprintf(
		"id: %d, name: %s, age: %d, created_at: %s, updated_at: %s",
		u.ID, u.Name, u.Age, u.CreatedAt, u.UpdatedAt))
	return
}

func getUserNum(dbMap *DbMap) (int64, error) {
	sql, _, err := squirrel.Select("COUNT(*)").From("user").ToSql()
	if err != nil {
		return 0, err
	}

	return dbMap.SelectInt(sql)
}

func getUsers(dbMap *DbMap) ([]*User, error) {
	sql, _, err := squirrel.Select("*").From("user").ToSql()
	if err != nil {
		return nil, err
	}

	var users []*User
	if _, err := dbMap.Select(&users, sql); err != nil {
		return nil, err
	}

	return users, err
}

func getUserByID(dbMap *DbMap, id int64) (*User, error) {
	sql, _, err := squirrel.Select("*").From("user").Where("id = ?").ToSql()
	if err != nil {
		return nil, err
	}

	var u User
	if err := dbMap.SelectOne(&u, sql, id); err != nil {
		return nil, err
	}

	return &u, nil
}

func main() {
	dbMap, err := newDbMap()
	checkError(err)
	defer dbMap.Db.Close()

	err = dbMap.initDB()
	checkError(err)

	// create
	u1 := newUser("m0t0k1ch1", 27)
	u2 := newUser("m0t0k1ch2", 28)
	err = dbMap.Insert(u1, u2)
	checkError(err)

	// read - user num
	userNum, err := getUserNum(dbMap)
	checkError(err)
	fmt.Println("userNum:", userNum)
	fmt.Println("---")

	// read - each user
	users, err := getUsers(dbMap)
	checkError(err)
	for _, u := range users {
		u.PrintValues()
	}
	fmt.Println("---")

	time.Sleep(3 * time.Second)

	// update
	u := users[0]
	u.Age = 29
	_, err = dbMap.Update(u)
	checkError(err)

	// read - by id
	u, err = getUserByID(dbMap, u.ID)
	checkError(err)
	u.PrintValues()
	fmt.Println("---")

	// delete
	_, err = dbMap.Delete(u)
	checkError(err)

	// read - each user
	users, err = getUsers(dbMap)
	checkError(err)
	for _, u := range users {
		u.PrintValues()
	}

	return
}

func checkError(err error) {
	if err != nil {
		log.Fatal(err)
	}
	return
}

出力はこんな感じ。

userNum: 2
---
id: 1, name: m0t0k1ch1, age: 27, created_at: 2016-01-17 16:16:44 +0900 JST, updated_at: 2016-01-17 16:16:44 +0900 JST
id: 2, name: m0t0k1ch2, age: 28, created_at: 2016-01-17 16:16:44 +0900 JST, updated_at: 2016-01-17 16:16:44 +0900 JST
---
id: 1, name: m0t0k1ch1, age: 29, created_at: 2016-01-17 16:16:44 +0900 JST, updated_at: 2016-01-17 16:16:47 +0900 JST
---
id: 2, name: m0t0k1ch2, age: 28, created_at: 2016-01-17 16:16:44 +0900 JST, updated_at: 2016-01-17 16:16:44 +0900 JST