使用go-sqlmock进行Golang数据库交互测试

go-sqlmock是一个用于模拟SQL驱动的库，非常适合在不需要实际数据库连接的情况下测试数据库交互代码。下面我将详细介绍如何使用它。

安装go-sqlmock

首先安装go-sqlmock：

go get github.com/DATA-DOG/go-sqlmock

基本使用方法

1. 创建模拟数据库连接

import (
    "database/sql"
    "testing"
    
    "github.com/DATA-DOG/go-sqlmock"
)

func TestUserQuery(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()
    
    // 在这里编写测试代码...
}

2. 设置预期查询

// 设置预期查询
rows := sqlmock.NewRows([]string{"id", "name", "email"}).
    AddRow(1, "John Doe", "john@example.com").
    AddRow(2, "Jane Doe", "jane@example.com")

mock.ExpectQuery("^SELECT id, name, email FROM users WHERE id = \\?$").
    WithArgs(1).
    WillReturnRows(rows)

3. 执行查询并验证结果

// 执行查询
user, err := GetUser(db, 1)
if err != nil {
    t.Errorf("error was not expected while getting user: %s", err)
}

// 验证结果
if user.Name != "John Doe" {
    t.Errorf("expected name to be 'John Doe', got '%s'", user.Name)
}

// 确保所有期望都满足
if err := mock.ExpectationsWereMet(); err != nil {
    t.Errorf("there were unfulfilled expectations: %s", err)
}

完整示例

下面是一个完整的测试示例，包含查询和插入操作：

package main

import (
    "database/sql"
    "fmt"
    "testing"
    
    "github.com/DATA-DOG/go-sqlmock"
)

type User struct {
    ID    int
    Name  string
    Email string
}

func GetUser(db *sql.DB, id int) (*User, error) {
    var user User
    err := db.QueryRow("SELECT id, name, email FROM users WHERE id = ?", id).
        Scan(&user.ID, &user.Name, &user.Email)
    if err != nil {
        return nil, err
    }
    return &user, nil
}

func InsertUser(db *sql.DB, user *User) error {
    _, err := db.Exec("INSERT INTO users (name, email) VALUES (?, ?)", user.Name, user.Email)
    return err
}

func TestUserOperations(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()

    // 测试查询
    t.Run("Test GetUser", func(t *testing.T) {
        rows := sqlmock.NewRows([]string{"id", "name", "email"}).
            AddRow(1, "John Doe", "john@example.com")

        mock.ExpectQuery("^SELECT id, name, email FROM users WHERE id = \\?$").
            WithArgs(1).
            WillReturnRows(rows)

        user, err := GetUser(db, 1)
        if err != nil {
            t.Errorf("error was not expected while getting user: %s", err)
        }

        if user.Name != "John Doe" {
            t.Errorf("expected name to be 'John Doe', got '%s'", user.Name)
        }
    })

    // 测试插入
    t.Run("Test InsertUser", func(t *testing.T) {
        mock.ExpectExec("^INSERT INTO users").
            WithArgs("Alice", "alice@example.com").
            WillReturnResult(sqlmock.NewResult(1, 1))

        err := InsertUser(db, &User{Name: "Alice", Email: "alice@example.com"})
        if err != nil {
            t.Errorf("error was not expected while inserting user: %s", err)
        }
    })

    // 确保所有期望都满足
    if err := mock.ExpectationsWereMet(); err != nil {
        t.Errorf("there were unfulfilled expectations: %s", err)
    }
}

高级用法

1. 模拟事务

func TestTransaction(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()

    // 开始事务
    mock.ExpectBegin()
    
    // 执行更新
    mock.ExpectExec("UPDATE users").WithArgs("new@email.com", 1).
        WillReturnResult(sqlmock.NewResult(0, 1))
    
    // 提交事务
    mock.ExpectCommit()

    // 执行事务代码
    err = UpdateUserEmail(db, 1, "new@email.com")
    if err != nil {
        t.Errorf("error was not expected while updating user email: %s", err)
    }
}

2. 模拟错误

func TestQueryError(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()

    // 模拟查询错误
    mock.ExpectQuery("SELECT").WillReturnError(fmt.Errorf("some error"))

    _, err = GetUser(db, 1)
    if err == nil {
        t.Error("expected error but got none")
    }
}

最佳实践

1. 每个测试用例后检查ExpectationsWereMet：确保所有预期的SQL调用都发生了
2. 使用正则表达式匹配SQL：更灵活地匹配SQL语句
3. 为每个测试用例创建新的mock：避免测试间的相互影响
4. 模拟真实场景：返回的行和结果应该尽可能接近真实情况
5. 测试错误场景：不要只测试成功路径，也要测试错误处理

go-sqlmock是一个强大的工具，可以让你在不依赖真实数据库的情况下全面测试数据库交互代码。通过合理设置预期和验证结果，你可以确保你的数据库访问层在各种情况下都能正确工作。