gorp と squirrel で CRUD

これ の 2 つ目のやつの API サーバーを Go で書くので、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
comments powered by Disqus