Golang 使用go-sqlmock mock sql Query

Go語言的go-sqlmock函式庫可對sql driver的行為做mock，也就是可返回mock的查詢結果。

下面範例使用go-sqlmock對SQL查詢結果做mock來做單元測試。

範例環境：

• Go 1.17

主程式

參考「Golang sql PostgreSQL查詢範例」，主程式main.go中兩個查詢資料庫的函式GetAllEmployees()及GetEmployeeByID()。

測試程式

測試程式main_test.go在TestGetAllEmployees()及TestGetEmployeeByID()使用go-sqlmock對sql查詢進行mock。

參考「Golang go-sqlmock API簡介」。

main_test.go

package main

import (
    "testing"
    "time"

    "github.com/DATA-DOG/go-sqlmock"
)

func TestGetAllEmployees(t *testing.T) {
    db, mock, err := sqlmock.New()
    if err != nil {
        t.Fatalf("opening a stub database connection error=%s", err)
    }
    defer db.Close()

    // mock return rows
    rows := sqlmock.NewRows([]string{"id", "name", "age", "created_at"}).
        AddRow(1, "john", 33, time.Now()).
        AddRow(2, "mary", 28, time.Now())

    mock.ExpectQuery("^SELECT (.+) FROM employee$").WillReturnRows(rows)

    emps, err := GetAllEmployees(db)
    count := len(emps)
    expected := 2
    if count != expected {
        t.Errorf("Expected %d, but %d", expected, count)
    }
}

func TestGetEmployeeByID(t *testing.T) {
    db, mock, err := sqlmock.New()
    if err != nil {
        t.Fatalf("opening a stub database connection error=%s", err)
    }
    defer db.Close()

    createdAt, _ := time.Parse("2006-01-02", "2021-01-14")
    rows := sqlmock.NewRows([]string{"id", "name", "age", "created_at"}).
        AddRow(1, "john", 33, createdAt)

    // mock return rows
    mock.ExpectQuery("^SELECT (.+) FROM employee WHERE id = \\$1 LIMIT 1$").
        WillReturnRows(rows)

    emp, err := GetEmployeeByID(db, 1)

    expected := Employee{
        ID:        1,
        Name:      "john",
        Age:       33,
        CreatedAt: createdAt,
    }
    if *emp != expected {
        t.Errorf("Expected %v, but %v", expected, emp)
    }
}

github。

• Golang sql PostgreSQL查詢範例
• go-sqlmock/examples