Sqlx: Mocking sqlx pkg for testing

[sim4life]

Is there a straightforward way to mock sqlx package for unit testing?
I'm thinking of creating a mock wrapper of sqlx functions and call Data-Dog sqlmock pkg from those functions.
Is there a better approach?

[jmoiron]

It should be trivial to mock sqlx.DB; in fact, internally, a lot of functionality which is shared between Stmt, Tx and DB use interfaces to reduce code duplication.

I'm not sure how how you'd mock other sql functions; presumably you'd have to resort to DI.

[sim4life]

Yes, it was pretty straightforward.

mockDB, mock, err := sqlmock.New()
defer mockDB.Close()
sqlxDB = sqlx.NewDb(mockDB,"sqlmock")

Later on I used the sqlmock function:

mock.ExpectExec("INSERT INTO baskets").WillReturnResult(sqlmock.NewResult(newID, 1))

for the sqlx query:

sqlxDB.Exec("INSERT INTO baskets (user_id, name, created_at, updated_at) VALUES (?, ?, ?, ?)", basket.UserID, basket.Name, timeNow, timeNow)

and I used the sqlmock function:

rows := sqlmock.NewRows([]string{"id", "user_id", "name", "created_at", "updated_at"}).
            AddRow(1, userID, name, timeNow, timeNow)
mock.ExpectPrepare("^SELECT (.+) FROM baskets WHERE").ExpectQuery().WithArgs(userID).WillReturnRows(rows)

for the sqlx query:

sqlxDB.PrepareNamed("SELECT id, user_id , name, created_at, updated_at FROM baskets WHERE user_id = :user_id")

[gorilla001]

how to prepare insert query?

[djui]

@sim4life Is sqlmock DataDog's https://github.com/DATA-DOG/go-sqlmock ?

[nachogoca]

How could I mock Queryx without arguments?

I am getting error from sqlmock all expectations were already fulfilled, call to Query 'SELECT ...' with args [] was not expected" because Queryx adds args to Query call even when no args are give.

[xyzzzyx]

Hi @sim4life @nachogoca Were you guys able to mock with Queryx?
I have the following that I am trying to mock a basic query with where clause:

stmt, err := c.DB.Preparex(query)
    if err != nil {
        log.Fatalln(err)
    }
    defer stmt.Close()

    rows, err := stmt.Queryx(ID)
    if err != nil {
        log.Fatalln(err)
    }
    defer rows.Close()

but keep on getting query mismatch. Do you have an example for above case? Also I do not see any issues with SELECT (+.) FROM Table.

[sim4life]

@sim4life Is sqlmock DataDog's https://github.com/DATA-DOG/go-sqlmock ?

Yes

[sim4life]

Hi @sim4life @nachogoca Were you guys able to mock with Queryx?
I have the following that I am trying to mock a basic query with where clause:

stmt, err := c.DB.Preparex(query)
  if err != nil {
      log.Fatalln(err)
  }
  defer stmt.Close()

  rows, err := stmt.Queryx(ID)
  if err != nil {
      log.Fatalln(err)
  }
  defer rows.Close()

but keep on getting query mismatch. Do you have an example for above case? Also I do not see any issues with SELECT (+.) FROM Table.

Yes, I managed to mock SQLMock .. but I'm NOT sure about QueryX.
Can you share your code and error on gitpod.io or something?