Sqlmock在将整数参数替换为字母数字参数时无法匹配查询。

huangapple
go评论103阅读模式
英文:

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

问题

我正在尝试使用go-sqlmock来测试我的数据库函数，但是我无法通过这个测试。

这是我的模型：

  1. type User struct {
  2. 	ID            string `gorm:"primaryKey; size:11"`
  3. 	FirstName     string `gorm:"NOT NULL; size:255"`
  4. 	LastName      string `gorm:"NOT NULL; size:255"`
  5. 	Email         string `gorm:"NOT NULL; UNIQUE_INDEX"`
  6. 	Password      string `gorm:"NOT NULL"`
  7. 	CreatedAt     time.Time
  8. 	UpdatedAt     time.Time
  9. 	DeletedAt     *time.Time `sql:"index"`
  10. }

这是我的数据库函数：

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

我有两个测试示例，一个通过了，另一个没有通过。我像这样初始化了sqlmock对象：

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

这个测试通过了：

  1. t.Run("Get a user", func(t *testing.T) {
  2. 	expected := &user.User{
  3. 		Email: "alice@cc.cc",
  4. 	}
  6. 	u := NewUserRepo(gormDB)
  8. 	mock.
  9. 		ExpectQuery(
  10. 			`SELECT * FROM "users" WHERE "users"."deleted_at" IS NULL AND (("users"."id" = $1)) ORDER BY "users"."id" ASC LIMIT 1`).
  11. 		WithArgs("100").
  12. 		WillReturnRows(
  13. 			sqlmock.NewRows([]string{"email"}).
  14. 				AddRow("alice@cc.cc"))
  16. 	result, err := u.GetByID("100")
  18. 	assert.EqualValues(t, expected, result)
  19. 	assert.Nil(t, err)
  20. })

但是这个测试没有通过：

  1. t.Run("Get a user", func(t *testing.T) {
  2. 	expected := &user.User{
  3. 		Email: "alice@cc.cc",
  4. 	}
  6. 	u := NewUserRepo(gormDB)
  8. 	mock.
  9. 		ExpectQuery(
  10. 			`SELECT * FROM "users" WHERE "users"."deleted_at" IS NULL AND (("users"."id" = $1)) ORDER BY "users"."id" ASC LIMIT 1`).
  11. 		WithArgs("100a").
  12. 		WillReturnRows(
  13. 			sqlmock.NewRows([]string{"email"}).
  14. 				AddRow("alice@cc.cc"))
  16. 	result, err := u.GetByID("100a")
  18. 	assert.EqualValues(t, expected, result)
  19. 	assert.Nil(t, err)
  20. })

你可以看到唯一的区别是我用100a替换了100，因为主键是一个字符串字段。

这是我得到的错误信息：

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

我不知道我在这里做错了什么，我甚至将$1替换为"$1"也没有起作用。任何帮助将是很好的。谢谢。

英文:

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

  1. type User struct {
  2. 	ID            string `gorm:"primaryKey; size:11"`
  3. 	FirstName     string `gorm:"NOT NULL; size:255"`
  4. 	LastName      string `gorm:"NOT NULL; size:255"`
  5. 	Email         string `gorm:"NOT NULL; UNIQUE_INDEX"`
  6. 	Password      string `gorm:"NOT NULL"`
  7. 	CreatedAt     time.Time
  8. 	UpdatedAt     time.Time
  9. 	DeletedAt     *time.Time `sql:"index"`
  10. }

Here is my DB function

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

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

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

This one is working

  1. t.Run("Get a user", func(t *testing.T) {
  2. 	expected := &user.User{
  3. 		Email: "alice@cc.cc",
  4. 	}
  6. 	u := NewUserRepo(gormDB)
  8. 	mock.
  9. 		ExpectQuery(
  10. 			`SELECT * FROM "users" WHERE "users"."deleted_at" IS NULL AND (("users"."id" = $1)) ORDER BY "users"."id" ASC LIMIT 1`).
  11. 		WithArgs("100").
  12. 		WillReturnRows(
  13. 			sqlmock.NewRows([]string{"email"}).
  14. 				AddRow("alice@cc.cc"))
  16. 	result, err := u.GetByID("100")
  18. 	assert.EqualValues(t, expected, result)
  19. 	assert.Nil(t, err)
  20. })

But this one is not working

  1. t.Run("Get a user", func(t *testing.T) {
  2. 	expected := &user.User{
  3. 		Email: "alice@cc.cc",
  4. 	}
  6. 	u := NewUserRepo(gormDB)
  8. 	mock.
  9. 		ExpectQuery(
  10. 			`SELECT * FROM "users" WHERE "users"."deleted_at" IS NULL AND (("users"."id" = $1)) ORDER BY "users"."id" ASC LIMIT 1`).
  11. 		WithArgs("100a").
  12. 		WillReturnRows(
  13. 			sqlmock.NewRows([]string{"email"}).
  14. 				AddRow("alice@cc.cc"))
  16. 	result, err := u.GetByID("100a")
  18. 	assert.EqualValues(t, expected, result)
  19. 	assert.Nil(t, err)
  20. })

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

  1. Query: actual sql: 
  2. "SELECT * FROM "users" WHERE "users"."deleted_at" IS NULL AND ((100a)) ORDER BY "users"."id" ASC LIMIT 1" 
  3. does not equal to expected 
  4. "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.

答案1

得分: 1

这个问题的解决方法如下：

首先，我更新了我的DB获取函数：

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

然后，我将我的模拟测试更改为以下内容：

  1. mock.
  2.     ExpectQuery(
  3.         `SELECT * FROM "users" WHERE "users"."deleted_at" IS NULL AND ((id = $1)) ORDER BY "users"."id" ASC LIMIT 1`).
  4.     WithArgs("100a").
  5.     WillReturnRows(
  6.         sqlmock.NewRows([]string{"email"}).
  7.             AddRow("alice@cc.cc"))
  9. result, err := u.GetByID("100a")
英文:

Here is the fix for this issue.

First of all I update my DB get function

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

And changed my mock test to this

  1. mock.
  2. 	ExpectQuery(
  3. 		`SELECT * FROM "users" WHERE "users"."deleted_at" IS NULL AND ((id = $1)) ORDER BY "users"."id" ASC LIMIT 1`).
  4. 	WithArgs("100a").
  5. 	WillReturnRows(
  6. 		sqlmock.NewRows([]string{"email"}).
  7. 			AddRow("alice@cc.cc"))
  9. result, err := u.GetByID("100a")

huangapple
  • 本文由 发表于 2022年9月22日 05:29:42
  • 转载请务必保留本文链接：https://go.coder-hub.com/73807132.html
  • go
  • go-gorm
  • go-sqlmock
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定