Gorm delete with clauses sqlmock test

Question

I have a Gorm delete with the returning result:

expirationDate := time.Now().UTC().Add(-(48 * time.hour))
var deletedUsers Users
res := gormDB.WithContext(ctx).
    Table("my_users").
    Clauses(clause.Returning{Columns: []clause.Column{{Name: "email"}}}).
    Where("created_at < ?", expirationDate).
    Delete(&deletedUsers)

Now the test with clauses always fails. e.g. :

sqlMock.ExpectExec(`DELETE`)
    .WithArgs(expirationDate)
    .WillReturnResult(sqlmock.NewResult(1, 1))

Receiving error:

"call to Query 'DELETE FROM "my_users" WHERE created_at < $1 RETURNING "email"' with args [{Name: Ordinal:1 Value:2023-01-18 06:15:34.694274 +0000 UTC}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:\n - matches sql: 'DELETE'\n - is with arguments:\n 0 - 2023-01-18 06:15:34.694274 +0000 UTC\n - should return Result having:\n LastInsertId: 1\n RowsAffected: 1"

I tried many other sqlMock expectations, but they have a similar issue. Also, we don't have a return value in ExpectExec, only in ExpectQuery... Any chance someone has to test the Gorm query with the Clauses?

Answer 1

I was able to successfully manage what you need. First, let me share the files I wrote, and then I'll walk you through all of the relevant changes. The files are repo.go for production and repo_test.go for the test code.

repo.go

package gormdelete

import (
    "context"
    "time"

    "gorm.io/gorm"
    "gorm.io/gorm/clause"
)

type Users struct {
    Email string
}

func Delete(ctx context.Context, gormDB *gorm.DB) error {
    expirationDate := time.Now().UTC().Add(-(48 * time.Hour))

    var deletedUsers Users
    res := gormDB.WithContext(ctx).Table("my_users").Clauses(clause.Returning{Columns: []clause.Column{{Name: "email"}}}).Where("created_at < ?", expirationDate).Delete(&deletedUsers)
    if res.Error != nil {
        return res.Error
    }
    return nil
}

As you didn't provide the full file I tried to guess what was missing.

repo_test.go

package gormdelete

import (
    "context"
    "database/sql/driver"
    "testing"
    "time"

    "github.com/DATA-DOG/go-sqlmock"
    "github.com/stretchr/testify/assert"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

// this is taken directly from the docs
// https://github.com/DATA-DOG/go-sqlmock#matching-arguments-like-timetime
type AnyTime struct{}

// Match satisfies sqlmock.Argument interface
func (a AnyTime) Match(v driver.Value) bool {
    _, ok := v.(time.Time)
    return ok
}

func TestDelete(t *testing.T) {
    db, mock, err := sqlmock.New()
    if err != nil {
        t.Fatalf("an error was not expected: %v", err)
    }

    conn, _ := db.Conn(context.Background())
    gormDb, err := gorm.Open(postgres.New(postgres.Config{
        Conn: conn,
    }))

    row := sqlmock.NewRows([]string{"email"}).AddRow("test@example.com")
    mock.ExpectBegin()
    mock.ExpectQuery("DELETE FROM \"my_users\" WHERE created_at < ?").WithArgs(AnyTime{}).WillReturnRows(row)
    mock.ExpectCommit()

    err = Delete(context.Background(), gormDb)

    assert.Nil(t, err)
    if err = mock.ExpectationsWereMet(); err != nil {
        t.Errorf("not all expectations were met: %v", err)
    }
}

Here, there are more changes that it's worth mentioning:

1. I instantiated the AnyTime as per the documentation (you can see the link in the comment).
2. Again, I guessed the setup of the db, mock, and gormDb but I think it should be more or less the same.
3. I switch the usage of ExpectExec to ExpectQuery as we'll have back a result set as specified by the Clauses method in your repo.go file.
4. You've to wrap the ExpectQuery within an ExpectBegin and an ExpectCommit.
5. Finally, pay attention to the difference in how the driver expects the parameters in the SQL statement. In the production code, you can choose to use ? or $1. However, in the test code, you can only use the ? otherwise it won't match the expectations.

Hope to help a little bit, otherwise, let me know!