Sqlmock is not matching the query when replace integer argument with alpha-numeric one

Question

I am trying to test my DB functions using go-sqlmock and I am not able make this one test pass.

Here is my model

type User struct {
    ID            string `gorm:"primaryKey; size:11"`
    FirstName     string `gorm:"NOT NULL; size:255"`
    LastName      string `gorm:"NOT NULL; size:255"`
    Email         string `gorm:"NOT NULL; UNIQUE_INDEX"`
    Password      string `gorm:"NOT NULL"`
    CreatedAt     time.Time
    UpdatedAt     time.Time
    DeletedAt     *time.Time `sql:"index"`
}

Here is my DB function

func (u *userRepo) GetByID(id string) (*user.User, AppError) {
    var user user.User
    if err := u.db.First(&user, id).Error; err != nil {
        return nil, NewNotFoundError(err)
    }
    return &user, nil
}

I have two examples of tests, one is passing but the other does not. I am initializing sqlmock object like this

sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))

This one is working

t.Run("Get a user", func(t *testing.T) {
    expected := &user.User{
        Email: "alice@cc.cc",
    }

    u := NewUserRepo(gormDB)

    mock.
        ExpectQuery(
            `SELECT * FROM "users" WHERE "users"."deleted_at" IS NULL AND (("users"."id" = $1)) ORDER BY "users"."id" ASC LIMIT 1`).
        WithArgs("100").
        WillReturnRows(
            sqlmock.NewRows([]string{"email"}).
                AddRow("alice@cc.cc"))

    result, err := u.GetByID("100")

    assert.EqualValues(t, expected, result)
    assert.Nil(t, err)
})

But this one is not working

t.Run("Get a user", func(t *testing.T) {
    expected := &user.User{
        Email: "alice@cc.cc",
    }

    u := NewUserRepo(gormDB)

    mock.
        ExpectQuery(
            `SELECT * FROM "users" WHERE "users"."deleted_at" IS NULL AND (("users"."id" = $1)) ORDER BY "users"."id" ASC LIMIT 1`).
        WithArgs("100a").
        WillReturnRows(
            sqlmock.NewRows([]string{"email"}).
                AddRow("alice@cc.cc"))

    result, err := u.GetByID("100a")

    assert.EqualValues(t, expected, result)
    assert.Nil(t, err)
})

As you can see the only difference is I replace 100 by 100a which I can do because the primary key is a string field.

And this is the error I am getting

Query: actual sql: 
"SELECT * FROM "users" WHERE "users"."deleted_at" IS NULL AND ((100a)) ORDER BY "users"."id" ASC LIMIT 1" 
does not equal to expected 
"SELECT * FROM "users" WHERE "users"."deleted_at" IS NULL AND (("users"."id" = $1)) ORDER BY "users"."id" ASC LIMIT 1" 

I don't know what I am doing wrong here, I even replaced the $1 by "$1" which also didn't work. Any help would be great. Thanks.

Answer 1

Here is the fix for this issue.

First of all I update my DB get function

func (u *userRepo) GetByID(id string) (*user.User, AppError) {
    var user user.User
    if err := u.db.Where("id = ?", id).First(&user).Error; err != nil {
        return nil, NewNotFoundError(err)
    }
    return &user, nil
}

And changed my mock test to this

mock.
    ExpectQuery(
        `SELECT * FROM "users" WHERE "users"."deleted_at" IS NULL AND ((id = $1)) ORDER BY "users"."id" ASC LIMIT 1`).
    WithArgs("100a").
    WillReturnRows(
        sqlmock.NewRows([]string{"email"}).
            AddRow("alice@cc.cc"))

result, err := u.GetByID("100a")