A GORM Cheat Sheet for Everyday Queries and Associations

package main

import (
	"context"
	"errors"
	"fmt"
	"gorm.io/gorm/clause"
	"log"
	"time"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

func must(err error) {
	if err != nil {
		log.SetFlags(0)
		log.Fatal(err)
	}
}

// connect recreates a schema and returns a connection whose search_path points at it.
func connect(schema string, cfg *gorm.Config) *gorm.DB {
	base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	must(err)
	must(admin.Exec("drop schema if exists " + schema + " cascade").Error)
	must(admin.Exec("create schema " + schema).Error)
	if cfg == nil {
		cfg = &gorm.Config{Logger: logger.Discard}
	}
	db, err := gorm.Open(postgres.Open(base+" search_path="+schema), cfg)
	must(err)
	return db
}

type Team struct {
	ID      uint
	Name    string `gorm:"uniqueIndex;size:80"`
	Members []Member
}

type Member struct {
	ID        uint
	TeamID    uint
	Team      Team
	Email     string `gorm:"uniqueIndex"`
	Age       int
	Skills    []Skill `gorm:"many2many:member_skills"`
	CreatedAt time.Time
	DeletedAt gorm.DeletedAt `gorm:"index"`
}

type Skill struct {
	ID   uint
	Name string `gorm:"uniqueIndex"`
}

func main() {
	db := connect("l0735", &gorm.Config{Logger: logger.Discard, TranslateError: true})
	ctx := context.Background()
	must(db.AutoMigrate(&Team{}, &Member{}, &Skill{}))

	// create
	team := Team{Name: "core"}
	db.WithContext(ctx).Create(&team)
	db.Create(&[]Member{{TeamID: team.ID, Email: "[email protected]", Age: 30}, {TeamID: team.ID, Email: "[email protected]", Age: 41}, {TeamID: team.ID, Email: "[email protected]", Age: 25}})
	fmt.Println("create: team id", team.ID)

	// read
	var m Member
	db.First(&m, "email = ?", "[email protected]")
	var adults []Member
	db.Where("age >= ?", 30).Order("age desc").Limit(5).Find(&adults)
	var n int64
	db.Model(&Member{}).Where("age < ?", 40).Count(&n)
	var emails []string
	db.Model(&Member{}).Order("email").Pluck("email", &emails)
	fmt.Println("read:", m.Email, len(adults), n, emails)

	// structs: zero values are skipped, maps are not
	db.Where(&Member{Age: 0, Email: "[email protected]"}).Find(&adults)
	fmt.Println("struct condition ignores Age 0:", len(adults))
	db.Where(map[string]any{"age": 0}).Find(&adults)
	fmt.Println("map condition keeps age 0:", len(adults))

	// update
	db.Model(&m).Update("age", 42)
	db.Model(&m).Updates(Member{Age: 0, Email: "[email protected]"})
	db.Model(&m).Updates(map[string]any{"age": 0})
	db.First(&m, m.ID)
	fmt.Println("update: struct skipped Age 0, map wrote it:", m.Email, m.Age)
	res := db.Model(&Member{}).Where("age > ?", 0).Update("age", gorm.Expr("age + ?", 1))
	fmt.Println("expr update rows:", res.RowsAffected)

	// associations
	go1, rust := Skill{Name: "go"}, Skill{Name: "rust"}
	db.Create(&[]*Skill{&go1, &rust})
	db.Model(&m).Association("Skills").Append(&go1, &rust)
	var full Team
	db.Preload("Members.Skills").First(&full, team.ID)
	for _, mm := range full.Members {
		if len(mm.Skills) > 0 {
			fmt.Println("preload nested:", mm.Email, len(mm.Skills), "skills")
		}
	}
	var withTeam []Member
	db.Joins("Team").Where("members.age > ?", 20).Find(&withTeam)
	fmt.Println("joins:", len(withTeam), withTeam[0].Team.Name)

	// upsert and ignore
	r := db.Clauses(clause.OnConflict{Columns: []clause.Column{{Name: "name"}}, DoUpdates: clause.AssignmentColumns([]string{"name"})}).Create(&Team{Name: "core"})
	fmt.Println("upsert rows:", r.RowsAffected, r.Error)
	r = db.Clauses(clause.OnConflict{DoNothing: true}).Create(&Team{Name: "core"})
	fmt.Println("do nothing rows:", r.RowsAffected)

	// transaction
	err := db.Transaction(func(tx *gorm.DB) error {
		tx.Create(&Skill{Name: "sql"})
		return errors.New("abort")
	})
	var skills int64
	db.Model(&Skill{}).Count(&skills)
	fmt.Println("transaction:", err, "skills:", skills)

	// soft delete
	db.Delete(&Member{}, m.ID)
	var visible, all int64
	db.Model(&Member{}).Count(&visible)
	db.Unscoped().Model(&Member{}).Count(&all)
	fmt.Println("soft delete:", visible, all)
	err = db.Unscoped().Delete(&Member{}, m.ID).Error
	fmt.Println("hard delete with join rows:", errors.Is(err, gorm.ErrForeignKeyViolated))
	db.Unscoped().Model(&m).Association("Skills").Clear()
	db.Unscoped().Delete(&Member{}, m.ID)
	db.Unscoped().Model(&Member{}).Count(&all)
	fmt.Println("hard delete after clearing the links:", all)

	// errors
	fmt.Println("not found:", errors.Is(db.First(&Member{}, 9999).Error, gorm.ErrRecordNotFound))
	fmt.Println("duplicate:", errors.Is(db.Create(&Team{Name: "core", ID: 0}).Error, gorm.ErrDuplicatedKey))

	// raw
	var out []struct {
		Team  string
		Count int
	}
	db.Raw("select t.name as team, count(m.id) as count from teams t left join members m on m.team_id = t.id and m.deleted_at is null group by t.name").Scan(&out)
	fmt.Println("raw:", out)

	// dry run
	sql := db.Session(&gorm.Session{DryRun: true}).Where("age > ?", 5).Limit(3).Find(&[]Member{}).Statement.SQL.String()
	fmt.Println("dry run:", sql)
}

create: team id 1
read: [email protected] 2 2 [[email protected] [email protected] [email protected]]
struct condition ignores Age 0: 1
map condition keeps age 0: 0
update: struct skipped Age 0, map wrote it: [email protected] 0
expr update rows: 2
preload nested: [email protected] 2 skills
joins: 2 core
upsert rows: 1 <nil>
do nothing rows: 0
transaction: abort skills: 2
soft delete: 2 3
hard delete with join rows: true
hard delete after clearing the links: 2
not found: true
duplicate: true
raw: [{core 2}]
dry run: SELECT * FROM "members" WHERE age > $1 AND "members"."deleted_at" IS NULL LIMIT $2