Sql mismatch

[ivorytower1026]

Sql Mismatch

Here is my output error

ExecQuery: could not match actual sql: "INSERT INTO `arg_configs` (`created_at`,`updated_at`,`arg_name`,`arg_key`,`arg_value`,`arg_comment`) VALUES (?,?,?,?,?,?)" with expected regexp "INSERT INTO `arg_configs` (`created_at`,`updated_at`,`arg_name`,`arg_key`,`arg_value`,`arg_comment`)"; call to Rollback transaction, was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:
  - matches sql: 'INSERT INTO `arg_configs` (`created_at`,`updated_at`,`arg_name`,`arg_key`,`arg_value`,`arg_comment`)'
  - is with arguments:
    0 - {}
    1 - {}
    2 - Test Arg
    3 - test_key
    4 - test_value
    5 - Test comment
  - should return Result having:
      LastInsertId: 1
      RowsAffected: 1

Method under test

package args_configs

import (
	"medical_platform/biz/dal/mysql"
	"medical_platform/biz/model"
)

type CreateArgParams struct {
	ArgName    string `json:"arg_name,required"`
	ArgKey     string `json:"arg_key,required"`
	ArgValue   string `json:"arg_value,required"`
	ArgComment string `json:"arg_comment"`
}

type CreateArgResponse struct{}

func (p *CreateArgResponse) CreateArg(params *CreateArgParams) (code, msg string) {
	var argConfig, argForCheck model.ArgConfig
	mysql.DB.Debug().Model(model.ArgConfig{}).Where("arg_key = ?", params.ArgKey).First(&argForCheck)
	if argForCheck.ID != 0 {
		return "500", "The key is repeated!"
	}

	argConfig.ArgName = params.ArgName
	argConfig.ArgKey = params.ArgKey
	argConfig.ArgValue = params.ArgValue
	argConfig.ArgComment = params.ArgComment

	if err := mysql.DB.Debug().Model(&argConfig).Create(&argConfig).Error; err != nil {
		return "500", "create failed"
	}

	return "200", "ok"
}

The code that caused the error

if err := mysql.DB.Debug().Model(&argConfig).Create(&argConfig).Error; err != nil {
		return "500", "create failed"
	}

Test file

package args_configs

import (
	"github.com/DATA-DOG/go-sqlmock"
	"github.com/stretchr/testify/assert"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	m "medical_platform/biz/dal/mysql"
	"testing"
)

func TestCreateArg(t *testing.T) {
	// Create a new SQL mock
	sqlDB, mock, err := sqlmock.New()
	assert.NoError(t, err)
	defer sqlDB.Close()

	// Create a new GORM DB instance with the mock
	dialector := mysql.New(mysql.Config{
		Conn:                      sqlDB,
		SkipInitializeWithVersion: true,
	})
	db, err := gorm.Open(dialector, &gorm.Config{})
	assert.NoError(t, err)

	// Replace the global DB with our mocked DB
	m.DB = db
	argService := &CreateArgResponse{}
	//fixedTime := time.Now()
	// normal case
	t.Run("Normal case", func(t *testing.T) {
		mock.ExpectQuery("SELECT \\* FROM `arg_configs` WHERE arg_key = \\? ORDER BY `arg_configs`.`id` LIMIT \\?").
			WithArgs("test_key", 1).
			WillReturnRows(sqlmock.NewRows([]string{"id", "arg_name", "arg_key", "arg_value", "arg_comment"}))

		mock.ExpectBegin()
		mock.ExpectExec("INSERT INTO `arg_configs` (`created_at`,`updated_at`,`arg_name`,`arg_key`,`arg_value`,`arg_comment`)").
			WithArgs(sqlmock.AnyArg(), sqlmock.AnyArg(), "Test Arg", "test_key", "test_value", "Test comment").
			WillReturnResult(sqlmock.NewResult(1, 1))
		mock.ExpectCommit()

		params := &CreateArgParams{
			ArgName:    "Test Arg",
			ArgKey:     "test_key",
			ArgValue:   "test_value",
			ArgComment: "Test comment",
		}

		code, msg := argService.CreateArg(params)

		assert.Equal(t, "200", code)
		assert.Equal(t, "ok", msg)
	})

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("error have: %s", err)
	}
}

ArgConfig model

type ArgConfig struct {
	BaseModel
	ArgName    string `gorm:"comment:argument name"`
	ArgKey     string `gorm:"comment:argumeng key"`
	ArgValue   string `gorm:"comment:argument value"`
	ArgComment string `gorm:"comment:comment"`
}

BaseModel

type BaseModel struct {
	ID        uint `gorm:"primaryKey"`
	CreatedAt time.Time
	UpdatedAt time.Time
}

[ivorytower1026]

I used sqlmock.AnyArg() to map creation time and modification time, but still got sql mismatch error

[filikos]

Is your query escaped correctly?
Current:

q := "SELECT \\* FROM `arg_configs` WHERE arg_key = \\? ORDER BY `arg_configs`.`id` LIMIT \\?"

Want:

q:= `SELECT \* FROM "arg_configs" WHERE arg_key = \? ORDER BY 'arg_configs'\.'id' LIMIT \?`

[pdarulewski]

@ivorytower1026 I think the problem might be caused by gorm's ID field and it's related to this issue: go-gorm/gorm#7042 I can see you're using mysql though, so I'm not sure if it's actually related.

Try adding a row and using ExpectQuery with WillReturnRows like this:

dbMock.ExpectBegin()

addRow := sqlmock.NewRows([]string{"id"}).AddRow("1")
dbMock.ExpectQuery(`INSERT INTO "arg_configs" (.+) VALUES (.+) RETURNING "id"`).WillReturnRows(addRow)

dbMock.ExpectCommit()