Skip to main content

GORM CheatSheet

Import

import (
  "gorm.io/driver/mysql"
  "gorm.io/driver/postgres"
  "gorm.io/gorm"
)

func main() {
  // MYSQL:
  // refer https://github.com/go-sql-driver/mysql#dsn-data-source-name for details
  dsn := "user:pass@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
  db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
    CreateBatchSize: 1000,
  })

  // PostgreSQL:
  dsn := "host=localhost user=gorm password=gorm dbname=gorm port=9920 sslmode=disable TimeZone=Asia/Shanghai"
  db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})

  // SQLite:
  db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{})

  // SQL Server:
  dsn := "sqlserver://gorm:LoremIpsum86@localhost:9930?database=gorm"
  db, err := gorm.Open(sqlserver.Open(dsn), &gorm.Config{})

  // Connection Pool:
  sqlDB, err := db.DB()
  // SetMaxIdleConns sets the maximum number of connections in the idle connection pool.
  sqlDB.SetMaxIdleConns(10)
  // SetMaxOpenConns sets the maximum number of open connections to the database.
  sqlDB.SetMaxOpenConns(100)  
  // SetConnMaxLifetime sets the maximum amount of time a connection may be reused.
  sqlDB.SetConnMaxLifetime(time.Hour)

}
  • To handle time.Time correcxtly, include the parseTime as a parameter

CRUDCreate

// Create Single:
user := User{Name: "John", Age: 18, Birthday: time.Now()}

result := db.Create(&user) // pass pointer of data to Create
user.ID                    // returns inserted data's primary key
result.Error               // returns error
result.RowsAffected        // returns inserted records count

// Create Multiple:
users := []*User{
  {Name: "Jinzhu", Age: 18, Birthday: time.Now()},
  {Name: "Jackson", Age: 19, Birthday: time.Now()},
}

result := db.Create(users) // pass a slice to insert multiple row
result.Error               // returns error
result.RowsAffected        // returns inserted records count

for _, user := range users {
  user.ID // 1,2,3
}

// Create with selected fields:
db.Select("Name", "Age", "CreatedAt").Create(&user)
// INSERT INTO `users` (`name`,`age`,`created_at`) VALUES ("jinzhu", 18, "2020-07-04 11:05:21.775")

// Create but ignore some fields:
db.Omit("Name", "Age", "CreatedAt").Create(&user)
// INSERT INTO `users` (`birthday`,`updated_at`) VALUES ("2020-01-01 00:00:00.000", "2020-07-04 11:05:21.775")

// Batch Insert

var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
// This will insert individually:
db.Create(&users)
// This will insert in batches:
db.CreateInBatches(users, 100)

// Batch Insert with nested models
// Note: 5000 users with 1000 per batch = 5 batches for users
//       3 pets per users => 3000 pets per batch
db := db.Session(&gorm.Session{CreateBatchSize: 1000})
users = [5000]User{{Name: "jinzhu", Pets: []Pet{pet1, pet2, pet3}}...}
db.Create(&users)
// INSERT INTO users xxx (5 batches)
// INSERT INTO pets xxx (15 batches)

// Create from Map
db.Model(&User{}).Create(map[string]interface{}{
  "Name": "jinzhu", "Age": 18,
})

// batch insert from `[]map[string]interface{}{}`
db.Model(&User{}).Create([]map[string]interface{}{
  {"Name": "jinzhu_1", "Age": 18},
  {"Name": "jinzhu_2", "Age": 20},
})

Hooks

// Create Hook:
func (u *User) BeforeCreate(tx *gorm.DB) (err error) {
  u.UUID = uuid.New()

  if u.Role == "admin" {
    return errors.New("invalid role")
  }
  return
}

// To skip hook:
DB.Session(&gorm.Session{SkipHooks: true}).Create(&user)
Back to top