Total
学习 ORM 框架 gorm 的笔记, 基于GORM v1.22.4
相关代码在我的仓库找到gorm-note
1. Introduction
The fantastic ORM library for Golang, aims to be develpoer friendly.
2. Quick Start
go get -u gorm.io/gorm
go get -u gorm.io/driver/sqlite
package main
import (
"log"
"gorm.io/gorm/logger"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
)
type Product struct {
gorm.Model
Code string
Price uint
}
func main() {
db, err := gorm.Open(sqlite.Open("quick-start.db"), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
})
if err != nil {
panic("failed to connect database")
}
db.AutoMigrate(&Product{})
db.Create(&Product{
Code: "D42",
Price: 100,
})
var product Product
db.First(&product, 1)
log.Printf("Result:%v\n", product)
db.First(&product, "code = ?", "D42")
log.Printf("Result:%v\n", product)
db.Model(&product).Update("Price", 200)
log.Printf("Result:%v\n", product)
db.Model(&product).Updates(Product{Code: "F42", Price: 300})
log.Printf("Result:%v\n", product)
db.Model(&product).Updates(map[string]interface{}{"Code": "E42", "Price": 150})
log.Printf("Result:%v\n", product)
db.Delete(&product, 1)
log.Printf("Result:%v\n", product)
}
Logger: logger.Default.LogMode(logger.Info):将 log 等级设为 info，会打印执行的 SQL
3. Declaring Models
3.1 Declaring Models
模型是标准的 struct, 由 Go 的基本数据类型、实现了 Scanner 和 Valuer 接口的自定义类型及其指针或别名组成
例如：
type User struct {
ID uint
Name string
Email *string
Age uint8
Birthday *time.Time
MemberNumber sql.NullString
ActivatedAt sql.NullTime
CreatedAt time.Time
UpdatedAt time.Time
}
3.2 Conventions
GORM 倾向于约定，而不是配置。默认情况下，GORM 使用 ID 作为主键，使用结构体名的 蛇形复数 作为表名，字段名的 蛇形 作为列名，并使用 CreateAt,UpdateAt字段追踪创建和更新时间
3.2.1 使用 ID 作为主键
默认情况下， GORM 会使用 ID 作为表的主键
type User struct {
ID string
Name string
}
可以使用标签 primaryKey 将其他字段作为主键
type Animal struct {
ID int64
UUID string `gorm:"primaryKey"`
Name string
Age int64
}
可以通过将多个字段设为主键，以创建复合主键，例如：
type Product struct {
ID string `gorm:"primaryKey"`
LanguageCode string `gorm:"primaryKey"`
Code string
Name string
}
注意：默认情况下，整型 PrioritizedPrimaryField启用了 AutoIncrement, 要禁用它，需要为整型字段关闭 autoIncrement
type Product struct {
CategoryID uint64 `gorm:"primaryKey;autoIncrement:false"`
TypeID uint64 `gorm:"primaryKey;autoIncrement:false"`
}
3.2.2 复数表名
GORM 使用结构体名的 蛇形命名 作为表名，对于结构体 User, 其表名为 users
可以通过实现 Tabler 结构来更改默认表名：
type Tabler interface {
TableName() string
}
func (User) TableName() string {
return "profiles"
}
TableName 会将表名重写为 profiles
注意：TableName 不支持动态变化，它会被缓存下来以便后续使用，想要使用动态表名可以使用Scopes
func UserTable(user User) func (tx *gorm.DB) *gorm.DB {
return func (tx *gorm.DB) *gorm.DB {
if user.Admin {
return tx.Table("admin_users")
}
return tx.Table("users")
}
}
db.Scopes(UserTable(user)).Create(&user)
3.2.3 指定临时表名
可以使用 Table方法指定临时表名，例如：
// 根据 User 的字段创建 `deleted_users` 表
db.Table("deleted_users").AutoMigrate(&User{})
// 从另一张表查询数据
var deletedUsers []User
db.Table("deleted_users").Find(&deletedUsers)
// SELECT * FROM deleted_users;
db.Table("deleted_users").Where("name = ?", "jinzhu").Delete(&User{})
// DELETE FROM deleted_users WHERE name = 'jinzhu';
3.2.4 命名策略
GORM 允许用户通过覆盖默认的 NamingStrategy 更改默认的命名约定，需要实现 Namer 接口
type Namer interface {
TableName(table string) string
SchemaName(table string) string
ColumnName(table, column string) string
JoinTableName(table string) string
RelationshipFKName(Relationship) string
CheckerName(table, column string) string
IndexName(table, column string) string
}
默认的 NamingStrategy 也提供了几个选项
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
NamingStrategy: schema.NamingStrategy{
TablePrefix: "t_", // 表名前缀，`User`表为`t_users`
SingularTable: true, // 使用单数表名，启用该选项后，`User` 表将是`user`
NameReplacer: strings.NewReplacer("CID", "Cid"), // 在转为数据库名称之前，使用NameReplacer更改结构/字段名称。
},
})
3.2.5 列名
根据约定，数据表的列名是使用字段名的蛇形命名
type User struct {
ID uint // 列名是 `id`
Name string // 列名是 `name`
Birthday time.Time // 列名是 `birthday`
CreatedAt time.Time // 列名是 `created_at`
}
可以使用 column 标签或 NamingStrategy 来覆盖列名
type Animal struct {
AnimalID int64 `gorm:"column:beast_id"` // 将列名设为 `beast_id`
Birthday time.Time `gorm:"column:day_of_the_beast"` // 将列名设为 `day_of_the_beast`
Age int64 `gorm:"column:age_of_the_beast"` // 将列名设为 `age_of_the_beast`
}
3.2.6 时间戳追踪
CreatedAt
对于有 CreatedAt 字段的模型，创建记录时，若该字段为零值则将其设置为当前时间
db.Create(&user) // 将 `CreatedAt` 设为当前时间
user2 := User{Name: "jinzhu", CreatedAt: time.Now()}
db.Create(&user2) // user2 的 `CreatedAt` 不会被修改
// 想要修改该值，您可以使用 `Update`
db.Model(&user).Update("CreatedAt", time.Now())
UpdatedAt
对于有 UpadtedAt 字段的模型，更新记录时，将该字段的值设置为当前时间，创建记录时，若该字段为零值，则将其值设置为当前时间
db.Save(&user) // set `UpdatedAt` to current time
db.Model(&user).Update("name", "jinzhu") // will set `UpdatedAt` to current time
db.Model(&user).UpdateColumn("name", "jinzhu") // `UpdatedAt` won't be changed
user2 := User{Name: "jinzhu", UpdatedAt: time.Now()}
db.Create(&user2) // user2's `UpdatedAt` won't be changed when creating
user3 := User{Name: "jinzhu", UpdatedAt: time.Now()}
db.Save(&user3) // user3's `UpdatedAt` will change to current time when updating
3.3 gorm.Model
GORM 定义一个 gorm.Model结构体，其包括字段 ID,CreatedAt,UpdatedAt,DeletedAt
type Model struct {
ID uint `gorm:"primaryKey"`
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt gorm.DeletedAt `gorm:"index"`
}
可以将其嵌入结构体中以包含这些字段
3.4 高级选项
3.4.1 字段级权限控制
导出的字段在使用 GORM 进行 CRUD 操作时拥有全部权限，GORM 可以为使用标签控制字段的权限，如让字段只读，只写，只创建或被忽略（ GORM Migrator 创建表时，不会创建被忽略的字段）
type User struct {
Name string `gorm:"<-:create"` // 允许读和创建
Name string `gorm:"<-:update"` // 允许读和更新
Name string `gorm:"<-"` // 允许读和写（创建和更新）
Name string `gorm:"<-:false"` // 允许读，禁止写
Name string `gorm:"->"` // 只读（除非有自定义配置，否则禁止写）
Name string `gorm:"->;<-:create"` // 允许读和写
Name string `gorm:"->:false;<-:create"` // 仅创建（禁止从 db 读）
Name string `gorm:"-"` // 通过 struct 读写会忽略该字段
}
3.4.2 创建/更新时间追踪
GORM 约定使用 CreatedAt、UpdatedAt 追踪创建/更新时间。如果定义了这种字段，GORM 在创建、更新时会自动填充当前时间
要使用不同名称的字段，可以配置 autoCreateTime、autoUpdateTime 标签
如果想要保存 UNIX（毫/纳）秒时间戳，而不是 time，您只需简单地将 time.Time 修改为 int 即可
type User struct {
CreatedAt time.Time // Set to current time if it is zero on creating
UpdatedAt int // Set to current unix seconds on updating or if it is zero on creating
Updated int64 `gorm:"autoUpdateTime:nano"` // Use unix nano seconds as updating time
Updated int64 `gorm:"autoUpdateTime:milli"`// Use unix milli seconds as updating time
Created int64 `gorm:"autoCreateTime"` // Use unix seconds as creating time
}
3.4.3 嵌入结构体
对于匿名字段，GORM 会将其字段包含在父结构体中，例如：
type User struct {
gorm.Model
Name string
}
// 等效于
type User struct {
ID uint `gorm:"primaryKey"`
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt gorm.DeletedAt `gorm:"index"`
Name string
}
对于正常的结构体字段，你也可以通过标签 embedded 将其嵌入，例如：
type Author struct {
Name string
Email string
}
type Blog struct {
ID int
Author Author `gorm:"embedded"`
Upvotes int32
}
// 等效于
type Blog struct {
ID int64
Name string
Email string
Upvotes int32
}
可以使用标签 embeddedPrefix 来为 db 中的字段名添加前缀，例如：
type Blog struct {
ID int
Author Author `gorm:"embedded;embeddedPrefix:author_"`
Upvotes int32
}
// 等效于
type Blog struct {
ID int64
AuthorName string
AuthorEmail string
Upvotes int32
}
3.4.4 字段标签
声明 model 时，tag 是可选的，GORM 支持以下 tag： tag 名大小写不敏感，但建议使用 camelCase 风格
| 标签名 | 说明 |
|---|---|
| column | 指定 db 列名 |
| type | 列数据类型，推荐使用兼容性好的通用类型，例如：所有数据库都支持 bool、int、uint、float、string、time、bytes 并且可以和其他标签一起使用，例如：not null、size, autoIncrement… 像 varbinary(8) 这样指定数据库数据类型也是支持的。在使用指定数据库数据类型时，它需要是完整的数据库数据类型，如：MEDIUMINT UNSIGNED not NULL AUTO_INCREMENT |
| size | 指定列大小，例如：size:256 |
| primaryKey | 指定列为主键 |
| unique | 指定列为唯一 |
| default | 指定列的默认值 |
| precision | 指定列的精度 |
| scale | 指定列大小 |
| not null | 指定列为 NOT NULL |
| autoIncrement | 指定列为自动增长 |
| autoIncrementIncrement | 自动步长，控制连续记录之间的间隔 |
| embedded | 嵌套字段 |
| embeddedPrefix | 嵌入字段的列名前缀 |
| autoCreateTime | 创建时追踪当前时间，对于 int 字段，它会追踪秒级时间戳，您可以使用 nano/milli 来追踪纳秒、毫秒时间戳，例如：autoCreateTime:nano |
| autoUpdateTime | 创建/更新时追踪当前时间，对于 int 字段，它会追踪秒级时间戳，您可以使用 nano/milli 来追踪纳秒、毫秒时间戳，例如：autoUpdateTime:milli |
| index | 根据参数创建索引，多个字段使用相同的名称则创建复合索引，查看 索引 获取详情 |
| uniqueIndex | 与 index 相同，但创建的是唯一索引 |
| check | 创建检查约束，例如 check:age > 13，查看 约束 获取详情 |
| <- | 设置字段写入的权限， <-:create 只创建、<-:update 只更新、<-:false 无写入权限、<- 创建和更新权限 |
| -> | 设置字段读的权限，->:false 无读权限 |
| - | 忽略该字段，- 无读写权限 |
| comment | 迁移时为字段添加注释 |
3.4.5 关联标签
GORM 允许通过标签为关联配置外键、约束、many2many 表
4. Connecting to Database
GORM 官方支持的数据库类型有： MySQL, PostgreSQL, SQlite, SQL Server
4.1 MySQL
import (
"gorm.io/driver/mysql"
"gorm.io/gorm"
)
func main() {
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{})
}
**注意：**想要正确的处理 time.Time ，需要带上 parseTime 参数， (更多参数) 要支持完整的 UTF-8 编码，您需要将 charset=utf8 更改为 charset=utf8mb4 查看 此文章 获取详情
MySQl 驱动程序提供了 一些高级配置 可以在初始化过程中使用，例如：
db, err := gorm.Open(mysql.New(mysql.Config{
DSN: "gorm:gorm@tcp(127.0.0.1:3306)/gorm?charset=utf8&parseTime=True&loc=Local", // DSN data source name
DefaultStringSize: 256, // string 类型字段的默认长度
DisableDatetimePrecision: true, // 禁用 datetime 精度，MySQL 5.6 之前的数据库不支持
DontSupportRenameIndex: true, // 重命名索引时采用删除并新建的方式，MySQL 5.7 之前的数据库和 MariaDB 不支持重命名索引
DontSupportRenameColumn: true, // 用 `change` 重命名列，MySQL 8 之前的数据库和 MariaDB 不支持重命名列
SkipInitializeWithVersion: false, // 根据当前 MySQL 版本自动配置
}), &gorm.Config{})
4.1.2 自定义驱动
GORM 允许通过 DriverName 选项自定义 MySQL 驱动，例如：
import (
_ "example.com/my_mysql_driver"
"gorm.io/gorm"
)
db, err := gorm.Open(mysql.New(mysql.Config{
DriverName: "my_mysql_driver",
DSN: "gorm:gorm@tcp(localhost:9910)/gorm?charset=utf8&parseTime=True&loc=Local", // Data Source Name，参考 https://github.com/go-sql-driver/mysql#dsn-data-source-name
}), &gorm.Config{})
4.1.3 现有的数据库连接
GORM 允许通过一个现有的数据库连接来初始化 *gorm.DB
import (
"database/sql"
"gorm.io/gorm"
)
sqlDB, err := sql.Open("mysql", "mydb_dsn")
gormDB, err := gorm.Open(mysql.New(mysql.Config{
Conn: sqlDB,
}), &gorm.Config{})
4.2 PostgreSQL
import (
"gorm.io/driver/postgres"
"gorm.io/gorm"
)
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{})
我们使用 pgx 作为 postgres 的 database/sql 驱动，默认情况下，它会启用 prepared statement 缓存，你可以这样禁用它：
// https://github.com/go-gorm/postgres
db, err := gorm.Open(postgres.New(postgres.Config{
DSN: "user=gorm password=gorm dbname=gorm port=9920 sslmode=disable TimeZone=Asia/Shanghai",
PreferSimpleProtocol: true, // disables implicit prepared statement usage
}), &gorm.Config{})
4.2.1 自定义驱动
GORM 允许通过 DriverName 选项自定义 PostgreSQL 驱动，例如：
import (
_ "github.com/GoogleCloudPlatform/cloudsql-proxy/proxy/dialers/postgres"
"gorm.io/gorm"
)
db, err := gorm.Open(postgres.New(postgres.Config{
DriverName: "cloudsqlpostgres",
DSN: "host=project:region:instance user=postgres dbname=postgres password=password sslmode=disable",
})
4.2.2 现有的数据库连接
GORM 允许通过一个现有的数据库连接来初始化 *gorm.DB
import (
"database/sql"
"gorm.io/gorm"
)
sqlDB, err := sql.Open("postgres", "mydb_dsn")
gormDB, err := gorm.Open(postgres.New(postgres.Config{
Conn: sqlDB,
}), &gorm.Config{})
4.3 SQLite
import (
"gorm.io/driver/sqlite"
"gorm.io/gorm"
)
// github.com/mattn/go-sqlite3
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{})
注意： 您也可以使用
file::memory:?cache=shared替代文件路径。 这会告诉 SQLite 在系统内存中使用一个临时数据库。 (查看 SQLite 文档 获取详情)
4.4 SQL Server
import (
"gorm.io/driver/sqlserver"
"gorm.io/gorm"
)
// github.com/denisenkom/go-mssqldb
dsn := "sqlserver://gorm:LoremIpsum86@localhost:9930?database=gorm"
db, err := gorm.Open(sqlserver.Open(dsn), &gorm.Config{})
4.5 Clickhouse
https://github.com/go-gorm/clickhouse
import (
"gorm.io/driver/clickhouse"
"gorm.io/gorm"
)
func main() {
dsn := "tcp://localhost:9000?database=gorm&username=gorm&password=gorm&read_timeout=10&write_timeout=20"
db, err := gorm.Open(clickhouse.Open(dsn), &gorm.Config{})
// Auto Migrate
db.AutoMigrate(&User{})
// Set table options
db.Set("gorm:table_options", "ENGINE=Distributed(cluster, default, hits)").AutoMigrate(&User{})
// 插入
db.Create(&user)
// 查询
db.Find(&user, "id = ?", 10)
// 批量插入
var users = []User{user1, user2, user3}
db.Create(&users)
// ...
}
4.6 连接池
GORM 使用 database/sql 维护连接池
sqlDB, err := db.DB()
// SetMaxIdleConns 设置空闲连接池中连接的最大数量
sqlDB.SetMaxIdleConns(10)
// SetMaxOpenConns 设置打开数据库连接的最大数量。
sqlDB.SetMaxOpenConns(100)
// SetConnMaxLifetime 设置了连接可复用的最大时间。
sqlDB.SetConnMaxLifetime(time.Hour)
查看 通用接口 获取详情。
5. CRUD
5.1 Create
5.1.1 创建记录
package main
import (
"fmt"
"log"
"time"
"gorm.io/gorm/logger"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
)
type User struct {
gorm.Model
Name string
Age int
Birthday time.Time
}
const (
timeFormat = "2006-01-02 15:04:05.000"
)
func (u User) String() string {
return fmt.Sprintf("ID: %d, Name: %s, Age: %d, Birthday: %s\nCreatedAy: %s, UpadtedAt: %s, DeletedAt: %s",
u.ID, u.Name, u.Age, u.Birthday.Format(timeFormat), u.CreatedAt.Format(timeFormat), u.UpdatedAt.Format(timeFormat),
u.DeletedAt.Time.Format(timeFormat))
}
func main() {
db, err := gorm.Open(sqlite.Open("create.db"), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
})
if err != nil {
panic("failed to connect database")
}
sqlDB, _ := db.DB()
defer sqlDB.Close()
db.AutoMigrate(&User{})
user := User{Name: "kesa", Age: 18, Birthday: time.Now()}
result := db.Create(&user)
log.Printf("New record ID: %d", user.ID)
log.Printf("Error %v", result.Error)
log.Printf("Rows affected: %d", result.RowsAffected)
}
db.Create: 通过数据的指针创建记录,会将插入的数据的主键写入到对象中，所以这里必须是指针类型result.Error: 执行错误，nil 为成功result.RowsAffected: 影响记录数
result 的类型为 tx *DB:
// DB GORM DB definition
type DB struct {
*Config
Error error
RowsAffected int64
Statement *Statement
clone int
}
5.1.2 用指定字段创建记录
// ...
func main() {
// ...
user := User{Name: "kesa", Age: 18, Birthday: time.Now()}
result := db.Select("Name", "Age", "CreatedAt").Create(&user)
log.Printf("New record ID: %d", user.ID)
log.Printf("Error %v", result.Error)
log.Printf("Rows affected: %d", result.RowsAffected)
user1 := User{Name: "kesa", Age: 18, Birthday: time.Now()}
result = db.Omit("Name", "Age", "CreatedAt").Create(&user1)
log.Printf("New record ID: %d", user.ID)
log.Printf("Error %v", result.Error)
log.Printf("Rows affected: %d", result.RowsAffected)
}
Select: 创建记录并更新指定字段omit: 创建记录但忽略指定字段
执行程序，并查询数据：
sqlite> select * from users where id=11 or id = 12;
id created_at updated_at deleted_at name age birthday
-- ----------------------------------- ----------------------------------- ---------- ---- --- -----------------------------------
11 2021-11-25 17:29:25.000032484+08:00 2021-11-25 17:29:25.000032484+08:00 kesa 18
12 2021-11-25 17:29:26.071051452+08:00 2021-11-25 17:29:26.070793799+08:00
5.1.3 批量插入
批量插入数据可以将slice作为参数传给Create方法， GORM 将会单独生成一条 SQL 来执行并回填主键值，钩子方法也将被调用
// ...
func main() {
// ...
var users = []User{{Name: "user_1"}, {Name: "user_2"}, {Name: "user_3"}}
result := db.Create(&users)
log.Printf("Rows affected: %d,Error: %s", result.RowsAffected, result.Error.Error())
for _, user := range users {
log.Printf("Inserted ID: %d", user.ID)
}
}
通过 GORM 的日志可以看到，Create 方法通过一条 SQL 插入了所有数据
INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`,`birthday`) VALUES ("2021-11-25 17:54:34.823","2021-11-25 17:54:34.823",NULL,"user_1",0,"0000-00-00 00:00:00"),("2021-11-25 17:54:34.823","2021-11-25 17:54:34.823",NULL,"user_2",0,"0000-00-00 00:00:00"),("2021-11-25 17:54:34.823","2021-11-25 17:54:34.823",NULL,"user_3",0,"0000-00-00 00:00:00") RETURNING `id`
使用CreateInBatches进行分批创建，可以指定每批的数量
var users1 = []User{{Name: "user_1"}, {Name: "user_2"}, {Name: "user_3"}, {Name: "user_4"}, {Name: "user_5"}, {Name: "user_6"}}
result = db.CreateInBatches(users1, 2)
log.Printf("Rows affected: %d,Error: %v", result.RowsAffected, result.Error)
for _, user := range users1 {
log.Printf("Inserted ID: %d", user.ID)
}
启动后查看 GORM 日志可以看到 SQL 分为了三条执行
Upsert 和 Create With Associations也支持批量插入
注意
使用 CreateBatchSize 选项初始化 GORM 时，所有的创建和关联 INSERT 遵循该选项
修改上例中的 GORM 配置，添加 CreateBatchSize 为 3
db, err := gorm.Open(sqlite.Open("create-in-batches.db"), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
CreateBatchSize: 3,
})
// ...
var users = []User{{Name: "user_1"}, {Name: "user_2"}, {Name: "user_3"}}
result := db.Create(&users)
log.Printf("Rows affected: %d,Error: %s", result.RowsAffected, result.Error.Error())
for _, user := range users {
log.Printf("Inserted ID: %d", user.ID)
}
// ...
再次执行程序可以看到 Create 语句插入三条记录时只会执行一条 SQL 了
5.1.4 钩子函数
GORM 创建相关的钩子函数有 BeforeSave, BeforeCreate, AfterSave, AfterCreate ，创建记录时将调用这些钩子函数
钩子（Hook）是在创建，查询，更新，删除等操作之前，之后调用的函数
若函数返回错误，GORM 将停止后续的操作并回滚事务
钩子函数的签名为 func(*gorm.DB) error
创建操作时 hook 的调用顺序如下：
// 开始事务
BeforeSave
BeforeCreate
// 关联前的 save
// 插入记录至 db
// 关联后的 save
AfterCreate
AfterSave
// 提交或回滚事务
示例：
// ...
func (User) BeforeCreate(db *gorm.DB) error {
log.Println("Before Create")
return nil
}
func (User) BeforeSave(db *gorm.DB) error {
log.Println("Before Save")
return nil
}
func (User) AfterCreate(db *gorm.DB) error {
log.Println("After Create")
return nil
}
func (u User) AfterSave(db *gorm.DB) error {
log.Println("After Save")
if u.Age < 21 {
return errors.New("illegal age,roll back")
}
return nil
}
func main() {
//...
user := User{Name: "kesa", Age: 18, Birthday: time.Now()}
result := db.Create(&user)
log.Printf("Rows affected: %d, Error: %v", result.RowsAffected, result.Error)
log.Printf("New record ID: %d", user.ID)
}
上例在 AfterSave 钩子中加入了返回错误的条件，此时再次插入数据，将会返回错误，GORM 会将事务回滚
查询数据库可以看到数据库是没有数据的
5.1.5 根据 Map 创建
GORM 支持根据 map[string]interface{} 和 []map[string]interface{} 创建记录
data := map[string]interface{}{
"Name": "kesa-map",
"Age": 18,
}
result := db.Model(&User{}).Create(data)
log.Printf("Error %v", result.Error)
log.Printf("Rows affected: %d", result.RowsAffected)
datas := []map[string]interface{}{
{"Name": "kesa_map_1", "Age": 10},
{"Name": "kesa_map_2", "Age": 11},
{"Name": "kesa_map_3", "Age": 12},
}
result = db.Model(&User{}).Create(datas)
log.Printf("Error %v", result.Error)
log.Printf("Rows affected: %d", result.RowsAffected)
注意
使用 map 创建记录时， association 不会调用，map 中未涉及的字段也不会被更新
TODO:: 5.1.6 使用 SQL 表达式 Context Valuer 创建记录
// TODO
5.1.7 关联创建
创建关联数据时，若关联值为非零值，这些关联会被 upsert ，且它们的 Hook 方法也会被调用
package main
import (
"log"
"gorm.io/gorm/clause"
"gorm.io/gorm/logger"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
)
type User struct {
gorm.Model
Name string
CreditCard CreditCard
}
type CreditCard struct {
gorm.Model
Number string
UserID uint
}
func main() {
// ... db connection
db.AutoMigrate(&User{})
db.AutoMigrate(&CreditCard{})
user := User{Name: "kesa", CreditCard: CreditCard{Number: "12346"}}
result := db.Create(&user)
log.Printf("New user ID: %d, CreditCard ID: %d", user.ID, user.CreditCard.ID)
log.Printf("Rows affected: %d, Error: %v", result.RowsAffected, result.Error)
user1 := User{Name: "kesa", CreditCard: CreditCard{Number: "78910"}}
result = db.Omit(clause.Associations).Create(&user1)
log.Printf("New user ID: %d, CreditCard ID: %d", user1.ID, user1.CreditCard.ID)
log.Printf("Rows affected: %d, Error: %v", result.RowsAffected, result.Error)
}
db.Omit(clause.Associations).Create(&user1): 跳过所有的关联
也以通过 Select,Omit 方法来跳过关联保存
5.1.8 默认值
可以通过标签 default 为字段定义默认值
type User struct {
ID int64
Name string `gorm:"default:galeone"`
Age int64 `gorm:"default:18"`
}
插入记录到数据库时，默认值会被用于填充值为零值的字段
注意
像0,'',false等零值，不会将这些字段定义的默认值保存到数据库，需要使用指针类型或 Scanner/Valuer 来避免这个问题，例如：
type User struct {
gorm.Model
Name string
Age *int `gorm:"default:18"`
Active sql.NullBool `gorm:"default:true"`
}
若要数据库有默认值，必须为字段设置 default 标签，使用 default:(-) 在迁移时跳过默认值定义
type User struct {
ID string `gorm:"default:uuid_generate_v3()"` // db func
FirstName string
LastName string
Age uint8
FullName string `gorm:"->;type:GENERATED ALWAYS AS (concat(firstname,' ',lastname));default:(-);"`
}
使用 virtual/generate 值时，需要禁用其创建，更新的权限
5.1.9 Upset 及冲突
GORM 为不同数据库提供了兼容的 Upsert 支持
import "gorm.io/gorm/clause"
// 在冲突时，什么都不做
db.Clauses(clause.OnConflict{DoNothing: true}).Create(&user)
// 在`id`冲突时，将列更新为默认值
db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "id"}},
DoUpdates: clause.Assignments(map[string]interface{}{"role": "user"}),
}).Create(&users)
// MERGE INTO "users" USING *** WHEN NOT MATCHED THEN INSERT *** WHEN MATCHED THEN UPDATE SET ***; SQL Server
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE ***; MySQL
// 使用SQL语句
db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "id"}},
DoUpdates: clause.Assignments(map[string]interface{}{"count": gorm.Expr("GREATEST(count, VALUES(count))")}),
}).Create(&users)
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `count`=GREATEST(count, VALUES(count));
// 在`id`冲突时，将列更新为新值
db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "id"}},
DoUpdates: clause.AssignmentColumns([]string{"name", "age"}),
}).Create(&users)
// MERGE INTO "users" USING *** WHEN NOT MATCHED THEN INSERT *** WHEN MATCHED THEN UPDATE SET "name"="excluded"."name"; SQL Server
// INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age"; PostgreSQL
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `name`=VALUES(name),`age=VALUES(age); MySQL
// 在冲突时，更新除主键以外的所有列到新值。
db.Clauses(clause.OnConflict{
UpdateAll: true,
}).Create(&users)
// INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age", ...;
5.2 Read
5.2.1 检索单个对象
GORM 提供了 First, Take, Last 方法，以便从数据库中检索单个对象。
查询数据库时会添加 LIMIT 1 条件，若未找到记录会返回 ErrRecordNotFound
package main
import (
"log"
"strconv"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
"gorm.io/gorm/logger"
)
type User struct {
ID uint `gorm:"primaryKey"`
Name string
Age int
}
func main() {
db, err := gorm.Open(sqlite.Open("single-obj.db"), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
})
if err != nil {
log.Fatal("failed to connect db: ", err.Error())
}
sqlDB, _ := db.DB()
defer sqlDB.Close()
db.AutoMigrate(&User{})
CreateUsers(db)
var user, user1, user2 User
db.First(&user)
db.Take(&user1)
db.Last(&user2)
log.Printf("First user: %+v", user)
log.Printf("One of users: %+v", user1)
log.Printf("Last user: %+v", user2)
}
func CreateUsers(db *gorm.DB) {
users := make([]User, 10)
for i := 1; i < 11; i++ {
name := "user_" + strconv.Itoa(i)
age := 10 + i
users[i-1] = User{Name: name, Age: age}
}
db.Create(&users)
}
First: 获取第一条记录，主键升序 SQL: SELECT * FROM users ORDER BY id LIMIT 1;Take: 获取一条记录，无排序字段 SQL: SELECT * FROM users LIMIT 1;Last:获取最后一条记录，主键降序 SQL: SELECT * FROM users ORDER BY id DESC LIMIT 1;
若想要避免 ErrRecordNotFound 错误，可以使用 Find,可以接收 strcut 或 slice 参数
First 和 Last 会根据主键排序，分别查询第一条和最后一条记录，只有在 struct 是指针或通过 db.Model()指定 model 时才有效，若相关的 model 没有定义主键将按照第一个字段排序
// ...
func main() {
// ... db init
var user3 User
// SELECT * FROM users ORDER BY id LIMIT 1;
db.First(&user3)
// SELECT * FROM users ORDER BY id LIMIT 1;
ret := map[string]interface{}{}
db.Model(&User{}).First(&ret)
// invalid
ret1 := map[string]interface{}{}
db.Table("users").First(&ret1)
// SELECT * FROM users LIMIT 1;
ret2 := map[string]interface{}{}
db.Table("users").Take(&ret2)
// SELECT * FROM languages ORDER BY code LIMIT 1;
type Language struct {
Code string
Name string
}
db.First(&Language{})
}
5.2.2 用主键检索
若主键是数字型，可以使用内联条件检索，传入字符串参数时，需要注意 SQL 注入问题
// ...
func main() {
// ...
// SELECT * FROM users WHERE id = 10 ORDER BY id LIMIT 1;
var user User
db.First(&user, 10)
log.Printf("Record: %+v", user)
// SELECT * FROM users WHERE id = 10 ORDER BY id LIMIT 1;
var user2 User
db.First(&user2, "10")
log.Printf("Record: %+v", user2)
// SELECT * FROM users WHERE id IN (1,2,3)
var users []User
db.Find(&users, []int{1, 2, 3})
log.Printf("Record: %+v", users)
}
如果主键是字符串型：
// SELECT * FROM users WHERE id = '1b74413f-f3b8-409f-ac47-e8c062e3472a'
db.First(&user,"id = ?","1b74413f-f3b8-409f-ac47-e8c062e3472a")
5.2.3 检索全部对象
使用 Find 可以查询所有数据
result := db.Find(&users)
reuslt.RowsAffected 返回找到的记录数，result.Error返回出现的错误
5.2.4 条件查询
String
// ...
type User struct {
ID uint `gorm:"primaryKey"`
Name string
Age int
}
func main() {
// ...
db.AutoMigrate(&User{})
CreateUsers(db, 10)
// String
StringCondition(db)
}
func CreateUsers(db *gorm.DB, num int) {
var count int64
db.Model(&User{}).Count(&count)
if count > 0 {
return
}
users := make([]User, num)
for i := 0; i < num; i++ {
name := "user_" + strconv.Itoa(i+1)
age := 10 + i
users[i] = User{Name: name, Age: age}
}
db.Create(&users)
}
func PrintRecord(record interface{}) {
switch val := record.(type) {
case User:
log.Printf("Record: %+v", val)
case []User:
log.Printf("Records: %+v", val)
}
}
func StringCondition(db *gorm.DB) {
// SELECT * FROM users WHERE name = 'user' ORDER BY id LIMIT 1;
var user User
db.Where("name = ?", "user").First(&user)
PrintRecord(user)
// SELECT * FROM users WHERE name <> 'user';
var users []User
db.Where("name <> ?", "user").Find(&users)
PrintRecord(users)
// SELECT * FROM users WHERE name IN ('user_1','user_2','user_3')
var users1 []User
db.Where("name IN ?", []string{"user_1", "user_2", "user_3"}).Find(&users1)
PrintRecord(users1)
// SELECT * FROM users WHERE name LIKE '%user%';
var users3 []User
db.Where("name LIKE ?", "%user%").Find(&users3)
PrintRecord(users3)
// SELECT * FROM users WHERE name = 'user_1' AND age = 11;
var users4 []User
db.Where("name = ? AND age = ?", "user_1", 11).Find(&users4)
PrintRecord(users4)
// SELECT * FROM users WHERE age <= 10;
var users5 []User
db.Where("age <= ?", 15).Find(&users5)
PrintRecord(users5)
// SELECT * FROM users WHERE age BETWEEN 10 AND 16;
var users6 []User
db.Where("age BETWEEN ? AND ?", 10, 16).Find(&users6)
PrintRecord(users6)
}
Where 方法中可以直接使用 SQL 和占位符来构成条件查询
Struct and Map
// ...
func main() {
// ...
// Struct and Map
StructMapCondition(db)
}
// ...
func StructMapCondition(db *gorm.DB) {
// Struct
// SELECT * FROM users WHERE name = 'user_1' AND age = 10 ORDER BY id LIMIT 1;
var user User
db.Where(&User{Name: "user_1", Age: 10}).First(&user)
PrintRecord(user)
// SELECT * FROM users WHERE name = 'user_2' AND age = 11;
var user2 User
cond := map[string]interface{}{"Name": "user_2", "Age": 11}
db.Where(cond).Find(&user2)
PrintRecord(user2)
// SELECT * FROM users WHERE id IN (1,2,3)
var users []User
db.Where([]int{1, 2, 3}).Find(&users)
PrintRecord(users)
// SELECT * FROM users WHERE name = 'user_1' LIMIT 1;
var user3 User
db.Where(&User{Name: "user_1", Age: 0}).Take(&user3)
PrintRecord(user3)
// SELECT * FROM users WHERE name = 'user_2' AND age = 0 LIMIT 1;
var user4 User
db.Where(map[string]interface{}{"Name": "user_2", "Age": 0}).Take(&user4)
PrintRecord(user4)
// SELECT * FROM users WHERE name = 'user_3' AND age = 12 LIMIT 1;
var user5 User
db.Where(&User{Name: "user_3", Age: 12}, "name", "age").Take(&user5)
PrintRecord(user5)
// SELECT * FROM users WHERE age = 0 LIMIT 1;
var user6 User
db.Where(&User{Name: "user_3"}, "age").Take(&user6)
PrintRecord(user6)
}
也可以使用 Struct 和 Map 来构建查询条件
注意： 当使用 Struct 作为查询条件时，只能查询非零值字段，零值字段将不会用于构建查询条件
若需要使用零值字段查询，可以使用 Map 或 指定 Struct 查询字段
db.Where(map[string]interface{}{"Name": "user_2", "Age": 0}):使用 map 构建查询条件db.Where(&User{Name: "user_3"}, "age"): 指定 Struct 字段构建查询条件
内联条件
// ...
func main() {
// ...
// Inline Conditions
// SELECT * FROM users WHERE id = 1 ORDER BY id LIMIT 1;
var user User
db.First(&user, "id = ?", 1)
utils.PrintRecord(user)
// SELECT * FROM users WHERE name = 'user_1' LIMIT 1;
var user1 User
db.Take(&user1, "name = ?", "user_1")
utils.PrintRecord(user1)
// SELECT * FROM users WHERE name <> 'user_2' AND age >= 15;
var users []User
db.Find(&users, "name <> ? AND age >= ?", "user_2", 15)
utils.PrintRecord(users)
// SELECT * FROM users WHERE age = 16 LIMIT 1;
var user2 User
db.Take(&user2, User{Age: 16})
utils.PrintRecord(user2)
// SELECT * FROM users WHERE age = 13 LIMIT 1;
var user3 User
db.Take(&user3, map[string]interface{}{"Age": 13})
utils.PrintRecord(user3)
}
// ...
Not&Or Conditions
// ...
func main() {
// ...
// Not Conditions
// SELECT * FROM users WHERE NOT name = 'user_1' LIMIT 1;
var users []User
db.Not("name = ?", "user_1").Find(&users)
utils.PrintRecord(users)
// SELECT * FROM users WHERE name NOT IN ('user_2','user_3','user_4');
var users1 []User
db.Not(map[string]interface{}{"name": []string{"user_2", "user_3", "user_4"}}).Find(&users1)
utils.PrintRecord(users1)
// SELECT * FROM users WHERE name <> 'user_5' AND age <> 11;
var users2 []User
db.Not(User{Name: "user_5", Age: 11}).Find(&users2)
utils.PrintRecord(users2)
// SELECT * FROM users WHERE id NOT IN (1,2,3);
var users3 []User
db.Not([]int64{1, 2, 3}).Find(&users3)
utils.PrintRecord(users3)
// Or Conditions
// SELECT * FROM users WHERE name = 'user_1' OR age = 16;
var users4 []User
db.Where("name = ?", "user_1").Or("age = ?", 16).Find(&users4)
utils.PrintRecord(users4)
// SELECT * FROM user WHERE name = 'user_2' OR (name = 'user_3' AND age = 13) ;
var users5 []User
db.Where("name = 'user_2'").Or(User{Name: "user_3", Age: 13}).Find(&users5)
utils.PrintRecord(users5)
// SELECT * FROM users WHERE age = 10 OR (name = 'user_0' AND age = 10);
var users6 []User
db.Where("age = ?", 10).Or(map[string]interface{}{"name": "user_0", "age": 10}).Find(&users6)
utils.PrintRecord(users6)
}
// ...
Not和 Or 和Where 的构造类似
5.2.5 选择特定字段
使用 Select 可以指定查询字段，默认情况会查询所有的字段
// ...
func main() {
// ...
// SELECT name FROM users ;
var users []User
db.Select("name").Find(&users)
utils.PrintRecord(users)
// SELECT name,age FROM users ;
var users2 []User
db.Select([]string{"name", "age"}).Find(&users2)
utils.PrintRecord(users2)
}
// ...
5.2.6 Order
指定查询的排列方式
// ...
func main() {
// ...
// SELECT name,age FROM users ORDER BY age desc, name ;
var users []User
db.Select("name", "age").Order("age desc,name").Find(&users)
utils.PrintRecord(users)
// SELECT name,age FROM users ORDER BY age asc, name desc ;
var users2 []User
db.Select("name", "age").Order("age asc").Order("name desc").Find(&users2)
utils.PrintRecord(users2)
}
// ...
5.2.7 Limit&Offset
// ...
var (
UserAllFields = []string{"id", "name", "age"}
)
// ...
func main() {
// ...
// Limit&Offset
// SELECT id,name,age FROM users LIMIT 3 ;
var users []User
db.Select(UserAllFields).Limit(3).Find(&users)
utils.PrintRecord(users)
// SELECT id,name,age FROM users LIMIT 10 ;
// SELECT id,name,age FROM users ;
var users1, users2 []User
db.Select(UserAllFields).Limit(5).Find(&users1).Limit(-1).Find(&users2)
utils.PrintRecord(users1, users2)
// SELECT id,name,age FROM users OFFSET 3 ;
var users3 []User
db.Select(UserAllFields).Offset(3).Find(&users3)
utils.PrintRecord(users3)
// SELECT id,name,age FROM users OFFSET 3 LIMIT 4 ;
var users4 []User
db.Select(UserAllFields).Offset(3).Limit(4).Find(&users4)
utils.PrintRecord(users4)
// SELECT id,name,age FROM users OFFSET 3;
// SELECT id,name,age FROM users ;
var users5, users6 []User
db.Select(UserAllFields).Offset(3).Find(&users5).Offset(-1).Find(&users6)
utils.PrintRecord(users5, users6)
}
Limit(-1):可取消 LIMIT 条件Offset(-1): 可取消 OFFSET 条件db.Select(UserAllFields).Offset(3).Find(&users5).Offset(-1).Find(&users6): GORM 可以进行多次查询，调用两次Find可以查询两次，并且可以修改查询条件
为了能够打印多个结果，修改utils:
package utils
import (
"log"
"reflect"
)
func PrintRecord(record ...interface{}) {
for _, r := range record {
printSingle(r)
}
}
func printSingle(record interface{}) {
v := reflect.ValueOf(record)
k := v.Kind()
switch k {
case reflect.Struct:
log.Printf("Record: %+v", v)
case reflect.Slice:
log.Printf("Records: %+v", v)
default:
log.Printf("%#v", v)
}
}
5.2.8 Group By & Having
// ...
var (
GroupAndAvgAge = []string{"group", "AVG(age) as avg_age"}
)
type User struct {
ID uint `gorm:"primaryKey"`
Name string
Group string
Age int
}
type GrpByResult struct {
Group string
AvgAge float64
}
func main() {
db, err := gorm.Open(sqlite.Open(DBName), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
})
if err != nil {
log.Fatal("connect db failed: ", err.Error())
}
sqlDB, _ := db.DB()
defer sqlDB.Close()
db.AutoMigrate(&User{})
CreateUsers(db, UserCount)
// Group By & Having
// SELECT group,AVG(age) as avg_age FROM users GROUP BY `group` ;
var results []GrpAvgAge
db.Model(&User{}).Select(GroupAndAvgAge).Group("group").Find(&results)
utils.PrintRecord(results)
// SELECT group,AVG(age) as avg_age FROM users GROUP BY `group` HAVING avg_age > 14 ;
var results1 []GrpAvgAge
db.Model(&User{}).Select(GroupAndAvgAge).Group("group").Having("avg_age > ?", 14).Find(&results1)
utils.PrintRecord(results1)
}
func CreateUsers(db *gorm.DB, num int) {
var count int64
db.Model(&User{}).Count(&count)
if count > 0 {
return
}
users := make([]User, num)
for i := 0; i < num; i++ {
grp := "group_" + strconv.Itoa(i%3)
name := "user_" + strconv.Itoa(i)
age := 10 + i
users[i] = User{Name: name, Age: age, Group: grp}
}
db.Create(&users)
}
5.2.9 Distinct
// ...
// Distinct
// SELECT DISTINCT name FROM users ;
var result []map[string]interface{}
db.Model(&User{}).Distinct("group").Find(&result)
utils.PrintRecord(result)
// ...
5.2.10 Joins
// ...
var (
GroupAndAvgAge = []string{"group", "AVG(age) as avg_age"}
)
type User struct {
ID uint `gorm:"primaryKey"`
Name string
Group string
Age int
Email Email
}
type Email struct {
ID uint `gorm:"primaryKey"`
UserID uint
Email string
}
func main() {
db, err := gorm.Open(sqlite.Open(DBName), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
})
if err != nil {
log.Fatal("connect db failed: ", err.Error())
}
sqlDB, _ := db.DB()
defer sqlDB.Close()
db.AutoMigrate(&User{})
db.AutoMigrate(&Email{})
CreateUsers(db, UserCount)
// JOINS
// SELECT users.name,emails.email FROM users INNER JOIN emails ON users.id = emails.user_id;
var results []map[string]interface{}
db.Model(&User{}).Select("users.name", "emails.email").Joins("INNER JOIN emails ON users.id = emails.user_id").Find(&results)
utils.PrintRecord(results)
}
func CreateUsers(db *gorm.DB, num int) {
var count int64
db.Model(&User{}).Count(&count)
if count > 0 {
return
}
users := make([]User, num)
for i := 0; i < num; i++ {
grp := "group_" + strconv.Itoa(i%3)
name := "user_" + strconv.Itoa(i)
age := 10 + i
email := name + "@example.com"
users[i] = User{Name: name, Age: age, Group: grp, Email: Email{Email: email}}
}
db.Create(&users)
}
5.2.11 Scan
Scan 和 Find 类似都是将结果解析至 struct/map 中,两者的区别在于 Find 会调用所有注册的钩子函数，而Scan则不会
5.2.12 智能选择字段
GORM 可以通过 Select 选择特定字段，也可以通过结构体来选择字段
type User struct {
ID uint
Name string
Age int
Gender string
// 假设后面还有几百个字段...
}
type APIUser struct {
ID uint
Name string
}
// 查询时会自动选择 `id`, `name` 字段
db.Model(&User{}).Limit(10).Find(&APIUser{})
// SELECT `id`, `name` FROM `users` LIMIT 10
5.2.13 Locking
GORM 支持多种类型的锁，例如：
db.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&users)
// SELECT * FROM `users` FOR UPDATE
db.Clauses(clause.Locking{
Strength: "SHARE",
Table: clause.Table{Name: clause.CurrentTable},
}).Find(&users)
// SELECT * FROM `users` FOR SHARE OF `users`
5.2.14 SubQuery
A subquery can be nested within a query, GORM can generate subquery when using a *gorm.DB object as param
// ...
// SubQuery
// SELECT name,age FROM users WHERE age > (SELECT AVG(age) FROM users) ;
var results []map[string]interface{}
subQuery := db.Model(&User{}).Select("AVG(age)")
db.Model(&User{}).Select("name", "age").Where("age > (?)", subQuery).Find(&results)
utils.PrintRecord(results)
// ...
5.2.15 From SubQuery
GORM allows you using subquery in FROM clause with method Table：
// SELECT * FROM (SELECT name,age FROM users) as u WHERE age < 15 ;
var result []map[string]interface{}
db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age < ?", 15).Find(&result)
utils.PrintRecord(result)
5.2.16 Group Conditions
Easier to write complicated SQL query with Group Conditions
// SELECT * FROM users WHERE (name = 'user_0' AND age = 10) OR (age > 15) ;
var results2 []map[string]interface{}
db.Model(&User{}).Where(
db.Where("name = ?", "user_0").Where("age = ?", 10),
).Or(
db.Where("age > 15"),
).Find(&results2)
utils.PrintRecord(results2)
5.2.17 IN with multiple colums
Selecting IN with multiple columns
// SELECT * FROM users WHERE (name,age) IN (('user_0', 10),('user_1', 11),('user_2', 12));
var results3 []map[string]interface{}
db.Model(&User{}).Where("(name,age) IN ?", [][]interface{}{{"user_0", 10}, {"user_1", 11}, {"user_2", 12}}).Find(results3)
utils.PrintRecord(results3)
注意：sqlite3 不支持上述写法，在 sqlite3 中要写成
SELECT * FROM users WHERE (name,age) IN (VALUES ('user_0', 10),('user_1', 11),('user_2', 12));
5.2.18 Named Argument
GORM supports named arguments with sql.NamedArg or map[string]interface{}
// SELECT * FROM users WHERE name = 'user_1' OR age = 16;
var results4 []map[string]interface{}
db.Model(&User{}).
Select("name", "age").
Where("name = @name OR age = @age", sql.Named("name", "user_0"), sql.Named("age", 16)).
Find(&results4)
utils.PrintRecord(results4)
// SELECT * FROM users WHERE name = 'user_1' OR age = 16;
var results5 []map[string]interface{}
db.Model(&User{}).
Select("name", "age").
Where("name = @name OR age = @age", map[string]interface{}{"name": "user_1", "age": 16}).
Find(&results5)
utils.PrintRecord(results5)
5.2.19 Find To Map
GORM allows scan result to map[string]interface{} or []map[string]interface{}, don't forget to specify Model or Table,
result := map[string]interface{}{}
db.Model(&User{}).First(&result, "id = ?", 1)
var results []map[string]interface{}
db.Table("users").Find(&results)
5.2.20 FirstOrInit
Get first matched record or initialize a new instance with given conditions (only works with struct or map conditions)
// FirstOrInit
var user User
db.FirstOrInit(&user, User{Name: "non_existing"})
utils.PrintRecord(user)
//
var user2 User
db.FirstOrInit(&user2, map[string]interface{}{"name": "user_0"})
utils.PrintRecord(user2)
initialize struct with more attributes if record not found, thoes Attrs won't be used to build SQL query
// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1 ;
var user3 User
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user3)
utils.PrintRecord(user3)
Assign attributes to struct regardless it is found or not, thoes attributes won't be used to build SQL query and the final data won't be saved into database
// SELECT * FROM users WHERE name = 'user_0' ORDER BY id LIMIT 1 ; var user4 User
var user4 User
db.Where(User{Name: "user_0"}).Assign(User{Age: 20}).FirstOrInit(&user4)
utils.PrintRecord(user4)
5.2.21 FirstOrCreate
此方法和FirstOrInit 类似，未找到记录则会根据条件和属性新增记录
// FirstOrCreate
// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1 ;
var user User
db.FirstOrCreate(&user, User{Name: "non_existing"})
utils.PrintRecord(user)
// SELECT * FROM users WHERE name = 'user_0' ORDER BY id LIMIT 1 ;
var user2 User
db.FirstOrCreate(&user2, map[string]interface{}{"name": "user_0"})
utils.PrintRecord(user2)
// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1 ;
var user3 User
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user3)
utils.PrintRecord(user3)
// SELECT * FROM users WHERE name = 'user_0' ORDER BY id LIMIT 1 ; var user4 User
var user4 User
db.Where(User{Name: "user_0"}).Assign(User{Age: 20}).FirstOrCreate(&user4)
utils.PrintRecord(user4)
5.2.22 Optimizer/Index Hints
Optimizer hints allow to control the query optimizer to choose a certain query execution plan, GORM supports it with gorm.io/hints
import "gorm.io/hints"
db.Clauses(hints.New("MAX_EXECUTION_TIME(10000)")).Find(&User{})
// SELECT * /*+ MAX_EXECUTION_TIME(10000) */ FROM `users`
Index hints allow passing index hints to the database in case the query planner gets confused.
import "gorm.io/hints"
db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{})
// SELECT * FROM `users` USE INDEX (`idx_user_name`)
db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{})
// SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"
5.2.23 Iteration
GORM supports iterating through Rows
// Iteration
rows, err := db.Model(&User{}).Where("age > ?", 12).Rows()
if err != nil {
log.Println("query record error: ", err)
}
defer rows.Close()
for i := 1; rows.Next(); i++ {
log.Printf("[%d] Row", i)
var user User
db.ScanRows(rows, &user)
utils.PrintRecord(user)
}
5.2.24 Hooks
Available hooks for querying
// load data from database
// Preloading (eager loading)
AfterFind
// ...
func (u *User) AfterFind(*gorm.DB) (err error) {
u.Age += 10
return
}
func main(){
// Hooks
var user User
db.Where("`group` = ?", "group_0").Take(&user)
utils.PrintRecord(user)
}
5.2.25 Pluck
Query single column from databases and scan into a slice, if you want to query multiple columns, use Select with Scan instead
// Pluck
// SELECT age FROM users ;
var ages []int
db.Model(&User{}).Pluck("age", &ages)
utils.PrintRecord(ages)
// SELECT age FROM users ;
var ages2 []int
db.Model(&User{}).Select("age").Find(&ages2)
utils.PrintRecord(ages2)
// SELECT name FROM users ;
var names []string
db.Model(&User{}).Pluck("name", &names)
utils.PrintRecord(names)
// SELECT DISTINCT `group` FROM users ;
var groups []string
db.Model(&User{}).Distinct().Pluck("group", &groups)
utils.PrintRecord(groups)
5.2.26 Scopes
Scopes allows you to specify commonly-used queries which can be referenced as method calls
// ...
func main() {
// ...
// Scopes
// SELECT id,name,age,group FROM users WHERE age > 15 ;
var users []User
db.Select(UserAllFields).
Scopes(AgeGreaterThan15).
Find(&users)
utils.PrintRecord(users)
// SELECT id,name,age,group FROM users WHERE group IN ('group_0','group_2') ;
var users2 []User
db.Select(UserAllFields).
Scopes(GroupIn([]string{"group_0", "group_2"})).
Find(&users2)
utils.PrintRecord(users2)
}
func AgeGreaterThan15(db *gorm.DB) *gorm.DB {
return db.Where("age > ?", 15)
}
func GroupIn(groups []string) func(db *gorm.DB) *gorm.DB {
return func(db *gorm.DB) *gorm.DB {
return db.Where("`group` IN (?)", groups)
}
}
// ...
5.2.27 Count
Get matched records count
// Count
// SELECT COUNT(DISTINCT(`group`)) FROM users ;
var count int64
db.Model(&User{}).Distinct("group").Count(&count)
log.Printf("Groups count: %d", count)
// SELECT COUNT(*) FROM
var count2 int64
db.Model(&User{}).Count(&count2)
log.Printf("Records count: %d", count2)
5.2.28 FindInBatches
Query and process records in batch
// batch size 100
result := db.Where("processed = ?", false).FindInBatches(&results, 100, func(tx *gorm.DB, batch int) error {
for _, result := range results {
// batch processing found records
}
tx.Save(&results)
tx.RowsAffected // number of records in this batch
batch // Batch 1, 2, 3
// returns error will stop future batches
return nil
})
result.Error // returned error
result.RowsAffected // processed records count in all batches
5.3 Update
5.3.1 Save All Fields
Save will save all fields when performing the Updating SQL
// INSERT INTO users (name,group,age) VALUES ('user_0','',100) ;
user := User{Name: "user_0", Age: 100}
db.Save(&user)
utils.PrintRecord(user)
// UPDATE users SET name = 'user_0',age = 100,group = 'group_0' WHERE id =1 ;
var user2 User
db.Where(User{Name: "user_0"}).First(&user2)
log.Println("Before update")
utils.PrintRecord(user2)
user2.Age *= 10
db.Save(&user2)
log.Println("After update")
utils.PrintRecord(user2)
Save: Save update value in database, if the value doesn't have primary key, will insert it
5.3.2 Update single column
When updating a single column with Update, it needs to have any conditions or it will raise error ErrMissingWhereClause,
When using the Model method and its value has a primary value, the primary key will be used to build the condition
// Update single column
// Update with conditions
// UPDATE users SET name = 'user_0_edited' WHERE name = 'user_0' ;
db.Model(&User{}).Where("name = ?", "user_0").Update("name", "user_0_edited")
// UPDATE users SET name = 'user_1_edited' WHERE id = 2 ;
user := User{ID: 2}
db.Model(&user).Update("name", "user_1_edited")
utils.PrintRecord(user)
// Update with conditions and model value
// UPDATE users SET name = 'user_2_edited' WHERE id = 3 AND name = 'user_2'
user2 := User{ID: 3, Group: "group_2"}
db.Model(&user2).Where("name = ?", "user_2").Update("name", "user_2_edited")
utils.PrintRecord(user2)
注意：使用 Model 时，Update 之后会将更新的列值回写到对象中，故需要使用指针作为参数
Model 中若 主键 字段之外的字段有值，也不会作为 UPDATE 的条件，即 更新时只会将 model 中的主键字段作为条件
5.3.3 Update multiple columns
Updates supports update with struct or map[string]interface{}, when updating with struct it will only update non-zero fileds by default
Note
When update with struct, GORM will only update non-zero fields, you might want to user map to update attributes or use Select to specify fields to udpate
// Update multiple columns
// Update with struct
// UPDATE users SET name = 'user_0_edited' WHERE id = 1;
db.Model(&User{}).Where("id = ?", 1).Updates(User{Name: "user_0_edited", Age: 0})
// Update with map
// UPDATE users SET name = 'user_1_edited',age = 0 WHERE id = 2 ;
user := User{ID: 2}
db.Model(&user).Updates(map[string]interface{}{"name": "user_1_edited", "age": 0})
utils.PrintRecord(user)
// Update specified fields
// UPDATE users SET name = 'user_2_edited',age = 0 WHERE id = 3 ;
db.Model(&User{}).Where("id = ?", 3).Select("name", "age").Updates(User{Name: "user_2_edited"})
5.3.4 Udpate Selected Fields
If you want to update selected fields or ignore some fields when updating, you can use Selcet,Omit
// Update selected fields
// UPDATE users SET name = 'user_0_new' WHERE id = 1 ;
db.Model(&User{}).
Where("id = ?", 1).
Select("name").
Updates(User{Name: "user_0_new"})
// UPDATE users SET age = 10 WHERE id = 2 ;
db.Table("users").Where("id = ?", 2).
Omit("age").Updates(User{Name: "user_1_new", Age: 10})
// Select all fields
// UPDATE users SET id = 0,name = 'user_2_new',age = 0,group = '' WHERE id = 3;
db.Table("users").Where("id = ?", 3).Select("*").
Updates(User{Name: "user_2_new"})
// Select all fields but omit name
// UPDATE users SET id = 0,age = 0,group = '' WHERE id = 4;
db.Table("users").Where("id = ?", 4).Select("*").
Omit("name").Updates(User{Name: "user_3_new"})
注意 ：使用 Select("*") 后，主键也可能被修改
5.3.5 Update Hooks
Available hooks for updating
// begin transaction
BeforeSave
BeforeUpdate
// save before associations
// update database
// save after associations
AfterUpdate
AfterSave
// commit or rollback transaction
func (User) BeforeUpdate(db *gorm.DB) (err error) {
time.Sleep(1 * time.Second)
fmt.Println("Before update")
return
}
func (User) BeforeSave(db *gorm.DB) (err error) {
time.Sleep(1 * time.Second)
fmt.Println("Before save")
return
}
func (User) AfterUpdate(db *gorm.DB) (err error) {
time.Sleep(1 * time.Second)
fmt.Println("After update")
return
}
func (User) AfterSave(db *gorm.DB) (err error) {
time.Sleep(1 * time.Second)
fmt.Println("After save")
return
}
// ...
func main() {
// ...
// Update hooks
db.Model(&User{}).Where("id = ?", 1).Update("name", "user_0_new")
}
// ...
5.3.6 Batch Updates
If we haven't specified a record having primary key value with Model, GORM will perform a batch updates
// Update with struct
db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "hello", Age: 18})
// UPDATE users SET name='hello', age=18 WHERE role = 'admin';
// Update with map
db.Table("users").Where("id IN ?", []int{10, 11}).Updates(map[string]interface{}{"name": "hello", "age": 18})
// UPDATE users SET name='hello', age=18 WHERE id IN (10, 11);
5.3.7 Block Global Updates
If you perform a batch update without any conditions, GORM WON'T run it and will return ErrMissingWhereClause error by default
You have to use some conditions or use raw SQL or enable the AllowGlobalUpdate mode
// ...
// Global Update
// ErrMissingWhereClause
err = db.Model(&User{}).Update("name", "new_name").Error
log.Println(err.Error())
// UPDATE users SET name = 'new_name' WHERE 1 = 1;
db.Model(&User{}).Where("1 = 1").Update("name", "new_name")
// UPDATE users SET name = 'name_global_update' ;
db.Session(&gorm.Session{AllowGlobalUpdate: true}).Model(&User{}).Update("name", "name_global_update")
// ...
5.3.8 Updated Records Count
Get the number of rows affected by a update
// Get updated records count with `RowsAffected`
result := db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "hello", Age: 18})
// UPDATE users SET name='hello', age=18 WHERE role = 'admin';
result.RowsAffected // returns updated records count
result.Error // returns updating error
5.3.9 Update with SQL Expression
GORM allows updates column with SQL expression
// Update with SQL expression
// UPDATE users SET age = age * 2 + 100 WHERE id = 1;
db.Model(&User{}).Where("id = ?", 1).Update("age", gorm.Expr("age * ? + ?", 2, 100))
// UPDATE users SET name = name || '123' WHERE id = 2 ;
db.Model(&User{}).Where("id = ?", 2).Updates(map[string]interface{}{"name": gorm.Expr("name || ?", "_123")})
// UPDATE users SET age = age * 100 WHERE id = 3 ;
db.Model(&User{}).Where("id = ?", 3).UpdateColumn("age", gorm.Expr("age * ?", 100))
这里出现了 UpdateColumn,作用和Update 是一样的，那么有什么区别呢
这里看下源码
Update:
// Update update attributes with callbacks, refer: https://gorm.io/docs/update.html#Update-Changed-Fields
func (db *DB) Update(column string, value interface{}) (tx *DB) {
tx = db.getInstance()
tx.Statement.Dest = map[string]interface{}{column: value}
return tx.callbacks.Update().Execute(tx)
}
UpdateColumn:
func (db *DB) UpdateColumn(column string, value interface{}) (tx *DB) {
tx = db.getInstance()
tx.Statement.Dest = map[string]interface{}{column: value}
tx.Statement.SkipHooks = true
return tx.callbacks.Update().Execute(tx)
}
可以看到 UpdateColumn 会跳过 hooks，后续的文档还会告诉 UpdateColumn 将不会追踪 update time，这里接着看源码
// ConvertToAssignments convert to update assignments
func ConvertToAssignments(stmt *gorm.Statement) (set clause.Set) {
// ...
if !stmt.SkipHooks && stmt.Schema != nil {
for _, dbName := range stmt.Schema.DBNames {
field := stmt.Schema.LookUpField(dbName)
if field.AutoUpdateTime > 0 && value[field.Name] == nil && value[field.DBName] == nil {
if v, ok := selectColumns[field.DBName]; (ok && v) || !ok {
now := stmt.DB.NowFunc()
assignValue(field, now)
if field.AutoUpdateTime == schema.UnixNanosecond {
set = append(set, clause.Assignment{Column: clause.Column{Name: field.DBName}, Value: now.UnixNano()})
} else if field.AutoUpdateTime == schema.UnixMillisecond {
set = append(set, clause.Assignment{Column: clause.Column{Name: field.DBName}, Value: now.UnixNano() / 1e6})
} else if field.GORMDataType == schema.Time {
set = append(set, clause.Assignment{Column: clause.Column{Name: field.DBName}, Value: now})
} else {
set = append(set, clause.Assignment{Column: clause.Column{Name: field.DBName}, Value: now.Unix()})
}
}
}
}
}
// ...
}
可以看到当 skipHooks 为 false 时，更新操作将不会追踪更新时间
5.3.10 Update from SubQuery
Update a table by using SubQuery
// Update From SubQuery
// UPDATE users SET name = name || (SELECT email FROM emails WHERE emails.user_id = users.id) WHERE id = 1 ;
db.Model(&User{}).Where("id = ?", 1).
Update("name", gorm.Expr("name || (?)", db.Model(&Email{}).Select("email").
Where("emails.user_id = users.id ")))
5.3.11 Without Hooks/Time Tracking
If you want to skip Hooks methods and don’t track the update time when updating, you can use UpdateColumn,UpdateColumns, it works like Update,Updates
5.3.12 Returning Data From Modified Rows
Return changed data, only works for database support Returning, for example:
// return all columns
var users []User
DB.Model(&users).Clauses(clause.Returning{}).Where("role = ?", "admin").Update("salary", gorm.Expr("salary * ?", 2))
// UPDATE `users` SET `salary`=salary * 2,`updated_at`="2021-10-28 17:37:23.19" WHERE role = "admin" RETURNING *
// users => []User{{ID: 1, Name: "jinzhu", Role: "admin", Salary: 100}, {ID: 2, Name: "jinzhu.2", Role: "admin", Salary: 1000}}
// return specified columns
DB.Model(&users).Clauses(clause.Returning{Columns: []clause.Column{{Name: "name"}, {Name: "salary"}}}).Where("role = ?", "admin").Update("salary", gorm.Expr("salary * ?", 2))
// UPDATE `users` SET `salary`=salary * 2,`updated_at`="2021-10-28 17:37:23.19" WHERE role = "admin" RETURNING `name`, `salary`
// users => []User{{ID: 0, Name: "jinzhu", Role: "", Salary: 100}, {ID: 0, Name: "jinzhu.2", Role: "", Salary: 1000}}
5.3.13 Check Field has changed
GORM provides Changed method could be used in BeforeUpdate hooks, it will return the field changed or not
The Changed method only works with methods Update,Updates, and it only checks if the updating value from Update/Updates equals the model value, will return true if it is changed and not omitted
// ...
func (u *User) BeforeUpdate(tx *gorm.DB) (err error) {
// if age changed
if tx.Statement.Changed("name") {
fmt.Println("name changed")
tx.Statement.SetColumn("age", 100)
return
}
// if name or age changed
if tx.Statement.Changed("name", "age") {
fmt.Println("name or age changed")
return
}
// if any fields changed
if tx.Statement.Changed() {
fmt.Println("any fields changed")
return
}
return
}
// ...
// Check Field has changed
// UPDATE users SET name = 'new_name',age = 100 WHERE id = 1 ;
db.Model(&User{ID: 1, Name: "user_0"}).Update("name", "new_name")
// UPDATE users SET age = age * 2 WHERE id =2 ;
db.Model(&User{ID: 2, Age: 11}).Update("age", gorm.Expr("age * ?", 2))
// UPDATE users SET name = 'new_name',age = 1300 WHERE id = 3 ;
db.Model(&User{ID: 3, Age: 12, Name: "user_2"}).Updates(User{Name: "new_name", Age: 1300})
5.3.14 Change Updating Values
To change updating values in Before Hooks, you should use SetColumn unless it is a full updates with Save
func (user *User) BeforeSave(tx *gorm.DB) (err error) {
if pw, err := bcrypt.GenerateFromPassword(user.Password, 0); err == nil {
tx.Statement.SetColumn("EncryptedPassword", pw)
}
if tx.Statement.Changed("Code") {
s.Age += 20
tx.Statement.SetColumn("Age", s.Age+20)
}
}
db.Model(&user).Update("Name", "jinzhu")
5.4 Delete
5.4.1 Delete a Record
When deleting a record, the deleted value needs to have primary key or it will trigger a batch delete
// Delete a Record
// DELETE FROM users WHERE id = 1;
user := User{ID: 1, Name: "user_0"}
db.Delete(&user)
utils.PrintRecord(user)
// Error: WHERE conditions required
user2 := User{Name: "user_1"}
db.Delete(&user2)
utils.PrintRecord(user2)
// DELETE FROM users WHERE id = 3 AND name = 'user_2' ;
user3 := User{ID: 3}
db.Where("name = ?", "user_2").Delete(&user3)
utils.PrintRecord(user3)
// DELETE FROM users WHERE name = 'user_3' ;
db.Where("name = ?", "user_3").Delete(&User{})
删除时需要传入的 struct 中包含主键值，若没有且未定义其他条件则会返错误；
若 struct 中包含主键值之外的字段值，除主键之外的字段将不会用于构成删除条件，这点和 update 类似
可以使用 Where 手动添加条件
5.4.2 Delete with primary key
GORM allows to delete objects using primary key(s) with inline condition, it works with numbers
// Delete with primary key
// DELETE FROM users WHERE id = 1;
db.Delete(&User{}, 1)
// DELETE FROM users WHERE id = 2 ;
db.Delete(&User{}, "2")
// DELETE FROM users WHERE id IN (3,4,5) ;
db.Delete(&User{}, []int{3, 4, 5})
5.4.3 Delete Hooks
// begin transaction
BeforeDelete
// delete from database
AfterDelete
// commit or rollback transaction
func (User) BeforeDelete(tx *gorm.DB) (err error) {
fmt.Println("Before Delete")
return
}
func (User) AfterDelete(tx *gorm.DB) (err error) {
fmt.Println("After Delete")
return
}
// ...
db.Delete(&User{}, 1)
5.4.4 Batch Delete
The specified values has no primary value, GORM will perform a batch delete, it will delete all mathed records
// DELETE FROM users WHERE age > 10 ;
db.Where("age > ?",10).Delete(&User{})
5.4.5 Block Global Delete
If you perform a batch delete without any conditions, GORM WON’T run it, and will return ErrMissingWhereClause error
You have to use some conditions or use raw SQL or enable AllowGlobalUpdate mode
// DELTE FROM users ;
db.Session(&gorm.Session{AllowGlobalUpdate: true}).Delete(&User{})
5.4.6 Returning Data From Deleted Rows
Return deleted data, only works for database support Returning, for example:
// return all columns
var users []User
DB.Clauses(clause.Returning{}).Where("role = ?", "admin").Delete(&users)
// DELETE FROM `users` WHERE role = "admin" RETURNING *
// users => []User{{ID: 1, Name: "jinzhu", Role: "admin", Salary: 100}, {ID: 2, Name: "jinzhu.2", Role: "admin", Salary: 1000}}
// return specified columns
DB.Clauses(clause.Returning{Columns: []clause.Column{{Name: "name"}, {Name: "salary"}}}).Where("role = ?", "admin").Delete(&users)
// DELETE FROM `users` WHERE role = "admin" RETURNING `name`, `salary`
// users => []User{{ID: 0, Name: "jinzhu", Role: "", Salary: 100}, {ID: 0, Name: "jinzhu.2", Role: "", Salary: 1000}}
5.4.7 Soft Delete
If your model includes a gorm.DeletedAt field (which is included in gorm.Model), it will get soft delete ability automatically
When calling Delete, the record WON’T be removed from the database, but GORM will set the DeletedAt's value to the current time, and the data is not findable with normal Query methods anymore
If you don’t want to include grom.Model, you can enable the soft delete feature like:
type User struct {
ID int
DeletedAt gorm.DeletedAt
Name string
}
Find soft deleted records
You can find soft deleted records with Unscoped
type User struct {
// ...
DeletedAt gorm.DeletedAt
// ...
}
// ...
// Soft Delete
db.Delete(&User{}, []int{1, 2, 3})
// cannot find
var users []User
db.Find(&users, []int{1, 2, 3})
utils.PrintRecord(users)
// find with unscoped
var users2 []User
db.Unscoped().Find(&users2, []int{1, 2, 3})
utils.PrintRecord(users2)
5.4.8 Delete permanetly
You can delete matched records permanetly with Unscoped
db.Unscoped().Delete(&order)
5.4.9 Delete Flag
Use unix second as delete flag
when using unique field with soft delete, you should create a composite index with the unix second based DeletedAt
type User struct {
// ...
DeletedAt soft_delete.DeletedAt `gorm:"uniqueIndex:udx_name"`
// ...
}
// delete flag
db.Delete(&User{}, []int{1, 2, 3})
// cannot find
var users []User
db.Find(&users, []int{1, 2, 3})
utils.PrintRecord(users)
// find with unscoped
var users2 []User
db.Unscoped().Find(&users2, []int{1, 2, 3})
utils.PrintRecord(users2)
Use 1 / 0 as delete flag
import "gorm.io/plugin/soft_delete"
type User struct {
ID uint
Name string
IsDel soft_delete.DeletedAt `gorm:"softDelete:flag"`
}
// Query
SELECT * FROM users WHERE is_del = 0;
// Delete
UPDATE users SET is_del = 1 WHERE ID = 1;
6. Raw SQL and SQL Builder
6.1 Raw SQL
Query Raw SQL with Scan, Exec with Raw SQL
// Raw SQL
// Scan
var result map[string]interface{}
db.Raw("SELECT id,name,age FROM users WHERE id = ?", 1).Scan(&result)
utils.PrintRecord(result)
var results []map[string]interface{}
db.Raw("SELECT id,name FROM users WHERE age > ?", 10).Scan(&results)
utils.PrintRecord(results)
var sumAge int
db.Raw("SELECT SUM(age) FROM users").Scan(&sumAge)
log.Println("Sum age: ", sumAge)
// Exec
db.Exec("UPDATE users SET age = age + ?", 10)
NOTE
GORM allows cache prepared statement to increase performance
6.2 Named Argument
GORM supports named argument with sql.NamedArg, map[string]interface{} or struct
// Named argument
// SELECT * FROM users WHERE name = 'user_0' LIMIT 1;
var user User
db.Where("name = @name", sql.Named("name", "user_0")).Take(&user)
utils.PrintRecord(user)
// SELECT * FROM users WHERE age = 12 LIMIT 1 ;
var user2 User
db.Where("age = @age ", map[string]interface{}{"age": 12}).Take(&user2)
utils.PrintRecord(user2)
// Named Argument with Raw SQL
var result []map[string]interface{}
db.Raw("SELECT id,name FROM users WHERE age = @age OR id = @id ", sql.Named("age", 15), sql.Named("id", 3)).
Scan(&result)
utils.PrintRecord(result)
var result2 map[string]interface{}
db.Raw("SELECT name,age FROM users WHERE id = @id OR age > @age", map[string]interface{}{"id": 2, "age": 13}).
Scan(&result2)
utils.PrintRecord(result2)
// struct
type NameArg struct {
Name string
Age int
}
var result3 map[string]interface{}
db.Raw("SELECT * FROM users WHERE name = @Name AND age = @Age", NameArg{Name: "user_0", Age: 10}).Scan(&result3)
utils.PrintRecord(result3)
6.3 DryRun Mode
Generate SQL and its arguments without executing, can be used to prepare or test generated SQL
// DryRun
stmt := db.Session(&gorm.Session{DryRun: true}).Where("id = ?", 1).Take(&User{}).Statement
log.Printf("SQL: %s, VAL: %v", stmt.SQL.String(), stmt.Vars)
6.4 ToSQL
Returns generated SQL without executing
GORM uses the databases/sql's argument placeholders to construct the SQL statement, which will automatically escape arguments to avoid SQL injection, but the generated SQL don't provide the safety gurantees, please only use it for debugging
// DryRun
sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB {
return tx.Model(&User{}).Where("age > ?", 10).First(&User{})
})
log.Println("SQL: ", sql)
6.5 Row and Rows
// Row and Rows
// Row
var (
name string
age int
)
row := db.Model(&User{}).Where("age > 10", "user_0").Order("age desc").
Select("name", "age").Row()
row.Scan(&name, &age)
log.Printf("Name: %s,Age: %d", name, age)
// Rows
rows, _ := db.Model(&User{}).Where("age > ?", 15).Select("name", "age").Rows()
defer rows.Close()
for rows.Next() {
var name string
var age int
rows.Scan(&name, &age)
log.Printf("Row[name: %s,Age: %d]", name, age)
// Scan rows to struct
var user User
db.ScanRows(rows, &user)
utils.PrintRecord(user)
}
Row(): get result as*sql.RowRows(): get result as*sql.RowsScanRows: scan rows into struct
6.6 Clauses
GORM uses SQL builder generates SQL internally, for each operation, GORM creates a *gorm.Statement object, all GORM APIs add/change Clause for the Statement, at last, GORM generated SQL based on those clauses
For example, when querying with First, it adds the following clauses to the Statement
clause.Select{Columns: "*"}
clause.From{Tables: clause.CurrentTable}
clause.Limit{Limit: 1}
clause.OrderByColumn{
Column: clause.Column{Table: clause.CurrentTable, Name: clause.PrimaryKey},
}
Then GORM build finally querying SQL in the Query callbacks like:
Statement.Build("SELECT", "FROM", "WHERE", "GROUP BY", "ORDER BY", "LIMIT", "FOR")
Which generate SQL:
SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
6.7 Clause Options
GORM defined Many Clauses, and some clauses provide advanced options can be used for your application
Although most of them are rarely used, if you find GORM public API can’t match your requirements, may be good to check them out, for example:
db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&user)
// INSERT IGNORE INTO users (name,age...) VALUES ("jinzhu",18...);
6.8 StatementModifier
GORM provides interface StatementModifier allows you modify statement to match your requirements, take Hints as example
import "gorm.io/hints"
db.Clauses(hints.New("hint")).Find(&User{})
// SELECT * /*+ hint */ FROM `users`
7. Associations
7.1 Belongs To
A Belongs To association sets up a one-to-one connection with another model, such that each instance of the declaring model "belongs to" one instance of the other model
For example, if your application includes users and companies, and each user can be assigned to exactly one company, the following types represent that relationship. Notice here that, on the User object, there is both a CompanyID as well as a Company. By default, the CompanyID is implicity usesd to create a foreign key relationship between the User and Company tables, and thus must be included in the User struct in order to fill the Company inner strcut
type User struct {
ID uint `gorm:"primaryKey"`
Name string
Age int
CompanyID uint
Company Company
}
type Company struct {
ID uint `gorm:"primaryKey"`
Name string
}
7.1.1 Override Foreign Key
To define a belongs to relationship, the foreign key must exist, the default foreing key uses the owner's type name plus its primary field name.
For the above example, to define the User model that belongs to Company, the foreign key should be CompanyID by convention
GORM provides a way to customize the foreign key
type User struct {
gorm.Model
Name string
CompanyRefer int
Company Company `gorm:"foreignKey:CompanyRefer"`
// use CompanyRefer as foreign key
}
type Company struct {
ID int
Name string
}
7.1.2 Override References
For a belongs to relationship, GORM usually uses the owner's primary field as the foreign key's value, for the above example, it is Company's field ID
When you assign a user to a company, GORM will save the company's ID into the user's CompanyID field
You are able to change it with tag references
type User struct {
gorm.Model
Name string
CompanyID string
Company Company `gorm:"references:Code"` // use Code as references
}
type Company struct {
ID int
Code string
Name string
}
7.1.3 FOREIGN KEY Constraints
You can setup OnUpdate,OnDelete constraints with tag constraint, it will created when migrating with GORM,
type User struct {
gorm.Model
Name string
CompanyID int
Company Company `gorm:"constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`
}
type Company struct {
ID int
Name string
}
7.2 Has One
A has one association sets up a one-to-one connection with another model, but with somewhat different semantics (and consequences). This association indicates that each instance of a model contains or prossesses one instance of another model
For example, if your application includes users and credit cards, and each user can only have one credit card
type User struct {
ID uint `gorm:"primaryKey"`
Name string
Age int
CreditCard CreditCard
}
type CreditCard struct {
ID uint `gorm:"primaryKey"`
Number string
UserID uint
}
7.2.1 Override Foreign Key
For a has one relationship, a foreign key field must also exist, the owner will save the primary key of the model belongs to it into this field
The field's name is usually generated with has one model's type plus its primary key, for the above example it is UserID
When you give a credit card to the user, it will save the User's ID into its UserID field
If you want to use another field to save the relationship, you can change it with tag foreignKey
type User struct {
gorm.Model
CreditCard CreditCard `gorm:"foreignKey:UserName"`
// use UserName as foreign key
}
type CreditCard struct {
gorm.Model
Number string
UserName string
}
7.2.2 Override Reference
By default, the owned entity will save the has one model’s primary key into a foreign key, you could change to save another field’s value, like using Name for the below example.
You are able to change it with tag references, e.g:
type User struct {
gorm.Model
Name string `gorm:"index"`
CreditCard CreditCard `gorm:"foreignkey:UserName;references:name"`
}
type CreditCard struct {
gorm.Model
Number string
UserName string
}
7.2.3 Polymorphism Association
GORM supports polymorphism association for has one and has many, it will save owned entity's table name into polymorphic type's field, primary key into poltmorphic field
package main
import (
"log"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
"gorm.io/gorm/logger"
)
const (
DBName = "polymorphism-association.db"
)
type Cat struct {
ID uint `gorm:"primaryKey"`
Name string
Toy Toy `gorm:"polymorphic:Owner;"`
}
type Dog struct {
ID uint `gorm:"primaryKey"`
Name string
Toy Toy `gorm:"polymorphic:Owner;"`
}
type Toy struct {
ID uint `gorm:"primaryKey"`
Name string
OwnerID uint
OwnerType string
}
func main() {
db := initializeDB()
sqlDB, _ := db.DB()
defer sqlDB.Close()
createTables(db)
// INSERT INTO cats (name) VALUES ('cat_1');
// INSERT INTO toys (name,owner_id,owner_type) VALUES ('toy_1','1','cats');
db.Create(&Cat{Name: "cat_1", Toy: Toy{Name: "toy_1"}})
}
func initializeDB() *gorm.DB {
db, err := gorm.Open(sqlite.Open(DBName), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
})
if err != nil {
log.Fatalln("connect db failed: ", err.Error())
}
return db
}
func createTables(db *gorm.DB) {
db.AutoMigrate(&Cat{})
db.AutoMigrate(&Dog{})
db.AutoMigrate(&Toy{})
}
You can change the polymorphic type value with tag polymorphicValue
type Dog struct {
ID int
Name string
Toy Toy `gorm:"polymorphic:Owner;polymorphicValue:master"`
}
type Toy struct {
ID int
Name string
OwnerID int
OwnerType string
}
db.Create(&Dog{Name: "dog1", Toy: Toy{Name: "toy1"}})
// INSERT INTO `dogs` (`name`) VALUES ("dog1")
// INSERT INTO `toys` (`name`,`owner_id`,`owner_type`) VALUES ("toy1","1","master")
7.2.4 Self-Referential Has One
type User struct {
gorm.Model
Name string
ManagerID *uint
Manager *User
}
7.2.5 FOREIGN KEY Constraints
You can setup OnUpdate, OnDelete constraints with tag constraint, it will be created when migrating with GORM, for example:
type User struct {
gorm.Model
CreditCard CreditCard `gorm:"constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`
}
type CreditCard struct {
gorm.Model
Number string
UserID uint
}
You are also allowed to delete selected has one associations with Select when deleting, checkout Delete with Select for details
7.3 Has Many
A has many association sets up a one-to-many connection with another model, unlike has one, the owner could have zero or many instances of models
For example, if your application includes users and credit card, and each user can have many credit cards
type User struct {
ID uint `gorm:"primaryKey"`
Name string
Age int
CreditCard []CreditCard
}
type CreditCard struct {
ID uint `gorm:"primaryKey"`
Number string
UserID uint
}
7.3.1 Override Foreign Key
To define a has many relationship, a foreign key must exist. The default foreign key’s name is the owner’s type name plus the name of its primary key field
For example, to define a model that belongs to User, the foreign key should be UserID.
To use another field as foreign key, you can customize it with a foreignKey tag, e.g:
type User struct {
gorm.Model
CreditCards []CreditCard `gorm:"foreignKey:UserRefer"`
}
type CreditCard struct {
gorm.Model
Number string
UserRefer uint
}
7.3.2 Override References
GORM usually uses the owner’s primary key as the foreign key’s value, for the above example, it is the User‘s ID,
When you assign credit cards to a user, GORM will save the user’s ID into credit cards’ UserID field.
You are able to change it with tag references, e.g:
type User struct {
gorm.Model
MemberNumber string
CreditCards []CreditCard `gorm:"foreignKey:UserNumber;references:MemberNumber"`
}
type CreditCard struct {
gorm.Model
Number string
UserNumber string
}
7.3.3 Polymorphism Association
GORM supports polymorphism association for has one and has many, it will save owned entity’s table name into polymorphic type’s field, primary key value into the polymorphic field
type Dog struct {
ID int
Name string
Toys []Toy `gorm:"polymorphic:Owner;"`
}
type Toy struct {
ID int
Name string
OwnerID int
OwnerType string
}
db.Create(&Dog{Name: "dog1", Toys: []Toy{{Name: "toy1"}, {Name: "toy2"}}})
// INSERT INTO `dogs` (`name`) VALUES ("dog1")
// INSERT INTO `toys` (`name`,`owner_id`,`owner_type`) VALUES ("toy1","1","dogs"), ("toy2","1","dogs")
You can change the polymorphic type value with tag polymorphicValue, for example:
type Dog struct {
ID int
Name string
Toys []Toy `gorm:"polymorphic:Owner;polymorphicValue:master"`
}
type Toy struct {
ID int
Name string
OwnerID int
OwnerType string
}
db.Create(&Dog{Name: "dog1", Toy: []Toy{{Name: "toy1"}, {Name: "toy2"}}})
// INSERT INTO `dogs` (`name`) VALUES ("dog1")
// INSERT INTO `toys` (`name`,`owner_id`,`owner_type`) VALUES ("toy1","1","master"), ("toy2","1","master")
7.3.4 Self-Referential Has Many
type User struct {
gorm.Model
Name string
ManagerID *uint
Team []User `gorm:"foreignkey:ManagerID"`
}
7.3.5 FOREIGN KEY Constraints
You can setup OnUpdate, OnDelete constraints with tag constraint, it will be created when migrating with GORM, for example:
type User struct {
gorm.Model
CreditCards []CreditCard `gorm:"constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`
}
type CreditCard struct {
gorm.Model
Number string
UserID uint
}
You are also allowed to delete selected has many associations with Select when deleting, checkout Delete with Select for details
7.4 Many To Many
Many to many add a join table between two models
For example, if your application includes users and languages, and a user can speak many languages, and many users can speak a specified language
type User struct {
gorm.Model
Languages []Language `gorm:"many2many:user_languages;"`
}
type Language struct {
gorm.Model
Name string
}
When using GORM AutoMigrate to create a table for User, GORM will create the join table automatically
7.4.1 Back-Reference
type User struct {
gorm.Model
Languages []*Language `gorm:"many2many:user_languages;"`
}
type Language struct {
gorm.Model
Name string
Users []*User `gorm:"many2many:user_languages;"`
}
7.4.2 Override Foreign Key
To override them, you can use tag foreignKey, references, joinForeignKey, joinReferences, not necessary to use them together, you can just use one of them to override some foreign keys/references
type User struct {
gorm.Model
Profiles []Profile `gorm:"many2many:user_profiles;foreignKey:Refer;joinForeignKey:UserReferID;References:UserRefer;joinReferences:ProfileRefer"`
Refer uint `gorm:"index:,unique"`
}
type Profile struct {
gorm.Model
Name string
UserRefer uint `gorm:"index:,unique"`
}
// Which creates join table: user_profiles
// foreign key: user_refer_id, reference: users.refer
// foreign key: profile_refer, reference: profiles.user_refer
NOTE: Some databases only allow create database foreign keys that reference on a field having unique index, so you need to specify the unique index tag if you are creating database foreign keys when migrating
7.4.3 Self-Referential Many2Many
Self-referencing many2many relationship
type User struct {
gorm.Model
Friends []*User `gorm:"many2many:user_friends"`
}
// Which creates join table: user_friends
// foreign key: user_id, reference: users.id
// foreign key: friend_id, reference: users.id
7.4.4 Customize JoinTable
JoinTable can be a full-featured model, like having Soft Delete，Hooks supports and more fields, you can setup it with SetupJoinTable, for example:
NOTE: Customized join table’s foreign keys required to be composited primary keys or composited unique index
type Person struct {
ID int
Name string
Addresses []Address `gorm:"many2many:person_addresses;"`
}
type Address struct {
ID uint
Name string
}
type PersonAddress struct {
PersonID int `gorm:"primaryKey"`
AddressID int `gorm:"primaryKey"`
CreatedAt time.Time
DeletedAt gorm.DeletedAt
}
func (PersonAddress) BeforeCreate(db *gorm.DB) error {
// ...
}
// Change model Person's field Addresses' join table to PersonAddress
// PersonAddress must defined all required foreign keys or it will raise error
err := db.SetupJoinTable(&Person{}, "Addresses", &PersonAddress{})
7.4.5 FOREIGN KEY Constraints
You can setup OnUpdate, OnDelete constraints with tag constraint, it will be created when migrating with GORM, for example:
type User struct {
gorm.Model
Languages []Language `gorm:"many2many:user_speaks;"`
}
type Language struct {
Code string `gorm:"primarykey"`
Name string
}
// CREATE TABLE `user_speaks` (`user_id` integer,`language_code` text,PRIMARY KEY (`user_id`,`language_code`),CONSTRAINT `fk_user_speaks_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,CONSTRAINT `fk_user_speaks_language` FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE SET NULL ON UPDATE CASCADE);
You are also allowed to delete selected many2many relations with Select when deleting, checkout Delete with Select for details
7.4.6 Composite Foreign Keys
If you are using Composite Primary Keys for your models, GORM will enable composite foreign keys by default
You are allowed to override the default foreign keys, to specify multiple foreign keys, just separate those keys’ name by commas, for example:
type Tag struct {
ID uint `gorm:"primaryKey"`
Locale string `gorm:"primaryKey"`
Value string
}
type Blog struct {
ID uint `gorm:"primaryKey"`
Locale string `gorm:"primaryKey"`
Subject string
Body string
Tags []Tag `gorm:"many2many:blog_tags;"`
LocaleTags []Tag `gorm:"many2many:locale_blog_tags;ForeignKey:id,locale;References:id"`
SharedTags []Tag `gorm:"many2many:shared_blog_tags;ForeignKey:id;References:id"`
}
// Join Table: blog_tags
// foreign key: blog_id, reference: blogs.id
// foreign key: blog_locale, reference: blogs.locale
// foreign key: tag_id, reference: tags.id
// foreign key: tag_locale, reference: tags.locale
// Join Table: locale_blog_tags
// foreign key: blog_id, reference: blogs.id
// foreign key: blog_locale, reference: blogs.locale
// foreign key: tag_id, reference: tags.id
// Join Table: shared_blog_tags
// foreign key: blog_id, reference: blogs.id
// foreign key: tag_id, reference: tags.id
7.5 Association Mode
7.5.1 Auto Create/Update
GORM will auto-save associations and its reference using Upsert when creating/updating a record.
user := User{
Name: "jinzhu",
BillingAddress: Address{Address1: "Billing Address - Address 1"},
ShippingAddress: Address{Address1: "Shipping Address - Address 1"},
Emails: []Email{
{Email: "[email protected]"},
{Email: "[email protected]"},
},
Languages: []Language{
{Name: "ZH"},
{Name: "EN"},
},
}
db.Create(&user)
// BEGIN TRANSACTION;
// INSERT INTO "addresses" (address1) VALUES ("Billing Address - Address 1"), ("Shipping Address - Address 1") ON DUPLICATE KEY DO NOTHING;
// INSERT INTO "users" (name,billing_address_id,shipping_address_id) VALUES ("jinzhu", 1, 2);
// INSERT INTO "emails" (user_id,email) VALUES (111, "[email protected]"), (111, "[email protected]") ON DUPLICATE KEY DO NOTHING;
// INSERT INTO "languages" ("name") VALUES ('ZH'), ('EN') ON DUPLICATE KEY DO NOTHING;
// INSERT INTO "user_languages" ("user_id","language_id") VALUES (111, 1), (111, 2) ON DUPLICATE KEY DO NOTHING;
// COMMIT;
db.Save(&user)
If you want to update associations’s data, you should use the FullSaveAssociations mode:
db.Session(&gorm.Session{FullSaveAssociations: true}).Updates(&user)
// ...
// INSERT INTO "addresses" (address1) VALUES ("Billing Address - Address 1"), ("Shipping Address - Address 1") ON DUPLICATE KEY SET address1=VALUES(address1);
// INSERT INTO "users" (name,billing_address_id,shipping_address_id) VALUES ("jinzhu", 1, 2);
// INSERT INTO "emails" (user_id,email) VALUES (111, "[email protected]"), (111, "[email protected]") ON DUPLICATE KEY SET email=VALUES(email);
// ...
7.5.2 Skip Auto Create/Update
To skip the auto save when creating/updating, you can use Select or Omit, for example:
user := User{
Name: "jinzhu",
BillingAddress: Address{Address1: "Billing Address - Address 1"},
ShippingAddress: Address{Address1: "Shipping Address - Address 1"},
Emails: []Email{
{Email: "[email protected]"},
{Email: "[email protected]"},
},
Languages: []Language{
{Name: "ZH"},
{Name: "EN"},
},
}
db.Select("Name").Create(&user)
// INSERT INTO "users" (name) VALUES ("jinzhu", 1, 2);
db.Omit("BillingAddress").Create(&user)
// Skip create BillingAddress when creating a user
db.Omit(clause.Associations).Create(&user)
// Skip all associations when creating a user
NOTE
For many2many associations, GORM will upsert the associations before creating the join table references, if you want to skip the upserting of associations, you could skip it like:
db.Omit("Languages.*").Create(&user)
The following code will skip the creation of the association and its references
db.Omit("Languages").Create(&user)
7.5.3 Select/Omit Association fields
user := User{
Name: "jinzhu",
BillingAddress: Address{Address1: "Billing Address - Address 1", Address2: "addr2"},
ShippingAddress: Address{Address1: "Shipping Address - Address 1", Address2: "addr2"},
}
// Create user and his BillingAddress, ShippingAddress
// When creating the BillingAddress only use its address1, address2 fields and omit others
db.Select("BillingAddress.Address1", "BillingAddress.Address2").Create(&user)
db.Omit("BillingAddress.Address2", "BillingAddress.CreatedAt").Create(&user)
7.5.4 Association Mode
Assocication Mode contains some commonly used helper methods to handle relationships
// Start Association Mode
var user User
db.Model(&user).Association("Languages")
// `user` is the source model, it must contains primary key
// `Languages` is a relationship's field name
// If the above two requirements matched, the AssociationMode should be started successfully, or it should return error
db.Model(&user).Association("Languages").Error
Find Associations
Find matched associations
// SELECT `languages`.`id`,`languages`.`name` FROM `languages`
// JOIN `user_languages` ON `user_languages`.`language_id` = `languages`.`id`
// AND `user_languages`.`user_id` = 1
// WHERE name IN ('lang_0')
var languages []Language
db.Model(&User{ID: 1}).Where("name IN ?", []string{"lang_0"}).Association("Languages").Find(&languages)
utils.PrintRecord(languages)
Append Associations
Append new associations for many to many,has many
replace current association for has one,belongs to
many to many has many 新增 association
// append new associations
// SELECT `languages`.`id`,`languages`.`name` FROM `languages`
// JOIN `user_languages` ON `user_languages`.`language_id` = `languages`.`id`
// AND `user_languages`.`user_id` = 1
// WHERE name IN ('lang_0')
var languages []Language
db.Model(&User{ID: 1}).Where("name IN ?", []string{"lang_0"}).Association("Languages").Find(&languages)
utils.PrintRecord(languages)
has one belongs to 替换 association
// append association
// SELECT * FROM `credit_cards` WHERE `credit_cards`.`user_id` = 1
// INSERT INTO `credit_cards` (`number`,`user_id`) VALUES ("new_xxx",2) ON CONFLICT (`id`) DO UPDATE SET `user_id`=`excluded`.`user_id` RETURNING `id`
db.Model(&User{ID: 2}).Association("CreditCard").Append(&CreditCard{Number: "new_xxx"})
// INSERT INTO `credit_cards` (`number`,`user_id`) VALUES ("new_2nd_xxx",2) ON CONFLICT (`id`) DO UPDATE SET `user_id`=`excluded`.`user_id` RETURNING `id`
// UPDATE `credit_cards` SET `user_id`=NULL WHERE `credit_cards`.`id` <> 12 AND `credit_cards`.`user_id` = 2
db.Model(&User{ID: 2}).Association("CreditCard").Append(&CreditCard{Number: "new_2nd_xxx"})
可以看到，在为 ID 为 2 的 user append 新的关联的时候，新增关联然后去除原有的关联，从而实现了替换原关联的作用
Tips:
SQLITE3 不支持 RIGHT JOIN 和 FULL JOIN, 但是可以通过 LEFT JOIN 来进行模拟
SELECT u.id AS userID,u.name,c.number,c.id AS creditCardID FROM users AS u LEFT JOIN credit_cards AS c ON u.id = c.user_id
UNION ALL
SELECT u.id,u.name,c.number,c.id FROM credit_cards AS c LEFT JOIN users AS u ON c.user_id = u.id ;
Replace Associations
Replace current associations with new ones
// replace with new association
// INSERT INTO `languages` (`name`) VALUES ("replaced_lang") ON CONFLICT DO NOTHING RETURNING `id`
// INSERT INTO `user_languages` (`user_id`,`language_id`) VALUES (2,24) ON CONFLICT DO NOTHING
// DELETE FROM `user_languages` WHERE `user_languages`.`user_id` = 2 AND `user_languages`.`language_id` <> 24
db.Model(&User{ID: 2}).Association("Languages").Replace(&Language{Name: "replaced_lang"})
使用 FULL JOIN 查看数据
SELECT u.id AS userID,u.name,l.name AS lang,l.id AS langID FROM users AS u LEFT JOIN user_languages AS ul ON ul.user_id = u.id LEFT JOIN languages AS l ON l.id = ul.language_id
UNION ALL
SELECT u.id AS userID,u.name,l.name AS lang,l.id AS langID FROM languages AS l LEFT JOIN user_languages AS ul ON ul.language_id = l.id LEFT JOIN users AS u ON u.id = ul.user_id;
Delete Associations
Remove the relationship between source & arguments if exists, only delete the reference, won’t delete those objects from DB
// delete association
// DELETE FROM `user_languages` WHERE `user_languages`.`user_id` = 2 AND `user_languages`.`language_id` = 26
db.Model(&User{ID: 2}).Association("Languages").Delete(&Language{ID: 26})
Clear Associations
Remove all reference between soruce & association, won’t delete those associations
// clear associations
// DELETE FROM `user_languages` WHERE `user_languages`.`user_id` = 2
db.Model(&User{ID: 2}).Association("Languages").Clear()
Count Associations
Return the count of current associations
// SELECT count(*) FROM `languages` JOIN `user_languages` ON `user_languages`.`language_id` = `languages`.`id` AND `user_languages`.`user_id` = 1
db.Model(&User{ID: 1}).Association("Languages").Count()
Batch Data
// Find all roles for all users
db.Model(&users).Association("Role").Find(&roles)
// Delete User A from all user's team
db.Model(&users).Association("Team").Delete(&userA)
// Get distinct count of all users' teams
db.Model(&users).Association("Team").Count()
// For `Append`, `Replace` with batch data, the length of the arguments needs to be equal to the data's length or else it will return an error
var users = []User{user1, user2, user3}
// e.g: we have 3 users, Append userA to user1's team, append userB to user2's team, append userA, userB and userC to user3's team
db.Model(&users).Association("Team").Append(&userA, &userB, &[]User{userA, userB, userC})
// Reset user1's team to userA，reset user2's team to userB, reset user3's team to userA, userB and userC
db.Model(&users).Association("Team").Replace(&userA, &userB, &[]User{userA, userB, userC})
7.5.5 Delete with Select
Delete with Select
You are allowed to delete selected has one/has many/many2many relations with Select when deleting records, for example:
// delete user's account when deleting user
db.Select("Account").Delete(&user)
// delete user's Orders, CreditCards relations when deleting user
db.Select("Orders", "CreditCards").Delete(&user)
// delete user's has one/many/many2many relations when deleting user
db.Select(clause.Associations).Delete(&user)
// delete each user's account when deleting users
db.Select("Account").Delete(&users)
NOTE: Associations will only be deleted if the deleting records’s primary key is not zero, GORM will use those priamry keys as conditions to delete selected associations
// DOESN'T WORK
db.Select("Account").Where("name = ?", "jinzhu").Delete(&User{})
// will delete all user with name `jinzhu`, but those user's account won't be deleted
db.Select("Account").Where("name = ?", "jinzhu").Delete(&User{ID: 1})
// will delete the user with name = `jinzhu` and id = `1`, and user `1`'s account will be deleted
db.Select("Account").Delete(&User{ID: 1})
// will delete the user with id = `1`, and user `1`'s account will be deleted
7.5.6 Association Tags
| Tag | Description |
|---|---|
| foreignKey | Specifies column name of the current model that is used as a foreign key to the join table |
| references | Specifies column name of the reference’s table that is mapped to the foreign key of the join table |
| polymorphic | Specifies polymorphic type such as model name |
| polymorphicValue | Specifies polymorphic value, default table name |
| many2many | Specifies join table name |
| joinForeignKey | Specifies foreign key column name of join table that maps to the current table |
| joinReferences | Specifies foreign key column name of join table that maps to the reference’s table |
| constraint | Relations constraint, e.g: OnUpdate,OnDelete |
7.6 Preloading(Eager Loading)
7.6.1 Preload
GORM allows eager loading relations in other SQL with Preload
package main
import (
"gorm-note/utils"
"log"
"math/rand"
"strconv"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
"gorm.io/gorm/logger"
)
const (
DBName = "preloading.db"
UserCount = 10
)
type User struct {
ID uint `gorm:"primaryKey"`
Name string
Orders []Order
}
type Order struct {
ID uint `gorm:"primaryKey"`
UserID uint
Price float64
}
func main() {
db := initializeDB()
sqlDB, _ := db.DB()
defer sqlDB.Close()
createTables(db)
createUsers(db, UserCount)
// Preloading
// SELECT * FROM `orders` WHERE `orders`.`user_id` = 1
// SELECT * FROM `users` WHERE id = 1
var user User
db.Preload("Orders").Where("id = ?", 1).Find(&user)
utils.PrintRecord(user)
}
func initializeDB() *gorm.DB {
db, err := gorm.Open(sqlite.Open(DBName), &gorm.Config{
Logger: logger.Default.LogMode(logger.Info),
})
if err != nil {
log.Fatalln("connect db failed: ", err.Error())
}
return db
}
func createTables(db *gorm.DB) {
db.AutoMigrate(&User{})
db.AutoMigrate(&Order{})
}
func createUsers(db *gorm.DB, num int) {
var count int64
db.Model(&User{}).Count(&count)
if count > 0 {
return
}
users := make([]User, num)
for i := 0; i < num; i++ {
name := "user_" + strconv.Itoa(i)
price := rand.Float64() * float64(i)
price2 := rand.Float64() * float64(i)
users[i] = User{Name: name, Orders: []Order{{Price: price}, {Price: price2}}}
}
db.Create(&users)
}
7.6.2 Joins Preloading
Preload loads the association data in a separate query, Join Prelod will loads association data using inner join
NOTE
Join Preload works with one-to-one relation, e.g: has one, belongs to
// Joins preload work with has-one or belongs-to
// SELECT `users`.`id`,`users`.`name`,`CreditCard`.`id` AS `CreditCard__id`,`CreditCard`.`user_id` AS `CreditCard__user_id`,`CreditCard`.`number` AS `CreditCard__number` FROM `users`
// LEFT JOIN `credit_cards` `CreditCard` ON `users`.`id` = `CreditCard`.`user_id` WHERE `users`.`id` = 2 LIMIT 1
var user3 User
db.Joins("CreditCard").Take(&user3, 2)
utils.PrintRecord(user3)
// not working, will cause panic
var user2 User
db.Joins("Orders").Find(&user2, 2)
utils.PrintRecord(user2)
当 joins preloading 应用在 has-many, many-to-many 的情形下时将会触发 panic
7.6.3 Preload All
clause.Association can work with Preload similar like Select when creating/updating, you can use it to Preload all associations
// SELECT * FROM `credit_cards` WHERE `credit_cards`.`user_id` IN (1,2,3,4,5,6,7,8,9,10)
// SELECT * FROM `orders` WHERE `orders`.`user_id` IN (1,2,3,4,5,6,7,8,9,10)
// SELECT * FROM `users`
var users []User
db.Preload(clause.Associations).Find(&users)
utils.PrintRecord(users)
cluase.Association won’t preload nested associations, but you can use it with Nested Preloading together
db.Preload("Orders.OrderItems.Product").Preload(clause.Associations).Find(&users)
7.6.4 Preload with conditions
GORM allows Preload associations with conditions, it works similar to inline condition
Preload 的内联条件是针对关联表的
// preload with conditions
// SELECT * FROM `orders` WHERE `orders`.`user_id` = 2 AND price > 0.500000
// SELECT * FROM `users` WHERE id = 2
var user4 User
db.Where("id = ?", 2).Preload("Orders", "price > ?", 0.5).Find(&user4)
utils.PrintRecord(user4)
7.6.5 Custom Preloading SQL
You are able to custom preloading SQL by passing in func(db *gorm.DB) *gorm.DB
// custom preloading SQL
// SELECT * FROM `orders` WHERE `orders`.`user_id` = 2 ORDER BY price
// SELECT * FROM `users` WHERE id = 2
var user5 User
db.Where(&User{ID: 2}).Preload("Orders", func(db *gorm.DB) *gorm.DB {
return db.Order("price")
}).Find(&user5)
utils.PrintRecord(user5)
7.6.6 Nested Preloading
GORM supports nested preloading, for example:
db.Preload("Orders.OrderItems.Product").Preload("CreditCard").Find(&users)
// Customize Preload conditions for `Orders`
// And GORM won't preload unmatched order's OrderItems then
db.Preload("Orders", "state = ?", "paid").Preload("Orders.OrderItems").Find(&users)
8. Tutorials
8.1 Context
GORM provides Context support, you can use it with method WithContext
8.1.1 Single Session Mode
Single session mode usually used when you want to perform a single operation
db.WithContext(ctx).Find(&users)
8.1.2 Continuous session mode
Continuous session mode usually used when you want to perform a group of operations, for example:
tx := db.WithContext(ctx)
tx.First(&user, 1)
tx.Model(&user).Update("role", "admin")
8.1.3 Context in Hooks/Callbacks
You could access the Context object from current Statement, for example:
func (u *User) BeforeCreate(tx *gorm.DB) (err error) {
ctx := tx.Statement.Context
// ...
return
}
8.1.4 Chi Middleware Example
Continuous session mode which might be helpful when handling API requests, for example, you can set up *gorm.DB with Timeout Context in middlewares, and then use the *gorm.DB when processing all requests
Following is a Chi middleware example:
func SetDBMiddleware(next http.Handler) http.Handler {
return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
timeoutContext, _ := context.WithTimeout(context.Background(), time.Second)
ctx := context.WithValue(r.Context(), "DB", db.WithContext(timeoutContext))
next.ServeHTTP(w, r.WithContext(ctx))
})
}
r := chi.NewRouter()
r.Use(SetDBMiddleware)
r.Get("/", func(w http.ResponseWriter, r *http.Request) {
db, ok := ctx.Value("DB").(*gorm.DB)
var users []User
db.Find(&users)
// lots of db operations
})
r.Get("/user", func(w http.ResponseWriter, r *http.Request) {
db, ok := ctx.Value("DB").(*gorm.DB)
var user User
db.First(&user)
// lots of db operations
})
NOTE Set
ContextwithWithContextis goroutine-safe, refer Session for details
8.1.5 Logger
Logger accepts Context too, you can use it for log tracking, refer Logger for details
8.2 Error Handling
In Go, error handling is important
You are encouraged to do error check after any Finisher Methods
8.2.1 Error Handling
Error hanling in GORM is different than idiomatic Go code because of its chainable API
If any error occurs, GORM will set *gorm.DB*'s Error field, you need to check it likes this:
if err := db.Where("name = ?", "jinzhu").First(&user).Error; err != nil {
// error handling...
}
Or
if result := db.Where("name = ?", "jinzhu").First(&user); result.Error != nil {
// error handling...
}
8.2.2 ErrRecordNotFound
GORM returns ErrRecordNotFound when failed to find data with First,Last,Take, if there are several errors happended, you can check the ErrRecordNotFound error with errors.Is
// Check if returns RecordNotFound error
err := db.First(&user, 100).Error
errors.Is(err, gorm.ErrRecordNotFound)
8.3 Method Chaining
GORM allows method chaining, so you can write code like this:
db.Where("name = ?", "jinzhu").Where("age = ?", 18).First(&user)
There are three kinds of methods in GORM: Chain Method,Finisher Method,New Session Method
8.3.1 Chain Method
Chain methods are methods to modify or add Clauses to current Statement, like:
Where,Select,Omit,Joins,Scopes,Preload,Raw(Raw can’t be used with other chainable methods to build SQL) …
Here is the full lists, also check out the SQL Builder for more details about Clauses.
8.3.2 Finisher Mehod
Finishers are immediate methods that execute registered callbacks, which will generate and execute SQL, like those methods:
Create, First, Find, Take, Save, Update, Delete, Scan, Row, Rows…
Check out the full lists here.
8.3.3 New Session Method
After a nen initialized *gorm.DB or a New Session Method, following methods call will create a new Statement instance instead of using the current one
GORM defined Session,WithContext,Debug methods as New Session Method,refer Session for more details
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
// db is a new initialized *gorm.DB, which falls under `New Session Mode`
db.Where("name = ?", "jinzhu").Where("age = ?", 18).Find(&users)
// `Where("name = ?", "jinzhu")` is the first method call, it will create a new `Statement`
// `Where("age = ?", 18)` reuses the `Statement`, and adds conditions to the `Statement`
// `Find(&users)` is a finisher, it executes registered Query Callbacks, generates and runs the following SQL:
// SELECT * FROM users WHERE name = 'jinzhu' AND age = 18;
db.Where("name = ?", "jinzhu2").Where("age = ?", 20).Find(&users)
// `Where("name = ?", "jinzhu2")` is also the first method call, it creates new `Statement` too
// `Where("age = ?", 20)` reuses the `Statement`, and add conditions to the `Statement`
// `Find(&users)` is a finisher, it executes registered Query Callbacks, generates and runs the following SQL:
// SELECT * FROM users WHERE name = 'jinzhu2' AND age = 20;
db.Find(&users)
// `Find(&users)` is a finisher method and also the first method call for a `New Session Mode` `*gorm.DB`
// It creates a new `Statement` and executes registered Query Callbacks, generates and runs following SQL:
// SELECT * FROM users;
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
// db is a new initialized *gorm.DB, which falls under `New Session Mode`
tx := db.Where("name = ?", "jinzhu")
// `Where("name = ?", "jinzhu")` is the first method call, it creates a new `Statement` and adds conditions
tx.Where("age = ?", 18).Find(&users)
// `tx.Where("age = ?", 18)` REUSES above `Statement`, and adds conditions to the `Statement`
// `Find(&users)` is a finisher, it executes registered Query Callbacks, generates and runs the following SQL:
// SELECT * FROM users WHERE name = 'jinzhu' AND age = 18
tx.Where("age = ?", 28).Find(&users)
// `tx.Where("age = ?", 18)` REUSES above `Statement` also, and add conditions to the `Statement`
// `Find(&users)` is a finisher, it executes registered Query Callbacks, generates and runs the following SQL:
// SELECT * FROM users WHERE name = 'jinzhu' AND age = 18 AND age = 28;
NOTE
In example 2, the first query affected the second generated SQL, as GORM reused the Statement. This might cause unexpected issues, refer to Goroutine Safety for how to avoid it.
8.3.4 Method Chain Safety/Goroutine Safety
Methods will create new Statement instance for new initialized *gorm.DB or after a New Session Method, so to reuse a *gorm.DB you need to make sure they are under New Session Mode
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
// Safe for a new initialized *gorm.DB
for i := 0; i < 100; i++ {
go db.Where(...).First(&user)
}
tx := db.Where("name = ?", "jinzhu")
// NOT Safe as reusing Statement
for i := 0; i < 100; i++ {
go tx.Where(...).First(&user)
}
ctx, _ := context.WithTimeout(context.Background(), time.Second)
ctxDB := db.WithContext(ctx)
// Safe after a `New Session Method`
for i := 0; i < 100; i++ {
go ctxDB.Where(...).First(&user)
}
ctx, _ := context.WithTimeout(context.Background(), time.Second)
ctxDB := db.Where("name = ?", "jinzhu").WithContext(ctx)
// Safe after a `New Session Method`
for i := 0; i < 100; i++ {
go ctxDB.Where(...).First(&user) // `name = 'jinzhu'` will apply to the query
}
tx := db.Where("name = ?", "jinzhu").Session(&gorm.Session{})
// Safe after a `New Session Method`
for i := 0; i < 100; i++ {
go tx.Where(...).First(&user) // `name = 'jinzhu'` will apply to the query
}
在并发情形下，一定要注意使用 New Session Mode 以确保线程安全
8.4 Session
GORM provides Session method, which is a New Session Method, it allows to create a new session mode with configuration
// Session Configuration
type Session struct {
DryRun bool
PrepareStmt bool
NewDB bool
SkipHooks bool
SkipDefaultTransaction bool
DisableNestedTransaction bool
AllowGlobalUpdate bool
FullSaveAssociations bool
QueryFields bool
CreateBatchSize int
Context context.Context
Logger logger.Interface
NowFunc func() time.Time
}
8.4.1 DryRun
Generates SQL without executing. It can be used to prepare or test generated SQL, for example:
// session mode
stmt := db.Session(&Session{DryRun: true}).First(&user, 1).Statement
stmt.SQL.String() //=> SELECT * FROM `users` WHERE `id` = $1 ORDER BY `id`
stmt.Vars //=> []interface{}{1}
// globally mode with DryRun
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{DryRun: true})
// different databases generate different SQL
stmt := db.Find(&user, 1).Statement
stmt.SQL.String() //=> SELECT * FROM `users` WHERE `id` = $1 // PostgreSQL
stmt.SQL.String() //=> SELECT * FROM `users` WHERE `id` = ? // MySQL
stmt.Vars //=> []interface{}{1}
To generate the final SQL, you could use following code:
// NOTE: the SQL is not always safe to execute, GORM only uses it for logs, it might cause SQL injection
db.Dialector.Explain(stmt.SQL.String(), stmt.Vars...)
// SELECT * FROM `users` WHERE `id` = 1
8.4.2 PrepareStmt
PrepareStmt creates prepared statements where executing any SQL and caches them to speed up future calls
// globally mode, all DB operations will create prepared statements and cache them
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
PrepareStmt: true,
})
// session mode
tx := db.Session(&Session{PrepareStmt: true})
tx.First(&user, 1)
tx.Find(&users)
tx.Model(&user).Update("Age", 18)
// returns prepared statements manager
stmtManger, ok := tx.ConnPool.(*PreparedStmtDB)
// close prepared statements for *current session*
stmtManger.Close()
// prepared SQL for *current session*
stmtManger.PreparedSQL // => []string{}
// prepared statements for current database connection pool (all sessions)
stmtManger.Stmts // map[string]*sql.Stmt
for sql, stmt := range stmtManger.Stmts {
sql // prepared SQL
stmt // prepared statement
stmt.Close() // close the prepared statement
}
8.4.3 NewDB
Create a new DB without conditions with option NewDB
tx := db.Where("name = ?", "jinzhu").Session(&gorm.Session{NewDB: true})
tx.First(&user)
// SELECT * FROM users ORDER BY id LIMIT 1
tx.First(&user, "id = ?", 10)
// SELECT * FROM users WHERE id = 10 ORDER BY id
// Without option `NewDB`
tx2 := db.Where("name = ?", "jinzhu").Session(&gorm.Session{})
tx2.First(&user)
// SELECT * FROM users WHERE name = "jinzhu" ORDER BY id
8.4.4 Skip Hooks
If you want to skip Hooks methods, you can use the SkipHooks session mode, for example:
DB.Session(&gorm.Session{SkipHooks: true}).Create(&user)
DB.Session(&gorm.Session{SkipHooks: true}).Create(&users)
DB.Session(&gorm.Session{SkipHooks: true}).CreateInBatches(users, 100)
DB.Session(&gorm.Session{SkipHooks: true}).Find(&user)
DB.Session(&gorm.Session{SkipHooks: true}).Delete(&user)
DB.Session(&gorm.Session{SkipHooks: true}).Model(User{}).Where("age > ?", 18).Updates(&user)
8.4.5 DisableNestedTransaction
When using Transaction method inside a DB transaction, GORM will use SavePoint(savePointName), RollbackTo(savePointName) to give you the nested transaction support. You can disable it by using DisableNestedTransactionoption
db.Session(&gorm.Session{
DisableNestedTransaction: true,
}).CreateInBatches(&users, 100)
8.4.6 AllowGlobalUpdate
GORM doesn’t allow global update/delete by default, will return ErrMissingWhereClause error. You can set this option to true to enable it, for example:
db.Session(&gorm.Session{
AllowGlobalUpdate: true,
}).Model(&User{}).Update("name", "jinzhu")
// UPDATE users SET `name` = "jinzhu"
8.4.7 FullSaveAssociations
GORM will auto-save associations and its reference using Upsert when creating/updating a record. If you want to update associations’ data, you should use the FullSaveAssociations mode, for example:
db.Session(&gorm.Session{FullSaveAssociations: true}).Updates(&user)
// ...
// INSERT INTO "addresses" (address1) VALUES ("Billing Address - Address 1"), ("Shipping Address - Address 1") ON DUPLICATE KEY SET address1=VALUES(address1);
// INSERT INTO "users" (name,billing_address_id,shipping_address_id) VALUES ("jinzhu", 1, 2);
// INSERT INTO "emails" (user_id,email) VALUES (111, "[email protected]"), (111, "[email protected]") ON DUPLICATE KEY SET email=VALUES(email);
// ...
8.4.8 Context
With the Context option, you can set the Context for following SQL operations, for example:
timeoutCtx, _ := context.WithTimeout(context.Background(), time.Second)
tx := db.Session(&Session{Context: timeoutCtx})
tx.First(&user) // query with context timeoutCtx
tx.Model(&user).Update("role", "admin") // update with context timeoutCtx
GORM also provides shortcut method WithContext, here is the definition:
func (db *DB) WithContext(ctx context.Context) *DB {
return db.Session(&Session{Context: ctx})
}
8.4.9 Logger
Gorm allows customizing built-in logger with the Logger option, for example:
newLogger := logger.New(log.New(os.Stdout, "\r\n", log.LstdFlags),
logger.Config{
SlowThreshold: time.Second,
LogLevel: logger.Silent,
Colorful: false,
})
db.Session(&Session{Logger: newLogger})
db.Session(&Session{Logger: logger.Default.LogMode(logger.Silent)})
Checkout Logger for more details.
8.4.10 NowFunc
NowFunc allows changing the function to get current time of GORM, for example:
db.Session(&Session{
NowFunc: func() time.Time {
return time.Now().Local()
},
})
8.4.11 Debug
Debug is a shortcut method to change session’s Logger to debug mode, here is the definition:
func (db *DB) Debug() (tx *DB) {
return db.Session(&Session{
Logger: db.Logger.LogMode(logger.Info),
})
}
8.4.12 QueryFields
Select by fields
db.Session(&gorm.Session{QueryFields: true}).Find(&user)
// SELECT `users`.`name`, `users`.`age`, ... FROM `users` // with this option
// SELECT * FROM `users` // without this option
这个设置比较重要，可以避免使用 SELECT *
8.4.13 CreateBatchSize
Default batch size
users = [5000]User{{Name: "jinzhu", Pets: []Pet{pet1, pet2, pet3}}...}
db.Session(&gorm.Session{CreateBatchSize: 1000}).Create(&users)
// INSERT INTO users xxx (5 batches)
// INSERT INTO pets xxx (15 batches)
8.5 Hooks
8.5.1 Object Life Cycle
Hooks are functions that are called before or after creation/quering/updating/deletion
If you have defined specified methods for a model, it will be called automatically when creating, updating, querying, deleting, and if any callback returns an error, GORM will stop future operations and rollback current transaction.
The type of hook methods should be func(*gorm.DB) error
8.5.2 Creating an object
Available hooks for creating
// begin transaction
BeforeSave
BeforeCreate
// save before associations
// insert into database
// save after associations
AfterCreate
AfterSave
// commit or rollback transaction
Code Example:
func (u *User) BeforeCreate(tx *gorm.DB) (err error) {
u.UUID = uuid.New()
if !u.IsValid() {
err = errors.New("can't save invalid data")
}
return
}
func (u *User) AfterCreate(tx *gorm.DB) (err error) {
if u.ID == 1 {
tx.Model(u).Update("role", "admin")
}
return
}
NOTE Save/Delete operations in GORM are running in transactions by default, so changes made in that transaction are not visible until it is committed, if you return any error in your hooks, the change will be rollbacked
func (u *User) AfterCreate(tx *gorm.DB) (err error) {
if !u.IsValid() {
return errors.New("rollback invalid user")
}
return nil
}
8.5.3 Updating an object
Available hooks for updating
// begin transaction
BeforeSave
BeforeUpdate
// save before associations
// update database
// save after associations
AfterUpdate
AfterSave
// commit or rollback transaction
Code Example:
func (u *User) BeforeUpdate(tx *gorm.DB) (err error) {
if u.readonly() {
err = errors.New("read only user")
}
return
}
// Updating data in same transaction
func (u *User) AfterUpdate(tx *gorm.DB) (err error) {
if u.Confirmed {
tx.Model(&Address{}).Where("user_id = ?", u.ID).Update("verfied", true)
}
return
}
8.5.4 Deleting an object
Available hooks for deleting
// begin transaction
BeforeDelete
// delete from database
AfterDelete
// commit or rollback transaction
Code Example:
// Updating data in same transaction
func (u *User) AfterDelete(tx *gorm.DB) (err error) {
if u.Confirmed {
tx.Model(&Address{}).Where("user_id = ?", u.ID).Update("invalid", false)
}
return
}
8.5.5 Querying an object
Available hooks for querying
// load data from database
// Preloading (eager loading)
AfterFind
Code Example:
func (u *User) AfterFind(tx *gorm.DB) (err error) {
if u.MemberShip == "" {
u.MemberShip = "user"
}
return
}
8.5.6 Modify current operation
func (u *User) BeforeCreate(tx *gorm.DB) error {
// Modify current operation through tx.Statement, e.g:
tx.Statement.Select("Name", "Age")
tx.Statement.AddClause(clause.OnConflict{DoNothing: true})
// tx is new session mode with the `NewDB` option
// operations based on it will run inside same transaction but without any current conditions
var role Role
err := tx.First(&role, "name = ?", user.Role).Error
// SELECT * FROM roles WHERE name = "admin"
// ...
return err
}
8.6 Transactions
8.6.1 Disable Default Transaction
GORM perform write (create/update/delete) operations run inside a transaction to ensure data consistency, you can disable it during initialization if it is not required, you will gain about 30%+ performance improvement after that
// Globally disable
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
SkipDefaultTransaction: true,
})
// Continuous session mode
tx := db.Session(&Session{SkipDefaultTransaction: true})
tx.First(&user, 1)
tx.Find(&users)
tx.Model(&user).Update("Age", 18)
8.6.2 Transaction
To perform a set of operations within a transaction, the general flow is as below
db.Transaction(func(tx *gorm.DB) error {
// do some database operations in the transaction (use 'tx' from this point, not 'db')
if err := tx.Create(&Animal{Name: "Giraffe"}).Error; err != nil {
// return any error will rollback
return err
}
if err := tx.Create(&Animal{Name: "Lion"}).Error; err != nil {
return err
}
// return nil will commit the whole transaction
return nil
})
8.6.3 Nested Transactions
GORM supports nested transactions, you can rollback a subset of operations performed within the scope of a larger transaction, for example:
db.Transaction(func(tx *gorm.DB) error {
tx.Create(&user1)
tx.Transaction(func(tx2 *gorm.DB) error {
tx2.Create(&user2)
return errors.New("rollback user2") // Rollback user2
})
tx.Transaction(func(tx2 *gorm.DB) error {
tx2.Create(&user3)
return nil
})
return nil
})
// Commit user1, user3
8.6.4 Control the transaction manually
Gorm supports calling transaction control functions (commit / rollback) directly, for example:
// begin a transaction
tx := db.Begin()
// do some database operations in the transaction (use 'tx' from this point, not 'db')
tx.Create(...)
// ...
// rollback the transaction in case of error
tx.Rollback()
// Or commit the transaction
tx.Commit()
8.6.5 A Specific Example
func CreateAnimals(db *gorm.DB) error {
// Note the use of tx as the database handle once you are within a transaction
tx := db.Begin()
defer func() {
if r := recover(); r != nil {
tx.Rollback()
}
}()
if err := tx.Error; err != nil {
return err
}
if err := tx.Create(&Animal{Name: "Giraffe"}).Error; err != nil {
tx.Rollback()
return err
}
if err := tx.Create(&Animal{Name: "Lion"}).Error; err != nil {
tx.Rollback()
return err
}
return tx.Commit().Error
}
8.6.6 SavePoint, RollbackTo
GORM provides SavePoint, RollbackTo to save points and roll back to a savepoint, for example:
tx := db.Begin()
tx.Create(&user1)
tx.SavePoint("sp1")
tx.Create(&user2)
tx.RollbackTo("sp1") // Rollback user2
tx.Commit() // Commit user1
8.7 Migration
8.7.1 Auto Migration
Automatically migrate your schema, to keep your schema up to date
NOTE:
AutoMigrate will create tables, missing foreign keys, constraints, columns and indexes. It will change existing column’s type if its size, precision, nullable changed. It WON’T delete unused columns to protect your data.
db.AutoMigrate(&User{})
db.AutoMigrate(&User{}, &Product{}, &Order{})
// Add table suffix when creating tables
db.Set("gorm:table_options", "ENGINE=InnoDB").AutoMigrate(&User{})
NOTE
AutoMigrate creates database foreign key constraints automatically, you can disable this feature during initialization, for example:
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{ DisableForeignKeyConstraintWhenMigrating: true,})
8.7.2 Migrator Interface
GORM provides a migrator interface, which contains unified API interfaces for each database that could be used to build your database-independent migrations, for example:
SQLite doesn’t support ALTER COLUMN, DROP COLUMN, GORM will create a new table as the one you are trying to change, copy all data, drop the old table, rename the new table
MySQL doesn’t support rename column, index for some versions, GORM will perform different SQL based on the MySQL version you are using
type Migrator interface {
// AutoMigrate
AutoMigrate(dst ...interface{}) error
// Database
CurrentDatabase() string
FullDataTypeOf(*schema.Field) clause.Expr
// Tables
CreateTable(dst ...interface{}) error
DropTable(dst ...interface{}) error
HasTable(dst interface{}) bool
RenameTable(oldName, newName interface{}) error
// Columns
AddColumn(dst interface{}, field string) error
DropColumn(dst interface{}, field string) error
AlterColumn(dst interface{}, field string) error
HasColumn(dst interface{}, field string) bool
RenameColumn(dst interface{}, oldName, field string) error
MigrateColumn(dst interface{}, field *schema.Field, columnType *sql.ColumnType) error
ColumnTypes(dst interface{}) ([]*sql.ColumnType, 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
}
8.7.3 CurrentDatabase
Returns current using database name
db.Migrator().CurrentDatabase()
8.7.4 Tables
// Create table for `User`
db.Migrator().CreateTable(&User{})
// Append "ENGINE=InnoDB" to the creating table SQL for `User`
db.Set("gorm:table_options", "ENGINE=InnoDB").Migrator().CreateTable(&User{})
// Check table for `User` exists or not
db.Migrator().HasTable(&User{})
db.Migrator().HasTable("users")
// Drop table if exists (will ignore or delete foreign key constraints when dropping)
db.Migrator().DropTable(&User{})
db.Migrator().DropTable("users")
// Rename old table to new table
db.Migrator().RenameTable(&User{}, &UserInfo{})
db.Migrator().RenameTable("users", "user_infos")
8.7.5 Columns
type User struct {
Name string
}
// Add name field
db.Migrator().AddColumn(&User{}, "Name")
// Drop name field
db.Migrator().DropColumn(&User{}, "Name")
// Alter name field
db.Migrator().AlterColumn(&User{}, "Name")
// Check column exists
db.Migrator().HasColumn(&User{}, "Name")
type User struct {
Name string
NewName string
}
// Rename column to new name
db.Migrator().RenameColumn(&User{}, "Name", "NewName")
db.Migrator().RenameColumn(&User{}, "name", "new_name")
// ColumnTypes
db.Migrator().ColumnTypes(&User{}) ([]*sql.ColumnType, error)
8.7.6 Constraints
type UserIndex struct {
Name string `gorm:"check:name_checker,name <> 'jinzhu'"`
}
// Create constraint
db.Migrator().CreateConstraint(&User{}, "name_checker")
// Drop constraint
db.Migrator().DropConstraint(&User{}, "name_checker")
// Check constraint exists
db.Migrator().HasConstraint(&User{}, "name_checker")
Create foreign keys for relations
type User struct {
gorm.Model
CreditCards []CreditCard
}
type CreditCard struct {
gorm.Model
Number string
UserID uint
}
// create database foreign key for user & credit_cards
db.Migrator().CreateConstraint(&User{}, "CreditCards")
db.Migrator().CreateConstraint(&User{}, "fk_users_credit_cards")
// ALTER TABLE `credit_cards` ADD CONSTRAINT `fk_users_credit_cards` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
// check database foreign key for user & credit_cards exists or not
db.Migrator().HasConstraint(&User{}, "CreditCards")
db.Migrator().HasConstraint(&User{}, "fk_users_credit_cards")
// drop database foreign key for user & credit_cards
db.Migrator().DropConstraint(&User{}, "CreditCards")
db.Migrator().DropConstraint(&User{}, "fk_users_credit_cards")
8.7.7 Indexes
type User struct {
gorm.Model
Name string `gorm:"size:255;index:idx_name,unique"`
}
// Create index for Name field
db.Migrator().CreateIndex(&User{}, "Name")
db.Migrator().CreateIndex(&User{}, "idx_name")
// Drop index for Name field
db.Migrator().DropIndex(&User{}, "Name")
db.Migrator().DropIndex(&User{}, "idx_name")
// Check Index exists
db.Migrator().HasIndex(&User{}, "Name")
db.Migrator().HasIndex(&User{}, "idx_name")
type User struct {
gorm.Model
Name string `gorm:"size:255;index:idx_name,unique"`
Name2 string `gorm:"size:255;index:idx_name_2,unique"`
}
// Rename index name
db.Migrator().RenameIndex(&User{}, "Name", "Name2")
db.Migrator().RenameIndex(&User{}, "idx_name", "idx_name_2")
8.7.8 Constraints
GORM creates constraints when auto migrating or creating table, see Constraints or Database Indexes for details
8.7.9 Other Migration Tools
GORM’s AutoMigrate works well for most cases, but if you are looking for more serious migration tools, GORM provides a generic DB interface that might be helpful for you.
// returns `*sql.DB`
db.DB()
Refer to Generic Interface for more details.
8.8 Logger
GORM has a default logger implementation, it will print Slow SQL and happening errors by default
The logger accepts few options, you can customize it during initialization
newLogger := logger.New(
log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
logger.Config{
SlowThreshold: time.Second, // Slow SQL threshold
LogLevel: logger.Silent, // Log level
IgnoreRecordNotFoundError: true, // Ignore ErrRecordNotFound error for logger
Colorful: false, // Disable color
},
)
// Globally mode
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{
Logger: newLogger,
})
// Continuous session mode
tx := db.Session(&Session{Logger: newLogger})
tx.First(&user)
tx.Model(&user).Update("Age", 18)
8.8.1 Log Levels
GORM defined log levels: Slient,Error,Warn,Info
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{
Logger: logger.Default.LogMode(logger.Silent),
})
8.8.2 Debug
Debug a single operation, change current operation’s log level to logger.Info
db.Debug().Where("name = ?", "jinzhu").First(&User{})
8.8.3 Customize Logger
Refer to GORM’s default logger for how to define your own one
The logger needs to implement the following interface, it accepts context, so you can use it for log tracing
type Interface interface {
LogMode(LogLevel) Interface
Info(context.Context, string, ...interface{})
Warn(context.Context, string, ...interface{})
Error(context.Context, string, ...interface{})
Trace(ctx context.Context, begin time.Time, fc func() (sql string, rowsAffected int64), err error)
}
8.9 Generic Database Interface sql.DB
GORM provides the method DB which returns a generic database interface *sql.DB from the current *gorm.DB
// Get generic database object sql.DB to use its functions
sqlDB, err := db.DB()
// Ping
sqlDB.Ping()
// Close
sqlDB.Close()
// Returns database statistics
sqlDB.Stats()
NOTE
If the underlying database connection is not a *sql.DB, like in a transaction, it will returns error
8.9.1 Connection Pool
// Get generic database object sql.DB to use its functions
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)
8.10 Performance
GORM optimizes many things to improve the performance, the default performance should goog for most applications, but there are still some tips for how to improve it for your application
8.10.1 Disable Default Transaction
GORM perform write (create/update/delete) operations run inside a transaction to ensure data consistency, which is bad for performance, you can disable it during initialization
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
SkipDefaultTransaction: true,
})
8.10.2 Caches Prepared Statement
Creates a prepared statement when executing any SQL and caches them to speed up future calls
// Globally mode
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
PrepareStmt: true,
})
// Session mode
tx := db.Session(&Session{PrepareStmt: true})
tx.First(&user, 1)
tx.Find(&users)
tx.Model(&user).Update("Age", 18)
NOTE
Also refer how to enable interpolateparams for MySQL to reduce roundtrip https://github.com/go-sql-driver/mysql#interpolateparams
8.10.3 SQL Builder with PreparedStmt
Prepared Statement works with RAW SQL also, for example:
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
PrepareStmt: true,
})
db.Raw("select sum(age) from users where role = ?", "admin").Scan(&age)
You can also use GORM API to prepare SQL with DryRun Mode, and execute it with prepared statement later, checkout Session Mode for details
8.10.4 Select Fields
By default GORM select all fields when quering, you can use Select to specify fields you want
db.Select("Name", "Age").Find(&Users{})
Or define a smaller API struct to use the smart select fields feature
type User struct {
ID uint
Name string
Age int
Gender string
// hundreds of fields
}
type APIUser struct {
ID uint
Name string
}
// Select `id`, `name` automatically when query
db.Model(&User{}).Limit(10).Find(&APIUser{})
// SELECT `id`, `name` FROM `users` LIMIT 10
8.10.5 Iteration/FindInBatches
Query and process records with iteration or in batches
8.10.6 Index Hints
Index is used to speed up data search and SQL query performance. Index Hints gives the optimizer information about how to choose indexes during query processing, which gives the flexibility to choose a more efficient execution plan than the optimizer
import "gorm.io/hints"
db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{})
// SELECT * FROM `users` USE INDEX (`idx_user_name`)
db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{})
// SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"
db.Clauses(
hints.ForceIndex("idx_user_name", "idx_user_id").ForOrderBy(),
hints.IgnoreIndex("idx_user_name").ForGroupBy(),
).Find(&User{})
// SELECT * FROM `users` FORCE INDEX FOR ORDER BY (`idx_user_name`,`idx_user_id`) IGNORE INDEX FOR GROUP BY (`idx_user_name`)"
8.10.7 Read/Write Splitting
Increase data throughput through read/write splitting, check out Database Resolver
8.11 Customize Data Types
GORM provides few interfaces that allow users to define well-supported customized data types for GORM
8.11.1 Scanner / Valuer
The customized data type has to implement the Scanner and Valuer interfaces, so GORM knowns to how to receive/save it into the database
For example:
type JSON json.RawMessage
// Scan scan value into Jsonb, implements sql.Scanner interface
func (j *JSON) Scan(value interface{}) error {
bytes, ok := value.([]byte)
if !ok {
return errors.New(fmt.Sprint("Failed to unmarshal JSONB value:", value))
}
result := json.RawMessage{}
err := json.Unmarshal(bytes, &result)
*j = JSON(result)
return err
}
// Value return json value, implement driver.Valuer interface
func (j JSON) Value() (driver.Value, error) {
if len(j) == 0 {
return nil, nil
}
return json.RawMessage(j).MarshalJSON()
}
There are many third party packages implement the Scanner/Valuer interface, which can be used with GORM together, for example:
import (
"github.com/google/uuid"
"github.com/lib/pq"
)
type Post struct {
ID uuid.UUID `gorm:"type:uuid;default:uuid_generate_v4()"`
Title string
Tags pq.StringArray `gorm:"type:text[]"`
}
8.11.2 GormDataTypeInterface
GORM will read column’s database type from tag type, if not found, will check if the struct implemented interface GormDBDataTypeInterface or GormDataTypeInterface and will use its result as data type
type GormDataTypeInterface interface {
GormDataType() string
}
type GormDBDataTypeInterface interface {
GormDBDataType(*gorm.DB, *schema.Field) string
}
The result of GormDataType will be used as the general data type and can be obtained from schema.Field‘s field DataType, which might be helpful when writing plugins or hooks for example:
func (JSON) GormDataType() string {
return "json"
}
type User struct {
Attrs JSON
}
func (user User) BeforeCreate(tx *gorm.DB) {
field := tx.Statement.Schema.LookUpField("Attrs")
if field.DataType == "json" {
// do something
}
}
GormDBDataType usually returns the right data type for current driver when migrating, for example:
func (JSON) GormDBDataType(db *gorm.DB, field *schema.Field) string {
// use field.Tag, field.TagSettings gets field's tags
// checkout https://github.com/go-gorm/gorm/blob/master/schema/field.go for all options
// returns different database type based on driver name
switch db.Dialector.Name() {
case "mysql", "sqlite":
return "JSON"
case "postgres":
return "JSONB"
}
return ""
}
If the struct hasn’t implemented the GormDBDataTypeInterface or GormDataTypeInterface interface, GORM will guess its data type from the struct’s first field, for example, will use string for NullString
type NullString struct {
String string // use the first field's data type
Valid bool
}
type User struct {
Name NullString // data type will be string
}
8.11.3 GormValuerInterface
GORM provides a GormValuerInterface interface, which can allow to create/update from SQL Expr or value based on context, for example:
// GORM Valuer interface
type GormValuerInterface interface {
GormValue(ctx context.Context, db *gorm.DB) clause.Expr
}
8.11.4 Create/Update from SQL Expr
type Location struct {
X, Y int
}
func (loc Location) GormDataType() string {
return "geometry"
}
func (loc Location) GormValue(ctx context.Context, db *gorm.DB) clause.Expr {
return clause.Expr{
SQL: "ST_PointFromText(?)",
Vars: []interface{}{fmt.Sprintf("POINT(%d %d)", loc.X, loc.Y)},
}
}
// Scan implements the sql.Scanner interface
func (loc *Location) Scan(v interface{}) error {
// Scan a value into struct from database driver
}
type User struct {
ID int
Name string
Location Location
}
db.Create(&User{
Name: "jinzhu",
Location: Location{X: 100, Y: 100},
})
// INSERT INTO `users` (`name`,`point`) VALUES ("jinzhu",ST_PointFromText("POINT(100 100)"))
db.Model(&User{ID: 1}).Updates(User{
Name: "jinzhu",
Location: Location{X: 100, Y: 100},
})
// UPDATE `user_with_points` SET `name`="jinzhu",`location`=ST_PointFromText("POINT(100 100)") WHERE `id` = 1
You can also create/update with SQL Expr from map, checkout Create From SQL Expr and Update with SQL Expression for details
8.11.5 Value based on Context
If you want to create or update a value depends on current context, you can also implements the GormValuerInterface interface, for example:
type EncryptedString struct {
Value string
}
func (es EncryptedString) GormValue(ctx context.Context, db *gorm.DB) (expr clause.Expr) {
if encryptionKey, ok := ctx.Value("TenantEncryptionKey").(string); ok {
return clause.Expr{SQL: "?", Vars: []interface{}{Encrypt(es.Value, encryptionKey)}}
} else {
db.AddError(errors.New("invalid encryption key"))
}
return
}
8.11.6 Clause Expression
If you want to build some query helpers, you can make a struct that implements the clause.Expression interface:
type Expression interface {
Build(builder Builder)
}
Checkout JSON and SQL Builder for details, the following is an example of usage:
// Generates SQL with clause Expression
db.Find(&user, datatypes.JSONQuery("attributes").HasKey("role"))
db.Find(&user, datatypes.JSONQuery("attributes").HasKey("orgs", "orga"))
// MySQL
// SELECT * FROM `users` WHERE JSON_EXTRACT(`attributes`, '$.role') IS NOT NULL
// SELECT * FROM `users` WHERE JSON_EXTRACT(`attributes`, '$.orgs.orga') IS NOT NULL
// PostgreSQL
// SELECT * FROM "user" WHERE "attributes"::jsonb ? 'role'
// SELECT * FROM "user" WHERE "attributes"::jsonb -> 'orgs' ? 'orga'
db.Find(&user, datatypes.JSONQuery("attributes").Equals("jinzhu", "name"))
// MySQL
// SELECT * FROM `user` WHERE JSON_EXTRACT(`attributes`, '$.name') = "jinzhu"
// PostgreSQL
// SELECT * FROM "user" WHERE json_extract_path_text("attributes"::json,'name') = 'jinzhu'
8.11.7 Customized Data Types Collections
We created a Github repo for customized data types collections https://github.com/go-gorm/datatypes
8.12 Scopes
Scopes allow you to re-use commonly used logic, the shared logic needs to be defined as type func(*gorm.DB) *gorm.DB
8.12.1 Query
Scope examples for querying
func AmountGreaterThan1000(db *gorm.DB) *gorm.DB {
return db.Where("amount > ?", 1000)
}
func PaidWithCreditCard(db *gorm.DB) *gorm.DB {
return db.Where("pay_mode = ?", "card")
}
func PaidWithCod(db *gorm.DB) *gorm.DB {
return db.Where("pay_mode = ?", "cod")
}
func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {
return func (db *gorm.DB) *gorm.DB {
return db.Scopes(AmountGreaterThan1000).Where("status IN (?)", status)
}
}
db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders)
// Find all credit card orders and amount greater than 1000
db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders)
// Find all COD orders and amount greater than 1000
db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders)
// Find all paid, shipped orders that amount greater than 1000
8.12.2 Pagination
func Paginate(r *http.Request) func(db *gorm.DB) *gorm.DB {
return func (db *gorm.DB) *gorm.DB {
page, _ := strconv.Atoi(r.Query("page"))
if page == 0 {
page = 1
}
pageSize, _ := strconv.Atoi(r.Query("page_size"))
switch {
case pageSize > 100:
pageSize = 100
case pageSize <= 0:
pageSize = 10
}
offset := (page - 1) * pageSize
return db.Offset(offset).Limit(pageSize)
}
}
db.Scopes(Paginate(r)).Find(&users)
db.Scopes(Paginate(r)).Find(&articles)
8.12.3 Dynamically Table
Use Scopes to dynamically set the query Table
func TableOfYear(user *User, year int) func(db *gorm.DB) *gorm.DB {
return func(db *gorm.DB) *gorm.DB {
tableName := user.TableName() + strconv.Itoa(year)
return db.Table(tableName)
}
}
DB.Scopes(TableOfYear(user, 2019)).Find(&users)
// SELECT * FROM users_2019;
DB.Scopes(TableOfYear(user, 2020)).Find(&users)
// SELECT * FROM users_2020;
// Table form different database
func TableOfOrg(user *User, dbName string) func(db *gorm.DB) *gorm.DB {
return func(db *gorm.DB) *gorm.DB {
tableName := dbName + "." + user.TableName()
return db.Table(tableName)
}
}
DB.Scopes(TableOfOrg(user, "org1")).Find(&users)
// SELECT * FROM org1.users;
DB.Scopes(TableOfOrg(user, "org2")).Find(&users)
// SELECT * FROM org1.users;
8.12.4 Updates
Scope examples for updating/deleting
func CurOrganization(r *http.Request) func(db *gorm.DB) *gorm.DB {
return func (db *gorm.DB) *gorm.DB {
org := r.Query("org")
if org != "" {
var organization Organization
if db.Session(&Session{}).First(&organization, "name = ?", org).Error == nil {
return db.Where("org_id = ?", organization.ID)
}
}
db.AddError("invalid organization")
return db
}
}
db.Model(&article).Scopes(CurOrganization(r)).Update("Name", "name 1")
// UPDATE articles SET name = "name 1" WHERE org_id = 111
db.Scopes(CurOrganization(r)).Delete(&Article{})
// DELETE FROM articles WHERE org_id = 111
8.13 Conventions
8.13.1 ID as Primary Key
GORM uses the field with the name ID as the tables’s primary key by default
type User struct {
ID string // field named `ID` will be used as a primary field by default
Name string
}
You can set other fields as primary key with tag primaryKey
// Set field `UUID` as primary field
type Animal struct {
ID int64
UUID string `gorm:"primaryKey"`
Name string
Age int64
}
8.13.2 Pluralized Table Name
GORM pluralizes struct name to snake_cases as table name, for struct User, its table name is users by convention
TableName
You can change the default table name by implementing the Tabler interface
type Tabler interface {
TableName() string
}
// TableName overrides the table name used by User to `profiles`
func (User) TableName() string {
return "profiles"
}
NOTE
TableName doesn’t allow dynamic name, its result will be cached for future, to use dynamic name, you can use Scopes
func UserTable(user User) func (tx *gorm.DB) *gorm.DB {
return func (tx *gorm.DB) *gorm.DB {
if user.Admin {
return tx.Table("admin_users")
}
return tx.Table("users")
}
}
db.Scopes(UserTable(user)).Create(&user)
Temporarily specify a name
Temporarily specify table name with Table method, for example:
// Create table `deleted_users` with struct User's fields
db.Table("deleted_users").AutoMigrate(&User{})
// Query data from another table
var deletedUsers []User
db.Table("deleted_users").Find(&deletedUsers)
// SELECT * FROM deleted_users;
db.Table("deleted_users").Where("name = ?", "jinzhu").Delete(&User{})
// DELETE FROM deleted_users WHERE name = 'jinzhu';
Check out From SubQuery for how to use SubQuery in FROM clause
NamingStrategy
GORM allows users change the default naming conventions by overriding the default NamingStrategy, which is used to build TableName, ColumnName, JoinTableName, RelationshipFKName, CheckerName, IndexName, Check out GORM Config for details
8.13.3 Column Name
Column name uses the field’s name’s snake_case by convention
type User struct {
ID uint // column name is `id`
Name string // column name is `name`
Birthday time.Time // column name is `birthday`
CreatedAt time.Time // column name is `created_at`
}
You can override the column name with tag column or use NamingStrategy
type Animal struct {
AnimalID int64 `gorm:"column:beast_id"` // set name to `beast_id`
Birthday time.Time `gorm:"column:day_of_the_beast"` // set name to `day_of_the_beast`
Age int64 `gorm:"column:age_of_the_beast"` // set name to `age_of_the_beast`
}
8.13.4 Timestamp Tracking
CreatedAt
For models having CreatedAt field, the field will be set to the current time when the record is first created if its value is zero
db.Create(&user) // set `CreatedAt` to current time
user2 := User{Name: "jinzhu", CreatedAt: time.Now()}
db.Create(&user2) // user2's `CreatedAt` won't be changed
// To change its value, you could use `Update`
db.Model(&user).Update("CreatedAt", time.Now())
UpdatedAt
For models having UpdatedAt field, the field will be set to the current time when the record is updated or created if its value is zero
db.Save(&user) // set `UpdatedAt` to current time
db.Model(&user).Update("name", "jinzhu") // will set `UpdatedAt` to current time
db.Model(&user).UpdateColumn("name", "jinzhu") // `UpdatedAt` won't be changed
user2 := User{Name: "jinzhu", UpdatedAt: time.Now()}
db.Create(&user2) // user2's `UpdatedAt` won't be changed when creating
user3 := User{Name: "jinzhu", UpdatedAt: time.Now()}
db.Save(&user3) // user3's `UpdatedAt` will change to current time when updating
NOTE GORM supports having multiple time tracking fields and track with UNIX (nano/milli) seconds, checkout Models for more details
8.14 Settings
GORM provides Set,Get,InstanceSet,InstanceGet methods allow users pass values to hooks or other methods
GORM uses this for some features, like pass creating table options when migratinig table
// Add table suffix when creating tables
db.Set("gorm:table_options", "ENGINE=InnoDB").AutoMigrate(&User{})
8.14.1 Set/Get
Use Set / Get pass settings to hooks methods, for example:
type User struct {
gorm.Model
CreditCard CreditCard
// ...
}
func (u *User) BeforeCreate(tx *gorm.DB) error {
myValue, ok := tx.Get("my_value")
// ok => true
// myValue => 123
}
type CreditCard struct {
gorm.Model
// ...
}
func (card *CreditCard) BeforeCreate(tx *gorm.DB) error {
myValue, ok := tx.Get("my_value")
// ok => true
// myValue => 123
}
myValue := 123
db.Set("my_value", myValue).Create(&User{})
8.14.2 InstanceSet/InstanceGet
Use InstanceSet / InstanceGet pass settings to current *Statement‘s hooks methods, for example:
type User struct {
gorm.Model
CreditCard CreditCard
// ...
}
func (u *User) BeforeCreate(tx *gorm.DB) error {
myValue, ok := tx.InstanceGet("my_value")
// ok => true
// myValue => 123
}
type CreditCard struct {
gorm.Model
// ...
}
// When creating associations, GORM creates a new `*Statement`, so can't read other instance's settings
func (card *CreditCard) BeforeCreate(tx *gorm.DB) error {
myValue, ok := tx.InstanceGet("my_value")
// ok => false
// myValue => nil
}
myValue := 123
db.InstanceSet("my_value", myValue).Create(&User{})
9. Advanced Topics
9.1 DBResolver
DBResolver adds muliple databases support to GORM, the following features are supported
Multiple sources, replicas
Read/Write Splitting
Automatic connection switching based on the working table/struct
Manual connection switching
Sources/Replicas load balancing
Works for RAW SQL
9.1.1 Usage
import (
"gorm.io/gorm"
"gorm.io/plugin/dbresolver"
"gorm.io/driver/mysql"
)
db, err := gorm.Open(mysql.Open("db1_dsn"), &gorm.Config{})
db.Use(dbresolver.Register(dbresolver.Config{
// use `db2` as sources, `db3`, `db4` as replicas
Sources: []gorm.Dialector{mysql.Open("db2_dsn")},
Replicas: []gorm.Dialector{mysql.Open("db3_dsn"), mysql.Open("db4_dsn")},
// sources/replicas load balancing policy
Policy: dbresolver.RandomPolicy{},
}).Register(dbresolver.Config{
// use `db1` as sources (DB's default connection), `db5` as replicas for `User`, `Address`
Replicas: []gorm.Dialector{mysql.Open("db5_dsn")},
}, &User{}, &Address{}).Register(dbresolver.Config{
// use `db6`, `db7` as sources, `db8` as replicas for `orders`, `Product`
Sources: []gorm.Dialector{mysql.Open("db6_dsn"), mysql.Open("db7_dsn")},
Replicas: []gorm.Dialector{mysql.Open("db8_dsn")},
}, "orders", &Product{}, "secondary"))
9.1.2 Transaction
when using transaction, DBResolver will use the transaction and won’t switch to sources/replicas
9.2.3 Automatic connection switching
DBResolver will automatically switch connection based on the working table/struct
For RAW SQL, DBResolver will extract the table name from the SQL to match the resolver, and will use sources unless the SQL begins with SELECT (excepts SELECT... FOR UPDATE), for example:
// `User` Resolver Examples
db.Table("users").Rows() // replicas `db5`
db.Model(&User{}).Find(&AdvancedUser{}) // replicas `db5`
db.Exec("update users set name = ?", "jinzhu") // sources `db1`
db.Raw("select name from users").Row().Scan(&name) // replicas `db5`
db.Create(&user) // sources `db1`
db.Delete(&User{}, "name = ?", "jinzhu") // sources `db1`
db.Table("users").Update("name", "jinzhu") // sources `db1`
// Global Resolver Examples
db.Find(&Pet{}) // replicas `db3`/`db4`
db.Save(&Pet{}) // sources `db2`
// Orders Resolver Examples
db.Find(&Order{}) // replicas `db8`
db.Table("orders").Find(&Report{}) // replicas `db8`
9.2.4 Read/Write Splitting
Read/Write splitting with DBResolver based on the current used GORM callbacks.
For Query, Row callback, will use replicas unless Write mode specified For Raw callback, statements are considered read-only and will use replicas if the SQL starts with SELECT
9.2.5 Manual connection switching
// Use Write Mode: read user from sources `db1`
db.Clauses(dbresolver.Write).First(&user)
// Specify Resolver: read user from `secondary`'s replicas: db8
db.Clauses(dbresolver.Use("secondary")).First(&user)
// Specify Resolver and Write Mode: read user from `secondary`'s sources: db6 or db7
db.Clauses(dbresolver.Use("secondary"), dbresolver.Write).First(&user)
9.2.6 Load Balancing
GORM supports load balancing sources/relicas based on policy, the policy should be a struct implements following interface:
type Policy interface {
Resolve([]gorm.ConnPool) gorm.ConnPool
}
Currently only the RandomPolicy implemented and it is the default option if no other policy specified.
9.2.7 Connection Pool
db.Use(
dbresolver.Register(dbresolver.Config{ /* xxx */ }).
SetConnMaxIdleTime(time.Hour).
SetConnMaxLifetime(24 * time.Hour).
SetMaxIdleConns(100)
SetMaxOpenConns(200)
)
9.2 Prometheus
GORM provides Prometheus plugin to collect DBStats or user-defined metrics
https://github.com/go-gorm/prometheus
9.2.1 Usage
import (
"gorm.io/gorm"
"gorm.io/driver/sqlite"
"gorm.io/plugin/prometheus"
)
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{})
db.Use(prometheus.New(prometheus.Config{
DBName: "db1", // use `DBName` as metrics label
RefreshInterval: 15, // Refresh metrics interval (default 15 seconds)
PushAddr: "prometheus pusher address", // push metrics if `PushAddr` configured
StartServer: true, // start http server to expose metrics
HTTPServerPort: 8080, // configure http server port, default port 8080 (if you have configured multiple instances, only the first `HTTPServerPort` will be used to start server)
MetricsCollector: []prometheus.MetricsCollector {
&prometheus.MySQL{
VariableNames: []string{"Threads_running"},
},
}, // user defined metrics
}))
9.2.2 User-Defined Metrics
You can define your metrics and collect them with GORM Prometheus plugin, which needs to implements MetricsCollector interface
type MetricsCollector interface {
Metrics(*Prometheus) []prometheus.Collector
}
9.2.3 MySQL
GORM provides an example for how to collect MySQL Status as metrics, check it out prometheus.MySQL
&prometheus.MySQL{
Prefix: "gorm_status_",
// Metrics name prefix, default is `gorm_status_`
// For example, Threads_running's metric name is `gorm_status_Threads_running`
Interval: 100,
// Fetch interval, default use Prometheus's RefreshInterval
VariableNames: []string{"Threads_running"},
// Select variables from SHOW STATUS, if not set, uses all status variables
}
9.3 Hints
GORM provides optimizer/index/comment hints support
https://github.com/go-gorm/hints
9.3.1 Optimizer Hints
import "gorm.io/hints"
db.Clauses(hints.New("hint")).Find(&User{})
// SELECT * /*+ hint */ FROM `users`
9.3.2 Index Hints
import "gorm.io/hints"
db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{})
// SELECT * FROM `users` USE INDEX (`idx_user_name`)
db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{})
// SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"
db.Clauses(
hints.ForceIndex("idx_user_name", "idx_user_id").ForOrderBy(),
hints.IgnoreIndex("idx_user_name").ForGroupBy(),
).Find(&User{})
// SELECT * FROM `users` FORCE INDEX FOR ORDER BY (`idx_user_name`,`idx_user_id`) IGNORE INDEX FOR GROUP BY (`idx_user_name`)"
9.3.3 Comment Hints
import "gorm.io/hints"
db.Clauses(hints.Comment("select", "master")).Find(&User{})
// SELECT /*master*/ * FROM `users`;
db.Clauses(hints.CommentBefore("insert", "node2")).Create(&user)
// /*node2*/ INSERT INTO `users` ...;
db.Clauses(hints.CommentAfter("select", "node2")).Create(&user)
// /*node2*/ INSERT INTO `users` ...;
db.Clauses(hints.CommentAfter("where", "hint")).Find(&User{}, "id = ?", 1)
// SELECT * FROM `users` WHERE id = ? /* hint */
9.4 Database Indexes
GORM allows create databases index with tag index,uniqueIndex, those indexes will be created when AutoMigrate or CreateTable with GORM
9.4.1 Index Tag
GORM accepts lots of index settings, like class, type, where, comment, expression, sort, collate, option
Check the following example for how to use it
type User struct {
Name string `gorm:"index"`
Name2 string `gorm:"index:idx_name,unique"`
Name3 string `gorm:"index:,sort:desc,collate:utf8,type:btree,length:10,where:name3 != 'jinzhu'"`
Name4 string `gorm:"uniqueIndex"`
Age int64 `gorm:"index:,class:FULLTEXT,comment:hello \\, world,where:age > 10"`
Age2 int64 `gorm:"index:,expression:ABS(age)"`
}
// MySQL option
type User struct {
Name string `gorm:"index:,class:FULLTEXT,option:WITH PARSER ngram INVISIBLE"`
}
// PostgreSQL option
type User struct {
Name string `gorm:"index:,option:CONCURRENTLY"`
}
9.4.2 uniqueIndex
tag uniqueIndex works similar like index, it equals to index:,unique
type User struct {
Name1 string `gorm:"uniqueIndex"`
Name2 string `gorm:"uniqueIndex:idx_name,sort:desc"`
}
9.4.3 Composite Indexes
Use same index name for two fields will creates composite indexes, for example:
type User struct {
Name string `gorm:"index:idx_member"`
Number string `gorm:"index:idx_member"`
}
9.4.4 Fields Priority
The column order of a composite index has an impact on its performance so it must be chosen carefully
You can specify the order with the priority option, the default priority value is 10, if priority value is the same, the order will be based on model struct’s field index
type User struct {
Name string `gorm:"index:idx_member"`
Number string `gorm:"index:idx_member"`
}
// column order: name, number
type User struct {
Name string `gorm:"index:idx_member,priority:2"`
Number string `gorm:"index:idx_member,priority:1"`
}
// column order: number, name
type User struct {
Name string `gorm:"index:idx_member,priority:12"`
Number string `gorm:"index:idx_member"`
}
// column order: number, name
9.4.5 Multiple indexes
A field accepts multiple index, uniqueIndex tags that will create multiple indexes on a field
type UserIndex struct {
OID int64 `gorm:"index:idx_id;index:idx_oid,unique"`
MemberNumber string `gorm:"index:idx_id"`
}
9.5 Constraints
GORM allows create database constraints with tag, constraints will be created when AutoMigrate or CreateTable with GORM
9.5.1 CHECK Constraint
Create CHECK constraints with tag check
type UserIndex struct {
Name string `gorm:"check:name_checker,name <> 'jinzhu'"`
Name2 string `gorm:"check:name <> 'jinzhu'"`
Name3 string `gorm:"check:,name <> 'jinzhu'"`
}
9.5.2 Index Constraint
Checkout Database Indexes
9.5.3 Foreign Key Constraint
GORM will creates foreign keys constraints for associations, you can disable this feature during initialization:
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
DisableForeignKeyConstraintWhenMigrating: true,
})
GORM allows you setup FOREIGN KEY constraints’s OnDelete, OnUpdate option with tag constraint, for example:
type User struct {
gorm.Model
CompanyID int
Company Company `gorm:"constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`
CreditCard CreditCard `gorm:"constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`
}
type CreditCard struct {
gorm.Model
Number string
UserID uint
}
type Company struct {
ID int
Name string
}
9.6 Composite Primary Key
Set multiple fields as primary key creates composite primary key, for example:
type Product struct {
ID string `gorm:"primaryKey"`
LanguageCode string `gorm:"primaryKey"`
Code string
Name string
}
Note
integer PrioritizedPrimaryField enables AutoIncrement by default, to disable it, you need to turn off autoIncrement for the int fields:
type Product struct {
CategoryID uint64 `gorm:"primaryKey;autoIncrement:false"`
TypeID uint64 `gorm:"primaryKey;autoIncrement:false"`
}
9.6 Security
GORM uses the database/sql‘s argument placeholders to construct the SQL statement, which will automatically escape arguments to avoid SQL injection
NOTE
The SQL from Logger is not fully escaped like the one executed, be careful when copying and executing it in SQL console
9.6.1 Query Condition
User’s input should be only used as an argument, for example:
userInput := "jinzhu;drop table users;"
// safe, will be escaped
db.Where("name = ?", userInput).First(&user)
// SQL injection
db.Where(fmt.Sprintf("name = %v", userInput)).First(&user)
9.6.2 Inline Condition
// will be escaped
db.First(&user, "name = ?", userInput)
// SQL injection
db..First(&user, fmt.Sprintf("name = %v", userInput))
9.6.3 SQL injection Methods
To support some features, some inputs are not escaped, be careful when using user’s input with those methods
db.Select("name; drop table users;").First(&user)
db.Distinct("name; drop table users;").First(&user)
db.Model(&user).Pluck("name; drop table users;", &names)
db.Group("name; drop table users;").First(&user)
db.Group("name").Having("1 = 1;drop table users;").First(&user)
db.Raw("select name from users; drop table users;").First(&user)
db.Exec("select name from users; drop table users;")
db.Order("name; drop table users;").First(&user)
The general rule to avoid SQL injection is don’t trust user-submitted data, you can perform whitelist validation to test user input against an existing set of known, approved, and defined input, and when using user’s input, only use them as an argument.
9.7 Gorm Config
GORM provides Config can be used during initialization
type Config struct {
SkipDefaultTransaction bool
NamingStrategy schema.Namer
Logger logger.Interface
NowFunc func() time.Time
DryRun bool
PrepareStmt bool
DisableNestedTransaction bool
AllowGlobalUpdate bool
DisableAutomaticPing bool
DisableForeignKeyConstraintWhenMigrating bool
}
9.7.1 SkipDefaultTransaction
GORM perform write (create/update/delete) operations run inside a transaction to ensure data consistency, you can disable it during initialization if it is not required
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
SkipDefaultTransaction: true,
})
9.7.2 NamingStrategy
GORM allows users to change the naming conventions by overriding the default NamingStrategy which need to implements interface Namer
type Namer interface {
TableName(table string) string
SchemaName(table string) string
ColumnName(table, column string) string
JoinTableName(table string) string
RelationshipFKName(Relationship) string
CheckerName(table, column string) string
IndexName(table, column string) string
}
The default NamingStrategy also provides few options, like:
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
NamingStrategy: schema.NamingStrategy{
TablePrefix: "t_", // table name prefix, table for `User` would be `t_users`
SingularTable: true, // use singular table name, table for `User` would be `user` with this option enabled
NameReplacer: strings.NewReplacer("CID", "Cid"), // use name replacer to change struct/field name before convert it to db name
},
})
9.7.3 Logger
Allow to change GORM’s default logger by overriding this option, refer Logger for more details
9.7.4 NowFunc
Change the function to be used when creating a new timestamp
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
NowFunc: func() time.Time {
return time.Now().Local()
},
})
9.7.5 DryRun
Generate SQL without executing, can be used to prepare or test generated SQL, refer Session for details
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
DryRun: false,
})
9.7.6 PrepareStmt
PreparedStmt creates a prepared statement when executing any SQL and caches them to speed up future calls, refer Session for details
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
PrepareStmt: false,
})
9.7.7 DisableNestedTransaction
When using Transaction method inside a db transaction, GORM will use SavePoint(savedPointName), RollbackTo(savedPointName) to give you the nested transaction support, you could disable it by using the DisableNestedTransaction option, refer Session for details
9.7.8 AllowGlobalUpdate
Enable global update/delete, refer Session for details
9.7.9 DisableAutomaticPing
GORM automatically ping database after initialized to check database availability, disable it by setting it to true
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
DisableAutomaticPing: true,
})
9.7.10 DisableForeignKeyConstraintWhenMigrating
GORM creates database foreign key constraints automatically when AutoMigrate or CreateTable, disable this by setting it to true, refer Migration for details
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
DisableForeignKeyConstraintWhenMigrating: true,
})
9.8 Write Plugins
9.8.1 Callbacks
GORM itself is powered by Callbacks, it has callbacks for Create, Query, Update, Delete, Row, Raw, you could fully customize GORM with them as you want
Callbacks are registered into the global *gorm.DB, not the session-level, if you require *gorm.DB with different callbacks, you need to initialize another *gorm.DB
9.8.2 Register Callback
Register a callback into callbacks
func cropImage(db *gorm.DB) {
if db.Statement.Schema != nil {
// crop image fields and upload them to CDN, dummy code
for _, field := range db.Statement.Schema.Fields {
switch db.Statement.ReflectValue.Kind() {
case reflect.Slice, reflect.Array:
for i := 0; i < db.Statement.ReflectValue.Len(); i++ {
// Get value from field
if fieldValue, isZero := field.ValueOf(db.Statement.ReflectValue.Index(i)); !isZero {
if crop, ok := fieldValue.(CropInterface); ok {
crop.Crop()
}
}
}
case reflect.Struct:
// Get value from field
if fieldValue, isZero := field.ValueOf(db.Statement.ReflectValue); !isZero {
if crop, ok := fieldValue.(CropInterface); ok {
crop.Crop()
}
}
// Set value to field
err := field.Set(db.Statement.ReflectValue, "newValue")
}
}
// All fields for current model
db.Statement.Schema.Fields
// All primary key fields for current model
db.Statement.Schema.PrimaryFields
// Prioritized primary key field: field with DB name `id` or the first defined primary key
db.Statement.Schema.PrioritizedPrimaryField
// All relationships for current model
db.Statement.Schema.Relationships
// Find field with field name or db name
field := db.Statement.Schema.LookUpField("Name")
// processing
}
}
db.Callback().Create().Register("crop_image", cropImage)
// register a callback for Create process
9.8.1 Delete Callback
Delete a callback from callbacks
db.Callback().Create().Remove("gorm:create")
// delete callback `gorm:create` from Create callbacks
9.8.3 Replace Callback
Replace a callback having the same name with the new one
db.Callback().Create().Replace("gorm:create", newCreateFunction)
// replace callback `gorm:create` with new function `newCreateFunction` for Create process
9.8.4 Register Callback with orders
Register callbacks with orders
// before gorm:create
db.Callback().Create().Before("gorm:create").Register("update_created_at", updateCreated)
// after gorm:create
db.Callback().Create().After("gorm:create").Register("update_created_at", updateCreated)
// after gorm:query
db.Callback().Query().After("gorm:query").Register("my_plugin:after_query", afterQuery)
// after gorm:delete
db.Callback().Delete().After("gorm:delete").Register("my_plugin:after_delete", afterDelete)
// before gorm:update
db.Callback().Update().Before("gorm:update").Register("my_plugin:before_update", beforeUpdate)
// before gorm:create and after gorm:before_create
db.Callback().Create().Before("gorm:create").After("gorm:before_create").Register("my_plugin:before_create", beforeCreate)
// before any other callbacks
db.Callback().Create().Before("*").Register("update_created_at", updateCreated)
// after any other callbacks
db.Callback().Create().After("*").Register("update_created_at", updateCreated)
9.8.5 Defined Callbacks
GORM has defined some callbacks to power current GORM features, check them out before starting your plugins
9.8.6 Plugin
GORM provides a Use method to register plugins, the plugin needs to implement the Plugin interface
type Plugin interface {
Name() string
Initialize(*gorm.DB) error
}
The Initialize method will be invoked when registering the plugin into GORM first time, and GORM will save the registered plugins, access them like:
db.Config.Plugins[pluginName]
Checkout Prometheus as example
9.9 Write Driver
9.9.1 Write new driver
GORM provides official support for sqlite, mysql, postgres, sqlserver.
Some databases may be compatible with the mysql or postgres dialect, in which case you could just use the dialect for those databases.
For others, you can create a new driver, it needs to implement the dialect interface.
type Dialector interface {
Name() string
Initialize(*DB) error
Migrator(db *DB) Migrator
DataTypeOf(*schema.Field) string
DefaultValueOf(*schema.Field) clause.Expression
BindVarTo(writer clause.Writer, stmt *Statement, v interface{})
QuoteTo(clause.Writer, string)
Explain(sql string, vars ...interface{}) string
}
Checkout the MySQL Driver as example