GORM: A Simple Guide on CRUD

GORM is a ORM in Golang for handling relational databases. This is a brief walkthrough of all necessary commands and functions you might wanna use.

Connection

Like every other Go pacakge:

import (
  "github.com/jinzhu/gorm"
  // import "github.com/jinzhu/gorm/dialects/mysql"
  // import _ "github.com/jinzhu/gorm/dialects/postgres"
  // import _ "github.com/jinzhu/gorm/dialects/sqlite"
  // import _ "github.com/jinzhu/gorm/dialects/mssql"
)

Select the sql database you use and import that package. And connect the database:

MySQL

db, err := gorm.Open("mysql", "user:password@/dbname?charset=utf8&parseTime=True&loc=Local")

Postgresql

db, err := gorm.Open("postgres", "user=gorm dbname=gorm sslmode=disable")

You have to manually create the database before you connect.

And remember to close the database once it is no longer in use, in order to save some memory:

defer db.Close()

Models & Tables

Define Models before creating the tables.

type User struct {
  ID       int
  Username string
}

func main() {
  // After db connection is created.
  db.CreateTable(&User{})

  // Also some useful functions
  db.HasTable(&User{}) // => true
  db.DropTable(&User{})
}

And this is how a Model can be customized:

type User struct {
  // auto-populate columns: id, created_at, updated_at, deleted_at
  gorm.Model
  // Or alternatively write:
  Model gorm.Model `gorm:"embedded"`

  // If you don't want to include that many columns, simply use:
  ID uint
  // Which gorm will still set it as primary_key

  // Set column type manually
  Username string `sql:"type:VARCHAR(255)"`

  // Set default value
  LastName string `sql:"DEFAULT:'Smith'"`

  // Ignored attribute will be treated as attr instead of column
  IgnoredField bool `sql:"-"`

  // Custom primary ket
  UserID int `gorm:"primary_key"`

  // Custom column name instead of default snake_case format
  FirstName string `gorm:"column:FirstName"`

  // AUTO_INCREMENT can only be set on key field
  Count     int `gorm:"AUTO_INCREMENT"`

  // Not Null & Unique field
  Username string `sql:"not null;unique"`
}

func (u *User) TableName() string {
  // custom table name, this is default
  return "users"
}

func (u *User) BeforeSave() (err error) {
  if u.Role != "admin" {
    err = errors.New("Permission denied.")
  }
  return
}

Query

The most basic ways of querying:

firstUser := User{}
db.First(&firstUser)

lastUser := User{}
db.Last(&lastUser)

allUsers := []User{}
db.Find(&allUsers)

Note that you cannot do db.First(&User{}) in one line because the retrieved data is bound to the variable instead of the returned value.

And with conditions:

// The old-fashioned way of querying id = 100
u := User{}
db.Find(&u, 100)

// If not found, the values will be default values,
// which means ID will be 0
if u.Model.ID == 0 {
  // Do something if record not found 
}

// Bind data to variable u with conditions
u := User{FirstName: "Foo"}
db.Where(&u).First(&u)
// OR
db.Find(&u)

// Query multiple records
users := []User{}
db.Where(&User{Salary: 200}).Find(&users)

Create

user := User{
  FirstName: "Arthur",
  LastName:  "Dent",
  Username:  "adent",
  Salary:    5000,
}

db.Create(&user)

// Seeding tables:
var users []User = []User{
  User{Username: "foobar", FirstName: "Foo", LastName: "Bar", Salary: 200},
  User{Username: "helloworld", FirstName: "Hello", LastName: "World", Salary: 200},
  User{Username: "john", FirstName: "John", Salary: 200},
}

for _, user := range users {
  db.Create(&user)
}

Update

user := User{FirstName: "Johnny", LastName: "Depp"}

// Select, edit, and save
db.Find(&user)
user.LastName = "Beeblebrox"
db.Save(&user)

// Update with column names, not attribute names
db.Model(&user).Update("first_name", "zipp")
db.Model(&user).Updates(
  map[string]interface{}{
    "first_name": "Zap",
    "last_name":  "Bee",
  })
// UpdateColumn will not trigger callback
db.Model(&user).UpdateColumn("first_name", "zipp")
db.Model(&user).UpdateColumns(
  map[string]interface{}{
    "first_name": "Zap",
    "last_name":  "Smith",
  })
// Or in this case Find() will do as well
db.Find(&user).Update("first_name", "zipp")

// Batch Update
db.Table("users").Where("last_name = ?", "Smith")
  .Update("first_name", "Penny")

// Update based on values in the columns
db.Table("users").Where("salary > ?", 3000).
  Update("salary", gorm.Expr("salary + 500"))

Delete

// Select several records and delete it
db.Table("users").Where("salary > ?", 3000).Delete(&User{})

// Select all records from a model and delete all
db.Model(&User{}).Delete(&User{})

// Generally gorm simply updates the deleted_at field instead of deleting the record.
// Delete record using ordinary deletion.
db.Unscoped().Delete(&user)

Transaction

You can use transactions to improve performance:

tx := db.Begin()
err := tx.Create(&user).Error
if err != nil {
  tx.Rollback()
}
tx.Commit()

References

Source

Official documentation

GoDoc