Gorm Example with Preloading

[LucasMHI]

trafficstars

Request

Hello! I'm currently working on a project using GORM for database interaction and have been having difficulty finding any examples for go-sqlmock that involve using the Preload method used by GORM, which is used loading associated data, https://gorm.io/docs/preload.html

For example, given a database model like this: (keep in mind this has not been 100% tested)

type Bar struct {
	ID    int    `gorm:"->;primary_key;AUTO_INCREMENT;column:id;type:int;" json:"id"`
	Thing string `gorm:"column:thing;type:varchar;size:30;" json:"thing"`
}

type Foo struct {
	ID    int `gorm:"->;primary_key;AUTO_INCREMENT;column:id;type:int;" json:"id"`
	BarID int `gorm:"column:bar_id;type:int;" json:"bar_id"`
	Bar   Bar `gorm:"foreignKey:BarID;" json:"bar"`
}

type GetFooInput struct {
	ID int
}

func GetFoo(ctx context.Context, db *gorm.DB, input GetFooInput) (Foo, error) {
	var foo Foo
	tx := db.
		Where("id = ?", input.ID).
		Preload("Bar").
		Find(&foo)

	return foo, tx.Error
}

What is wrong with this test function:

func TestGetFoo(t *testing.T) {
	var db *sql.DB
	var err error
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Errorf("an error '%s' was not expected when opening a stub database connection", err)
	}
	defer db.Close()

	gormDB, err := gorm.Open(mysql.New(mysql.Config{
		Conn:                      db,
		SkipInitializeWithVersion: true,
	}), &gorm.Config{})

	barRows := sqlmock.NewRows([]string{"id", "thing"}).
		AddRow(2, "a name")

	fooRows := sqlmock.NewRows([]string{"id", "bar_id"}).
		AddRow(1, 2)

	mock.ExpectQuery(regexp.QuoteMeta("SELECT * FROM `bar` WHERE id = ?")).
		WithArgs(2).
		WillReturnRows(barRows)

	mock.ExpectQuery(regexp.QuoteMeta("SELECT * FROM `foo` WHERE id = ?")).
		WithArgs(1).
		WillReturnRows(fooRows)

	foo, err := GetFoo(context.Background(), gormDB, GetFooInput{ID: 1})

	assert.Nil(t, err)
	assert.NotNil(t, foo)
	assert.Equal(t, 2, foo.Bar.ID)
	assert.Equal(t, "a name", foo.Bar.Thing)

	if err := mock.ExpectationsWereMet(); err != nil {
		assert.FailNow(t, "there were unfulfilled expectations: %s", err)
	}
}

I have tried this with a similar situation with preloading and such, however the SELECT * FROM 'bar' query is never executed, presumably because I'm missing something in regards to GORM's Preloading statement.

Does anyone have suggestions?

Thanks!!

[Dipesh-kayastha]

@LucasMHI have you got any solution? as I am facing same.

[SimFG]

mock.ExpectQuery(regexp.QuoteMeta("SELECT * FROM `bar` WHERE id = ?")).
		WithArgs(2).
		WillReturnRows(barRows)

change to:

mock.ExpectQuery(regexp.QuoteMeta("SELECT * FROM `bar` WHERE `bar`.`id` = ?"")).
		WithArgs(2).
		WillReturnRows(barRows)

[eversarmiento656]

maybe a bit late, but for it to work your test should look like this.

	mock.ExpectQuery(regexp.QuoteMeta("SELECT * FROM `bar` WHERE id = ?")).
		WithArgs(2).
		WillReturnRows(barRows)

	mock.ExpectQuery(regexp.QuoteMeta("SELECT * FROM `foo` WHERE id = ?")).
		WithArgs(1).
		WillReturnRows(fooRows)

change to

	mock.ExpectQuery("SELECT(.*)").
		WithArgs(1).
		WillReturnRows(fooRows)

	mock.ExpectQuery("SELECT(.*)").
		WithArgs(2).
		WillReturnRows(barRows)

I hope it will help a next reader

[reneINuvei]

db. Where("id = ?", input.ID). Preload("Bar"). Find(&foo)

it does not work

[vector233]

这个问题有几个关键点：

1. ExpectQuery执行的sql要完全匹配。
2. 第一个查询的结果中需要包含用于preload查询的关联字段。
3. 当使用第一个查询的关联字段结果值进行preload查询时，需要能查询到结果。

总的来说就是sql语句完全正确，且mock的数据关联关系完全正确。