使用go-sqlmock模拟PostgreSQL时遇执行查询未预期错误

错误信息 #

{"time":"2023-10-01T18:07:05.495811+03:00","level":"FATAL","prefix":"-","file":"repository.go","line":"15","message":"An error occurred while executing the query call to ExecQuery 'insert into public."Users" ("Id", "Name", "Email") values ($1,$2,$3)' with args [{Name: Ordinal:1 Value:e918b0bc-76f3-4380-a1fb-a14ca5a08fc3} {Name: Ordinal:2 Value:Jon Snow} {Name: Ordinal:3 Value:jon@jon.com}], was not expected, next expectation is: ExpectedBegin => expecting database transaction Begin"}

相关代码 #

测试文件 #

var user = &User{
    Id:    userIdUuid,
    Name:  "Jon Snow",
    Email: "jon@jon.com",
}
func TestUserRepository_create_whenEverythingIsOk(t *testing.T) {
    t.Parallel()
    //arrange
    db, mock, err := sqlmock.New()
    if err != nil {
        t.Fatalf("an error '%s' was not expoected when opening a stub database connection", err)
    }
    defer db.Close()
    mock.ExpectBegin()
    mock.ExpectExec(`insert into public."Users" ("Id", "Name", "Email") values ($1,$2,$3)`).WithArgs(user.Id, user.Name, user.Email)
    mock.ExpectCommit()
    repository := NewUserRepository(db)
    if err := repository.Create(*user); 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)
    }
}

仓库Create方法 #

func (repo *userRepository) Create(user User) (err error) {
    query := `insert into public."Users" ("Id", "Name", "Email") values ($1,$2,$3)`
    _, err := repo.db.Exec(query, user.Id, user.Name, user.Email)
    if err != nil {
        log.Fatalf("An error occurred while executing the query  %v", err)
    }
    return
}

DB初始化相关 #

func NewDb() (*sql.DB, error) {
    var config configs.Config
    if err := viper.Unmarshal(&config); err != nil {
        fmt.Println(err)
        return nil, err
    }
    db, err := sql.Open("postgres", fmt.Sprintf("%s", config.PostgresConnection[0]["connectionstring"]))

    if err != nil {
        panic("failed to connect database")
    }
    if err := db.Ping(); err != nil {
        log.Fatalf("unable to reach database: %v", err)
    }
    return db, nil
}

func Installer(e *echo.Echo) {
    db, err := db_helper.NewDb()
    if err != nil {
        panic(err)
    }
    userRepo := entities.NewUserRepository(db)
    userService := entities.NewUserService(userRepo)
    controller := entities.NewUserHandler(userService)

    e.GET("api/user/:id", controller.GetById)
    e.POST("api/user", controller.Create)
    e.GET("api/user", controller.GetUsers)
}

错误原因 #

测试代码中设置了mock.ExpectBegin()和mock.ExpectCommit()，预期会开启数据库事务并提交，但实际业务代码的Create方法直接调用db.Exec()执行插入操作，没有使用事务。sqlmock按照预期顺序等待事务开启的调用，却收到了普通的Exec查询，因此抛出不匹配错误。

解决方案 #

方案1：调整测试代码，移除事务预期 #

如果业务逻辑不需要事务，直接删除测试中事务相关的预期，并为ExpectExec添加返回结果的预期：

func TestUserRepository_create_whenEverythingIsOk(t *testing.T) {
    t.Parallel()
    //arrange
    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()
    // 移除事务预期，添加Exec的返回结果
    mock.ExpectExec(`insert into public."Users" ("Id", "Name", "Email") values ($1,$2,$3)`).
        WithArgs(user.Id, user.Name, user.Email).
        WillReturnResult(sqlmock.NewResult(1, 1))
    repository := NewUserRepository(db)
    if err := repository.Create(*user); 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)
    }
}

方案2：修改业务代码，使用事务执行插入 #

如果业务需要事务支持，调整Create方法，与测试预期对齐：

func (repo *userRepository) Create(user User) (err error) {
    // 开启事务
    tx, err := repo.db.Begin()
    if err != nil {
        log.Fatalf("Failed to begin transaction: %v", err)
        return err
    }
    query := `insert into public."Users" ("Id", "Name", "Email") values ($1,$2,$3)`
    _, err = tx.Exec(query, user.Id, user.Name, user.Email)
    if err != nil {
        // 出错回滚事务
        if rollbackErr := tx.Rollback(); rollbackErr != nil {
            log.Fatalf("Failed to rollback transaction: %v", rollbackErr)
        }
        log.Fatalf("An error occurred while executing the query: %v", err)
        return err
    }
    // 提交事务
    return tx.Commit()
}

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