[开发测试]golang 使用go-sqlmock对 insert update delete select做单元测试

上篇文章go 单元测试go-sqlmock 已经介绍了如何使用go-sqlmock进行 sql相关的单元测试。

本文着重介绍平时开发过程中常见的例子。

模拟insert

例如以下 insert 代码。

package orange

import (
        "database/sql"
        "fmt"
)


type OrangeProcess struct{
	Id int64
	Hostname string
	Port int
	StartTime string
	UID string
}


func WriteOrangeProcess(db *sql.DB, orangeProcess *OrangeProcess) error {
	sqlResult, err := db.Exec(`
			insert ignore
				into orange_process (
					hostname,
					port,
					start_time,
					uid,
				) values (
					?,
					?,
					NOW(),
					?
				)
			`,
		orangeProcess.Hostname, orangeProcess.Port,
		orangeProcess.UID,
	)
	if err != nil {
		return fmt.Errorf("insert ignore into orange_process failed:%s, orangeProcess:%+v", err, orangeProcess)
	}
	rows, err := sqlResult.RowsAffected()
	if err != nil {
		return fmt.Errorf("get sql RowsAffected failed:%s, orangeProcess:%+v", err, orangeProcess)
	}
	if rows == 0 {
		return fmt.Errorf("create orange_process record failed, orangeProcess:%+v", orangeProcess)
	}

	return nil
}

对应的单元测试代码如下：

package orange

import (
	"errors"
	"strings"
	"testing"

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

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

	orangeProcess := &OrangeProcess{Hostname:"number-1", Port:3306, UID:"AA-BB-CC"}

	sqlInsertIgnoreIntoSql := "insert ignore into orange_process"


	// 模拟 insert ignore into报错
	mock.ExpectExec(sqlInsertIgnoreIntoSql).WillReturnError(errors.New("insert error"))
	err = WriteOrangeProcess(db, orangeProcess)
	if !strings.Contains(err.Error(), "insert ignore into orange_process failed:insert error"){
		t.Fatalf("unexpected error:%s",err)
	}

	// 模拟 insert ignore into 返回Result 中存在错误
	mock.ExpectExec(sqlInsertIgnoreIntoSql).WillReturnResult(sqlmock.NewErrorResult(errors.New("result error")))
	err = WriteOrangeProcess(db, orangeProcess)
	if !strings.Contains(err.Error(), "get sql RowsAffected failed:result error") {
				t.Fatalf("unexpected error:%s",err)
	}

	// 模拟 insert ignore into 影响行数为0
	mock.ExpectExec(sqlInsertIgnoreIntoSql).WillReturnResult(sqlmock.NewResult(0, 0))
	err = WriteOrangeProcess(db, orangeProcess)
	if !strings.Contains(err.Error(), "create orange_process record failed") {
				t.Fatalf("unexpected error:%s",err)
	}

	// 模拟 insert ignore into 正常
	mock.ExpectExec(sqlInsertIgnoreIntoSql).WillReturnResult(sqlmock.NewResult(0, 1))
	err = WriteOrangeProcess(db, orangeProcess)
	if err != nil {
				t.Fatalf("unexpected error:%s",err)
	}
}

执行单测：

go test -run TestWriteOrangeProcess ./
ok  	/opt/workspace/orange	0.007s

模拟update

基本方式同上。

模拟delete

基本方式同上。

模拟select

select 代码如下：

package orange

import (
        "database/sql"
        "fmt"
)


type OrangeProcess struct{
	Id int64
	Hostname string
	Port int
	StartTime string
	UID string
}

func ReadOrangeProcess(db *sql.DB) ([]OrangeProcess, error) {
	res :=[]OrangeProcess{}
	query := `
		select
	      		id,
	      		hostname,
	      		port,
	      		start_time,
			uid
		from
			slave_failure_process`
	rows, err := db.Query(query)
	if err != nil {
		return res, fmt.Errorf("query failed:%s", err)

	}
	defer rows.Close()

	for rows.Next(){
		data := OrangeProcess{}
		if err := rows.Scan(&data.Id, &data.Hostname, &data.Port, &data.StartTime, &data.UID); err != nil {
			fmt.Println("Scan failed:", err)
		}

		fmt.Println("data:", data)
		res = append(res, data)
	}

	return res, nil
}

对应的单元测试代码如下：

package orange

import (
	"database/sql/driver"
	"errors"
	"strings"
	"testing"

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


func TestReadOrangeProcess(t *testing.T){
	mockDB, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("failed to open sqlmock database: %s", err)
	}
	defer mockDB.Close()

	sqlSelectSql := "select"

	// 模拟 select 报错
	mock.ExpectQuery(sqlSelectSql).WillReturnError(errors.New("select error"))
	_, err = ReadOrangeProcess(mockDB)
	if !strings.Contains(err.Error(), "select error"){
		t.Fatalf("unexpected error:%s",err)
	}

	// 模拟 select 正常
	rows := sqlmock.NewRows(
		[]string{"id", "hostname", "port", "start_time", "uid"},
	).AddRow([]driver.Value{123, "testhost001", 3306, "2022-03-05 22:28:00", "AABBCCDDEEFF"}...)
	mock.ExpectQuery(sqlSelectSql).WillReturnRows(rows)
	res, err := ReadOrangeProcess(mockDB)
	if err != nil {
		t.Fatalf("unexpected error:%s",err)
	}

	if res[0].Id != int64(123) {
		t.Fatalf("unexpected id:%d",res[0].Id)
	}

	if res[0].Hostname != "testhost001" {
		t.Fatalf("unexpected Hostname:%s",res[0].Hostname)
	}

	if res[0].Port != 3306 {
		t.Fatalf("unexpected Port:%d",res[0].Port)
	}

	if res[0].StartTime != "2022-03-05 22:28:00" {
		t.Fatalf("unexpected StartTime:%s",res[0].StartTime)
	}

	if res[0].UID != "AABBCCDDEEFF" {
		t.Fatalf("unexpected UID:%s",res[0].UID)
	}

}

执行单测代码：

go test -run TestReadOrangeProcess ./
ok  /op//workspace/orange	0.007s