Gorm different column order and test failure

php editor Xinyi is here to introduce to you a problem about Gorm: when we use Gorm to perform database queries, different column orders may cause test failure. This is because when Gorm performs a query, it will generate SQL statements based on the order of the fields in the structure. If we specify a specific column order in the query, but the field order in the structure does not match it, the test will fail. Therefore, when using Gorm for database queries, we need to pay attention to the order of the fields in the structure being consistent with the order of the columns in the query to avoid this problem.

Question content

In my code, I have the following model:

type ID uint64

type BaseModel struct {
    ID         ID        `gorm:"column:id;primaryKey;autoIncrement" json:"id"`
    UpdateDate time.Time `gorm:"column:update_date;default:CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" json:"update_date"`
    CreateDate time.Time `gorm:"column:create_date;default:CURRENT_TIMESTAMP" json:"create_date"`
}

type Rollback struct {
    BaseModel
    PID   ID     `gorm:"index"`
    Table       string `gorm:"column:tbl_name"`
    RollbackRow string `gorm:"type:longtext"`
}

I am using the CreateInBatches method of the gorm.DB structure.

I use go-sqlmock for unit testing. In this model, only insert operations are performed.

func expectRollbackInsert(mock sqlmock.Sqlmock, tablename []string) {
    args := make([]driver.Value, 0)
    for _, val := range tablename {
        args = append(args, 1, val, sqlmock.AnyArg(), sqlmock.AnyArg(), sqlmock.AnyArg())
    }
    mock.ExpectExec(regexp.QuoteMeta("INSERT INTO `rollback` (`payment_id`,`tbl_name`,`rollback_row`,`update_date`,`create_date`) VALUES (?,?,?,?,?)")).
        WithArgs(args...).
        WillReturnResult(sqlmock.NewResult(int64(len(tablename)), int64(len(tablename))))
}

My test cases sometimes fail due to the different order of create_date and update_date.

One of the failures is

ExecQuery: could not match actual sql: "INSERT INTO `rollback` (`pid`,`tbl_name`,`rollback_row`,`create_date`,`update_date`) VALUES (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?)" with expected regexp "INSERT INTO `rollback` \(`pid`,`tbl_name`,`rollback_row`,`update_date`,`create_date`\) VALUES \(\?,\?,\?,\?,\?\)"

For my use case, the order of the columns in the insert does not matter. How can I handle this in unit tests to handle all scenarios?

Solution

After reading the sqlmock documentation here, I got the following solution:

func expectRollbackInsert(mock sqlmock.Sqlmock, tablename []string) {
    args := make([]driver.Value, 0)
    for _, val := range tablename {
        args = append(args, 1, val, sqlmock.AnyArg(), sqlmock.AnyArg(), sqlmock.AnyArg())
    }
    mock.ExpectExec(regexp.QuoteMeta("INSERT INTO `rollback`")).
        WithArgs(args...).
        WillReturnResult(sqlmock.NewResult(int64(len(tablename)), int64(len(tablename))))
}

I deleted the columns and values. In my case, I don't need to care about the create_date and update_date fields, so it works just fine.

The above is the detailed content of Gorm different column order and test failure. For more information, please follow other related articles on the PHP Chinese website!