Gorm Database ORM Library

Official Documentation: GORM - The fantastic ORM library for Golang, aims to be developer friendly.

Repository: go-gorm/gorm: The fantastic ORM library for Golang, aims to be developer friendly (github.com)

In the Go community, for database interaction, there are two camps. One camp prefers simpler libraries like sqlx, which are not as powerful but allow you to have full control over SQL and optimize performance to the extreme. The other camp prefers ORM for development efficiency, which can save a lot of unnecessary trouble during development. When it comes to ORM, gorm is absolutely unavoidable in the Go language community. It's a very well-established ORM, similar to relatively younger ones like xorm, ent, etc. This article is about gorm. This article only explains its basic introductory content, just as a starting point. To learn more details, you can read the official documentation. Its Chinese documentation is quite complete, and the author is also one of the translators of the gorm documentation.

Features

Full-featured ORM
Associations (Has One, Has Many, Belongs To, Many To Many, Polymorphism, Single-table inheritance)
Hooks (Before/After Create/Save/Update/Delete/Find)
Preloading with Eager Loading (Preload, Joins)
Transactions, Nested Transactions, Save Points, Rollback To Saved Points
Context, Prepared Statement Mode, DryRun Mode
Batch Insert, FindInBatches, Find/Create with Map, CRUD with SQL Expressions, Context Valuer
SQL Builder, Upsert, Lock, Optimizer/Index/Comment Hint, Named Parameters, SubQuery
Composite Primary Key, Index, Constraint
Auto Migration
Custom Logger
Flexible Extensible Plugin API: Database Resolver (Multiple Databases, Read/Write Splitting), Prometheus...
Every feature is rigorously tested
Developer Friendly

Gorm does have some shortcomings. For example, almost all method parameters are empty interface types. Without looking at the documentation, you probably won't know what to pass. Sometimes you can pass a struct, sometimes a string, sometimes a map, sometimes a slice. The semantics are somewhat vague, and in many cases you still need to write SQL by hand.

As alternatives, there are two ORMs you can try. The first is aorm, which was open-sourced not long ago. It no longer requires you to write table field names by hand, mostly uses chain operations, and is based on reflection. Since the number of stars is not large, you can wait and see. The second is ent, open-sourced by facebook. It also supports chain operations, and in most cases you don't need to write SQL by hand. Its design philosophy is based on graphs (the one from data structures), and its implementation is based on code generation rather than reflection (which I agree with more). However, the documentation is all in English, which has a certain learning threshold.

Installation

Install the gorm library

$ go get -u gorm.io/gorm

Connection

Gorm currently supports the following databases

MySQL: "gorm.io/driver/mysql"
PostgreSQL: "gorm.io/driver/postgres"
SQLite: "gorm.io/driver/sqlite"
SQL Server: "gorm.io/driver/sqlserver"
TIDB: "gorm.io/driver/mysql", TIDB is compatible with mysql protocol
ClickHouse: "gorm.io/driver/clickhouse"

In addition, there are some other database drivers provided by third-party developers, such as oracle driver CengSin/oracle. This article will use MySQL for demonstration. Whatever database you use, you need to install the corresponding driver. Here we install MySQL's gorm driver.

$ go get -u gorm.io/driver/mysql

Then connect to the database using dsn (data source name). The driver library will automatically parse the dsn into corresponding configuration.

package main

import (
  "gorm.io/driver/mysql"
  "gorm.io/gorm"
  "log/slog"
)

func main() {
  dsn := "root:123456@tcp(192.168.48.138:3306)/hello?charset=utf8mb4&parseTime=True&loc=Local"
  db, err := gorm.Open(mysql.Open(dsn))
  if err != nil {
    slog.Error("db connect error", err)
  }
  slog.Info("db connect success")
}

Or manually pass configuration

package main

import (
  "gorm.io/driver/mysql"
  "gorm.io/gorm"
  "log/slog"
)

func main() {
  db, err := gorm.Open(mysql.New(mysql.Config{}))
  if err != nil {
    slog.Error("db connect error", err)
  }
  slog.Info("db connect success")
}

Both methods are equivalent; it depends on your usage preference.

Connection Configuration

By passing in the gorm.Config configuration struct, we can control some behaviors of gorm.

db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})

Below are some simple explanations. You can configure according to your needs.

type Config struct {
  // Disable default transaction, gorm will start a transaction for single create and update to maintain data consistency
  SkipDefaultTransaction bool
  // Custom naming strategy
  NamingStrategy schema.Namer
  // Save complete associations
  FullSaveAssociations bool
  // Custom logger
  Logger logger.Interface
  // Custom nowfunc, used to inject CreatedAt and UpdatedAt fields
  NowFunc func() time.Time
  // Only generate SQL without executing
  DryRun bool
  // Use prepared statements
  PrepareStmt bool
  // After establishing connection, ping the database
  DisableAutomaticPing bool
  // Ignore foreign keys when migrating database
  DisableForeignKeyConstraintWhenMigrating bool
  // Ignore association references when migrating database
  IgnoreRelationshipsWhenMigrating bool
  // Disable nested transactions
  DisableNestedTransaction bool
  // Allow global update, update without where clause
  AllowGlobalUpdate bool
  // Query all fields of the table
  QueryFields bool
  // Batch create size
  CreateBatchSize int
  // Enable error translation
  TranslateError bool

  // ClauseBuilders clause builder
  ClauseBuilders map[string]clause.ClauseBuilder
  // ConnPool db conn pool
  ConnPool ConnPool
  // Dialector database dialector
  Dialector
  // Plugins registered plugins
  Plugins map[string]Plugin

  callbacks  *callbacks
  cacheStore *sync.Map
}

Model

In gorm, a model corresponds to a database table. It is usually represented by a struct, such as the following struct.

type Person struct {
  Id      uint
  Name    string
  Address string
  Mom     string
  Dad     string
}

The struct's internal fields can consist of basic data types and types that implement the sql.Scanner and sql.Valuer interfaces. By default, the table name mapped by the Person struct is persons, which is in snake_case plural style, separated by underscores. Column names are also in snake_case style, for example Id corresponds to column name id. Gorm also provides some ways to configure this.

Specify Column Names

Through struct tags, we can specify column names for struct fields. This way, during entity mapping, gorm will use the specified column names.

type Person struct {
  Id      uint   `gorm:"column:ID;"`
  Name    string `gorm:"column:Name;"`
  Address string
  Mom     string
  Dad     string
}

Specify Table Names

By implementing the Table interface, you can specify the table name. It has only one method, which returns the table name.

type Tabler interface {
  TableName() string
}

In the implemented method, it returns the string person. During database migration, gorm will create a table named person.

type Person struct {
  Id      uint   `gorm:"column:ID;"`
  Name    string `gorm:"column:Name;"`
  Address string
  Mom     string
  Dad     string
}

func (p Person) TableName() string {
  return "person"
}

For naming strategies, you can also pass in your own strategy implementation when creating the connection to achieve custom effects.

Time Tracking

type Person struct {
  Id      uint
  Name    string
  Address string
  Mom     string
  Dad     string

  CreatedAt sql.NullTime
  UpdatedAt sql.NullTime
}

func (p Person) TableName() string {
  return "person"
}

When containing CreatedAt or UpdatedAt fields, during record creation or update, if their zero value, gorm will automatically use time.Now() to set the time.

db.Create(&Person{
    Name:    "jack",
    Address: "usa",
    Mom:     "lili",
    Dad:     "tom",
  })

// INSERT INTO `person` (`name`,`address`,`mom`,`dad`,`created_at`,`updated_at`) VALUES ('jack','usa','lili','tom','2023-10-25 14:43:57.16','2023-10-25 14:43:57.16')

Gorm also supports timestamp tracking

type Person struct {
  Id      uint   `gorm:"primaryKey;"`
  Name    string `gorm:"primaryKey;"`
  Address string
  Mom     string
  Dad     string

  // nanoseconds
  CreatedAt uint64 `gorm:"autoCreateTime:nano;"`
  // milliseconds
  UpdatedAt uint64 `gorm:"autoUpdateTime;milli;"`
}

Then during Create execution, it's equivalent to the following SQL

sql

INSERT INTO `person` (`name`,`address`,`mom`,`dad`,`created_at`,`updated_at`) VALUES ('jack','usa','lili','tom',1698216540519000000,1698216540)

In actual situations, if you need time tracking, I recommend storing timestamps on the backend, which is simpler to handle in cross-timezone scenarios.

Model

Gorm provides a preset Model struct, which contains an ID primary key, two time tracking fields, and a soft delete record field.

type Model struct {
    ID        uint `gorm:"primarykey"`
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt DeletedAt `gorm:"index"`
}

To use it, just embed it into your entity model.

type Order struct {
  gorm.Model
  Name string
}

This way it will automatically have all the features of gorm.Model.

Primary Key

By default, the field named Id is the primary key. You can use struct tags to specify the primary key field.

type Person struct {
  Id      uint `gorm:"primaryKey;"`
  Name    string
  Address string
  Mom     string
  Dad     string

  CreatedAt sql.NullTime
  UpdatedAt sql.NullTime
}

Multiple fields form a composite primary key

type Person struct {
  Id      uint `gorm:"primaryKey;"`
  Name    string `gorm:"primaryKey;"`
  Address string
  Mom     string
  Dad     string

  CreatedAt sql.NullTime
  UpdatedAt sql.NullTime
}

Index

You can specify column indexes through the index struct tag

type Person struct {
  Id      uint   `gorm:"primaryKey;"`
  Name    string `gorm:"primaryKey;"`
  Address string `gorm:"index:idx_addr,unique,sort:desc;"`
  Mom     string
  Dad     string

  // nanoseconds
  CreatedAt uint64 `gorm:"autoCreateTime:nano;"`
  // milliseconds
  UpdatedAt uint64 `gorm:"autoUpdateTime;milli;"`
}

In the above struct, a unique index is created for the Address field. Two fields using the same index name will create a composite index

type Person struct {
    Id      uint   `gorm:"primaryKey;"`
    Name    string `gorm:"primaryKey;"`
    Address string `gorm:"index:idx_addr,unique;"`
    School  string `gorm:"index:idx_addr,unique;"`
    Mom     string
    Dad     string

    // nanoseconds
    CreatedAt uint64 `gorm:"autoCreateTime:nano;"`
    // milliseconds
    UpdatedAt uint64 `gorm:"autoUpdateTime;milli;"`
}

Foreign Key

In a struct, foreign key relationships are defined by embedding structs, for example

type Person struct {
  Id   uint `gorm:"primaryKey;"`
  Name string

  MomId uint
  Mom   Mom `gorm:"foreignKey:MomId;"`

  DadId uint
  Dad   Dad `gorm:"foreignKey:DadId;"`
}

type Mom struct {
  Id   uint
  Name string

  Persons []Person `gorm:"foreignKey:MomId;"`
}

type Dad struct {
  Id   uint
  Name string

  Persons []Person `gorm:"foreignKey:DadId;"`
}

In the example, the Person struct has two foreign keys, referencing the primary keys of the Dad and Mom structs respectively. By default, it references the primary key. Person has a one-to-one relationship with Dad and Mom - a person can only have one father and one mother. Dad and Mom have a one-to-many relationship with Person because fathers and mothers can have multiple children.

Mom   Mom `gorm:"foreignKey:MomId;"`

The purpose of embedding structs is to facilitate specifying foreign keys and references. By default, the foreign key field name format is ReferencedTypeName+Id, such as MomId. By default, it references the primary key. You can specify a field to reference through struct tags

type Person struct {
  Id   uint `gorm:"primaryKey;"`
  Name string

  MomId uint
  Mom   Mom `gorm:"foreignKey:MomId;references:Sid;constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`

  DadId uint
  Dad   Dad `gorm:"foreignKey:DadId;constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`
}

type Mom struct {
  Id   uint
  Sid  uint `gorm:"uniqueIndex;"`
  Name string

  Persons []Person `gorm:"foreignKey:MomId;"`
}

Among them, constraint:OnUpdate:CASCADE,OnDelete:SET NULL; defines the foreign key constraint.

Hooks

An entity model can customize hooks

Create
Update
Delete
Query

The corresponding interfaces are as follows

// Triggered before create
type BeforeCreateInterface interface {
    BeforeCreate(*gorm.DB) error
}

// Triggered after create
type AfterCreateInterface interface {
    AfterCreate(*gorm.DB) error
}

// Triggered before update
type BeforeUpdateInterface interface {
    BeforeUpdate(*gorm.DB) error
}

// Triggered after update
type AfterUpdateInterface interface {
    AfterUpdate(*gorm.DB) error
}

// Triggered before save
type BeforeSaveInterface interface {
    BeforeSave(*gorm.DB) error
}

// Triggered after save
type AfterSaveInterface interface {
    AfterSave(*gorm.DB) error
}

// Triggered before delete
type BeforeDeleteInterface interface {
    BeforeDelete(*gorm.DB) error
}

// Triggered after delete
type AfterDeleteInterface interface {
    AfterDelete(*gorm.DB) error
}

// Triggered after query
type AfterFindInterface interface {
    AfterFind(*gorm.DB) error
}

Structs can customize behaviors by implementing these interfaces.

Tag Name	Description
`column`	Specifies db column name
`type`	Column data type, recommended to use compatible generic types, e.g., all databases support bool, int, uint, float, string, time, bytes and can be used with other tags, e.g., `not null`, `size`, `autoIncrement`... Specifying database data types like `varbinary(8)` is also supported. When using specified database data types, it needs to be the complete database data type, e.g., `MEDIUMINT UNSIGNED not NULL AUTO_INCREMENT`
`serializer`	Specifies serializer to serialize or deserialize data to/from database, e.g., `serializer:json/gob/unixtime`
`size`	Defines column data type size or length, e.g., `size: 256`
`primaryKey`	Defines column as primary key
`unique`	Defines column as unique key
`default`	Defines column default value
`precision`	Specifies column precision
`scale`	Specifies column scale
`not null`	Specifies column as NOT NULL
`autoIncrement`	Specifies column as auto-increment
`autoIncrementIncrement`	Auto-increment step, controls interval between consecutive records
`embedded`	Embedded field
`embeddedPrefix`	Column name prefix for embedded field
`autoCreateTime`	Tracks current time on create, for `int` fields it tracks timestamp seconds, you can use `nano`/`milli` to track nanoseconds/milliseconds timestamp, e.g., `autoCreateTime:nano`
`autoUpdateTime`	Tracks current time on create/update, for `int` fields it tracks timestamp seconds, you can use `nano`/`milli` to track nanoseconds/milliseconds timestamp, e.g., `autoUpdateTime:milli`
`index`	Creates index based on parameters, multiple fields using the same name creates composite index, see Indexes for details
`uniqueIndex`	Same as `index` but creates unique index
`check`	Creates check constraint, e.g., `check:age > 13`, see Constraints for details
`<-`	Sets field write permission, `<-:create` create only, `<-:update` update only, `<-:false` no write permission, `<-` create and update permission
`->`	Sets field read permission, `->:false` no read permission
`-`	Ignores this field, `-` means no read/write, `-:migration` means no migration permission, `-:all` means no read/write/migration permission
`comment`	Adds comment for field during migration
`foreignKey`	Specifies current model's column as foreign key for join table
`references`	Specifies referenced table's column name, which will be mapped as join table foreign key
`polymorphic`	Specifies polymorphic type, e.g., model name
`polymorphicValue`	Specifies polymorphic value, default table name
`many2many`	Specifies join table name
`joinForeignKey`	Specifies foreign key column name in join table, which will be mapped to current table
`joinReferences`	Specifies foreign key column name in join table, which will be mapped to referenced table
`constraint`	Relationship constraints, e.g., `OnUpdate`, `OnDelete`

Migration

The AutoMigrate method helps us with automatic migration. It will create tables, constraints, indexes, foreign keys, etc.

func (db *DB) AutoMigrate(dst ...interface{}) error

For example

type Person struct {
  Id      uint   `gorm:"primaryKey;"`
  Name    string `gorm:"type:varchar(100);uniqueIndex;"`
  Address string
}

type Order struct {
  Id   uint
  Name string
}

db.AutoMigrate(Person{}, Order{})
// CREATE TABLE `person` (`id` bigint unsigned AUTO_INCREMENT,`name` varchar(100),`address` longtext,PRIMARY KEY (`id`),UNIQUE INDEX `idx_person_name` (`name`))
// CREATE TABLE `orders` (`id` bigint unsigned AUTO_INCREMENT,`name` longtext,PRIMARY KEY (`id`))

Or we can operate manually by accessing the Migrator interface through the Migrator method

func (db *DB) Migrator() Migrator

It supports the following interface methods

type Migrator interface {
  // AutoMigrate
  AutoMigrate(dst ...interface{}) error

  // Database
  CurrentDatabase() string
  FullDataTypeOf(*schema.Field) clause.Expr
  GetTypeAliases(databaseTypeName string) []string

  // Tables
  CreateTable(dst ...interface{}) error
  DropTable(dst ...interface{}) error
  HasTable(dst interface{}) bool
  RenameTable(oldName, newName interface{}) error
  GetTables() (tableList []string, err error)
  TableType(dst interface{}) (TableType, error)

  // Columns
  AddColumn(dst interface{}, field string) error
  DropColumn(dst interface{}, field string) error
  AlterColumn(dst interface{}, field string) error
  MigrateColumn(dst interface{}, field *schema.Field, columnType ColumnType) error
  HasColumn(dst interface{}, field string) bool
  RenameColumn(dst interface{}, oldName, field string) error
  ColumnTypes(dst interface{}) ([]ColumnType, error)

  // Views
  CreateView(name string, option ViewOption) error
  DropView(name string) error

  // Constraints
  CreateConstraint(dst interface{}, name string) error
  DropConstraint(dst interface{}, name string) error
  HasConstraint(dst interface{}, name string) bool

  // Indexes
  CreateIndex(dst interface{}, name string) error
  DropIndex(dst interface{}, name string) error
  HasIndex(dst interface{}, name string) bool
  RenameIndex(dst interface{}, oldName, newName string) error
  GetIndexes(dst interface{}) ([]Index, error)
}

The method list involves multiple dimensions: database, table, column, view, index, constraint. For users who need customization, it allows more fine-grained operations.

Specify Table Comments

During migration, if you want to add table comments, you can set it as follows

db.Set("gorm:table_options", " comment 'person table'").Migrator().CreateTable(Person{})

Note that if you use the AutoMigrate() method for migration and there are reference relationships between structs, gorm will recursively create referenced tables first. This will cause both the referenced table and referencing table to have duplicate comments. So it's recommended to use the CreateTable method for creation.

TIP

When creating tables, the CreateTable method requires the referenced table to be created before the referencing table, otherwise it will report an error. The AutoMigrate method doesn't require this because it recursively creates tables following the reference relationships.

Create

When creating new records, the Create method is used in most cases

func (db *DB) Create(value interface{}) (tx *DB)

Given the following struct

type Person struct {
  Id   uint `gorm:"primaryKey;"`
  Name string
}

Create a record

user := Person{
    Name: "jack",
}

// Must pass a reference
db = db.Create(&user)

// Error occurred during execution
err = db.Error
// Number of rows affected
affected := db.RowsAffected

After creation, gorm will write the primary key into the user struct, which is why you must pass a pointer. If you pass a slice, it will batch create

user := []Person{
    {Name: "jack"},
    {Name: "mike"},
    {Name: "lili"},
}

db = db.Create(&user)

Similarly, gorm will write the primary key into the slice. When the data volume is too large, you can also use the CreateInBatches method to create in batches, because the generated INSERT INTO table VALUES (),() SQL statement will become very long, and each database has limits on SQL length. So when necessary, you can choose to create in batches.

db = db.CreateInBatches(&user, 50)

In addition, the Save method can also create records. Its function is to update the record when the primary key matches, otherwise insert.

func (db *DB) Save(value interface{}) (tx *DB)

user := []Person{
    {Name: "jack"},
    {Name: "mike"},
    {Name: "lili"},
}

db = db.Save(&user)

Upsert

The Save method can only match primary keys. We can build a more custom upsert by constructing a Clause. For example, this line of code

db.Clauses(clause.OnConflict{
    Columns:   []clause.Column{{Name: "name"}},
    DoNothing: false,
    DoUpdates: clause.AssignmentColumns([]string{"address"}),
    UpdateAll: false,
}).Create(&p)

Its function is: when the name field conflicts, update the value of the address field. If there's no conflict, it will create a new record. You can also do nothing when there's a conflict

db.Clauses(clause.OnConflict{
    Columns:   []clause.Column{{Name: "name"}},
    DoNothing: true,
}).Create(&p)

Or directly update all fields

db.Clauses(clause.OnConflict{
    Columns:   []clause.Column{{Name: "name"}},
    UpdateAll: true,
}).Create(&p)

Before using upsert, remember to add an index to the conflict field.

Query

First

For queries, gorm provides quite a few methods. The first is the First method

func (db *DB) First(dest interface{}, conds ...interface{}) (tx *DB)

Its function is to find the first record ordered by primary key ascending, for example

var person Person
result := db.First(&person)
err := result.Error
affected := result.RowsAffected

Pass a dest pointer to let gorm map the queried data to the struct.

Or use the Table and Model methods to specify the query table. The former receives a string table name, the latter receives an entity model.

db.Table("person").Find(&p)
db.Model(Person{}).Find(&p)

TIP

If the passed pointer element contains an entity model, such as a struct pointer or struct slice pointer, then you don't need to manually specify which table to query. This rule applies to all CRUD operations.

Take

The Take method is similar to First, the difference is it won't sort by primary key.

func (db *DB) Take(dest interface{}, conds ...interface{}) (tx *DB)

var person Person
result := db.Take(&person)
err := result.Error
affected := result.RowsAffected

Pluck

The Pluck method is used to batch query a single column from a table. The query results can be collected into a slice of a specified type, not necessarily an entity type slice.

func (db *DB) Pluck(column string, dest interface{}) (tx *DB)

For example, collect everyone's address into a string slice

var adds []string

// SELECT `address` FROM `person` WHERE name IN ('jack','lili')
db.Model(Person{}).Where("name IN ?", []string{"jack", "lili"}).Pluck("address", &adds)

Actually it's equivalent to

db.Select("address").Where("name IN ?", []string{"jack", "lili"}).Find(&adds)

Count

The Count method is used to count the number of entity records

func (db *DB) Count(count *int64) (tx *DB)

See a usage example

var count int64

// SELECT count(*) FROM `person`
db.Model(Person{}).Count(&count)

Find

The most commonly used method for batch queries is the Find method

func (db *DB) Find(dest interface{}, conds ...interface{}) (tx *DB)

It will find all records that match the given conditions

// SELECT * FROM `person`
var ps []Person
db.Find(&ps)

Select

By default, gorm queries all fields. We can specify fields through the Select method

func (db *DB) Select(query interface{}, args ...interface{}) (tx *DB)

For example

// SELECT `address`,`name` FROM `person` ORDER BY `person`.`id` LIMIT 1
db.Select("address", "name").First(&p)

Equivalent to

db.Select([]string{"address", "name"}).First(&p)

At the same time, you can use the Omit method to ignore fields

func (db *DB) Omit(columns ...string) (tx *DB)

For example

// SELECT `person`.`id`,`person`.`name` FROM `person` WHERE id IN (1,2,3,4)
db.Omit("address").Where("id IN ?", []int{1, 2, 3, 4}).Find(&ps)

Fields selected or ignored by Select and Omit will also take effect when creating and updating queries.

Where

Conditional queries use the Where method

func (db *DB) Where(query interface{}, args ...interface{}) (tx *DB)

Here's a simple example

var p Person

db.Where("id = ?", 1).First(&p)

Using multiple Where in chain operations will build multiple AND statements, such as

// SELECT * FROM `person` WHERE id = 1 AND name = 'jack' ORDER BY `person`.`id` LIMIT 1
db.Where("id = ?", 1).Where("name = ?", "jack").First(&p)

Or use the Or method to build OR statements

func (db *DB) Or(query interface{}, args ...interface{}) (tx *DB)

// SELECT * FROM `person` WHERE id = 1 OR name = 'jack' AND address = 'usa' ORDER BY `person`.`id` LIMIT 1
db.Where("id = ?", 1).
    Or("name = ?", "jack").
    Where("address = ?", "usa").
    First(&p)

There's also the Not method, which is similar

func (db *DB) Not(query interface{}, args ...interface{}) (tx *DB)

// SELECT * FROM `person` WHERE id = 1 OR name = 'jack' AND NOT name = 'mike' AND address = 'usa' ORDER BY `person`.`id` LIMIT 1
db.Where("id = ?", 1).
    Or("name = ?", "jack").
    Not("name = ?", "mike").
    Where("address = ?", "usa").
    First(&p)

For IN conditions, you can directly pass a slice into the Where method.

db.Where("address IN ?", []string{"cn", "us"}).Find(&ps)

Or for multi-column IN conditions, you need to use [][]any type to carry parameters

// SELECT * FROM `person` WHERE (id, name, address) IN ((1,'jack','uk'),(2,'mike','usa'))
db.Where("(id, name, address) IN ?", [][]any{{1, "jack", "uk"}, {2, "mike", "usa"}}).Find(&ps)

Gorm supports where grouping, which combines the above statements

db.Where(
    db.Where("name IN ?", []string{"cn", "uk"}).Where("id IN ?", []uint{1, 2}),
  ).Or(
    db.Where("name IN ?", []string{"usa", "jp"}).Where("id IN ?", []uint{3, 4}),
  ).Find(&ps)
// SELECT * FROM `person` WHERE (name IN ('cn','uk') AND id IN (1,2)) OR (name IN ('usa','jp') AND id IN (3,4))

Order

Sorting uses the Order method

func (db *DB) Order(value interface{}) (tx *DB)

See a usage example

var ps []Person

// SELECT * FROM `person` ORDER BY name ASC, id DESC
db.Order("name ASC, id DESC").Find(&ps)

You can also call it multiple times

// SELECT * FROM `person` ORDER BY name ASC, id DESC, address
db.Order("name ASC, id DESC").Order("address").Find(&ps)

Limit

The Limit and Offset methods are often used for pagination queries

func (db *DB) Limit(limit int) (tx *DB)

func (db *DB) Offset(offset int) (tx *DB)

Here's a simple pagination example

var (
    ps   []Person
    page = 2
    size = 10
)

// SELECT * FROM `person` LIMIT 10 OFFSET 10
db.Offset((page - 1) * size).Limit(size).Find(&ps)

Group

The Group and Having methods are mostly used for grouping operations

func (db *DB) Group(name string) (tx *DB)

func (db *DB) Having(query interface{}, args ...interface{}) (tx *DB)

See an example

var (
    ps []Person
)

// SELECT `address` FROM `person` GROUP BY `address` HAVING address IN ('cn','us')
db.Select("address").Group("address").Having("address IN ?", []string{"cn", "us"}).Find(&ps)

Distinct

The Distinct method is mostly used for deduplication

func (db *DB) Distinct(args ...interface{}) (tx *DB)

See an example

// SELECT DISTINCT `name` FROM `person` WHERE address IN ('cn','us')
db.Where("address IN ?", []string{"cn", "us"}).Distinct("name").Find(&ps)

SubQuery

A subquery is a nested query. For example, if you want to find all people whose id value is greater than the average

// SELECT * FROM `person` WHERE id > (SELECT AVG(id) FROM `person`
db.Where("id > (?)", db.Model(Person{}).Select("AVG(id)")).Find(&ps)

From subquery

// SELECT * FROM (SELECT * FROM `person` WHERE address IN ('cn','uk')) as p
db.Table("(?) as p", db.Model(Person{}).Where("address IN ?", []string{"cn", "uk"})).Find(&ps)

Lock

Gorm uses the clause.Locking clause to provide lock support

// SELECT * FROM `person` FOR UPDATE
db.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&ps)

// SELECT * FROM `person` FOR SHARE NOWAIT
db.Clauses(clause.Locking{Strength: "SHARE", Options: "NOWAIT"}).Find(&ps)

Iteration

You can get an iterator through the Rows method

func (db *DB) Rows() (*sql.Rows, error)

By traversing the iterator, use the ScanRows method to scan each row's result into a struct.

rows, err := db.Model(Person{}).Rows()
if err != nil {
    return
}
defer rows.Close()

for rows.Next() {
    var p Person
    err := db.ScanRows(rows, &p)
    if err != nil {
        return
    }
}

Update

Save

The Save method was mentioned during creation. It can also be used to update records, and it will update all fields, even if some struct fields are zero values. However, if the primary key doesn't match, it will perform an insert operation.

var p Person

db.First(&p)

p.Address = "poland"
// UPDATE `person` SET `name`='json',`address`='poland' WHERE `id` = 2
db.Save(&p)

You can see it adds all fields except the primary key to the SET statement.

Update

So in most cases, it's recommended to use the Update method

func (db *DB) Update(column string, value interface{}) (tx *DB)

It's mainly used to update a single column field

var p Person

db.First(&p)

// UPDATE `person` SET `address`='poland' WHERE id = 2
db.Model(Person{}).Where("id = ?", p.Id).Update("address", "poland")

Updates

The Updates method is used to update multiple columns, accepting structs and maps as parameters. When struct fields are zero values, those fields will be ignored, but not in maps.

func (db *DB) Updates(values interface{}) (tx *DB)

Here's an example

var p Person

db.First(&p)

// UPDATE `person` SET `name`='jojo',`address`='poland' WHERE `id` = 2
db.Model(p).Updates(Person{Name: "jojo", Address: "poland"})

// UPDATE `person` SET `address`='poland',`name`='jojo' WHERE `id` = 2
db.Model(p).Updates(map[string]any{"name": "jojo", "address": "poland"})

SQL Expressions

Sometimes, you often need to perform operations on fields like self-increment or self-decrement or other operations involving the field itself. Generally, you first query, then calculate, then update, or use SQL expressions.

func Expr(expr string, args ...interface{}) clause.Expr

See the following example

// UPDATE `person` SET `age`=age + age,`name`='jojo' WHERE `id` = 2
db.Model(p).Updates(map[string]any{"name": "jojo", "age": gorm.Expr("age + age")})

// UPDATE `person` SET `age`=age * 2 + age,`name`='jojo' WHERE `id` = 2
db.Model(p).Updates(map[string]any{"name": "jojo", "age": gorm.Expr("age * 2 + age")})

Delete

In gorm, deleting records uses the Delete method. It can directly pass an entity struct or pass conditions.

func (db *DB) Delete(value interface{}, conds ...interface{}) (tx *DB)

For example, directly pass a struct

var p Person

db.First(&p)

// DELETE FROM `person` WHERE `person`.`id` = 2
db.Delete(&p)

var p Person

db.First(&p)

// DELETE FROM `person` WHERE `person`.`id` = 2
db.Model(p).Delete(nil)

Or specify conditions

// DELETE FROM `person` WHERE id = 2
db.Model(Person{}).Where("id = ?", p.Id).Delete(nil)

Or simplify to

var p Person

db.First(&p)

// DELETE FROM `person` WHERE id = 2
db.Delete(&Person{}, "id = ?", 2)

// DELETE FROM `person` WHERE `person`.`id` = 2
db.Delete(&Person{}, 2)

For batch delete, pass a slice

// DELETE FROM `person` WHERE id IN (1,2,3)
db.Delete(&Person{}, "id IN ?", []uint{1, 2, 3})
// DELETE FROM `person` WHERE `person`.`id` IN (1,2,3)
db.Delete(&Person{}, []uint{1, 2, 3})

Soft Delete

If your entity model uses soft delete, during deletion, it performs an update operation by default. If you want to permanently delete, you can use the Unscoped method

db.Unscoped().Delete(&Person{}, []uint{1, 2, 3})

Association Definition

Gorm provides table association interaction capabilities, defining associations between structs through embedded structs and fields.

One-to-One

One-to-one relationship is the simplest. Normally, a person can only have one mother. See the following struct

type Person struct {
  Id      uint
  Name    string
  Address string
  Age     uint

  MomId sql.NullInt64
  Mom   Mom `gorm:"foreignKey:MomId;"`
}

type Mom struct {
  Id   uint
  Name string
}

The Person struct implements reference to Mom type by embedding the Mom struct. Among them, Person.MomId is the reference field, and primary key Mom.Id is the referenced field. This completes the one-to-one association. How to customize foreign keys, references, constraints, and default foreign key rules have been explained in Foreign Key Definition, so won't be repeated.

TIP

For foreign key fields, it's recommended to use types provided by the sql package, because foreign keys can be NULL by default. When using Create to create records, if you use ordinary types, the zero value 0 will also be created. Creating a non-existent foreign key is obviously not allowed.

One-to-Many

Below, add a school struct. The relationship between school and student is one-to-many. A school has multiple students, but a student can only attend one school.

type Person struct {
    Id      uint
    Name    string
    Address string
    Age     uint

    MomId sql.NullInt64
    Mom   Mom `gorm:"foreignKey:MomId;"`

    SchoolId sql.NullInt64
    School   School `gorm:"foreignKey:SchoolId;"`
}

type Mom struct {
    Id   uint
    Name string
}


type School struct {
    Id   uint
    Name string

    Persons []Person `gorm:"foreignKey:SchoolId;"`
}

school.Persons is []Person type, indicating it can have multiple students, while Person must contain a foreign key referencing School, which is Person.SchoolId.

Many-to-Many

A person can own many houses, and a house can be lived in by many people. This is a many-to-many relationship.

type Person struct {
  Id      uint
  Name    string
  Address string
  Age     uint

  MomId sql.NullInt64
  Mom   Mom `gorm:"foreignKey:MomId;"`

  SchoolId sql.NullInt64
  School   School `gorm:"foreignKey:SchoolId;"`

  Houses []House `gorm:"many2many:person_house;"`
}

type Mom struct {
  Id   uint
  Name string
}

type School struct {
  Id   uint
  Name string

  Persons []Person
}

type House struct {
  Id   uint
  Name string

  Persons []Person `gorm:"many2many:person_house;"`
}

type PersonHouse struct {
  PersonId sql.NullInt64
  Person   Person `gorm:"foreignKey:PersonId;"`
  HouseId  sql.NullInt64
  House    House `gorm:"foreignKey:HouseId;"`
}

Person and House hold each other's slice types to represent the many-to-many relationship. Many-to-many relationships generally require creating a join table, specified through many2many. The foreign keys of the join table must be specified correctly.

After creating the structs, let gorm automatically migrate to the database

tables := []any{
    School{},
    Mom{},
    Person{},
    House{},
    PersonHouse{},
}
for _, table := range tables {
    db.Migrator().CreateTable(&table)
}

Note the order of creation between referenced and referencing tables.

Association Operations

After creating the above three association relationships, next is how to use associations for CRUD. This mainly uses the Association method

func (db *DB) Association(column string) *Association

It receives an association parameter, whose value should be the field name of the referenced type embedded in the reference struct.

db.Model(&person).Association("Mom").Find(&mom)

For example, to find a person's mother through association, the Association parameter is Mom, which is the Person.Mom field name.

Create Association

// Define data
jenny := Mom{
    Name: "jenny",
}

mit := School{
    Name:    "MIT",
    Persons: nil,
}

h1 := House{
    Id:      0,
    Name:    "h1",
    Persons: nil,
}

h2 := House{
    Name:    "h2",
    Persons: nil,
}

jack := Person{
    Name:    "jack",
    Address: "usa",
    Age:     18,
}

mike := Person{
    Name:    "mike",
    Address: "uk",
    Age:     20,
}

// INSERT INTO `people` (`name`,`address`,`age`,`mom_id`,`school_id`) VALUES ('jack','usa',18,NULL,NULL)
db.Create(&jack)
// INSERT INTO `schools` (`name`) VALUES ('MIT')
db.Create(&mit)

// Add Person-Mom association, one-to-one association
// INSERT INTO `moms` (`name`) VALUES ('jenny') ON DUPLICATE KEY UPDATE `id`=`id`
// UPDATE `people` SET `mom_id`=1 WHERE `id` = 1
db.Model(&jack).Association("Mom").Append(&jenny)

// Add school-Person association, one-to-many association
// INSERT INTO `people` (`name`,`address`,`age`,`mom_id`,`school_id`,`id`) VALUES ('jack','usa',18,1,1,1),('mike','uk',20,NULL,1,DEFAULT) ON DUPLICATE KEY UPDATE `school_id`=VALUES(`school_id`)
db.Model(&mit).Association("Persons").Append([]Person{jack, mike})

// Add Person-Houses association, many-to-many association
// INSERT INTO `houses` (`name`) VALUES ('h1'),('h2') ON DUPLICATE KEY UPDATE `id`=`id`
// INSERT INTO `person_house` (`person_id`,`house_id`) VALUES (1,1),(1,2) ON DUPLICATE KEY UPDATE `person_id`=`person_id`
db.Model(&jack).Association("Houses").Append([]House{h1, h2})

If all records don't exist, during association creation, it will first create records then create the association.

Find Association

Below demonstrates how to find associations.

// One-to-one association find
var person Person
var mom Mom

// SELECT * FROM `people` ORDER BY `people`.`id` LIMIT 1
db.First(&person)
// SELECT * FROM `moms` WHERE `moms`.`id` = 1
db.Model(person).Association("Mom").Find(&mom)

// One-to-many association find
var school School
var persons []Person

// SELECT * FROM `schools` ORDER BY `schools`.`id` LIMIT 1
db.First(&school)
// SELECT * FROM `people` WHERE `people`.`school_id` = 1
db.Model(&school).Association("Persons").Find(&persons)

// Many-to-many association find
var houses []House

// SELECT `houses`.`id`,`houses`.`name` FROM `houses` JOIN `person_house` ON `person_house`.`house_id` = `houses`.`id` AND `person_house`.`person_id` IN (1,2)
db.Model(&persons).Association("Houses").Find(&houses)

Association find will find records matching conditions in the reference table based on existing data. For many-to-many relationships, gorm will automatically complete the table join process.

Update Association

Below demonstrates how to update associations

// One-to-one association update
var jack Person

lili := Mom{
    Name: "lili",
}

// SELECT * FROM `people` WHERE name = 'jack' ORDER BY `people`.`id` LIMIT 1
db.Where("name = ?", "jack").First(&jack)

// INSERT INTO `moms` (`name`) VALUES ('lili')
db.Create(&lili)

// INSERT INTO `moms` (`name`,`id`) VALUES ('lili',2) ON DUPLICATE KEY UPDATE `id`=`id`
// UPDATE `people` SET `mom_id`=2 WHERE `id` = 1
db.Model(&jack).Association("Mom").Replace(&lili)

// One-to-many association update

var mit School
newPerson := []Person{{Name: "bob"}, {Name: "jojo"}}
// INSERT INTO `people` (`name`,`address`,`age`,`mom_id`,`school_id`) VALUES ('bob','',0,NULL,NULL),('jojo','',0,NULL,NULL)
db.Create(&newPerson)

//  SELECT * FROM `schools` WHERE name = 'mit' ORDER BY `schools`.`id` LIMIT 1
db.Where("name = ?", "mit").First(&mit)

// INSERT INTO `people` (`name`,`address`,`age`,`mom_id`,`school_id`,`id`) VALUES ('bob','',0,NULL,1,4),('jojo','',0,NULL,1,5) ON DUPLICATE KEY UPDATE `school_id`=VALUES(`school_id`)
//  UPDATE `people` SET `school_id`=NULL WHERE `people`.`id` NOT IN (4,5) AND `people`.`school_id` = 1
db.Model(&mit).Association("Persons").Replace(newPerson)

// Many-to-many association update

// INSERT INTO `houses` (`name`) VALUES ('h3'),('h4'),('h5') ON DUPLICATE KEY UPDATE `id`=`id`
// INSERT INTO `person_house` (`person_id`,`house_id`) VALUES (1,3),(1,4),(1,5) ON DUPLICATE KEY UPDATE `person_id`=`person_id`
// DELETE FROM `person_house` WHERE `person_house`.`person_id` = 1 AND `person_house`.`house_id` NOT IN (3,4,5)
db.Model(&jack).Association("Houses").Replace([]House{{Name: "h3"}, {Name: "h4"}, {Name: "h5"}})

During association update, if both referenced data and referencing data don't exist, gorm will try to create them.

Delete Association

Below demonstrates how to delete associations

// One-to-one association delete
var (
    jack Person
    lili Mom
)

// SELECT * FROM `people` WHERE name = 'jack' ORDER BY `people`.`id` LIMIT 1
db.Where("name = ?", "jack").First(&jack)

//  SELECT * FROM `moms` WHERE name = 'lili' ORDER BY `moms`.`id` LIMIT 1
db.Where("name = ?", "lili").First(&lili)

// UPDATE `people` SET `mom_id`=NULL WHERE `people`.`id` = 1 AND `people`.`mom_id` = 2
db.Model(&jack).Association("Mom").Delete(&lili)

// One-to-many association delete

var (
    mit     School
    persons []Person
)

// SELECT * FROM `schools` WHERE name = 'mit' ORDER BY `schools`.`id` LIMIT 1
db.Where("name = ?", "mit").First(&mit)
// SELECT * FROM `people` WHERE name IN ('jack','mike')
db.Where("name IN ?", []string{"jack", "mike"}).Find(&persons)

// UPDATE `people` SET `school_id`=NULL WHERE `people`.`school_id` = 1 AND `people`.`id` IN (1,2)
db.Model(&mit).Association("Persons").Delete(&persons)

// Many-to-many association delete
var houses []House

// SELECT * FROM `houses` WHERE name IN ('h3','h4')
db.Where("name IN ?", []string{"h3", "h4"}).Find(&houses)

// DELETE FROM `person_house` WHERE `person_house`.`person_id` = 1 AND `person_house`.`house_id` IN (3,4)
db.Model(&jack).Association("Houses").Delete(&houses)

Association delete only deletes the reference relationship between them, not the entity records. We can also use the Clear method to directly clear the association

db.Model(&jack).Association("Houses").Clear()

If you want to delete the corresponding entity records, you can add the Unscoped operation after the Association operation (won't affect many2many)

db.Model(&jack).Association("Houses").Unscoped().Delete(&houses)

For one-to-many and many-to-many, you can use the Select operation to delete records

var (
    mit     School
)
db.Where("name = ?", "mit").First(&mit)

db.Select("Persons").Delete(&mit)

Preloading

Preloading is used to query association data. For entities with association relationships, it will first preload the associated referenced entities. The association query mentioned earlier queries the association relationships. Preloading directly queries entity records, including all association relationships. From a syntax perspective, association query needs to first query the specified []Person, then query the associated []Mom based on []Person. Preloading, from a syntax perspective, directly queries []Person and will also load all association relationships. However, in practice, the SQL they execute is almost the same. See an example

var users []Person

// SELECT * FROM `moms` WHERE `moms`.`id` = 1
// SELECT * FROM `people`
db.Preload("Mom").Find(&users)

This is a one-to-one association query example. Its output

[{Id:1 Name:jack Address:usa Age:18 MomId:{Int64:1 Valid:true} Mom:{Id:1 Name:jenny} SchoolId:{Int64:1 Valid:true} School:{Id:0 Name: Persons:[]} Houses:[]} {Id:2 Name:mike Address:uk Age:20 MomId:{Int64:0 Valid:false} Mom:{Id:0 Name:} SchoolId:{Int64:1 Valid:true} School:{Id:0 Name: Persons:[]} Houses:[]}]

You can see the associated Mom was also queried, but the school relationship was not preloaded, so all School structs are zero values. You can also use clause.Associations to indicate preloading all relationships, except nested relationships.

db.Preload(clause.Associations).Find(&users)

Below is a nested preloading example. Its function is to query all students of all school associations, the mother associated with each student, and the houses owned by each student, and also query the owner set of each house. School->Student->House->Student.

var schools []School

db.Preload("Persons").
    Preload("Persons.Mom").
    Preload("Persons.Houses").
    Preload("Persons.Houses.Persons").Find(&schools)

// Output code, logic can be ignored
for _, school := range schools {
    fmt.Println("school", school.Name)
    for _, person := range school.Persons {
        fmt.Println("person", person.Name)
        fmt.Println("mom", person.Mom.Name)
        for _, house := range person.Houses {
            var persons []string
            for _, p := range house.Persons {
                persons = append(persons, p.Name)
            }
            fmt.Println("house", house.Name, "owner", persons)
        }
        fmt.Println()
    }
}

Output is

school MIT
person jack
mom jenny
house h1 owner [jack]
house h2 owner [jack]

person mike
mom

You can see it outputs each school's each student's mother and their houses, and the owners of each house.

Transactions

Gorm enables transactions by default. Any insert and update operations will rollback after failure. You can disable it in Connection Configuration, performance will improve by about 30%. There are multiple methods for using transactions in gorm. Below is a simple introduction.

Automatic

Closure transaction, through the Transaction method, pass in a closure function. If the function return value is not nil, it will automatically rollback.

func (db *DB) Transaction(fc func(tx *DB) error, opts ...*sql.TxOptions) (err error)

See an example. Operations in the closure should use the parameter tx, not the external db.

var ps []Person

db.Transaction(func(tx *gorm.DB) error {
    err := tx.Create(&ps).Error
    if err != nil {
        return err
    }

    err = tx.Create(&ps).Error
    if err != nil {
        return err
    }

    err = tx.Model(Person{}).Where("id = ?", 1).Update("name", "jack").Error
    if err != nil {
        return err
    }

    return nil
})

Manual

Manual transactions are more recommended, where we control when to rollback and when to commit. Manual transactions use the following three methods

// Begin method is used to start a transaction
func (db *DB) Begin(opts ...*sql.TxOptions) *DB

// Rollback method is used to rollback a transaction
func (db *DB) Rollback() *DB

// Commit method is used to commit a transaction
func (db *DB) Commit() *DB

See an example. After starting a transaction, you should use tx to operate the ORM.

var ps []Person

tx := db.Begin()

err := tx.Create(&ps).Error
if err != nil {
    tx.Rollback()
    return
}

err = tx.Create(&ps).Error
if err != nil {
    tx.Rollback()
    return
}

err = tx.Model(Person{}).Where("id = ?", 1).Update("name", "jack").Error
if err != nil {
    tx.Rollback()
    return
}

tx.Commit()

You can specify savepoints

var ps []Person

tx := db.Begin()

err := tx.Create(&ps).Error
if err != nil {
    tx.Rollback()
    return
}

tx.SavePoint("createBatch")

err = tx.Create(&ps).Error
if err != nil {
    tx.Rollback()
    return
}

err = tx.Model(Person{}).Where("id = ?", 1).Update("name", "jack").Error
if err != nil {
    tx.RollbackTo("createBatch")
    return
}

tx.Commit()

Summary

If you have read all the above content and typed the code, then you can use gorm to perform CRUD operations on the database. Besides these operations, gorm has many other features. For more details, you can check the official documentation.

Gorm Database ORM Library ​

Features ​

Installation ​

Connection ​

Connection Configuration ​

Model ​

Specify Column Names ​

Specify Table Names ​

Time Tracking ​

Model ​

Primary Key ​

Index ​

Foreign Key ​

Hooks ​

Tags ​

Migration ​

Specify Table Comments ​

Create ​

Create ​

Upsert ​

Query ​

First ​

Take ​

Pluck ​

Count ​

Find ​

Select ​

Where ​

Order ​

Limit ​

Group ​

Distinct ​

SubQuery ​

Lock ​

Iteration ​

Update ​

Save ​

Update ​

Updates ​

SQL Expressions ​

Delete ​

Soft Delete ​

Association Definition ​

One-to-One ​

One-to-Many ​

Many-to-Many ​

Association Operations ​

Create Association ​

Find Association ​

Update Association ​

Delete Association ​

Preloading ​

Transactions ​

Automatic ​

Manual ​

Summary ​

Gorm Database ORM Library

Features

Installation

Connection

Connection Configuration

Model

Specify Column Names

Specify Table Names

Time Tracking

Model

Primary Key

Index

Foreign Key

Hooks

Tags

Migration

Specify Table Comments

Create

Create

Upsert

Query

First

Take

Pluck

Count

Find

Select

Where

Order

Limit

Group

Distinct

SubQuery

Lock

Iteration

Update

Save

Update

Updates

SQL Expressions

Delete

Soft Delete

Association Definition

One-to-One

One-to-Many

Many-to-Many

Association Operations

Create Association

Find Association

Update Association

Delete Association

Preloading

Transactions

Automatic

Manual

Summary