使用go-sqlmock模拟PostgreSQL的INSERT语句失败，如何解决？

我在用go-sqlmock为GORM编写的业务代码写单元测试时遇到了问题：业务代码会自动生成时间戳，用sqlmock.AnyArg()匹配动态时间值失败，测试不通过。

业务代码 #

func (c *ObjectStoreClient) CreateObject(data *file.Object) error {
    tx := Client.DB.Begin()

    err := tx.Create(&data).Error
    if err != nil {
        tx.Rollback()
        return err
    }

    return tx.Commit().Error
}

单元测试代码 #

func TestFxFS_Create(t *testing.T) {
    asserts := assert.New(t)
    objectID := "7dd48234-8b92-422b-b21e-27f9bd3074a2"

    // Success
    {
        fxfs := NewFS(rootID)
        mock.ExpectBegin()
        mock.ExpectExec("INSERT INTO (.+)").
            WithArgs("7dd48234-8b92-422b-b21e-27f9bd3074a2", "testFile", 0, "dir", "", "", rootID, 1, sqlmock.AnyArg(), sqlmock.AnyArg()).
            WillReturnResult(sqlmock.NewResult(1, 1))
        mock.ExpectCommit()

        data := &file.Object{
            ObjectID: objectID,
            FileInfo: &file.Info{
                FileName:       "testFile",
                FileSize:       0,
                FileType:       "dir",
                FileHash:       "",
                StorageID:      1,
                SourcePath:     "",
                ParentObjectID: rootID,
            },
        }

        resp, err := fxfs.Create(data)

        asserts.Empty(err)
        asserts.NotEmpty(resp)
        asserts.NotEmpty(resp.Object)
    }
}

错误信息 #

2024/05/03 11:28:49 /home/fanxing/project/clouddrive_Microservices/cmd/file/biz/fxfs/store/fs.go:18 call to Query 'INSERT INTO "objects" ("object_id","info_name","info_size","info_type","info_hash","info_source_path","info_parent_object_id","info_storage_id","info_created_time","info_mod_time") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING "id"' with args [{Name: Ordinal:1 Value:7dd48234-8b92-422b-b21e-27f9bd3074a2} {Name: Ordinal:2 Value:testFile} {Name: Ordinal:3 Value:0} {Name: Ordinal:4 Value:dir} {Name: Ordinal:5 Value:} {Name: Ordinal:6 Value:} {Name: Ordinal:7 Value:7dd48234-8b92-422b-b21e-27f9bd3074e5} {Name: Ordinal:8 Value:1} {Name: Ordinal:9 Value:2024-05-03 11:28:49.107053224 +0800 CST} {Name: Ordinal:10 Value:2024-05-03 11:28:49.107053224 +0800 CST}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:
  - matches sql: 'INSERT INTO (.+)'
  - is with arguments:
    0 - 7dd48234-8b92-422b-b21e-27f9bd3074a2
    1 - testFile
    2 - 0
    3 - dir
    4 - 
    5 - 
    6 - 7dd48234-8b92-422b-b21e-27f9bd3074e5
    7 - 1
    8 - {}
    9 - {}
  - should return Result having:
      LastInsertId: 1
      RowsAffected: 1
[0.246ms] [rows:0] INSERT INTO "objects" ("object_id","info_name","info_size","info_type","info_hash","info_source_path","info_parent_object_id","info_storage_id","info_created_time","info_mod_time") VALUES ('7dd48234-8b92-422b-b21e-27f9bd3074a2','testFile',0,'dir','','','7dd48234-8b92-422b-b21e-27f9bd3074e5',1,'2024-05-03 11:28:49.107','2024-05-03 11:28:49.107') RETURNING "id"
2024/05/03 11:28:49 ERROR 未知错误 err="call to Query 'INSERT INTO \"objects\" (\"object_id\",\"info_name\",\"info_size\",\"info_type\",\"info_hash\",\"info_source_path\",\"_parent_object_id\",\"info_storage_id\",\"info_created_time\",\"info_mod_time\") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING \"id\"' with args [{Name: Ordinal:1 Value:7dd48234-8b92-422b-b21e-27f9bd3074a2} {Name: Ordinal:2 Value:testFile} {Name: Ordinal:3 Value:0} {Name: Ordinal:4 Value:dir} {Name: Ordinal:5 Value:} {Name: Ordinal:6 Value:} {Name: Ordinal:7 Value:7dd48234-8b92-422b-b21e-27f9bd3074e5} {Name: Ordinal:8 Value:1} {Name: Ordinal:9 Value:2024-05-03 11:28:49.107053224 +0800 CST} {Name: Ordinal:10 Value:2024-05-03 11:28:49.107053224 +0800 CST}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:\n  - matches sql: 'INSERT INTO (.+)\n  - is with arguments:\n    0 - 7dd48234-8b92-422b-b21e-27f9bd3074a2\n    1 - testFile\n    2 - 0\n    3 - dir\n    4 - \n    5 - \n    6 - 7dd48234-8b92-422b-b21e-27f9bd3074e5\n    7 - 1\n    8 - {}\n    9 - {}\n  - should return Result having:\n      LastInsertId: 1\n      RowsAffected: 1"
--- FAIL: TestFxFS_Create (0.00s)
    fs_test.go:174: 
                Error Trace:    /home/fanxing/project/clouddrive_Microservices/cmd/file/biz/fxfs/fs_test.go:174
                Error:          Should be empty, but was create testFile: call to Query 'INSERT INTO "objects" ("object_id","info_name","info_size","info_type","info_hash","info_source_path","info_parent_object_id","info_storage_id","info_created_time","info_mod_time") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING "id"' with args [{Name: Ordinal:1 Value:7dd48234-8b92-422b-b21e-27f9bd3074a2} {Name: Ordinal:2 Value:testFile} {Name: Ordinal:3 Value:0} {Name: Ordinal:4 Value:dir} {Name: Ordinal:5 Value:} {Name: Ordinal:6 Value:} {Name: Ordinal:7 Value:7dd48234-8b92-422b-b21e-27f9bd3074e5} {Name: Ordinal:8 Value:1} {Name: Ordinal:9 Value:2024-05-03 11:28:49.107053224 +0800 CST} {Name: Ordinal:10 Value:2024-05-03 11:28:49.107053224 +0800 CST}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:
                                  - matches sql: 'INSERT INTO (.+)'
                                  - is with arguments:
                                    0 - 7dd48234-8b92-422b-b21e-27f9bd3074a2
                                    1 - testFile
                                    2 - 0
                                    3 - dir
                                    4 - 
                                    5 - 
                                    6 - 7dd48234-8b92-422b-b21e-27f9bd3074e5
                                    7 - 1
                                    8 - {}
                                    9 - {}
                                  - should return Result having:
                                      LastInsertId: 1
                                      RowsAffected: 1
                Test:           TestFxFS_Create
    fs_test.go:175: 
                Error Trace:    /home/fanxing/project/clouddrive_Microservices/cmd/file/biz/fxfs/fs_test.go:175
                Error:          Should NOT be empty, but was &{<nil> }
                Test:           TestFxFS_Create
    fs_test.go:176: 
                Error Trace:    /home/fanxing/project/clouddrive_Microservices/cmd/file/biz/fxfs/fs_test.go:176
                Error:          Should NOT be empty, but was <nil>
                Test:           TestFxFS_Create
FAIL
FAIL    drive/cmd/file/biz/fxfs 0.010s
FAIL

解决方案 #

问题根源有两个，针对性修改即可：

1. 替换ExpectExec为ExpectQuery #

PostgreSQL中带RETURNING子句的INSERT操作，GORM会调用Query而非Exec方法执行，所以测试里的mock.ExpectExec需要换成mock.ExpectQuery，同时SQL匹配规则要包含RETURNING部分。

2. 用类型匹配替代AnyArg #

sqlmock.AnyArg()在匹配时间戳时可能出现匹配失效，改用sqlmock.AnyOfTypeArgument("timestamp")明确匹配PostgreSQL的时间戳类型，确保动态生成的时间值能被正确识别。

修改后的测试核心代码 #

mock.ExpectBegin()
// 替换ExpectExec为ExpectQuery，匹配带RETURNING的INSERT语句
mock.ExpectQuery("INSERT INTO (.+) RETURNING (.+)").
    WithArgs("7dd48234-8b92-422b-b21e-27f9bd3074a2", "testFile", 0, "dir", "", "", rootID, 1, 
            sqlmock.AnyOfTypeArgument("timestamp"), sqlmock.AnyOfTypeArgument("timestamp")).
    // 返回模拟的RETURNING结果，对应id字段
    WillReturnRows(sqlmock.NewRows([]string{"id"}).AddRow(1))
mock.ExpectCommit()

// 最后验证所有mock期望都被执行
if err := mock.ExpectationsWereMet(); err != nil {
    t.Errorf("未满足所有mock期望: %v", err)
}

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