Unnecessary queries executed by gorm to insert data in database tables

user_884101 0
Viewed 1495

I am trying my hands on golang and gorm for creating a new feature in my current project.

I have following database structure in MySQL.

enter image description here

UserMaster table is at the top of hierarchy and its primary key column is being referred by many other tables and they themselves use it as Primary Columns. UserLogin table is one of such tables. This is is not the perfect database design but this is the db structure I will be working on and there are no chance the structure can be changed.

Following are the model classes I have created for UserMaster and UserLogin in go.

type UserMaster struct {
    UserId      string    `gorm:"column:UserId;PRIMARY_KEY";size:20`
    CreatedDate time.Time `gorm:"column:CreatedDate"`
    IsActive    bool      `gorm:"column:IsActive"`
    UserLogin   UserLogin `gorm:"foreignkey:Id"`
}

type UserLogin struct {
    Id            string    `gorm:"column:Id;PRIMARY_KEY";size:20"`
    UserName      string    `gorm:"column:UserName;unique"`
    EmailId       string    `gorm:"column:EmailId;unique"`
    PasswordHash  string    `gorm:"column:PasswordHash"`
    PasswordSalt  string    `gorm:"column:PasswordSalt"`
    LastLoginDate time.Time `gorm:"column:LastLoginDate"`
}

func (UserMaster) TableName() string {
    return "usermaster"
}

func (UserLogin) TableName() string {
    return "userlogin"
}

What I wish is to create rows in both UserMaster and UserLogin tables via gorm just by saving fully populated instance of UserMaster.

And following is the code I wrote for this.

func main() {
    db, err := gorm.Open("mysql", "user:password@tcp(localhost:3306)/ormsample")

    if err != nil {
        panic("Failed to connect the database.")
    }

    defer db.Close()

    createNewUser(db, "someuser009@yopmail.com", "j;dfasdasdf")
}

func createNewUser(db *gorm.DB, emailId string, password string) UserMaster {
    userId := createUserId()

    newUser := &UserMaster{}
    newUser.UserId = userId
    newUser.CreatedDate = time.Now()
    newUser.UserLogin = UserLogin{}
    newUser.UserLogin.UserName = emailId
    newUser.UserLogin.EmailId = emailId
    newUser.UserLogin.PasswordHash = password
    newUser.UserLogin.PasswordSalt = "SomeSalt"

    db.Debug().Model(&newUser).Create(&newUser)

    return *newUser
}

func createUserId() string {
    uuidWithHyphen := uuid.New()
    uuid := strings.Replace(uuidWithHyphen.String(), "-", "", -1)

    return strings.ToUpper(uuid[:12])
}

This whole thing works as expected, that means it is inserting records to both UserMaster and UserLogin table.

The problem is the way the gorm is performing this database operation. Following are the SQL queries being logged the terminal (coz I am using Debug() function on gorm db object).

INSERT INTO `usermaster` (`UserId`,`CreatedDate`,`IsActive`) 
    VALUES ('1601F7E41E29','2020-03-22 22:24:08',false) 

UPDATE `userlogin` SET `UserName` = 'someuser009@yopmail.com', 
  `EmailId` = 'someuser009@yopmail.com', `PasswordHash` = 'j;dfasdasdf', 
  `PasswordSalt` = 'SomeSalt', `LastLoginDate` = '0000-00-00 00:00:00'  
  WHERE `userlogin`.`Id` = '1601F7E41E29'

SELECT * FROM `userlogin`  WHERE `userlogin`.`Id` = '1601F7E41E29' 
 ORDER BY `userlogin`.`Id` ASC LIMIT 1

INSERT INTO `userlogin` (`Id`,`UserName`,`EmailId`,`PasswordHash`,`PasswordSalt`,`LastLoginDate`)
VALUES ('1601F7E41E29','someuser009@yopmail.com','someuser009@yopmail.com',
 'j;dfasdasdf','SomeSalt','0000-00-00 00:00:00')

As you notice above gorm executes 2 INSERT, 1 UPDATE and 1 SELECT queries to insert into two tables while it should be only 2 INSERT queries executed.

I searched a lot about this on various forums of golang and gorm but did not find anything which can explain this behavior.

Any help/guidance/direction to solve this issue will be greatly appreciated.

go-gorm go
1 Answers

Gorm call this function before create

func beforeCreateCallback(scope *Scope) {
    if !scope.HasError() {
        scope.CallMethod("BeforeSave")
    }
    if !scope.HasError() {
        scope.CallMethod("BeforeCreate")
    }
}

Here if primary key already set then gorm try to update existing data using the given primary key. That's why extra query executed. To solve this issue, use UserId as foreignkey for UserLogin which is primary key of UserLogin model so that gorm try to save child first then parent. Don't set primary key, use BeforeCreate hook to set 

func (user *UserLogin) BeforeCreate(scope *gorm.Scope) error {
  scope.SetColumn("Id", ), createUserId())
  return nil
}

Models like:

type UserMaster struct {
    UserId      uint      `gorm:"column:UserId;PRIMARY_KEY;size:20"`
    CreatedDate time.Time `gorm:"column:CreatedDate"`
    IsActive    bool      `gorm:"column:IsActive"`
    UserLogin   UserLogin `gorm:"foreignkey:UserId"`
}

type UserLogin struct {
    Id            uint      `gorm:"column:Id;PRIMARY_KEY;size:20"`
    UserName      string    `gorm:"column:UserName;"`
    EmailId       string    `gorm:"column:EmailId;"`
    PasswordHash  string    `gorm:"column:PasswordHash"`
    PasswordSalt  string    `gorm:"column:PasswordSalt"`
    LastLoginDate time.Time `gorm:"column:LastLoginDate"`
}

Using both Model(&user) and Create(&user) not needed. Model() mostly used before update existing data which may cause extra query. For only create you can try

db.Debug().Create(&newUser)

Gorm auto select Model UserMaster from newUser

user_42073060
Related