Testing with gorm and sqlmock

I am struggling with writing a test for my go lambda function with 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{})
}

And 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 don't know what I am doing wrong. This is the first time I'm using sqlmock. I've read up a few places, and my code looks fine, 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 ideas what I'm doing wrong?

the main problem I see is around the way you are expecting the Query to be. 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 mocking that you are using a transaction (ExpectBegin and ExpectCommit around the delete, that the query was made with an argument(WithArgs), and what the return of the query will be (WillReturnResult)

There are some other minor changes, like the `` around the table name (a MySQL idiom) and the name of the table (gorm will typically pluralize names so either you implemented TableName on Availability or it will default to availabilities).

The best way for you to see all these problems would be to change the DoCleanup to return errors and then look at them on the test:

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)
...

Doing this, and with the current code you would 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 was not expecting a Begin, after solving that you will get the other errors that are resolved in the first part of this answer.