Gorm Example with Preloading

[LucasMHI]

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的数据关联关系完全正确。

[diegommm]

Hi @LucasMHI! Please, consider that GORM is a separate library that is unrelated to sqlmock, and it has a completely different approach. GORM can help you do fast prototyping and generate a lot of SQL code for you, but you lose some control on what SQL code is being run and how. This means that GORM adds an abstraction layer that you have to account for. Consider the following:

• GORM is a higher level tool that already has tests that covers the generated SQL.
• If you want to use the GORM library, consider mocking it and instead test that a certain GORM-specific operation is carried out.
• If you test your SQL code when using GORM, then changes to the GORM library may affect the SQL code it generates, hence invalidating many or all of your tests.
• If you really care to test for the SQL code you run (and you may not for small projects, and that's fine), consider switching to database/sql instead and building the SQL code so that the code you run is predictable and deterministic.
• Otherwise, you will be trying to guess what GORM generated for you in that abstraction layer, and you may have to resort to reading the code in GORM to understand that and fix your tests very often.

Thank you!