Golang后端学习笔记 — 5. 使用Golang为数据库CRUD写单元测试

Golang CRUDCRUD

1. 测试 CreateAccount

account.sql.goCreateAccountdb/sqlcaccount_test.go

Golangtest

dbTestCreateAccount

GoTesttesting.T

package db

import "testing"

func TestCreateAccount(t *testing.T) {

}

Queries objectdb/sqlcmain_test.go

testQueries

var testQueries *Queries

TestMaintesting.M

GolangTestMain

package db

import "testing"

var testQueries *Queries

func TestMain(m *testing.M) {

}

在这里先创建与数据库的连接，目前先用硬编码的方式把dbDriver和dbSource作为常量，后面我们将改进它

package db

import (
	"database/sql"
	"log"
	"os"
	"testing"
)

var testQueries *Queries

const (
	dbDriver = "postgres"
	dbSource = "postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable"
)

func TestMain(m *testing.M) {
	conn, err := sql.Open(dbDriver, dbSource)
	if err != nil {
		log.Fatal("cannot connect to db:", err)
	}

	testQueries = New(conn)

	os.Exit(m.Run())
}

m.Run()os.Exitrun testdatabase/sqlpostgres

go get github.com/lib/pq

go.modgithub.com/lib/pqindirect

main_test.gogithub.com/lib/pq_

import (
	"database/sql"
	"log"
	"os"
	"testing"

	_ "github.com/lib/pq"
)

TestMainrun testgo.modindirect

go mod tidy

CreateAccountaccount_test.goTestCreateAccount

CreateAccountParams

arg := CreateAccountParams{
		Owner: "张三",
		Balance: 100,
		Currency: "RMB",
}

testQueries.CreateAccount()arg

account, err := testQueries.CreateAccount(context.Background(), arg)

testQueriesmain_test.goaccounterr

testify

go get github.com/stretchr/testify

account_test.go"github.com/stretchr/testify/require"

require.NoError(t, err)

account

require.NotEmpty(t, account)

之后，我们要检查，账户的所有者、余额和币种是否与输入的一致：

	require.Equal(t, arg.Owner, account.Owner)
	require.Equal(t, arg.Balance, account.Balance)
	require.Equal(t, arg.Currency, account.Currency)

IDpostgres

require.NotZero(t, account.ID)

created_at

package db

import (
	"context"
	"testing"

	"github.com/stretchr/testify/require"
)

func TestCreateAccount(t *testing.T) {
	arg := CreateAccountParams{
		Owner:    "张三",
		Balance:  100,
		Currency: "RMB",
	}

	account, err := testQueries.CreateAccount(context.Background(), arg)

	// err 必须为 nil
	require.NoError(t, err)

	// account 不能为空对象
	require.NotEmpty(t, account)

	// 账户的所有者、余额和币种是否与输入的一致
	require.Equal(t, arg.Owner, account.Owner)
	require.Equal(t, arg.Balance, account.Balance)
	require.Equal(t, arg.Currency, account.Currency)

	// 检查ID是否自动生成的，必须不为0
	require.NotZero(t, account.ID)

	require.NotZero(t, account.CreatedAt)
}

run testoknavicataccountsRun package testsaccount.sql.go

2. 生成测试数据

张三

通过生成随机数据，我们将节省大量的时间来确定要使用的值，代码也会更简洁易懂，并且由于数据是随机的，它将帮我们避免多个单元测试之间的冲突，比如，数据库中某个字段有唯一约束。

utilrandom.gopackage util

init()rand.Seed()time.Now().UnixNano()

package util

import (
	"math/rand"
	"time"
)

func init() {
	rand.Seed(time.Now().UnixNano())
}

RandomInt

func RandomInt(min, max int64) int64 {
	return min + rand.Int63n(max-min+1)
}

接下来，再编写一个生成随机字符串的函数，为此，需要声明一个包含所有字符串的字母表，简单起见，只用了26个小写字母:

var alphabet = "abcdefghijklmopqrstuvwxyz"

func RandomString(n int) string {
	var sb strings.Builder
	k := len(alphabet)

	for i := 0; i < n; i++ {
		c := alphabet[rand.Intn(k)]
		sb.WriteByte(c)
	}

	return sb.String()
}

随机生成中文的姓名

func RandomOwner() string {
	return RandomString(6)
}

同样，定义一个生成随机金额的函数，假设它是0到1000的整数

func RandomMoney() int64 {
	return RandomInt(0, 1000)
}

"RMB", "USD", "EUR", "CAD"

func RandomCurrency() string {
	currencies := []string{"RMB", "USD", "EUR", "CAD"}
	n := len(currencies)
	return currencies[rand.Intn(n)]
}

random.go

package util

import (
	"math/rand"
	"strings"
	"time"
)

var alphabet = "abcdefghijklmopqrstuvwxyz"

func init() {
	rand.Seed(time.Now().UnixNano())
}

/**
* 生成随机整数
 */
func RandomInt(min, max int64) int64 {
	return min + rand.Int63n(max-min+1)
}

/**
* 生成随机字符串
 */
func RandomString(n int) string {
	var sb strings.Builder
	k := len(alphabet)

	for i := 0; i < n; i++ {
		c := alphabet[rand.Intn(k)]
		sb.WriteByte(c)
	}

	return sb.String()
}

/**
* 随机生成账户所有者
 */
func RandomOwner() string {
	return RandomString(6)
}

/**
* 随机生成金额
 */
func RandomMoney() int64 {
	return RandomInt(0, 1000)
}

/**
* 随机生成币种
 */
func RandomCurrency() string {
	currencies := []string{"RMB", "USD", "EUR", "CAD"}
	n := len(currencies)
	return currencies[rand.Intn(n)]
}

account_test.go

"张三"util.RandomOwner()100util.RandomMoney()RMButil.RandomCurrency()

	arg := CreateAccountParams{
		Owner:    util.RandomOwner(),
		Balance:  util.RandomMoney(),
		Currency: util.RandomCurrency(),
	}

run testnavicat

Makefile

test:
	go test -v -cover ./...

-v-cover./...Makefile

postgres:
	docker run --name postgres14 -e POSTGRES_PASSWORD=123456 -e POSTGRES_USER=root -p 5432:5432 -d postgres:14-alpine

createdb:
	docker exec -it postgres14 createdb --username=root --owner=root simple_bank

dropdb:
	docker exec -it postgres14 dropdb simple_bank

migrateup:
	migrate --path db/migration --database="postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose up

migratedown:
	migrate --path db/migration --database="postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose down

sqlc:
	sqlc generate

test:
	go test -v -cover ./...

.PHONY: postgres, createdb, dropdb, migrateup, migratedown, sqlc, test

来到项目终端，运行：

make test

可以看到，运行完成测试时打印出了详细的日志。
测试日志

make test-count=1go test -v -cover ./... -count=1

CRUD

GetAccountaccount_test.goTestGetAccountCRUDAccount

AccountAccount

把之前的代码重构一下，如下：

package db

import (
	"context"
	"simplebank/util"
	"testing"
	"time"

	"github.com/stretchr/testify/require"
)

func createRandomAccount(t *testing.T) Account {
	arg := CreateAccountParams{
		Owner:    util.RandomOwner(),
		Balance:  util.RandomMoney(),
		Currency: util.RandomCurrency(),
	}

	account, err := testQueries.CreateAccount(context.Background(), arg)

	// err 必须为 nil
	require.NoError(t, err)

	// account 不能为空对象
	require.NotEmpty(t, account)

	// 账户的所有者、余额和币种是否与输入的一致
	require.Equal(t, arg.Owner, account.Owner)
	require.Equal(t, arg.Balance, account.Balance)
	require.Equal(t, arg.Currency, account.Currency)

	// 检查ID是否自动生成的，必须不为0
	require.NotZero(t, account.ID)

	require.NotZero(t, account.CreatedAt)

	return account
}
func TestCreateAccount(t *testing.T) {
	createRandomAccount(t)
}

func TestGetAccount(t *testing.T) {
	account1 := createRandomAccount(t)
	// 查询 account, 参数为 account1 的 id，把结果给 account2
	account2, err := testQueries.GetAccount(context.Background(), account1.ID)
	// 这里应该没错误
	require.NoError(t, err)
	// account2 也必须不是空的
	require.NotEmpty(t, account2)

	// account2 的所有字段的值应该和 account1 所有字段的值相同
	require.Equal(t, account2.ID, account1.ID)
	require.Equal(t, account2.Owner, account1.Owner)
	require.Equal(t, account2.Balance, account1.Balance)
	require.Equal(t, account2.Currency, account1.Currency)
	require.WithinDuration(t, account1.CreatedAt, account2.CreatedAt, time.Second)
}

TestGetAccountrun test

TestUpdateAccount()

account1 := createRandomAccount(t)

然后，定义参数，如下：

func TestUpdateAccount(t *testing.T) {
	account1 := createRandomAccount(t)

	arg := UpdateAccountParams{
		ID:      account1.ID,
		Balance: util.RandomMoney(),
	}

	account2, err := testQueries.UpdateAccount(context.Background(), arg)
	require.NoError(t, err)
	require.NotEmpty(t, account2)

	// 比较 account2 和 account1, 除了 Balance，其他的字段都应该相同
	require.Equal(t, account2.ID, account1.ID)
	require.Equal(t, account2.Owner, account1.Owner)
	// 这里使用 arg.Balance 和 account2.Balance 比较
	require.Equal(t, account2.Balance, arg.Balance)
	require.Equal(t, account2.Currency, account1.Currency)
	require.WithinDuration(t, account1.CreatedAt, account2.CreatedAt, time.Second)
}

再次运行这个函数的单元测试，可以看到，也测试通过了！

TestDeleteAccount

func TestDeleteAccount(t *testing.T) {
	account1 := createRandomAccount(t)
	err := testQueries.DeleteAccount(context.Background(), account1.ID)
	require.NoError(t, err)

	// 为了验证账户确实被删除了，再查找一次
	account2, err := testQueries.GetAccount(context.Background(), account1.ID)
	// 因为已经删除掉了，这里必须有错误
	require.Error(t, err)
	// 更准确的说，错误应该是 sql.ErrNoRows
	require.EqualError(t, err, sql.ErrNoRows.Error())
	// account2 也应该是空的
	require.Empty(t, account2)
}

run test

ListAccounts

func TestListAccounts(t *testing.T) {
	for i := 0; i < 10; i++ {
		createRandomAccount(t)
	}

	arg := ListAccountsParams{
		Limit:  5,
		Offset: 5,
	}

	accounts, err := testQueries.ListAccounts(context.Background(), arg)
	require.NoError(t, err)
	// accounts 切片的长度为 5
	require.Len(t, accounts, 5)

	// 变量 accounts, 其中的每个 account 都不能为空
	for _, account := range accounts {
		require.NotEmpty(t, account)
	}
}

run test

run package testsaccount.sql.goaccount_test.go

package db

import (
	"context"
	"database/sql"
	"simplebank/util"
	"testing"
	"time"

	"github.com/stretchr/testify/require"
)

func createRandomAccount(t *testing.T) Account {
	arg := CreateAccountParams{
		Owner:    util.RandomOwner(),
		Balance:  util.RandomMoney(),
		Currency: util.RandomCurrency(),
	}

	account, err := testQueries.CreateAccount(context.Background(), arg)

	// err 必须为 nil
	require.NoError(t, err)

	// account 不能为空对象
	require.NotEmpty(t, account)

	// 账户的所有者、余额和币种是否与输入的一致
	require.Equal(t, arg.Owner, account.Owner)
	require.Equal(t, arg.Balance, account.Balance)
	require.Equal(t, arg.Currency, account.Currency)

	// 检查ID是否自动生成的，必须不为0
	require.NotZero(t, account.ID)

	require.NotZero(t, account.CreatedAt)

	return account
}
func TestCreateAccount(t *testing.T) {
	createRandomAccount(t)
}

func TestGetAccount(t *testing.T) {
	account1 := createRandomAccount(t)
	// 查询 account, 参数为 account1 的 id，把结果给 account2
	account2, err := testQueries.GetAccount(context.Background(), account1.ID)
	// 这里应该没错误
	require.NoError(t, err)
	// account2 也必须不是空的
	require.NotEmpty(t, account2)

	// account2 的所有字段的值应该和 account1 所有字段的值相同
	require.Equal(t, account2.ID, account1.ID)
	require.Equal(t, account2.Owner, account1.Owner)
	require.Equal(t, account2.Balance, account1.Balance)
	require.Equal(t, account2.Currency, account1.Currency)
	require.WithinDuration(t, account1.CreatedAt, account2.CreatedAt, time.Second)
}

func TestUpdateAccount(t *testing.T) {
	account1 := createRandomAccount(t)

	arg := UpdateAccountParams{
		ID:      account1.ID,
		Balance: util.RandomMoney(),
	}

	account2, err := testQueries.UpdateAccount(context.Background(), arg)
	require.NoError(t, err)
	require.NotEmpty(t, account2)

	// 比较 account2 和 account1, 除了 Balance，其他的字段都应该相同
	require.Equal(t, account2.ID, account1.ID)
	require.Equal(t, account2.Owner, account1.Owner)
	// 这里使用 arg.Balance 和 account2.Balance 比较
	require.Equal(t, account2.Balance, arg.Balance)
	require.Equal(t, account2.Currency, account1.Currency)
	require.WithinDuration(t, account1.CreatedAt, account2.CreatedAt, time.Second)
}

func TestDeleteAccount(t *testing.T) {
	account1 := createRandomAccount(t)
	err := testQueries.DeleteAccount(context.Background(), account1.ID)
	require.NoError(t, err)

	// 为了验证账户确实被删除了，再查找一次
	account2, err := testQueries.GetAccount(context.Background(), account1.ID)
	// 因为已经删除掉了，这里必须有错误
	require.Error(t, err)
	// 更准确的说，错误应该是 sql.ErrNoRows
	require.EqualError(t, err, sql.ErrNoRows.Error())
	// account2 也应该是空的
	require.Empty(t, account2)
}

func TestListAccounts(t *testing.T) {
	for i := 0; i < 10; i++ {
		createRandomAccount(t)
	}

	arg := ListAccountsParams{
		Limit:  5,
		Offset: 5,
	}

	accounts, err := testQueries.ListAccounts(context.Background(), arg)
	require.NoError(t, err)
	// accounts 切片的长度为 5
	require.Len(t, accounts, 5)

	// 变量 accounts, 其中的每个 account 都不能为空
	for _, account := range accounts {
		require.NotEmpty(t, account)
	}
}

4. 随机生成中文姓名的测试数据

Owner

random.go

var lastNames = []string{"李", "王", "张", "刘", "陈", "杨", "黄", "赵", "周", "吴", "徐", "孙", "朱", "马", "胡", "郭", "林", "何", "高", "梁", "郑", "罗", "宋", "谢", "唐", "韩", "曹", "许", "邓", "萧", "冯", "曾", "程", "蔡", "彭", "潘", "袁", "於", "董", "余", "苏", "叶", "吕", "魏", "蒋", "田", "杜", "丁", "沈", "姜", "范", "江", "傅", "钟", "卢", "汪", "戴", "崔", "任", "陆", "廖", "姚", "方", "金", "邱", "夏", "谭", "韦", "贾", "邹", "石", "熊", "孟", "秦", "阎", "薛", "侯", "雷", "白", "龙", "段", "郝", "孔", "邵", "史", "毛", "常", "万", "顾", "赖", "武", "康", "贺", "严", "尹", "钱", "施", "牛", "洪", "龚"}
var maleNames = []string{"豪", "言", "玉", "意", "泽", "彦", "轩", "景", "正", "程", "诚", "宇", "澄", "安", "青", "泽", "轩", "旭", "恒", "思", "宇", "嘉", "宏", "皓", "成", "宇", "轩", "玮", "桦", "宇", "达", "韵", "磊", "泽", "博", "昌", "信", "彤", "逸", "柏", "新", "劲", "鸿", "文", "恩", "远", "翰", "圣", "哲", "家", "林", "景", "行", "律", "本", "乐", "康", "昊", "宇", "麦", "冬", "景", "武", "茂", "才", "军", "林", "茂", "飞", "昊", "明", "明", "天", "伦", "峰", "志", "辰", "亦"}
var femaleNames = []string{"佳", "彤", "自", "怡", "颖", "宸", "雅", "微", "羽", "馨", "思", "纾", "欣", "元", "凡", "晴", "玥", "宁", "佳", "蕾", "桑", "妍", "萱", "宛", "欣", "灵", "烟", "文", "柏", "艺", "以", "如", "雪", "璐", "言", "婷", "青", "安", "昕", "淑", "雅", "颖", "云", "艺", "忻", "梓", "江", "丽", "梦", "雪", "沁", "思", "羽", "羽", "雅", "访", "烟", "萱", "忆", "慧", "娅", "茹", "嘉", "幻", "辰", "妍", "雨", "蕊", "欣", "芸", "亦"}

func RandomChineseFirstname(names []string, wordNum int64) string {
	n := len(names)
	var sb strings.Builder
	for i := 1; i < int(wordNum); i++ {
		sb.WriteString(names[rand.Intn(n)])
	}
	return sb.String()
}

/**
* 生成随机的中文姓名
 */
func RandomChineseOwner() string {
	n := len(lastNames)
	lastname := lastNames[rand.Intn(n)]

	// 随机男女
	gender := RandomInt(0, 1)
	// 随机几个字的名，2个或3个
	len := RandomInt(2, 3)

	var firstname = ""
	if gender == 0 {
		firstname = RandomChineseFirstname(femaleNames, len)
	} else {
		firstname = RandomChineseFirstname(maleNames, len)
	}

	return lastname + firstname
}

account_test.goutil.RandomOwner()util.RandomChineseOwner()

	arg := CreateAccountParams{
		Owner:    util.RandomChineseOwner(),
		Balance:  util.RandomMoney(),
		Currency: util.RandomCurrency(),
	}

make test