there is a remaining expectation which was not matched: ExpectedQuery => expecting Query, QueryContext or QueryRow which

Question

I have this repository

func (r *WorkspaceRepository) Delete(id any) (bool, error) {
    if err := r.db.Delete(&model.Workspace{}, "id = ?", id).Error; err != nil {
        return false, err
    }

    return true, nil
}

Which does a Soft-Delete, the query which this Delete operation runs is this one

UPDATE "workspaces" SET "deleted_at"='2022-07-04 09:09:20.778' WHERE id = 'c4610193-b43a-4ed7-9ed6-9d67b3f97502' AND "workspaces"."deleted_at" IS NULL

My test

uid := uuid.New()

mock.ExpectBegin()
mock.ExpectQuery(regexp.QuoteMeta(`UPDATE "workspaces" SET`)).WithArgs(sqlmock.AnyArg(), uid)
mock.ExpectCommit()

_, err = repository.Delete(uid)
assert.NoError(t, err)

I got the error

    workspace_test.go:165: 
                Error Trace:    workspace_test.go:165
                Error:          Received unexpected error:
                                call to ExecQuery 'UPDATE "workspaces" SET "deleted_at"=$1 WHERE id = $2 AND "workspaces"."deleted_at" IS NULL' with args [{Name: Ordinal:1 Value:2022-07-04 10:54:16.4863731 -0300 -03} {Name: Ordinal:2 Value:ae4124d9-ed16-4dcf-beb9-fcdcf27ca7da}], was not expected, next expectation is: ExpectedQuery => expecting Query, QueryContext or QueryRow which:
                                  - matches sql: 'UPDATE "workspaces" SET'
                                  - is with arguments:
                                    0 - {}
                                    1 - ae4124d9-ed16-4dcf-beb9-fcdcf27ca7da; call to Rollback transaction, was not expected, next expectation is: ExpectedQuery => expecting Query, QueryContext or QueryRow which:
                                  - matches sql: 'UPDATE "workspaces" SET'
                                  - is with arguments:
                                    0 - {}
                                    1 - ae4124d9-ed16-4dcf-beb9-fcdcf27ca7da
                Test:           TestDeleteWorkspace

Answer 1

It took me some time to figure out, but again, sqlmock error explains the problem if you know how to read the error message.

In our code, we execute UPDATE statement that triggers Exec db driver command instead of Query that is used for selects.

The error message explains that sqlmock expects code to perform one of these operations:

next expectation is: ExpectedQuery => expecting Query, QueryContext or QueryRow

We need to use ExpectExec() instead of ExpectQuery() in test to match our code. We also need to define how many records are updated in our mocked DB by adding WillReturnResult(sqlmock.NewResult(1, 1)).

Fix one line and test should pass:

mock.ExpectExec(regexp.QuoteMeta(`UPDATE "workspaces" SET`)).WithArgs(sqlmock.AnyArg(), uid).WillReturnResult(sqlmock.NewResult(1, 1))