Error on INSERT, query was not expected

[rafareyes7]

Hi, I'm trying to run an unit test that executes an INSERT into DB, so I mocked exactly the same query string that the gorm log displays with the same arguments but I'm still having problem while trying to execute the test, (with postgres driver)

It seems like the sql-mock does not match the SQL that I defined in the mock and display an error like:

"call to Query 'INSERT INTO "customer" ("customer_key","first_name","middle_name","last_surname") VALUES ($1,$2,$3,$4) RETURNING "customer"."customer_key"' with args [{Name: Ordinal:1 Value:RLKuxK-wnj8SUn50iFMjsrCtikTLTEmUFP7fOSE2veI=} {Name: Ordinal:2 Value:Martín} {Name: Ordinal:3 Value:Constan} {Name: Ordinal:4 Value:Smith}] was not expected "

GO Playgound Running example

repository.go

...
func (r *repository) Register(customer *models.Customer) (*string, error) {
    err := r.db.Create(&customer).Error
    if err != nil {
        return nil, err
    }

    return customer.CustomerKey, nil
}
...

repository_test.go

func TestRegister(t *testing.T) {
    qStr := `INSERT  INTO "customer" ("customer_key","first_name","middle_name","last_surname") 
    VALUES ($1,$2,$3,$4) RETURNING "customer"."customer_key"`

    t.Run("Must return the newly created customer key, if given customer input is valid", func(t *testing.T) {
        r := mocks.CustomerModel

        mock.MatchExpectationsInOrder(false)
        mock.ExpectBegin()
        mock.ExpectQuery(regexp.QuoteMeta(qStr)).
            WithArgs(r.CustomerKey, r.FirstName, r.MiddleName, r.LastSurname).
            WillReturnRows(sqlmock.NewRows([]string{"customer_key"}).AddRow(r.CustomerKey))
        mock.ExpectCommit()
        customerKey, err := repo.Register(r)

        assert.Nil(t, err)
        assert.NotNil(t, customerKey)
    })
}

and I get the following error:

Running tool: /usr/local/go/bin/go test -timeout 30s /internal/customer -run ^(TestRegister)$
call to Query 'INSERT  INTO "customer" ("customer_key","first_name","middle_name","last_surname") VALUES ($1,$2,$3,$4) RETURNING "customer"."customer_key"' with args [{Name: Ordinal:1 Value:RLKuxK-wnj8SUn50iFMjsrCtikTLTEmUFP7fOSE2veI=}
 {Name: Ordinal:2 Value:Martín} {Name: Ordinal:3 Value:Constan} 
{Name: Ordinal:4 Value:Smith}] 
was not expected <----------------------ERROR-------------------------

INSERT  INTO "customer" ("customer_key","first_name","middle_name","last_surname") VALUES ('RLKuxK-wnj8SUn50iFMjsrCtikTLTEmUFP7fOSE2veI=','Martín','','Constan','Smith'') RETURNING "customer"."customer_key"  
[0 rows affected or returned ]

(data-repository/internal/customer/repository.go:57) 
[2020-01-22 19:39:18]  call to Query 'INSERT INTO "customer" ("customer_key","first_name","middle_name","last_surname") VALUES ($1,$2,$3,$4) RETURNING "customer"."customer_key"' with args [{Name: Ordinal:1 Value:RLKuxK-wnj8SUn50iFMjsrCtikTLTEmUFP7fOSE2veI=} {Name: Ordinal:2 Value:Martín} {Name: Ordinal:3 Value:Constan} {Name: Ordinal:4 Value:Smith}] 
was not expected <----------------------ERROR AGAIN-------------------------

--- FAIL: TestRegister (0.00s)
    --- FAIL: TestRegister/Must_return_the_newly_created_customer_key,_if_given_customer_input_is_valid (0.00s)
        data-repository/internal/customer/repository_test.go:89: 
                Error Trace:    repository_test.go:89
                Error:          Expected nil, but got: &status.statusError{Code:13, Message:"An error occurred.", Details:[]*any.Any{(*any.Any)(0xc000215f90)}, XXX_NoUnkeyedLiteral:struct {}{}, XXX_unrecognized:[]uint8(nil), XXX_sizecache:0}
                Test:           TestRegister/Must_return_the_newly_created_customer_key,_if_given_customer_input_is_valid
FAIL
FAIL    /internal/customer  0.008s
FAIL
Error: Tests failed.

I'm using the following versions:

github.com/DATA-DOG/go-sqlmock v1.4.0 github.com/jinzhu/gorm v1.9.11

I would appreciate any help...

[navono]

@rafareyes7 Do you have a reproducible demo?

[rafareyes7]

@navono yes, I just edited the post and add a GO Playgound Running example

[muhlemmer]

@rafareyes7: I had the same issues. The problem seems to be with special character, like (..) of $1:

func TestMock(t *testing.T) {
    query := "insert into test_table (first, second) values ($1, $2)"
    db, mock, err := sqlmock.New()
    if err != nil {
        t.Fatal(err)
    }

    mock.ExpectExec(query).WillReturnResult(driver.ResultNoRows)
    if _, err := db.Exec(query); err != nil {
        t.Error(err)
    }
    mock.ExpectClose()
    db.Close()

}

I tried the following values for query:

• "insert into test_table (first, second) values ($1, $2)" -> fails
• "insert into test_table (first, second) values" -> fails
• "insert into test_table values $1" -> fails
• "insert into test_table values" -> works!

The default QueryMatcher is a regex one. And I assume that its not playing well with those characters. I didn't really dig into the sources, but found changing the QueryMatcher for a simple compare check does the trick! Changing the QueryMatcher is documented here: https://godoc.org/github.com/DATA-DOG/go-sqlmock#QueryMatcher

[moemoe89]

@muhlemmer I've experienced similar issue when using brackets () on my query. Is it work if you escaping the brackets or special characters? for example: insert into test_table \\(first, second\\) values

[yagehu]

Also need to escape characters like ? and *

[ehduardu]

I have the same error, no solution seems to work

[H4xorPL]

also you need to escape $ characters

insert into test_table \(first, second\) values \(\$1, \$2\)

@ehduardu If your INSERT is RETURNing check if you are expecting a query rather than exec.

[diegommm]

Hi @rafareyes7! sqlmock uses the standard library's regexp package. An alternative to workaround the differences that Postgres drivers have that they use positional parameters (i.e. $1, $2, etc.) instead of the standard SQL92 question mark ? is to use the following snippet: ([?]|[$][0-9]+) instead of the positional $n or the ?.

This is just an untested example of how to use regular expressions. Please, refer to the linked standard regexp package for reference.

Thank you!