Testing with gorm and sqlmock

Question content

I am trying to write a test for my go lambda function using sqlmock and gorm.

This is the function I want to test:

func docleanup(con *gorm.db) {
    sixmonthsago := time.now().adddate(0, -6, 0).format("2006-02-01")
    con.where("date_to <= ?", sixmonthsago).delete(&availability{})
    con.where("date_to <= ?", sixmonthsago).delete(&reservation{})
}

This is my test:

func testdocleanup(m *testing.t) {
    var mock sqlmock.sqlmock
    var db *sql.db
    var err error

    db, mock, err = sqlmock.new()
    assert.nil(m, err)

    dialector := mysql.new(mysql.config{
        dsn:                       "sqlmock_db_0",
        drivername:                "mysql",
        conn:                      db,
        skipinitializewithversion: true,
    })

    conn, err := gorm.open(dialector, &gorm.config{})
    if err != nil {
        m.errorf("failed to open connection to db: %v", err)
    }

    if conn == nil {
        m.error("failed to open connection to db: conn is nil")
    }

    defer db.close()

    mock.expectquery(fmt.sprintf("delete from availability where date_to <= '%s'", time.now().adddate(0, -6, 0).format("2006-02-01")))
    mock.expectquery(fmt.sprintf("delete from reservations where date_to <= '%s'", time.now().adddate(0, -6, 0).format("2006-02-01")))

    docleanup(conn)

    err = mock.expectationsweremet()
    assert.nil(m, err)

}

I do not know what I did wrong. This is my first time using sqlmock. I've read some places and my code looks good but I'm not getting the results. My error is:

Expected nil, but got: &errors.errorString{s:"there is a remaining expectation which was not matched: ExpectedQuery => expecting Query, QueryContext or QueryRow which:\n  - matches sql: 'DELETE FROM availability WHERE date_to <= '2022-13-06''\n  - is without arguments"}

Any idea what I did wrong?

Correct Answer

The main problem I see is with the way you expect the query. instead of

    mock.expectquery(fmt.sprintf("delete from availability where date_to <= '%s'", time.now().adddate(0, -6, 0).format("2006-02-01")))

You should have:

    mock.expectbegin()
    mock.expectexec("delete from `availability` where date_to <= ?").
        withargs(time.now().adddate(0, -6, 0).format("2006-02-01")).
        willreturnresult(sqlmock.newresult(0, 0))
    mock.expectcommit()

This will tell the simulation that you are using transactions (expectbegin and expectcommit around deletes), that the query is being made with arguments (withargs), and that the query is What is the return result (willreturnresult)

There are some other subtle changes, such as `` around the table name (mysql idiom) and table names (gorm will usually pluralize the name, so you either availability implement tablename, or default to availability).

The best way to see all of these issues is to change docleanup to return errors and then see them in your tests:

func docleanup(con *gorm.db) error {
    sixmonthsago := time.now().adddate(0, -6, 0).format("2006-02-01")
    tx := con.where("date_to <= ?", sixmonthsago).delete(&availability{})
    if tx.error != nil {
        return tx.error
    }
    tx = con.where("date_to <= ?", sixmonthsago).delete(&reservation{})
    if tx.error != nil {
        return tx.error
    }
    return nil
}
...
    err = docleanup(conn)
    assert.nil(m, err)
...

Do this, and using the current code, you will get

Expected nil, but got: &errors.errorString{s:"call to database transaction Begin, was not expected, next expectation is: ExpectedQuery => expecting Query, QueryContext or QueryRow which:\n  - matches sql: 'DELETE FROM availability WHERE date_to <= '2022-13-06''\n  - is without arguments"}

This tells you that sqlmock is not expecting begin, and by fixing that you will get the other errors addressed in the first part of this answer.

The above is the detailed content of Testing with gorm and sqlmock. For more information, please follow other related articles on the PHP Chinese website!