Go Unit test - could not match actual sql with expected regexp while mocking gorm using go-sqlmock?

Question

When unit testing I mock gorm using sqlmock. But when running all initial setup are fine but error happen when the actual query and testing query matches. I have given below all codes.

user.go

func (r *users) GetUserByID(userID uint) (*domain.User, *errors.RestErr) {
    var resp domain.User

    res := r.DB.Model(&domain.User{}).Where("id = ?", userID).First(&resp)

    if res.RowsAffected == 0 {
        logger.Error("error occurred when getting user by user id", res.Error)
        return nil, errors.NewNotFoundError(errors.ErrRecordNotFound)
    }

    return &resp, nil
}

Mock

user_test.go

type Suite struct {
    suite.Suite
    DB         *gorm.DB
    mock       sqlmock.Sqlmock
    db         *sql.DB
    err        error
    repository repository.IUsers
}

func (s *Suite) SetupSuite() {
    s.db, s.mock, s.err = sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))

    require.NoError(s.T(), s.err)

    dialector := mysql.New(mysql.Config{
        DSN:                       "sqlmock_db_0",
        DriverName:                "mysql",
        Conn:                      s.db,
        SkipInitializeWithVersion: true,
    })

    s.DB, s.err = gorm.Open(dialector, &gorm.Config{})

    require.NoError(s.T(), s.err)
    s.repository = impl.NewMySqlUsersRepository(s.DB)
}

func (s *Suite) AfterTest(_, _ string) {
    require.NoError(s.T(), s.mock.ExpectationsWereMet())
}

func TestInit(t *testing.T) {
    suite.Run(t, new(Suite))
}

// .................Start Testing....................

func (s *Suite) Test_repository_Get() {
    var (
        id        = uint(1)
        user_name = "user-name"
        fast_name = "fast-name"
    )

    s.mock.ExpectQuery(regexp.QuoteMeta(`SELECT * FROM "users" WHERE (id = $1) ORDER BY "users"."id" LIMIT 1`)).
        WithArgs(uint(id)).
        WillReturnRows(sqlmock.NewRows([]string{"id", "user_name", "fast_name"}).
            AddRow(uint(id), user_name, fast_name))

    res, _ := s.repository.GetUserByID(uint(id))
    require.Nil(s.T(), deep.Equal(&domain.User{ID: uint(id), UserName: user_name, LastName: fast_name}, res))
}

when I execute res, _ := s.repository.GetUserByID(uint(id)) this line it goes user.go file. Here in sql query line Getting error Like this given below .

actual sql: "SELECT * FROM `users` WHERE id = ? ORDER BY `users`.`id` LIMIT 1" 
does not equal to expected "SELECT \* FROM "users" WHERE \(id = \$1\) ORDER BY "users"\."id" LIMIT 1"


=> expecting Query, QueryContext or QueryRow which:
     - matches sql: 'SELECT \* FROM "users" WHERE \(id = \$1\) ORDER BY "users"\."id" LIMIT 1'
     - is with arguments:
                0 - 1
     - should return rows:
                row 0 - [1 user-name fast-name]

What is the issue here?

this "SELECT * FROM `users` WHERE id = ? ORDER BY `users`.`id` LIMIT 1"` is not equal to "SELECT \* FROM "users" WHERE $id = \$1$ ORDER BY "users"\."id" LIMIT 1". Where the first query is what the mock driver received as input from gorm (ie actual sql), and the second query is what you have written as expectation. Your expectation is wrong. — user4466350
– user4466350, Commented Jul 26, 2021 at 11:09

Md. Abu Farhad · Accepted Answer · 2021-07-29 11:17:46Z

2

The main problem caused here the sql query. For Get-Item, we won't need full query here. Gorm automatically generate this. only need to pass

"SELECT(.*)"

I found this from a blog post on Medium. Here you can find details

Bellow code works fine

func (s *Suite) Test_repository_GetUserByID() {
var (
    id         = uint(1)
    user_name  = "user-name"
    first_name = "first-name"
)
s.mock.ExpectQuery("SELECT(.*)").
    WithArgs(id).
    WillReturnRows(sqlmock.NewRows([]string{"id", "user_name", "first_name"}).
        AddRow(uint(id), user_name, first_name))

 res, _ := s.repository.GetUserByID(uint(id))
 require.Nil(s.T(), deep.Equal(&domain.User{ID: uint(id), UserName: user_name, FirstName: first_name}, res))
 }

edited Jul 29, 2021 at 11:17

answered Jul 29, 2021 at 10:43

Md. Abu Farhad

4411 gold badge8 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4466350 Over a year ago

friendly speaking, this is rather a workaround than a solution. It works ! we agree on that, but i feel like you missed the point. So just in case i want to share with you this little link go-database-sql.org/… which i hope will open your eyes about the initial issue

Sunbula Khasanova Over a year ago

it works but not the correct solution though. For example if i change id to i which is incorrect it still says my test is passed: res := r.DB.Model(&domain.User{}).Where("i = ?", userID).First(&resp)

Collectives™ on Stack Overflow

Go Unit test - could not match actual sql with expected regexp while mocking gorm using go-sqlmock?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related