使用go-sqlmock模拟gorm `updates`时出现` not not match实际sql`错误？

Question

repository.go

func (repo *Repository) Update(info *model.Requests) error{
   if info == nil{
      return nil
   }
   columnChanges := map[string]interface{}{
      status:       “completed",
   }
   if err := repo.db.Table(“requests").Model(info).Where(fmt.Sprintf("%s = ? AND  %s = ? AND %s = ? AND %s = ?",
      requestID, systemID, requestType, status),info.RequestID, info.SystemID, info.RequestType,”progress").Updates(columnChanges).Error; err != nil {
      return err
   }
   return nil
}

模拟

repository_test.go

func TestRepository_Update(t *testing.T) {
   type testData struct {
      input     *model.Requests
      queryString string
      queryArgs   []driver.Value
      updateErr   error
      hasErr    bool
   }

   db, mock, _ := sqlmock.New()
   defer db.Close()
   dbInstance, _ := gorm.Open("postgres", db)

   testDataList := []testData{
        {
   input: &model.Requests{
      RequestID: 4,
      SystemID: 2,
      RequestType: “mobile",
      Status: “completed",
   },
   queryString: `UPDATE "requests" SET "status" = $1 WHERE (“request_id" = $2 AND “system_id" = $3 AND “request_type" = $4 AND "status" = $5) `,
   queryArgs:   []driver.Value{“completed", 2, 4, “mobile", “progress"},
   updateErr:   nil,
   hasErr: false,
},
}

    for _, data := range testDataList {
      repo := Repository(zerolog.Nop(), dbInstance)

      if data.queryString != "" {
         mock.ExpectBegin()
         m := mock.ExpectExec(data.queryString).WithArgs(data.queryArgs...)
         if data.hasErr {
            m.WillReturnError(data.updateErr)
         } else {
            m.WillReturnResult(sqlmock.NewResult(1, 1))
         }
         mock.ExpectCommit()
      }

      resultErr := repo.Requests(data.input)

      if data.hasErr {
         assert.NotNil(t, resultErr)
      } else {
         assert.Nil(t, resultErr) //Error thrown in this line 
      }

      if err := mock.ExpectationsWereMet(); err != nil {
         t.Errorf("there were unfulfilled expectations: %s", err)
      }
   }
}

我得到一个错误

ExecQuery: could not match actual sql: "UPDATE "requests" SET "status" = $1 WHERE (request_id = $2 AND system_id = $3 AND request_type = $4 AND status = $5)" with expected regexp "UPDATE "requests" SET "status" = $1 WHERE ("request_id" = $2 AND "system_id" = $3 AND "request_type" = $4 AND "status" = $5)”



            Error Trace:    repository_test.go:<line number>
            Error:          Expected nil, but got: &errors.errorString{s:"ExecQuery: could not match actual sql: \"UPDATE \"requests\" SET \"status\" = $1 WHERE (request_id = $2 AND system_id = $3 AND request_type = $4 AND status = $5)\" with expected regexp \"UPDATE \"requests\" SET \"status\" = $1 WHERE (\”request_id\" = $2 AND \”system_id\" = $3 AND \”request_type\" = $4 AND \"status\" = $5)\""}
            Test:           Repository_Update
    repository_test.go:<lineNumber>: there were unfulfilled expectations: there is a remaining expectation which was not matched: ExpectedExec => expecting Exec or ExecContext which:
          - matches sql: 'UPDATE "requests" SET "status" = $1 WHERE (“request_id" = $2 AND “system_id" = $3 AND “request_type" = $4 AND "status" = $5) '
          - is with arguments:
            0 - success
            1 - 2
            2 - 4
            3 - image
            4 - pending
          - should return Result having:
              LastInsertId: 1
              RowsAffected: 1

当我将gorm日志级别设置为true时，这是我看到的实际SQL

UPDATE "requests" SET "status" = ‘completed'  WHERE (request_id = 5 AND  system_id = 1 AND request_type = ‘mobile' AND status = ‘progress')

我试过了，把ExpectExec改成了ExpectPrepare().ExpectExec或mock.ExpectQuery(regexp.QuoteMeta，还有其他一些在go-sqlmock问题中提到的选项。它们都不起作用。用这个敲打了2天。请帮帮忙。

Answer 1

原因是您没有转义查询字符串的正则表达式。Sqlmock期望在您的期望中使用正则表达式。

现在，如果您想要匹配精确的查询字符串，而不是它的一部分。您可以指定sqlmock mock选项，如下所示：

db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))

答案在文档中，如果您只需阅读一次api文档，就可以节省您的时间。