Testing with gorm and sqlmock: could not match query with expected regexp

I am working on a project using GORM and I am trying to write tests using SQLMock. Below is the code snippet defining my custom types and a database model using GORM:

// ActionType action type
type ActionType int

const (
    // ActionTypeFixedPayment fixed payment action type
    ActionTypeFixedPayment ActionType = 1
    // ActionTypePercentileCommissionExemption percentile commission exemption action type
    ActionTypePercentileCommissionExemption ActionType = 2
)

var ActionTypeValues = map[ActionType]string{
    ActionTypeFixedPayment:                  "FixedPayment",
    ActionTypePercentileCommissionExemption: "Commission",
}

func (a ActionType) String() string {
    return ActionTypeValues[a]
}

// IncentiveAction incentive action
type IncentiveAction struct {
    database.Model
    IncentiveDateID uint           `json:"incentive_date_id" gorm:"column:incentive_date_id;not null;index"`
    DriverID        uint           `json:"driver_id" gorm:"column:driver_id;not null;index"`
    StateID         uint           `json:"state_id" gorm:"column:state_id;not null;index"`
    Type            ActionType     `json:"type" gorm:"column:type;not null;index"`
    Value           string         `json:"value" gorm:"column:value;type:VARCHAR"`
    Meta            datatypes.JSON `json:"meta" gorm:"column:meta;type:jsonb"`
}

func (m *IncentiveAction) TableName() string {
    return "incentive_actions"
}

I have written a simple function to insert data into database using the following code:

type actionRepo struct {
    dbRead,
    dbWrite *gorm.DB
}

func (sr *actionRepo) Create(ctx context.Context, data models.IncentiveAction) (
    *models.IncentiveAction, error) {

    err := sr.dbWrite.WithContext(ctx).Create(&data).Error
    if err != nil {
        fmt.Errorf("%s", err.Error())
    }

    return &data, err
}

I want to write a test for this function, so I created a mock database using gorm and sqlmock:

// MockDB get fresh mocked db instance
func MockDB(t *testing.T) (*gorm.DB, sqlmock.Sqlmock, func() error) {
    db, mock, err := sqlmock.New()
    if err != nil {
        t.Fatalf("an error '%s' was not expected when opening a stub database connection", err)
    }

    pgDialect := postgres.New(postgres.Config{Conn: db})

    gdb, err := gorm.Open(pgDialect, &gorm.Config{
        SkipDefaultTransaction: true,
    })
    if err != nil {
        t.Fatalf("error when opening database connection: %s", err)
    }

    return gdb, mock, db.Close
}

and finally I'm writing my test to create a record in my database and later I want to check the results using the following code:

func TestActionRepo_Create(t *testing.T) {
    assertions := assert.New(t)
    db, dbMock, closeDb := MockDB(t)
    defer closeDb()

    repo := NewActionRepo(db, db)
    ctx := context.Background()

    actionToCreate := models.IncentiveAction{
        IncentiveDateID: 1,
        DriverID:        2,
        StateID:         3,
        Type:            models.ActionTypeFixedPayment,
        Value:           "5000",
        Meta:            nil,
    }

    actionRows := sqlmock.NewRows([]string{"id"})
    actionRows.AddRow(uint(1))

    actionQuery := `INSERT INTO "incentive_actions" ("created_at","updated_at","incentive_date_id","driver_id","state_id","type","value","meta") VALUES ($1,$2,$3,$4,$5,$6,$7,$8) RETURNING "id"`

    dbMock.ExpectQuery(regexp.QuoteMeta(actionQuery)).
        WithArgs(sqlmock.AnyArg(), sqlmock.AnyArg(), 1, 2, 3, models.ActionTypeFixedPayment, "5000", nil).
        WillReturnRows(actionRows)

    action, err := repo.Create(ctx, actionToCreate)

    assertions.NoError(err)
    assertions.Equal(uint(1), action.ID)
}

running this code results in error:

Query: could not match actual sql: "INSERT INTO "incentive_actions" ("created_at","updated_at","incentive_date_id","driver_id","state_id","type","value","meta") VALUES ($1,$2,$3,$4,$5,$6,$7,NULL) RETURNING "id"" with expected regexp "INSERT INTO "incentive_actions" \("created_at","updated_at","incentive_date_id","driver_id","state_id","type","value","meta"\) VALUES \(\$1,\$2,\$3,\$4,\$5,\$6,\$7,\$8\) RETURNING "id""
            Test:           TestActionRepo_Create

Error:          Not equal:
                            expected: 0x1
                            actual  : 0x0
            Test:           TestActionRepo_Create

I suspect the issue might be related to how GORM and SQLMock handle the meta field, but I'm not sure how to resolve it. I'm using GORM version 1.25.10 and SQLMock version 1.5.2. This test used to work in previous versions like GORM 1.21.

Could anyone provide guidance on how to fix this issue or any insights into what might be going wrong? Thank you!

Solution

You are correct that it's related to the "meta" field. What appears to be happening is that when GORM is building the expression, it encounters the nil value you are passing for “meta” and builds the SQL statement with “NULL” in place of the last viable ($8).

This is because the meta field is use the datatypes.JSON type. When GORM calls its internal AddVar() method, during reflection it calls the GormValue() method on the JSON datatype which returns “NULL” if the underlying json.RawMessage byte slice length is zero. To get the strings to match and pass the test, you can populate the meta field with some json test data:

metaAttr := datatypes.JSON([]byte(`{"name":"Jackie Daytona"}`))
actionToCreate := IncentiveAction{
    IncentiveDateID: 1,
    DriverID:        2,
    StateID:         3,
    Type:            ActionTypeFixedPayment,
    Value:           "5000",
    Meta:            metaAttr,
}

actionRows := sqlmock.NewRows([]string{"id"})
actionRows.AddRow(uint(1))

actionQuery := `INSERT INTO "incentive_actions" ("created_at","updated_at","incentive_date_id","driver_id","state_id","type","value","meta") VALUES ($1,$2,$3,$4,$5,$6,$7,$8) RETURNING "id"`
dbMock.ExpectQuery(regexp.QuoteMeta(actionQuery)).
    WithArgs(sqlmock.AnyArg(), sqlmock.AnyArg(), 1, 2, 3, ActionTypeFixedPayment, "5000", metaAttr).
    WillReturnRows(actionRows)