Go测试：如何用go-sqlmock和Gorm正确Mock SQL SELECT语句？

我正在做Go语言测试实践，基于Echo框架开发CRUD操作，现在在测试service.go文件。这个文件依赖连接本地PostgreSQL的gorm.DB仓库。之前用类似配置测试Create函数没问题，但测试GetOne函数时出错，求指导如何用go-sqlmock和Gorm正确Mock返回值。

相关代码 #

repository.go #

func (r *repo) GetByID(id int) (models.Entry, error) {
    var entry models.Entry
    err := r.Db.Table("entries").First(&entry, id).Error
    if err != nil {
        return models.Entry{}, err
    }

    return entry, nil
}

service.go #

func (s *service) GetOne(id int) (models.Entry, int, error) {
    entry, err := s.entryRepo.GetByID(id)

    if err != nil {
        return models.Entry{}, http.StatusNotFound, err
    }
    return entry, http.StatusOK, err
}

service_test.go #

func Test_GetOne(t *testing.T) {
    mockDb, mock, err := sqlmock.New()
    if err != nil {
        t.Error("Failed to create mock database", err.Error())
    }

    defer mockDb.Close()

    dialector := postgres.New(postgres.Config{
        Conn:       mockDb,
        DriverName: "postgres",
    })

    db, _ := gorm.Open(dialector, &gorm.Config{})

    t.Run("Should successfully get an entry", func(t *testing.T) {
        rows := sqlmock.NewRows([]string{"all the keys"}).AddRow("all the values")

        mock.ExpectBegin()
        mock.ExpectQuery("SELECT").WithArgs(1).WillReturnRows(rows)
        mock.ExpectCommit()

        serv := InitService(db)
        
        entry, status, err := serv.GetOne(1)

        if err := mock.ExpectationsWereMet(); err != nil {
            t.Error("SQL expressions were not met", err.Error())
        }
    })
}

问题分析与解决步骤 #

你的测试代码存在几个关键问题，导致Mock匹配失败：

1. SQL语句匹配过于宽泛
   Gorm生成的First查询会包含具体的表名、字段和条件，直接用"SELECT"作为匹配条件无法精准匹配实际SQL。需要用正则匹配Gorm生成的完整语句：

mock.ExpectQuery(regexp.QuoteMeta(`SELECT * FROM "entries" WHERE "entries"."id" = $1 ORDER BY "entries"."id" LIMIT $2`)).
    WithArgs(1, 1).
    WillReturnRows(rows)

记得导入regexp包，用QuoteMeta转义SQL中的特殊字符。

2. 多余的事务期望
   Gorm的First方法是只读操作，默认不会开启事务，测试中添加的ExpectBegin和ExpectCommit会导致期望不满足，直接删除这两行即可。

3. Rows字段需与实际模型对应
   不能用"all the keys"占位，要替换成models.Entry对应的数据库列名（注意Gorm默认将驼峰字段转蛇形，比如结构体的Title对应数据库的title）。示例：

rows := sqlmock.NewRows([]string{"id", "title", "content"}).
    AddRow(1, "测试标题", "测试内容")

4. 补上错误检查
   测试中db, _ := gorm.Open(...)忽略了错误，应该加上检查：

db, err := gorm.Open(dialector, &gorm.Config{})
if err != nil {
    t.Fatalf("Failed to open gorm db: %v", err)
}

修改后的完整测试代码 #

import (
    "regexp"
    "testing"
    "net/http"

    "github.com/DATA-DOG/go-sqlmock"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

func Test_GetOne(t *testing.T) {
    mockDb, mock, err := sqlmock.New()
    if err != nil {
        t.Fatalf("Failed to create mock database: %v", err)
    }
    defer mockDb.Close()

    dialector := postgres.New(postgres.Config{
        Conn:       mockDb,
        DriverName: "postgres",
    })

    db, err := gorm.Open(dialector, &gorm.Config{})
    if err != nil {
        t.Fatalf("Failed to open gorm db: %v", err)
    }

    t.Run("Should successfully get an entry", func(t *testing.T) {
        rows := sqlmock.NewRows([]string{"id", "title", "content"}).
            AddRow(1, "Test Entry", "Sample content")

        mock.ExpectQuery(regexp.QuoteMeta(`SELECT * FROM "entries" WHERE "entries"."id" = $1 ORDER BY "entries"."id" LIMIT $2`)).
            WithArgs(1, 1).
            WillReturnRows(rows)

        serv := InitService(db)
        entry, status, err := serv.GetOne(1)

        if err != nil {
            t.Fatalf("Unexpected error: %v", err)
        }
        if status != http.StatusOK {
            t.Errorf("Expected status %d, got %d", http.StatusOK, status)
        }
        if entry.ID != 1 || entry.Title != "Test Entry" {
            t.Errorf("Returned entry does not match expected value")
        }

        if err := mock.ExpectationsWereMet(); err != nil {
            t.Errorf("Unmet SQL expectations: %v", err)
        }
    })
}

内容的提问来源于stack exchange，提问作者ChuChuwi