在对数据库进行单元测试时,可以使用sqlmock模拟数据库的返回来进行测试,从而无需部署真是的数据库实例。
本文记录了工作中遇到的一些场景,写得不好还请见谅
1.基本使用
-
创建mock
db, mock, err := sqlmock.New() if nil != err { t.Fatalf("Init sqlmock failed, err %v", err) } defer db.Close()
db 为数据库连接 *sql.DB
mock 为sqlmock的实例
-
通过已有连接初始化gorm实例
gormDB, err := gorm.Open(mysql.New(mysql.Config{
SkipInitializeWithVersion: true,
Conn: db,
}), &gorm.Config{})
if nil != err {
t.Fatalf("Init DB with sqlmock failed, err %v", err)
} else {
if db, err := database.KnodiDB.DB(); nil != err {
db.SetMaxIdleConns(10)
db.SetMaxOpenConns(10)
db.Ping()
}
}
t 为单元测试实例 *testing.T
SkipInitializeWithVersion 需要设置为true,否则会报错。如果此选项不设置,gorm初始化时会调用select version(),如果sqlmock没有mock这个调用则会报错。
-
配置期望的调用请求
mock.ExpectQuery("^SELECT \\* FROM `agents` WHERE voip_number = \\?").WillReturnRows(rows)
ExpectQuery的参数为正则表达式,需要去匹配gorm生成的sql语句,返回值类型为*ExpectedQuery
ExpectedQuery支持几种模拟的返回值
func (e *ExpectedQuery) WillReturnError(err error) *ExpectedQuery
func (e *ExpectedQuery) WillDelayFor(duration time.Duration) *ExpectedQuery
func (e *ExpectedQuery) WillReturnRows(rows ...*Rows) *ExpectedQuery
WillReturnRows 即返回一行或多行模拟的数据
WillReturnError 即返回模拟的error
如果调用WillReturnRows, 则还需要去模拟数据。模拟数据的方法如下:
rows := sqlmock.NewRows([]string{"id", "app_id", "agent_id", "role", "worker_id", "work_mod", "phone_number", "state", "voip_number", "password"}).
AddRow(1, "test_12345", "1@test_12345", "admin", "A0001", 0, "18600000000", 1, "1000", "1234”).
AddRow(2, "test_12345", "1@test_12345", "admin", "A0001", 0, "18600000000", 1, "1000", "1234")
模拟返回多行数据则调用多次AddRow即可,模拟不返回任何数据(即record not found)则不调用任何AddRow,只NewRows即可,如下
rows := sqlmock.NewRows([]string{"id", "app_id", "agent_id", "role", "worker_id", "work_mod", "phone_number", "state", "voip_number", "password"})
-
调用测试接口进行测试
被测试函数如下所示:
// Base 数据库表基础字段
type Base struct {
ID uint `gorm:"primary_key"`
CreatedAt time.Time `gorm:"type:timestamp;not null;default:CURRENT_TIMESTAMP"`
UpdatedAt time.Time `gorm:"type:timestamp;not null;default:CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"`
}
// SoftDelBase 支持伪删除
type SoftDelBase struct {
Base
DeletedAt *time.Time
}
type Agent struct {
SoftDelBase
AppID string `gorm:"size:191;not null"`
AgentID string `gorm:"size:191;not null;index"`
Role string `gorm:"size:191;default normal"`
WorkerID string `gorm:"size:191"`
WorkMod int `gorm:"size:1;default:0"`
PhoneNumber string `gorm:"size:11"`
State int `gorm:"size:4;default:0"`
VoipNumber string `gorm:"size:16"`
Password string `gorm:"size:16"`
}
func FindAgentByVoip(number string) (*Agent, bool) {
agent := Agent{}
result := database.KnodiDB.First(&agent, "voip_number = ?", number)
if nil == result.Error {
return &agent, true
}
if !errors.Is(result.Error, gorm.ErrRecordNotFound) {
log.Errorf("DB Error %v", result.Error)
}
return nil, false
}
测试函数如下:
1)测试有返回值的查询
t.Run("FindByVoip=WithRowReturn", func(t *testing.T) {
rows := sqlmock.NewRows([]string{"id", "app_id", "agent_id", "role", "worker_id", "work_mod", "phone_number", "state", "voip_number", "password"}).
AddRow(1, "test_12345", "1@test_12345", "admin", "A0001", 0, "18600000000", 1, "1000", "1234")
mock.ExpectQuery("^SELECT \\* FROM `agents` WHERE voip_number = \\?").WillReturnRows(rows)
agent, b := schema.FindAgentByVoip("1000")
if b { // 注意这里是b
fmt.Printf("agent %v\n", agent)
t.Log("Pass")
} else {
t.Error("Fail")
}
if err := mock.ExpectationsWereMet(); err != nil {
t.Errorf("there were unfulfilled expectations: %s", err)
}
})
当gorm执行查询时,sqlmock会将你配置的row中的数据返回
2)测试无返回值的查询
t.Run("FindByVoip=WithoutRowReturn", func(t *testing.T) {
rows := sqlmock.NewRows([]string{"id", "app_id", "agent_id", "role", "worker_id", "work_mod", "phone_number", "state", "voip_number", "password"})
mock.ExpectQuery("^SELECT \\* FROM `agents` WHERE voip_number = \\?").WillReturnRows(rows)
_, b := schema.FindAgentByVoip("1000")
if !b { // 注意这里是!b
t.Log("Pass")
} else {
t.Error("Fail")
}
if err := mock.ExpectationsWereMet(); err != nil {
t.Errorf("there were unfulfilled expectations: %s", err)
}
})
当gorm执行查询时,sqlmock会返回空行
3)测试数据库返回错误
t.Run("FindByVoip=WithError", func(t *testing.T) {
mock.ExpectQuery("^SELECT \\* FROM `agents` WHERE voip_number = \\?").WillReturnError(errors.New("some error"))
_, b := schema.FindAgentByVoip("1000")
if !b { // 注意这里是!b
t.Log("Pass")
} else {
t.Error("Fail")
}
if err := mock.ExpectationsWereMet(); err != nil {
t.Errorf("there were unfulfilled expectations: %s", err)
}
})
当gorm执行查询时,sqlmock会返回error
-
其他方法的测试
1)测试 INSERT
// 返回结果为被更改的数据行数
mock.ExpectExec("^INSERT INTO `apps` \\(.*\\) VALUES \\(.*\\)").WillReturnResult(sqlmock.NewResult(1, 1))
// 返回error
mock.ExpectExec("^INSERT INTO `apps` \\(.*\\) VALUES \\(.*\\)").WillReturnError(errors.New("some error"))
2)测试事务
mock.ExpectBegin() // 匹配begin
mock.ExpectExec("UPDATE products").WillReturnResult(sqlmock.NewResult(1, 1))
mock.ExpectExec("INSERT INTO product_viewers").WithArgs(2, 3).WillReturnResult(sqlmock.NewResult(1, 1))
mock.ExpectCommit() // 匹配commit