sqlmock与查询不匹配，但查询相同并且日志输出显示相同 [英] sqlmock is not matching query, but query is identical and log output shows the same

问题描述

我正在尝试使用sqlmock使用Gorm编写一些代码的测试.我想出了为插入功能编写测试的方法，但是现在我想尽办法使更新生效.

I'm trying to write tests for some code using Gorm using sqlmock. I figured out writing tests for my insert function but now pulling my hair out trying to get an update working.

工作流的第一部分仅从数据库查询记录.即使日志输出显示它们相同，我也无法使其与我的SQL相匹配.

First piece of the workflow merely queries the record from the database. I can't get it to match my SQL even though the log output shows them as being identical.

这是错误消息:

(/path/to/my/project/database.go:263)
[2020-01-08 10:29:40]  Query: could not match actual sql: "SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = ?)) ORDER BY "storage_pools"."id" ASC LIMIT 1" with expected regexp "SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = ?)) ORDER BY "storage_pools"."id" ASC LIMIT 1"

我还尝试使用ExpectQuery的ExpectExec插入.

I also tried using ExpectExec insert of ExpectQuery.

    for _, c := range cases {

        db, mock, err := sqlmock.New()
        if err != nil {
            t.Fatal(err)
        }

        DB, err := gorm.Open("sqlite3", db)
        if err != nil {
            t.Fatal(err)
        }
        DB.LogMode(true)

        mock.ExpectQuery(`SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = ?)) ORDER BY "storage_pools"."id" ASC LIMIT 1`)

        err = UpdateStoragePool(DB, &c.givenPool)
        if !reflect.DeepEqual(c.wantedError, err) {
            t.Fatalf("expecting errror %q, got %q", c.wantedError, err)
        }

        // if we didn't have any errors during the tx, check all expectations were met
        if c.wantedError == nil {
            if err := mock.ExpectationsWereMet(); err != nil {
                t.Fatalf(err.Error())
            }
        }

    }

我也尝试过:

mock.ExpectQuery(`SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = '1')) ORDER BY "storage_pools"."id" ASC LIMIT 1`).WithArgs(1)  

mock.ExpectExec(`SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = ?)) ORDER BY "storage_pools"."id" ASC LIMIT 1`)  

mock.ExpectExec(`SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = '1')) ORDER BY "storage_pools"."id" ASC LIMIT 1`).WithArgs(1)

有人知道我在做什么错吗?

Anyone have any ideas what I'm doing wrong here?

*更新*

由于某些原因，这不适用于select语句:

This DOES NOT work for select statements for some reason:

        mock.ExpectExec(`SELECT \* FROM "storage_pools"`).
            WithArgs(c.givenPool.PoolId).WillReturnResult(sqlmock.NewResult(1, 1))

[2020-01-13 10:32:21]  call to Query 'SELECT * FROM "storage_pools"  WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = ?)) ORDER BY "storage_pools"."id" ASC LIMIT 1' with args [{Name: Ordinal:1 Value:1}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:
  - matches sql: 'SELECT \* FROM "storage_pools"'
  - is with arguments:
    0 - 1
  - should return Result having:
      LastInsertId: 1
      RowsAffected: 1

这确实可行，但现在我在哪里遇到了一个新问题.对于初学者，Gorm出于某种原因正在执行2条select语句...第一个起作用并找到该行，第二个查询找不到相同的行.我在这里不知所措.即将放弃这个图书馆.我本可以在尝试使之工作的时候写自己的书.

This DOES WORK but now I've hit a new problem where. For starters Gorm is doing 2 select statements for some reason... The first one works and finds the row, the second query does not find the same row. I'm at a loss here. About to just give up on this library. I could have written my own in the time we've spent trying to get it working.

        db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
        if err != nil {
            t.Fatal(err)
        }

        DB, err := gorm.Open("postgres", db)
        if err != nil {
            t.Fatal(err)
        }
        DB.LogMode(true)

        mockedRow := sqlmock.NewRows([]string{"id", "created_at", "updated_at", "poolid"}).AddRow(1, time.Now(), time.Now(), "1")

        // Mock the complete transaction
        mock.ExpectQuery(`SELECT * FROM "storage_pools"  WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = ?)) ORDER BY "storage_pools"."id" ASC LIMIT 1`).
            WithArgs(c.givenPool.PoolId).
            WillReturnRows(mockedRow)

        mock.ExpectQuery(`SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND "storage_pools"."id" = ? AND ((poolid = ?)) ORDER BY "storage_pools"."id" ASC`).
            WithArgs(1, c.givenPool.PoolId).
            WillReturnRows(mockedRow)

推荐答案

mock.ExpectExec()函数不执行简单的字符串比较.而是使用输入字符串作为RegExp来匹配查询.

mock.ExpectExec() function does not perform simple strings comparison. Instead it uses input string as RegExp to match the query.

SQL匹配字符串中的某些字符是保留的RegExp字符，应进行转义以匹配SQL.

Some characters in your SQL match string are reserved RegExp characters and should be escaped to match SQL.

转义后，您的字符串应如下所示:

Your string should look like this after escaping:

SELECT \* FROM "storage_pools" WHERE "storage_pools"\."deleted_at" IS NULL AND \(\(poolid \= \?\)\) ORDER BY "storage_pools"\."id" ASC LIMIT 1

提示:您可以使用 https://www.regex-escape在线进行字符串转义.com/preg_quote-online.php 或其他一些网站

Hint: You can escape your string online using https://www.regex-escape.com/preg_quote-online.php or some other site

另外的想法:在没有为精确SQL增加太多额外价值的情况下，使用完全SQL匹配进行测试可能很脆弱.

Additional thought: Test with exact SQL match can be fragile without adding much extra value for exact SQL.

如果有人进行了无害更改(例如添加多余的空格字符)，则测试可能会给您带来假阳性结果.另一方面，全文匹配不能捕获与SQL不兼容的数据库架构更改.

Test can give you false positive result if anyone made harmless change in it like adding extra space character. From other side, full text match does not catch DB schema changes that are not compatible with SQL.

我最终完成了我的项目的设置:

I ended up with this setup for my projects:

使用mock.ExpectExec()和基本子字符串(如INSERT INTO history)运行单元测试.这使测试变得不那么脆弱.同时，我们还在此测试中进行了大量检查，以验证代码执行流程:

Run unit tests with mock.ExpectExec() with basic substrings like INSERT INTO history. That makes tests much less fragile. At the same time we are still checking a lot in this test to verify code execution flow:

1. SQL参数数量
2. 这些SQL参数的值
3. 确保使用mock.ExpectationsWereMet()执行SQL命令

最重要的是，我们必须为我们的SQL查询运行集成测试.这是确保我们的SQL正确并与最新的数据库更改保持最新的唯一方法.

On top of that we have to run integration tests for our SQL queries. That is the only way to make sure that our SQL are correct and up to date with latest DB changes.

P.S.避免在选择中使用*.明确显示字段名称.

P.S. Avoid * in select. Be explicit with field names.

Update1:​​

Update1:

小心弦的情况. "SELECT"和"SELECT"是两个不同的字符串.

Be careful with strings case. "SELECT" and "select" are two different strings.

我当前项目中的一些代码片段:

Some code snippets from my current project:

// insert
sqlMock.ExpectExec("INSERT INTO eeo").
        WithArgs("2018-12-31", "John Dow", "title"}).
        WillReturnResult(sqlmock.NewResult(mock.EeoID, 1))

// select
rows := sqlmock.NewRows([]string{"req_id", "state"})
sqlMock.ExpectQuery("select").WithArgs(mock.CandidateID).WillReturnRows(rows)

这篇关于sqlmock与查询不匹配，但查询相同并且日志输出显示相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！