sql - sqlmock:期望值不匹配(完全相同的查询)

Question

我无法获得与之相称的经历。我复制并粘贴了相同的查询，它甚至没有任何需要转义的字符。
我有以下代码:

 query := "SELECT id, user_id, name, description, created_at, updated_at FROM products"

 settings := config.GetSettings()

 if limit == 0 {
     limit = settings.MaxElementsPerPagination
 }

 stmt, err := db.Prepare(query)
 if err != nil {
     err = fmt.Errorf("failed to prepare the select products statement: %v", err)
     return
 }
 defer stmt.Close()

 rows, err := stmt.Query()
 if err != nil {
     if errors.Is(err, sql.ErrNoRows) {
         err = fmt.Errorf("failed to select products: %w", errs.ErrNotExistentObject)
         return
     }

     err = fmt.Errorf("failed to select products: %v", err)
     return
 }

 var product models.Product

 for rows.Next() {
     err = rows.Scan(
         &product.ID,
         &product.UserID,
         &product.Name,
         &product.Description,
         &product.CreatedAt,
         &product.UpdatedAt,
     )
     if err != nil {
         err = fmt.Errorf("failed to scan product: %v", err)
         return
     }

     products = append(products, product)
 }

这是我的测试代码:

    // Setup database
    db, mock, err := sqlmock.New()
    assert.Nil(t, err)

    database.New(db)
   
   // Without Regex
    mock.ExpectQuery("SELECT id, user_id, name, description, created_at, updated_at FROM products")
   // With Regex
    // mock.ExpectQuery("SELECT (.+) FROM `products`")

   // I try it with rows

   // rows := sqlmock.NewRows([]string{"id", "user_id", "name", "description", "created_at", "updated_at"})

   // rows.AddRow(1, 1, "TV Monitor", "Monitor for TV", time.Now(), time.Now())
   // rows.AddRow(2, 1, "Router", "Awesome router", time.Now(), nil)

   // Without Regex
    // mock.ExpectQuery("SELECT id, user_id, name, description, created_at, updated_at FROM products").
   //     WillReturnRows(rows)
   // With Regex
   // mock.ExpectQuery("SELECT (.+) FROM `products`").
   //     WillReturnRows(rows)

    // Setup server
    e := echo.New()
    req := httptest.NewRequest(http.MethodGet, "/", nil)
    req.Header.Set(echo.HeaderContentType, echo.MIMEApplicationJSON)
    rec := httptest.NewRecorder()
    c := e.NewContext(req, rec)

    if assert.NoError(t, handlers.GetProducts(c)) {
        assert.Equal(t, http.StatusOK, rec.Code)
        assert.Equal(t, "pong", rec.Body.String())
    }

    assert.Nil(t, mock.ExpectationsWereMet())

我已经寻找了各种教程，但是没有很多sqlmock示例。我从未使用过它，也不明白为什么这行不通。

Answer 1

为什么模拟SQL查询？最好编写一个真实的集成测试来检查真实数据库的查询。对于为业务逻辑而不是为数据库交互编写单元测试，模拟是一个不错的选择。