Use go-sqlmock to test gorm issues, comparing queries with mock.ExpectQuery and regexp.QuoteMeta

During the development process, it is a common requirement to use go-sqlmock to test gorm problems. go-sqlmock is a tool for simulating database operations, while gorm is a popular Go language ORM library. During the testing process, we often need to compare whether the query statement meets expectations. To do this we can use mock.ExpectQuery and regexp.QuoteMeta for comparison. This method can help us better test and debug the code and ensure the correctness and stability of the program. Next, we will introduce in detail how to use go-sqlmock for gorm testing, and show how to use mock.ExpectQuery and regexp.QuoteMeta to compare query statements.

Question content

I have a problem comparing expected query with gorm's real query, here is my code:

package repository

import (
    "regexp"
    "testing"

    "github.com/data-dog/go-sqlmock"
    "your_go_root/pkg/domain"
    "github.com/stretchr/testify/assert"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)


var successgettransaction domain.transaction = domain.transaction{
    id:          2,
    buyerid:     2,
    sellerid:    5,
    itemid:      2,
    messageid:   2,
    expireddate: "2022-09-010 01:01:00",
    createdat:   "2022-09-08 01:01:00",
}

func testsuccessgettransactionbyid(t *testing.t) {

    db, mock, err := sqlmock.new()
    assert.noerror(t, err)
    gdb, err := gorm.open(mysql.new(mysql.config{
        conn:                      db,
        skipinitializewithversion: true,
    }), &gorm.config{})
    assert.noerror(t, err)

    rows := sqlmock.newrows([]string{"id", "buyer_id", "seller_id", "item_id", "message_id", "expired_date", "created_at"}).
        addrow(2, 2, 5, 2, 2, "2022-09-010 01:01:00", "2022-09-08 01:01:00")
    mock.expectquery(regexp.quotemeta("select * from transaction where id = ?;")).willreturnrows(rows)

    repo := defaultclient(gdb)
    actualsectionlist, _ := repo.gettransactionbyid(2)
    
    assert.equal(t, successgettransaction, actualsectionlist, "ambas listas deberian ser iguales")
    assert.noerror(t, mock.expectationsweremet())
}

This is the module domain:

package domain

type transaction struct {
    id          int64  `gorm:"primarykey;column:id"`
    buyerid     int64  `gorm:"column:buyer_id"`
    sellerid    int64  `gorm:"column:seller_id"`
    itemid      int    `gorm:"column:item_id"`
    messageid   int    `gorm:"column:message_id"`
    expireddate string `gorm:"column:expired_date"`
    createdat   string `gorm:"column:created_at"`
}

func (transaction) tablename() string {
    return "transaction"
}

type transactionstatus struct {
    id             int64  `gorm:"primarykey;column:id"`
    transactionid  int64  `gorm:"column:transaction_id"`
    status         int    `gorm:"column:status"`
    notificationid int    `gorm:"column:notification_id"`
    createdat      string `gorm:"column:created_at"`
}

func (transactionstatus) tablename() string {
    return "transaction_status"
}

This is the function I'm testing:

package repository

import (
    "fmt"

    "your_go_root/pkg/domain"
    "gorm.io/gorm"
)

type repositoryclient interface {
    gettransactionbyid(id int) (domain.transaction, error)
}

type repositoryclient struct {
    db *gorm.db
}

func defaultclient(db *gorm.db) repositoryclient {
    return &repositoryclient{
        db: db,
    }
}

func (rc repositoryclient) gettransactionbyid(id int) (domain.transaction, error) {
    trans := domain.transaction{}
    status := rc.db.where("id = ?", id).find(&trans)

    if status.error != nil {
        return domain.transaction{}, status.error
    }
    if trans == (domain.transaction{}) {
        return domain.transaction{}, fmt.errorf("error finding transaction id %v", id)
    }
    return trans, nil
}

This is the error I receive from the console:

Query: could not match actual sql: "SELECT * FROM `transaction` WHERE id = ?" with expected regexp "SELECT \* FROM transaction WHERE id = \?;"[0m[33m[0.218ms] [34;1m[rows:0][0m SELECT * FROM `transaction` WHERE id = 2

There exists an answer with "select(.*)" instead in this section, but from what I've read, that's not a real solution

Workaround

Let I try to help solve this problem. I downloaded all your files and domain.go and repository.go look good to me.
However, I found some small issues in the repository_test.go file:

1. The backtick is missing in the sql query you wrote
2. Extra ;
at the end of the query
3. Missing call to withargs(2) method

If you adjust these little things, you should end up with code that looks like this:

// ... omitted for brevity
func TestSuccessGetTransactionByID(t *testing.T) {
    db, mock, err := sqlmock.New()
    assert.NoError(t, err)
    gdb, err := gorm.Open(mysql.New(mysql.Config{
        Conn:                      db,
        SkipInitializeWithVersion: true,
    }), &gorm.Config{})
    assert.NoError(t, err)

    rows := sqlmock.NewRows([]string{"id", "buyer_id", "seller_id", "item_id", "message_id", "expired_date", "created_at"}).AddRow(2, 2, 5, 2, 2, "2022-09-010 01:01:00", "2022-09-08 01:01:00")
    mock.ExpectQuery(regexp.QuoteMeta("SELECT * FROM `transaction` WHERE id = ?")).WithArgs(2).WillReturnRows(rows)

    repo := DefaultClient(gdb)
    actualSectionList, _ := repo.GetTransactionByID(2)

    assert.Equal(t, successGetTransaction, actualSectionList, "ambas listas deberian ser iguales")
    assert.NoError(t, mock.ExpectationsWereMet())
}

Then if you try to run the test it should work.
Please let me know if this solves your problem, thanks!

The above is the detailed content of Use go-sqlmock to test gorm issues, comparing queries with mock.ExpectQuery and regexp.QuoteMeta. For more information, please follow other related articles on the PHP Chinese website!