sqlmock is not matching query, but query is identical and log output shows the same

Question

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.

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.

Here is the error message:

(/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"

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())
            }
        }

    }

I've also tried:

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?

* UPDATE *

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

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)

score 61 · Answer 1 · answered Aug 06 '20 at 10:44

61

Try this:

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

Put your query into this function regexp.QuoteMeta().

answered Aug 06 '20 at 10:44

J.Y.Wang

685
7
11

1

This is the most useful thing I could find on this topic. Thanks! Escaping the query manually is almost impossible for large queries. – vcmkrtchyan Sep 23 '20 at 15:35
1

Most helpful answer. Thanks – Sayooj V R Jan 19 '21 at 04:08
3

it would be nice if you explained that `.ExpectQuery()` is parsing the string as regex and requires escape characters – Martin Meli Nov 15 '21 at 11:48

Dmitry Harnitski · Answer 2 · 2020-01-13T19:34:05.240

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

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

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

Additional thought: Test with exact SQL match can be fragile without adding much extra value for exact 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:

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:

Number of SQL parameters
Values of these SQL parameters
Ensure that SQL command executed using mock.ExpectationsWereMet()

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. Avoid * in select. Be explicit with field names.

Update1:

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)

I couldn't get it to work using "INSERT INTO history" approach. That approach works for my insert tests but for some reason I can't get it to work for a select statement. — , Jan 13 '20 at 18:27
How do you go about SELECT statements? I have yet to get ExpectExec to work. I can never get the query to match no matter how much escaping I do. Also how to you confirm the row returned using ExpectExec since it doesn't support WillReturnRows. — , Jan 13 '20 at 19:10
@Crashk1d Reg EX is case sensitive. Make sure you use case "SELECT" (not "select") in test — Dmitry Harnitski, Jan 13 '20 at 19:27
What database are you using? I absolutely cannot get ExpectQuery("select") to work. Tried every possible permutation until I was blue in the face. That works fine in my tests for my insert functions but not here. I'm also using Gorm, which I think is the cause for my frustration here. Between me using mysql, and the Gorm mysql driver not the greatest, I'm hitting stuff here. Gorm does a lot under the hood. And the error messages and logging for sqlmock leave much to be desired. — , Jan 13 '20 at 22:24
agreed, i have copy pasted the exact query from GORM Logs, but still it's not able to match. Doesn't work with either MySQL and Sqlite3. May just get rid of the whole gorm and do plain SQL at this point as it's not really worth the headache. — goyalankit, Jul 14 '20 at 20:11
@Crashk1d I'm using postgresql & mock.ExpectQuery("select") woks fine. In fact only this works perfectly in my case. — Sachin Raut, Jul 31 '20 at 17:45

score 1 · Answer 3 · answered Apr 05 '20 at 17:42

This is a strange solution but worked for me. Probably a bug in sqlmock. Duplicate your mockedRow variable and plug them in ExpectQuery.

mockedRow := sqlmock.NewRows([]string{"id", "created_at", "updated_at", "poolid"}).AddRow(1, time.Now(), time.Now(), "1")
mockedRow2 := 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(mockedRow2)

Alternatively you can create an array of mockedRow as follows:

mockedRow := []*sqlmock.Rows{
sqlmock.NewRows([]string{"id", "created_at", "updated_at", "poolid"}).AddRow(1, time.Now(), time.Now(), "1"),
sqlmock.NewRows([]string{"id", "created_at", "updated_at", "poolid"}).AddRow(1, time.Now(), time.Now(), "1"),
}
And use it as WillReturnRows(mockedRow[0]) and WillReturnRows(mockedRow[1])

sqlmock is not matching query, but query is identical and log output shows the same

3 Answers3

Linked

Related