Query could not match actual sql

[Osiris1975]

Hi,

So I'm so happy to have found this library, but I've spent two days now trying to get this query to work so I can mock. I've seen various other posts here about properly escaping my query, but I still can't seem to get it working. Here's my error message:

[2020-04-06 16:30:01]  Query: could not match actual sql: "SELECT * FROM "fields" WHERE ("fields"."id" = $1) ORDER BY "fields"."id" ASC LIMIT 1" with expected regexp "SELECT \\* FROM "fields" WHERE \\("fields"\\."id" = \\$1\\) ORDER BY "fields"\\."id" ASC LIMIT 1" 

Here's my unit test:

type Suite struct {
    suite.Suite
    DB   *gorm.DB
    mock sqlmock.Sqlmock

    repository Repository
    field      *Field
}

func (s *Suite) SetupSuite() {
    var (
        db  *sql.DB
        err error
    )

    db, s.mock, err = sqlmock.New()
    require.NoError(s.T(), err)

    s.DB, err = gorm.Open("postgres", db)
    require.NoError(s.T(), err)

    s.DB.LogMode(true)

    s.repository = NewDatabaseRepo(s.DB)
}

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

func (s *Suite) TestDb_Find() {
    user := rand.Int63()
    now := time.Now().UTC()
    var (
        row = []string{
            "id", "partner_id", "qualified_name", "description", "data_type", "created_at", "created_by",
            "updated_at", "updated_by"}
        id            = rand.Int63()
        partnerId     = rand.Int()
        qualifiedName = "user.cgh.first_name"
        description   = "CGH user's first name"
        dataType      = "string"
        createdAt     = now
        createdBy     = user
        updatedAt     = now
        updatedBy     = user
    )

    s.mock.ExpectQuery(
        `SELECT \\* FROM "fields"  WHERE \\("fields"\\."id" = \\$1\\) ORDER BY "fields"\\."id" ASC LIMIT 1`).
        WithArgs(id).
        WillReturnRows(sqlmock.NewRows(row).
            AddRow(id, partnerId, qualifiedName, description, dataType, createdAt, createdBy, updatedAt, updatedBy))

    res, err := s.repository.Find(id)
    expected := &Field{
        Id:            id,
        PartnerId:     partnerId,
        QualifiedName: qualifiedName,
        Description:   description,
        DataType:      DataType(1),
        CreatedAt:     now,
        CreatedBy:     user,
        UpdatedAt:     now,
        UpdatedBy:     user,
    }
    assert.NoError(s.T(), err)
    assert.Equal(s.T(), expected, res)
}

Here's the Fields object model from the test:

type Field struct {
    Id            int64     `json:"id" gorm:"primary_key" readonly:"true"`
    PartnerId     int       `json:"partner_id" gorm:"not_null" readonly:"true"`
    QualifiedName string    `json:"qualified_name" binding:"required" gorm:"unique; not_null"`
    Description   string    `json:"description"`
    DataType      DataType  `json:"data_type" binding:"required" enums:"string,int,double,boolean,array,datetime,date"`
    CreatedAt     time.Time `json:"created_at" readonly:"true"`
    CreatedBy     int64     `json:"created_by" readonly:"true"`
    UpdatedAt     time.Time `json:"updated_at" readonly:"true"`
    UpdatedBy     int64     `json:"updated_by" readonly:"true"`
}

I have tried the following iterations of the query with no success:

    s.mock.ExpectQuery(
        `SELECT \\* FROM "fields"  WHERE \\("fields"."id" = ?\\) ORDER BY "fields"."id" ASC LIMIT 1`).
        WithArgs(id).
        WillReturnRows(sqlmock.NewRows(row).
            AddRow(id, partnerId, qualifiedName, description, dataType, createdAt, createdBy, updatedAt, updatedBy))

and

    s.mock.ExpectQuery(regexp.QuoteMeta(
        `SELECT * FROM "fields"  WHERE ("fields"."id" = 1) ORDER BY "fields"."id" ASC LIMIT 1`)).
        WithArgs(id).
        WillReturnRows(sqlmock.NewRows(row).
            AddRow(id, partnerId, qualifiedName, description, dataType, createdAt, createdBy, updatedAt, updatedBy))

and also using regexp.QuoteMeta which supposedly does the escaping for you, though I noticed in the output it only put one / instead of 2 as I've seen in some other posts here.

    s.mock.ExpectQuery(regexp.QuoteMeta(
        `SELECT * FROM "fields"  WHERE ("fields"."id" = 1) ORDER BY "fields"."id" ASC LIMIT 1`)).
        WithArgs(id).
        WillReturnRows(sqlmock.NewRows(row).
            AddRow(id, partnerId, qualifiedName, description, dataType, createdAt, createdBy, updatedAt, updatedBy))

Also, here's the code for the method under test:

type Repository interface {
    Find(int64) (*Field, error)
}

type Repo struct {
    db *gorm.DB
}

type RegistrationService interface {
    Repository
}

func NewDatabaseRepo(db *gorm.DB) Repository {
    return &Repo{db: db}
}

// Find a Field object by its id.
func (r *Repo) Find(id int64) (*Field, error) {
    var f Field
    if err := r.db.Debug().Where(&Field{Id: id}).First(&f).Error; err != nil {
        return nil, err
    }
    return &f, nil
}

Any help would be greatly appreciated!

[l3pp4rd]

Hi, Instead of trying to escape the query, you could just use an equals query string matcher.

db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))

You can even create your own matcher to print which column did not match. I personally used regex matching to a level like matching just SELECT part of query if I knew that there was only one select being tested. Asserting a query string will require to update all tests if you would just add sorting for instance.

[Osiris1975]

@l3pp4rd thanks very much for responding. I have made the line change you suggested, and that combined with changing my query to:

    s.mock.ExpectQuery("SELECT * FROM \"fields\" WHERE (\"fields\".\"id\" = $1) ORDER BY \"fields\".\"id\" ASC LIMIT 1").
        WithArgs(id).
        WillReturnRows(sqlmock.NewRows(row).
            AddRow(id, partnerId, qualifiedName, description, dataType, createdAt, createdBy, updatedAt, updatedBy))

worked, thanks very much!