GORM allows select specific fields with Select, if you often use this in your application, maybe you want to define a smaller struct for API usage which can select specific fields automatically, for example:
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 querying
db.Model(&User{}).Limit(10).Find(&APIUser{})
// SELECT `id`, `name` FROM `users` LIMIT 10{% note warn %}
NOTE QueryFields mode will select by all fields' name for current model
{% endnote %}
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
QueryFields: true,
})
db.Find(&user)
// SELECT `users`.`name`, `users`.`age`, ... FROM `users` // with this option
// Session Mode
db.Session(&gorm.Session{QueryFields: true}).Find(&user)
// SELECT `users`.`name`, `users`.`age`, ... FROM `users`GORM supports different types of locks, for example:
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`
db.Clauses(clause.Locking{
Strength: "UPDATE",
Options: "NOWAIT",
}).Find(&users)
// SELECT * FROM `users` FOR UPDATE NOWAITRefer Raw SQL and SQL Builder for more detail
A subquery can be nested within a query, GORM can generate subquery when using a *gorm.DB object as param
db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders)
// SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders");
subQuery := db.Select("AVG(age)").Where("name LIKE ?", "name%").Table("users")
db.Select("AVG(age) as avgage").Group("name").Having("AVG(age) > (?)", subQuery).Find(&results)
// SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")GORM allows you using subquery in FROM clause with method Table, for example:
db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age = ?", 18}).Find(&User{})
// SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18
subQuery1 := db.Model(&User{}).Select("name")
subQuery2 := db.Model(&Pet{}).Select("name")
db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{})
// SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as pEasier to write complicated SQL query with Group Conditions
db.Where(
db.Where("pizza = ?", "pepperoni").Where(db.Where("size = ?", "small").Or("size = ?", "medium")),
).Or(
db.Where("pizza = ?", "hawaiian").Where("size = ?", "xlarge"),
).Find(&Pizza{}).Statement
// SELECT * FROM `pizzas` WHERE (pizza = "pepperoni" AND (size = "small" OR size = "medium")) OR (pizza = "hawaiian" AND size = "xlarge")Selecting IN with multiple columns
db.Where("(name, age, role) IN ?", [][]interface{}{{"jinzhu", 18, "admin"}, {"jinzhu2", 19, "user"}}).Find(&users)
// SELECT * FROM users WHERE (name, age, role) IN (("jinzhu", 18, "admin"), ("jinzhu 2", 19, "user"));GORM supports named arguments with sql.NamedArg or map[string]interface{}{}, for example:
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user)
// SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"
db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu"}).First(&user)
// SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu" ORDER BY `users`.`id` LIMIT 1Check out Raw SQL and SQL Builder for more detail
GORM allows scan result to map[string]interface{} or []map[string]interface{}, don't forget to specify Model or Table, for example:
result := map[string]interface{}{}
db.Model(&User{}).First(&result, "id = ?", 1)
var results []map[string]interface{}
db.Table("users").Find(&results)Get first matched record or initialize a new instance with given conditions (only works with struct or map conditions)
// User not found, initialize it with give conditions
db.FirstOrInit(&user, User{Name: "non_existing"})
// user -> User{Name: "non_existing"}
// Found user with `name` = `jinzhu`
db.Where(User{Name: "jinzhu"}).FirstOrInit(&user)
// user -> User{ID: 111, Name: "Jinzhu", Age: 18}
// Found user with `name` = `jinzhu`
db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"})
// user -> User{ID: 111, Name: "Jinzhu", Age: 18}initialize struct with more attributes if record not found, those Attrs won't be used to build SQL query
// User not found, initialize it with give conditions and Attrs
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// user -> User{Name: "non_existing", Age: 20}
// User not found, initialize it with give conditions and Attrs
db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// user -> User{Name: "non_existing", Age: 20}
// Found user with `name` = `jinzhu`, attributes will be ignored
db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 20}).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;
// user -> User{ID: 111, Name: "Jinzhu", Age: 18}Assign attributes to struct regardless it is found or not, those attributes won't be used to build SQL query and the final data won't be saved into database
// User not found, initialize it with give conditions and Assign attributes
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)
// user -> User{Name: "non_existing", Age: 20}
// Found user with `name` = `jinzhu`, update it with Assign attributes
db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 20}).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;
// user -> User{ID: 111, Name: "Jinzhu", Age: 20}Get first matched record or create a new one with given conditions (only works with struct, map conditions), RowsAffected returns created/updated record's count
// User not found, create a new record with give conditions
result := db.FirstOrCreate(&user, User{Name: "non_existing"})
// INSERT INTO "users" (name) VALUES ("non_existing");
// user -> User{ID: 112, Name: "non_existing"}
// result.RowsAffected // => 0
// Found user with `name` = `jinzhu`
result := db.Where(User{Name: "jinzhu"}).FirstOrCreate(&user)
// user -> User{ID: 111, Name: "jinzhu", "Age": 18}
// result.RowsAffected // => 0Create struct with more attributes if record not found, those Attrs won't be used to build SQL query
// User not found, create it with give conditions and Attrs
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
// user -> User{ID: 112, Name: "non_existing", Age: 20}
// Found user with `name` = `jinzhu`, attributes will be ignored
db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
// user -> User{ID: 111, Name: "jinzhu", Age: 18}Assign attributes to the record regardless it is found or not and save them back to the database.
// User not found, initialize it with give conditions and Assign attributes
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
// user -> User{ID: 112, Name: "non_existing", Age: 20}
// Found user with `name` = `jinzhu`, update it with Assign attributes
db.Where(User{Name: "jinzhu"}).Assign(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
// UPDATE users SET age=20 WHERE id = 111;
// user -> User{ID: 111, Name: "jinzhu", Age: 20}Optimizer hints allow to control the query optimizer to choose a certain query execution plan, GORM supports it with gorm.io/hints, e.g:
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`)"Refer Optimizer Hints/Index/Comment for more details
GORM supports iterating through Rows
rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Rows()
defer rows.Close()
for rows.Next() {
var user User
// ScanRows is a method of `gorm.DB`, it can be used to scan a row into a struct
db.ScanRows(rows, &user)
// do something
}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 batchesGORM allows hooks AfterFind for a query, it will be called when querying a record, refer Hooks for details
func (u *User) AfterFind(tx *gorm.DB) (err error) {
if u.Role == "" {
u.Role = "user"
}
return
}Query single column from database and scan into a slice, if you want to query multiple columns, use Select with Scan instead
var ages []int64
db.Model(&users).Pluck("age", &ages)
var names []string
db.Model(&User{}).Pluck("name", &names)
db.Table("deleted_users").Pluck("name", &names)
// Distinct Pluck
db.Model(&User{}).Distinct().Pluck("Name", &names)
// SELECT DISTINCT `name` FROM `users`
// Requesting more than one column, use `Scan` or `Find` like this:
db.Select("name", "age").Scan(&users)
db.Select("name", "age").Find(&users)Scopes allows you to specify commonly-used queries which can be referenced as method calls
func AmountGreaterThan1000(db *gorm.DB) *gorm.DB {
return db.Where("amount > ?", 1000)
}
func PaidWithCreditCard(db *gorm.DB) *gorm.DB {
return db.Where("pay_mode_sign = ?", "C")
}
func PaidWithCod(db *gorm.DB) *gorm.DB {
return db.Where("pay_mode_sign = ?", "C")
}
func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {
return func (db *gorm.DB) *gorm.DB {
return db.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 1000Checkout Scopes for details
Get matched records count
var count int64
db.Model(&User{}).Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Count(&count)
// SELECT count(1) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'
db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
// SELECT count(1) FROM users WHERE name = 'jinzhu'; (count)
db.Table("deleted_users").Count(&count)
// SELECT count(1) FROM deleted_users;
// Count with Distinct
db.Model(&User{}).Distinct("name").Count(&count)
// SELECT COUNT(DISTINCT(`name`)) FROM `users`
db.Table("deleted_users").Select("count(distinct(name))").Count(&count)
// SELECT count(distinct(name)) FROM deleted_users
// Count with Group
users := []User{
{Name: "name1"},
{Name: "name2"},
{Name: "name3"},
{Name: "name3"},
}
db.Model(&User{}).Group("name").Count(&count)
count // => 3GORM 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: "jinzhu@example.com"},
{Email: "jinzhu-2@example.com"},
},
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, "jinzhu@example.com"), (111, "jinzhu-2@example.com") 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, "jinzhu@example.com"), (111, "jinzhu-2@example.com") ON DUPLICATE KEY SET email=VALUES(email);
// ...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: "jinzhu@example.com"},
{Email: "jinzhu-2@example.com"},
},
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 warn %} 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){% endnote %}
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)Association 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").ErrorFind matched associations
db.Model(&user).Association("Languages").Find(&languages)Find associations with conditions
codes := []string{"zh-CN", "en-US", "ja-JP"}
db.Model(&user).Where("code IN ?", codes).Association("Languages").Find(&languages)
db.Model(&user).Where("code IN ?", codes).Order("code desc").Association("Languages").Find(&languages)Append new associations for many to many, has many, replace current association for has one, belongs to
db.Model(&user).Association("Languages").Append([]Language{languageZH, languageEN})
db.Model(&user).Association("Languages").Append(&Language{Name: "DE"})
db.Model(&user).Association("CreditCard").Append(&CreditCard{Number: "411111111111"})Replace current associations with new ones
db.Model(&user).Association("Languages").Replace([]Language{languageZH, languageEN})
db.Model(&user).Association("Languages").Replace(Language{Name: "DE"}, languageEN)Remove the relationship between source & arguments if exists, only delete the reference, won't delete those objects from DB.
db.Model(&user).Association("Languages").Delete([]Language{languageZH, languageEN})
db.Model(&user).Association("Languages").Delete(languageZH, languageEN)Remove all reference between source & association, won't delete those associations
db.Model(&user).Association("Languages").Clear()Return the count of current associations
db.Model(&user).Association("Languages").Count()
// Count with conditions
codes := []string{"zh-CN", "en-US", "ja-JP"}
db.Model(&user).Where("code IN ?", codes).Association("Languages").Count()Association Mode supports batch data, e.g:
// 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})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 warn %} 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{% endnote %}
| 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 |
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 implicitly used 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 struct.
// `User` belongs to `Company`, `CompanyID` is the foreign key
type User struct {
gorm.Model
Name string
CompanyID int
Company Company
}
type Company struct {
ID int
Name string
}Refer to Eager Loading for details on populating the inner struct.
To define a belongs to relationship, the foreign key must exist, the default foreign 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, for example:
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
}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, e.g:
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
}Please checkout Association Mode for working with belongs to relations
GORM allows eager loading belongs to associations with Preload or Joins, refer Preloading (Eager loading) for details
You can setup OnUpdate, OnDelete constraints with tag constraint, it will be created when migrating with GORM, for example:
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
}GORM 2.0 is a rewrite from scratch, it introduces some incompatible-API change and many improvements
- Performance Improvements
- Modularity
- Context, Batch Insert, Prepared Statement Mode, DryRun Mode, Join Preload, Find To Map, Create From Map, FindInBatches supports
- Nested Transaction/SavePoint/RollbackTo SavePoint supports
- Named Argument, Group Conditions, Upsert, Locking, Optimizer/Index/Comment Hints supports, SubQuery improvements
- Full self-reference relationships supports, Join Table improvements, Association Mode for batch data
- Multiple fields support for tracking create/update time, which adds support for UNIX (milli/nano) seconds
- Field permissions support: read-only, write-only, create-only, update-only, ignored
- New plugin system: multiple databases, read/write splitting support with plugin Database Resolver, prometheus integrations...
- New Hooks API: unified interface with plugins
- New Migrator: allows to create database foreign keys for relationships, constraints/checker support, enhanced index support
- New Logger: context support, improved extensibility
- Unified Naming strategy: table name, field name, join table name, foreign key, checker, index name rules
- Better customized data type support (e.g: JSON)
Breaking Changes:
gorm.Openreturns*gorm.DBinstead ofgorm.DB- Updating will only update changed fields
- Soft Delete's will only check
deleted_at IS NULL - New ToDBName logic
Common initialisms from golint like
HTTP,URIwas converted to lowercase, soHTTP's db name ishttp, but noth_t_t_p, but for some other initialisms not in the list, likeSKU, it's db name wass_k_u, this change fixed it tosku - Error
RecordNotFoundhas been renamed toErrRecordNotFound mssqldialect has been renamed togithub.com/jinzhu/gorm/dialects/mssqlHstorehas been moved to packagegithub.com/jinzhu/gorm/dialects/postgres
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"`
}GORM officially supports databases MySQL, PostgreSQL, SQLite, SQL Server
import (
"gorm.io/driver/mysql"
"gorm.io/gorm"
)
func main() {
// refer https://github.com/go-sql-driver/mysql#dsn-data-source-name for details
dsn := "user:pass@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
}{% note warn %}
NOTE:
To handle time.Time correctly, you need to include parseTime as a parameter. (more parameters)
To fully support UTF-8 encoding, you need to change charset=utf8 to charset=utf8mb4. See this article for a detailed explanation
{% endnote %}
MySQL Driver provides few advanced configurations can be used during initialization, for example:
db, err := gorm.Open(mysql.New(mysql.Config{
DSN: "gorm:gorm@tcp(127.0.0.1:3306)/gorm?charset=utf8&parseTime=True&loc=Local", // data source name
DefaultStringSize: 256, // default size for string fields
DisableDatetimePrecision: true, // disable datetime precision, which not supported before MySQL 5.6
DontSupportRenameIndex: true, // drop & create when rename index, rename index not supported before MySQL 5.7, MariaDB
DontSupportRenameColumn: true, // `change` when rename column, rename column not supported before MySQL 8, MariaDB
SkipInitializeWithVersion: false, // auto configure based on currently MySQL version
}), &gorm.Config{})GORM allows customize the MySQL driver with the DriverName option, for example:
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, refer https://github.com/go-sql-driver/mysql#dsn-data-source-name
}), &gorm.Config{})GORM allows to initialize *gorm.DB with an existing database connection
import (
"database/sql"
"gorm.io/driver/mysql"
"gorm.io/gorm"
)
sqlDB, err := sql.Open("mysql", "mydb_dsn")
gormDB, err := gorm.Open(mysql.New(mysql.Config{
Conn: sqlDB,
}), &gorm.Config{})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{})We are using pgx as postgres's database/sql driver, it enables prepared statement cache by default, to disable it:
// 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{})GORM allows customize the PostgreSQL driver with the DriverName option, for example:
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",
})GORM allows to initialize *gorm.DB with an existing database connection
import (
"database/sql"
"gorm.io/driver/postgres"
"gorm.io/gorm"
)
sqlDB, err := sql.Open("pgx", "mydb_dsn")
gormDB, err := gorm.Open(postgres.New(postgres.Config{
Conn: sqlDB,
}), &gorm.Config{})import (
"gorm.io/driver/sqlite" // Sqlite driver based on GGO
// "github.com/glebarez/sqlite" // Pure go SQLite driver, checkout https://github.com/glebarez/sqlite for details
"gorm.io/gorm"
)
// github.com/mattn/go-sqlite3
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{}){% note warn %}
NOTE: You can also use file::memory:?cache=shared instead of a path to a file. This will tell SQLite to use a temporary database in system memory. (See SQLite docs for this)
{% endnote %}
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{})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{})
// Insert
db.Create(&user)
// Select
db.Find(&user, "id = ?", 10)
// Batch Insert
var users = []User{user1, user2, user3}
db.Create(&users)
// ...
}GORM using database/sql to maintain connection pool
sqlDB, err := db.DB()
// SetMaxIdleConns sets the maximum number of connections in the idle connection pool.
sqlDB.SetMaxIdleConns(10)
// SetMaxOpenConns sets the maximum number of open connections to the database.
sqlDB.SetMaxOpenConns(100)
// SetConnMaxLifetime sets the maximum amount of time a connection may be reused.
sqlDB.SetConnMaxLifetime(time.Hour)Refer Generic Interface for details
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 are encouraged to make a driver, pull request welcome!
GORM allows create database constraints with tag, constraints will be created when AutoMigrate or CreateTable with GORM
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'"`
}Checkout Database Indexes
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
}GORM provides Context support, you can use it with method WithContext
Single session mode usually used when you want to perform a single operation
db.WithContext(ctx).Find(&users)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")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
}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 %}
NOTE Set Context with WithContext is goroutine-safe, refer Session for details
{% endnote %}
Logger accepts Context too, you can use it for log tracking, refer Logger for details
GORM uses the field with the name ID as the table'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
}Also check out Composite Primary Key
GORM pluralizes struct name to snake_cases as table name, for struct User, its table name is users by convention
You can change the default table name by implementing the Tabler interface, for example:
type Tabler interface {
TableName() string
}
// TableName overrides the table name used by User to `profiles`
func (User) TableName() string {
return "profiles"
}{% note warn %}
NOTE TableName doesn't allow dynamic name, its result will be cached for future, to use dynamic name, you can use Scopes, for example:
{% endnote %}
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 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
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
Column db 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`
}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())You can disable the timestamp tracking by setting autoCreateTime tag to false, for example:
type User struct {
CreatedAt time.Time `gorm:"autoCreateTime:false"`
}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 updatingYou can disable the timestamp tracking by setting autoUpdateTime tag to false, for example:
type User struct {
UpdatedAt time.Time `gorm:"autoUpdateTime:false"`
}{% note %} NOTE GORM supports having multiple time tracking fields and track with UNIX (nano/milli) seconds, checkout Models for more details {% endnote %}
user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}
result := db.Create(&user) // pass pointer of data to Create
user.ID // returns inserted data's primary key
result.Error // returns error
result.RowsAffected // returns inserted records countCreate a record and assign a value to the fields specified.
db.Select("Name", "Age", "CreatedAt").Create(&user)
// INSERT INTO `users` (`name`,`age`,`created_at`) VALUES ("jinzhu", 18, "2020-07-04 11:05:21.775")Create a record and ignore the values for fields passed to omit.
db.Omit("Name", "Age", "CreatedAt").Create(&user)
// INSERT INTO `users` (`birthday`,`updated_at`) VALUES ("2020-01-01 00:00:00.000", "2020-07-04 11:05:21.775")To efficiently insert large number of records, pass a slice to the Create method. GORM will generate a single SQL statement to insert all the data and backfill primary key values, hook methods will be invoked too.
var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
db.Create(&users)
for _, user := range users {
user.ID // 1,2,3
}You can specify batch size when creating with CreateInBatches, e.g:
var users = []User{{Name: "jinzhu_1"}, ...., {Name: "jinzhu_10000"}}
// batch size 100
db.CreateInBatches(users, 100)Batch Insert is also supported when using Upsert and Create With Associations
{% note warn %}
NOTE initialize GORM with CreateBatchSize option, all INSERT will respect this option when creating record & associations
{% endnote %}
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
CreateBatchSize: 1000,
})
db := db.Session(&gorm.Session{CreateBatchSize: 1000})
users = [5000]User{{Name: "jinzhu", Pets: []Pet{pet1, pet2, pet3}}...}
db.Create(&users)
// INSERT INTO users xxx (5 batches)
// INSERT INTO pets xxx (15 batches)GORM allows user defined hooks to be implemented for BeforeSave, BeforeCreate, AfterSave, AfterCreate. These hook method will be called when creating a record, refer Hooks for details on the lifecycle
func (u *User) BeforeCreate(tx *gorm.DB) (err error) {
u.UUID = uuid.New()
if u.Role == "admin" {
return errors.New("invalid role")
}
return
}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)GORM supports create from map[string]interface{} and []map[string]interface{}{}, e.g:
db.Model(&User{}).Create(map[string]interface{}{
"Name": "jinzhu", "Age": 18,
})
// batch insert from `[]map[string]interface{}{}`
db.Model(&User{}).Create([]map[string]interface{}{
{"Name": "jinzhu_1", "Age": 18},
{"Name": "jinzhu_2", "Age": 20},
}){% note warn %} NOTE When creating from map, hooks won't be invoked, associations won't be saved and primary key values won't be back filled {% endnote %}
GORM allows insert data with SQL expression, there are two ways to achieve this goal, create from map[string]interface{} or Customized Data Types, for example:
// Create from map
db.Model(User{}).Create(map[string]interface{}{
"Name": "jinzhu",
"Location": clause.Expr{SQL: "ST_PointFromText(?)", Vars: []interface{}{"POINT(100 100)"}},
})
// INSERT INTO `users` (`name`,`location`) VALUES ("jinzhu",ST_PointFromText("POINT(100 100)"));
// Create from customized data type
type Location struct {
X, Y int
}
// Scan implements the sql.Scanner interface
func (loc *Location) Scan(v interface{}) error {
// Scan a value into struct from database driver
}
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)},
}
}
type User struct {
Name string
Location Location
}
db.Create(&User{
Name: "jinzhu",
Location: Location{X: 100, Y: 100},
})
// INSERT INTO `users` (`name`,`location`) VALUES ("jinzhu",ST_PointFromText("POINT(100 100)"))When creating some data with associations, if its associations value is not zero-value, those associations will be upserted, and its Hooks methods will be invoked.
type CreditCard struct {
gorm.Model
Number string
UserID uint
}
type User struct {
gorm.Model
Name string
CreditCard CreditCard
}
db.Create(&User{
Name: "jinzhu",
CreditCard: CreditCard{Number: "411111111111"}
})
// INSERT INTO `users` ...
// INSERT INTO `credit_cards` ...You can skip saving associations with Select, Omit, for example:
db.Omit("CreditCard").Create(&user)
// skip all associations
db.Omit(clause.Associations).Create(&user)You can define default values for fields with tag default, for example:
type User struct {
ID int64
Name string `gorm:"default:galeone"`
Age int64 `gorm:"default:18"`
}Then the default value will be used when inserting into the database for zero-value fields
{% note warn %}
NOTE Any zero value like 0, '', false won't be saved into the database for those fields defined default value, you might want to use pointer type or Scanner/Valuer to avoid this, for example:
{% endnote %}
type User struct {
gorm.Model
Name string
Age *int `gorm:"default:18"`
Active sql.NullBool `gorm:"default:true"`
}{% note warn %}
NOTE You have to setup the default tag for fields having default or virtual/generated value in database, if you want to skip a default value definition when migrating, you could use default:(-), for example:
{% endnote %}
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:(-);"`
}When using virtual/generated value, you might need to disable its creating/updating permission, check out Field-Level Permission
GORM provides compatible Upsert support for different databases
import "gorm.io/gorm/clause"
// Do nothing on conflict
db.Clauses(clause.OnConflict{DoNothing: true}).Create(&user)
// Update columns to default value on `id` conflict
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
// Use SQL expression
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));
// Update columns to new value on `id` conflict
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
// Update all columns, except primary keys, to new value on conflict
db.Clauses(clause.OnConflict{
UpdateAll: true,
}).Create(&users)
// INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age", ...;Also checkout FirstOrInit, FirstOrCreate on Advanced Query
Checkout Raw SQL and SQL Builder for more details
GORM provides few interfaces that allow users to define well-supported customized data types for GORM, takes json as an example
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[]"`
}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
}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
}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` = 1You can also create/update with SQL Expr from map, checkout Create From SQL Expr and Update with SQL Expression for details
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
}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'We created a Github repo for customized data types collections https://github.com/go-gorm/datatypes, pull request welcome ;)
DBResolver adds multiple 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
- Transaction
https://github.com/go-gorm/dbresolver
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"))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`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
// 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)When using transaction, DBResolver will keep using the transaction and won't switch to sources/replicas based on configuration
But you can specifies which DB to use before starting a transaction, for example:
// Start transaction based on default replicas db
tx := DB.Clauses(dbresolver.Read).Begin()
// Start transaction based on default sources db
tx := DB.Clauses(dbresolver.Write).Begin()
// Start transaction based on `secondary`'s sources
tx := DB.Clauses(dbresolver.Use("secondary"), dbresolver.Write).Begin()GORM supports load balancing sources/replicas 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.
db.Use(
dbresolver.Register(dbresolver.Config{ /* xxx */ }).
SetConnMaxIdleTime(time.Hour).
SetConnMaxLifetime(24 * time.Hour).
SetMaxIdleConns(100).
SetMaxOpenConns(200)
)When deleting a record, the deleted value needs to have primary key or it will trigger a Batch Delete, for example:
// Email's ID is `10`
db.Delete(&email)
// DELETE from emails where id = 10;
// Delete with additional conditions
db.Where("name = ?", "jinzhu").Delete(&email)
// DELETE from emails where id = 10 AND name = "jinzhu";GORM allows to delete objects using primary key(s) with inline condition, it works with numbers, check out Query Inline Conditions for details
db.Delete(&User{}, 10)
// DELETE FROM users WHERE id = 10;
db.Delete(&User{}, "10")
// DELETE FROM users WHERE id = 10;
db.Delete(&users, []int{1,2,3})
// DELETE FROM users WHERE id IN (1,2,3);GORM allows hooks BeforeDelete, AfterDelete, those methods will be called when deleting a record, refer Hooks for details
func (u *User) BeforeDelete(tx *gorm.DB) (err error) {
if u.Role == "admin" {
return errors.New("admin user not allowed to delete")
}
return
}The specified value has no primary value, GORM will perform a batch delete, it will delete all matched records
db.Where("email LIKE ?", "%jinzhu%").Delete(&Email{})
// DELETE from emails where email LIKE "%jinzhu%";
db.Delete(&Email{}, "email LIKE ?", "%jinzhu%")
// DELETE from emails where email LIKE "%jinzhu%";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, for example:
db.Delete(&User{}).Error // gorm.ErrMissingWhereClause
db.Where("1 = 1").Delete(&User{})
// DELETE FROM `users` WHERE 1=1
db.Exec("DELETE FROM users")
// DELETE FROM users
db.Session(&gorm.Session{AllowGlobalUpdate: true}).Delete(&User{})
// DELETE FROM usersReturn 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}}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.
// user's ID is `111`
db.Delete(&user)
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;
// Batch Delete
db.Where("age = ?", 20).Delete(&User{})
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;
// Soft deleted records will be ignored when querying
db.Where("age = 20").Find(&user)
// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;If you don't want to include gorm.Model, you can enable the soft delete feature like:
type User struct {
ID int
Deleted gorm.DeletedAt
Name string
}You can find soft deleted records with Unscoped
db.Unscoped().Where("age = 20").Find(&users)
// SELECT * FROM users WHERE age = 20;You can delete matched records permanently with Unscoped
db.Unscoped().Delete(&order)
// DELETE FROM orders WHERE id=10;Use unix second as delete flag
import "gorm.io/plugin/soft_delete"
type User struct {
ID uint
Name string
DeletedAt soft_delete.DeletedAt
}
// Query
SELECT * FROM users WHERE deleted_at = 0;
// Delete
UPDATE users SET deleted_at = /* current unix second */ WHERE ID = 1;{% note warn %}
INFO when using unique field with soft delete, you should create a composite index with the unix second based DeletedAt field, e.g:
import "gorm.io/plugin/soft_delete"
type User struct {
ID uint
Name string `gorm:"uniqueIndex:udx_name"`
DeletedAt soft_delete.DeletedAt `gorm:"uniqueIndex:udx_name"`
}{% endnote %}
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;In Go, error handling is important.
You are encouraged to do error check after any Finisher Methods
Error handling 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 like 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...
}GORM returns ErrRecordNotFound when failed to find data with First, Last, Take, if there are several errors happened, you can check the ErrRecordNotFound error with errors.Is, for example:
// Check if returns RecordNotFound error
err := db.First(&user, 100).Error
errors.Is(err, gorm.ErrRecordNotFound)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 warn %}
NOTE If the underlying database connection is not a *sql.DB, like in a transaction, it will returns error
{% endnote %}
// 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)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
}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,
})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
NoLowerCase: true, // skip the snake_casing of names
NameReplacer: strings.NewReplacer("CID", "Cid"), // use name replacer to change struct/field name before convert it to db name
},
})Allow to change GORM's default logger by overriding this option, refer Logger for more details
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()
},
})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,
})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,
})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
Enable global update/delete, refer Session for details
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,
})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,
})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.
// User has many CreditCards, UserID is the foreign key
type User struct {
gorm.Model
CreditCards []CreditCard
}
type CreditCard struct {
gorm.Model
Number string
UserID uint
}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
}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
}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")Please checkout Association Mode for working with has many relations
GORM allows eager loading has many associations with Preload, refer Preloading (Eager loading) for details
type User struct {
gorm.Model
Name string
ManagerID *uint
Team []User `gorm:"foreignkey:ManagerID"`
}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
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 possesses one instance of another model.
For example, if your application includes users and credit cards, and each user can only have one credit card.
// User has one CreditCard, CreditCardID is the foreign key
type User struct {
gorm.Model
CreditCard CreditCard
}
type CreditCard struct {
gorm.Model
Number string
UserID uint
}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, e.g:
type User struct {
gorm.Model
CreditCard CreditCard `gorm:"foreignKey:UserName"`
// use UserName as foreign key
}
type CreditCard struct {
gorm.Model
Number string
UserName string
}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
}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 the polymorphic field
type Cat struct {
ID int
Name string
Toy Toy `gorm:"polymorphic:Owner;"`
}
type Dog struct {
ID int
Name string
Toy Toy `gorm:"polymorphic:Owner;"`
}
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","dogs")You can change the polymorphic type value with tag polymorphicValue, for example:
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")Please checkout Association Mode for working with has one relations
GORM allows eager loading has one associations with Preload or Joins, refer Preloading (Eager loading) for details
type User struct {
gorm.Model
Name string
ManagerID *uint
Manager *User
}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
GORM provides optimizer/index/comment hints support
https://github.com/go-gorm/hints
import "gorm.io/hints"
db.Clauses(hints.New("hint")).Find(&User{})
// SELECT * /*+ hint */ FROM `users`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`)"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 */Hooks are functions that are called before or after creation/querying/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
Available hooks for creating
// begin transaction
BeforeSave
BeforeCreate
// save before associations
// insert into database
// save after associations
AfterCreate
AfterSave
// commit or rollback transactionCode 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 warn %} 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 {% endnote %}
func (u *User) AfterCreate(tx *gorm.DB) (err error) {
if !u.IsValid() {
return errors.New("rollback invalid user")
}
return nil
}Available hooks for updating
// begin transaction
BeforeSave
BeforeUpdate
// save before associations
// update database
// save after associations
AfterUpdate
AfterSave
// commit or rollback transactionCode 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
}Available hooks for deleting
// begin transaction
BeforeDelete
// delete from database
AfterDelete
// commit or rollback transactionCode 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
}Available hooks for querying
// load data from database
// Preloading (eager loading)
AfterFindCode Example:
func (u *User) AfterFind(tx *gorm.DB) (err error) {
if u.MemberShip == "" {
u.MemberShip = "user"
}
return
}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
}The fantastic ORM library for Golang aims to be developer friendly.
- Full-Featured ORM
- Associations (Has One, Has Many, Belongs To, Many To Many, Polymorphism, Single-table inheritance)
- Hooks (Before/After Create/Save/Update/Delete/Find)
- Eager loading with
Preload,Joins - Transactions, Nested Transactions, Save Point, RollbackTo to Saved Point
- Context, Prepared Statement Mode, DryRun Mode
- Batch Insert, FindInBatches, Find/Create with Map, CRUD with SQL Expr and Context Valuer
- SQL Builder, Upsert, Locking, Optimizer/Index/Comment Hints, Named Argument, SubQuery
- Composite Primary Key, Indexes, Constraints
- Auto Migrations
- Logger
- Extendable, flexible plugin API: Database Resolver (Multiple Databases, Read/Write Splitting) / Prometheus...
- Every feature comes with tests
- Developer Friendly
go get -u gorm.io/gorm
go get -u gorm.io/driver/sqlitepackage main
import (
"gorm.io/gorm"
"gorm.io/driver/sqlite"
)
type Product struct {
gorm.Model
Code string
Price uint
}
func main() {
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
if err != nil {
panic("failed to connect database")
}
// Migrate the schema
db.AutoMigrate(&Product{})
// Create
db.Create(&Product{Code: "D42", Price: 100})
// Read
var product Product
db.First(&product, 1) // find product with integer primary key
db.First(&product, "code = ?", "D42") // find product with code D42
// Update - update product's price to 200
db.Model(&product).Update("Price", 200)
// Update - update multiple fields
db.Model(&product).Updates(Product{Price: 200, Code: "F42"}) // non-zero fields
db.Model(&product).Updates(map[string]interface{}{"Price": 200, "Code": "F42"})
// Delete - delete product
db.Delete(&product, 1)
}GORM allows create database index with tag index, uniqueIndex, those indexes will be created when AutoMigrate or CreateTable with GORM
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"`
}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"`
}Use same index name for two fields will creates composite indexes, for example:
// create composite index `idx_member` with columns `name`, `number`
type User struct {
Name string `gorm:"index:idx_member"`
Number string `gorm:"index:idx_member"`
}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, nameIf you are creating shared composite indexes with an embedding struct, you can't specify the index name, as embedding the struct more than once results in the duplicated index name in db.
In this case, you can use index tag composite, it means the id of the composite index. All fields which have the same composite id of the struct are put together to the same index, just like the original rule. But the improvement is it lets the most derived/embedding struct generates the name of index by NamingStrategy. For example:
type Foo struct {
IndexA int `gorm:"index:,unique,composite:myname"`
IndexB int `gorm:"index:,unique,composite:myname"`
}If the table Foo is created, the name of composite index will be idx_foo_myname.
type Bar0 struct {
Foo
}
type Bar1 struct {
Foo
}Respectively, the name of composite index is idx_bar0_myname and idx_bar1_myname.
composite only works if not specify the name of index.
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"`
}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, for example:
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)GORM defined log levels: Silent, Error, Warn, Info
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{
Logger: logger.Default.LogMode(logger.Silent),
})Debug a single operation, change current operation's log level to logger.Info
db.Debug().Where("name = ?", "jinzhu").First(&User{})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)
}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.
// User has and belongs to many languages, `user_languages` is the join table
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
// User has and belongs to many languages, use `user_languages` as join table
type User struct {
gorm.Model
Languages []*Language `gorm:"many2many:user_languages;"`
}
type Language struct {
gorm.Model
Name string
Users []*User `gorm:"many2many:user_languages;"`
}For a many2many relationship, the join table owns the foreign key which references two models, for example:
type User struct {
gorm.Model
Languages []Language `gorm:"many2many:user_languages;"`
}
type Language struct {
gorm.Model
Name string
}
// Join Table: user_languages
// foreign key: user_id, reference: users.id
// foreign key: language_id, reference: languages.idTo 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 warn %}
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
{% endnote %}
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.idGORM allows eager loading has many associations with Preload, refer Preloading (Eager loading) for details
Please checkout Association Mode for working with many2many relations
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 warn %} NOTE: Customized join table's foreign keys required to be composited primary keys or composited unique index {% endnote %}
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{})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
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.idAlso check out Composite Primary Keys
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.
After a Chain method, Finisher Method, GORM returns an initialized *gorm.DB instance, which is NOT safe to reuse anymore, or new generated SQL might be polluted by the previous conditions, for example:
queryDB := DB.Where("name = ?", "jinzhu")
queryDB.First(&user)
// SELECT * FROM users WHERE name = "jinzhu"
queryDB.Where("name = ?", "jinzhu2").First(&user2)
// SELECT * FROM users WHERE name = "jinzhu" AND name = "jinzhu2"In order to reuse a initialized *gorm.DB instance, you can use a New Session Method to create a shareable *gorm.DB, e.g:
queryDB := DB.Where("name = ?", "jinzhu").Session(&gorm.Session{})
queryDB.First(&user)
// SELECT * FROM users WHERE name = "jinzhu"
queryDB.Where("name = ?", "jinzhu2").First(&user2)
// SELECT * FROM users WHERE name = "jinzhu2"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.
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.
GORM defined Session, WithContext, Debug methods as New Session Method, refer Session for more details.
After a Chain method, Finisher Method, GORM returns an initialized *gorm.DB instance, which is NOT safe to reuse anymore, you should use a New Session Method to mark the *gorm.DB as shareable.
Let's explain it with examples:
Example 1:
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
// db is a new initialized `*gorm.DB`, which is safe to reuse
db.Where("name = ?", "jinzhu").Where("age = ?", 18).Find(&users)
// `Where("name = ?", "jinzhu")` is the first chain method call, it will create an initialized `*gorm.DB` instance, aka `*gorm.Statement`
// `Where("age = ?", 18)` is the second chain method call, it reuses the above `*gorm.Statement`, adds new condition `age = 18` to it
// `Find(&users)` is a finisher method, it executes registered Query Callbacks, which 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 chain method call, it creates a new `*gorm.Statement`
// `Where("age = ?", 20)` reuses the above `Statement`, and add conditions to it
// `Find(&users)` is a finisher method, 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 call, it also creates a new `Statement` and executes registered Query Callbacks, generates and runs the following SQL:
// SELECT * FROM users;(Bad) Example 2:
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
// db is a new initialized *gorm.DB, which is safe to reuse
tx := db.Where("name = ?", "jinzhu")
// `Where("name = ?", "jinzhu")` returns an initialized `*gorm.Statement` instance after chain method `Where`, which is NOT safe to reuse
// good case
tx.Where("age = ?", 18).Find(&users)
// `tx.Where("age = ?", 18)` use the above `*gorm.Statement`, adds new condition to it
// `Find(&users)` is a finisher method call, it executes registered Query Callbacks, generates and runs the following SQL:
// SELECT * FROM users WHERE name = 'jinzhu' AND age = 18
// bad case
tx.Where("age = ?", 28).Find(&users)
// `tx.Where("age = ?", 18)` also use the above `*gorm.Statement`, and keep adding conditions to it
// So the following generated SQL is polluted by the previous conditions:
// SELECT * FROM users WHERE name = 'jinzhu' AND age = 18 AND age = 28;Example 3:
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
// db is a new initialized *gorm.DB, which is safe to reuse
tx := db.Where("name = ?", "jinzhu").Session(&gorm.Session{})
tx := db.Where("name = ?", "jinzhu").WithContext(context.Background())
tx := db.Where("name = ?", "jinzhu").Debug()
// `Session`, `WithContext`, `Debug` returns `*gorm.DB` marked as safe to reuse, newly initialized `*gorm.Statement` based on it keeps current conditions
// good case
tx.Where("age = ?", 18).Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' AND age = 18
// good case
tx.Where("age = ?", 28).Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' AND age = 28;Automatically migrate your schema, to keep your schema up to date.
{% note warn %} 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. {% endnote %}
db.AutoMigrate(&User{})
db.AutoMigrate(&User{}, &Product{}, &Order{})
// Add table suffix when creating tables
db.Set("gorm:table_options", "ENGINE=InnoDB").AutoMigrate(&User{}){% note warn %} NOTE AutoMigrate creates database foreign key constraints automatically, you can disable this feature during initialization, for example: {% endnote %}
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
DisableForeignKeyConstraintWhenMigrating: true,
})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
GetTables() (tableList []string, err error)
// Columns
AddColumn(dst interface{}, field string) error
DropColumn(dst interface{}, field string) error
AlterColumn(dst interface{}, field string) error
MigrateColumn(dst interface{}, field *schema.Field, columnType ColumnType) error
HasColumn(dst interface{}, field string) bool
RenameColumn(dst interface{}, oldName, field string) error
ColumnTypes(dst interface{}) ([]ColumnType, error)
// 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
}Returns current using database name
db.Migrator().CurrentDatabase()// 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")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{}) ([]gorm.ColumnType, error)
type ColumnType interface {
Name() string
DatabaseTypeName() string // varchar
ColumnType() (columnType string, ok bool) // varchar(64)
PrimaryKey() (isPrimaryKey bool, ok bool)
AutoIncrement() (isAutoIncrement bool, ok bool)
Length() (length int64, ok bool)
DecimalSize() (precision int64, scale int64, ok bool)
Nullable() (nullable bool, ok bool)
Unique() (unique bool, ok bool)
ScanType() reflect.Type
Comment() (value string, ok bool)
DefaultValue() (value string, ok bool)
}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")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")GORM creates constraints when auto migrating or creating table, see Constraints or Database Indexes for details
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.
Models are normal structs with basic Go types, pointers/alias of them or custom types implementing Scanner and Valuer interfaces
For Example:
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
}GORM prefer convention over configuration, by default, GORM uses ID as primary key, pluralize struct name to snake_cases as table name, snake_case as column name, and uses CreatedAt, UpdatedAt to track creating/updating time
If you follow the conventions adopted by GORM, you'll need to write very little configuration/code, If convention doesn't match your requirements, GORM allows you to configure them
GORM defined a gorm.Model struct, which includes fields ID, CreatedAt, UpdatedAt, DeletedAt
// gorm.Model definition
type Model struct {
ID uint `gorm:"primaryKey"`
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt gorm.DeletedAt `gorm:"index"`
}You can embed it into your struct to include those fields, refer Embedded Struct
Exported fields have all permission when doing CRUD with GORM, and GORM allows you to change the field-level permission with tag, so you can make a field to be read-only, write-only, create-only, update-only or ignored
{% note warn %} NOTE ignored fields won't be created when using GORM Migrator to create table {% endnote %}
type User struct {
Name string `gorm:"<-:create"` // allow read and create
Name string `gorm:"<-:update"` // allow read and update
Name string `gorm:"<-"` // allow read and write (create and update)
Name string `gorm:"<-:false"` // allow read, disable write permission
Name string `gorm:"->"` // readonly (disable write permission unless it configured)
Name string `gorm:"->;<-:create"` // allow read and create
Name string `gorm:"->:false;<-:create"` // createonly (disabled read from db)
Name string `gorm:"-"` // ignore this field when write and read with struct
Name string `gorm:"-:all"` // ignore this field when write, read and migrate with struct
Name string `gorm:"-:migration"` // ignore this field when migrate with struct
}GORM use CreatedAt, UpdatedAt to track creating/updating time by convention, and GORM will set the current time when creating/updating if the fields are defined
To use fields with a different name, you can configure those fields with tag autoCreateTime, autoUpdateTime
If you prefer to save UNIX (milli/nano) seconds instead of time, you can simply change the field's data type from time.Time to 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
}For anonymous fields, GORM will include its fields into its parent struct, for example:
type User struct {
gorm.Model
Name string
}
// equals
type User struct {
ID uint `gorm:"primaryKey"`
CreatedAt time.Time
UpdatedAt time.Time
DeletedAt gorm.DeletedAt `gorm:"index"`
Name string
}For a normal struct field, you can embed it with the tag embedded, for example:
type Author struct {
Name string
Email string
}
type Blog struct {
ID int
Author Author `gorm:"embedded"`
Upvotes int32
}
// equals
type Blog struct {
ID int64
Name string
Email string
Upvotes int32
}And you can use tag embeddedPrefix to add prefix to embedded fields' db name, for example:
type Blog struct {
ID int
Author Author `gorm:"embedded;embeddedPrefix:author_"`
Upvotes int32
}
// equals
type Blog struct {
ID int64
AuthorName string
AuthorEmail string
Upvotes int32
}Tags are optional to use when declaring models, GORM supports the following tags:
Tags are case insensitive, however camelCase is preferred.
| Tag Name | Description |
|---|---|
| column | column db name |
| type | column data type, prefer to use compatible general type, e.g: bool, int, uint, float, string, time, bytes, which works for all databases, and can be used with other tags together, like not null, size, autoIncrement... specified database data type like varbinary(8) also supported, when using specified database data type, it needs to be a full database data type, for example: MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT |
| serializer | specifies serializer for how to serialize and deserialize data into db, e.g: serializer:json/gob/unixtime |
| size | specifies column data size/length, e.g: size:256 |
| primaryKey | specifies column as primary key |
| unique | specifies column as unique |
| default | specifies column default value |
| precision | specifies column precision |
| scale | specifies column scale |
| not null | specifies column as NOT NULL |
| autoIncrement | specifies column auto incrementable |
| autoIncrementIncrement | auto increment step, controls the interval between successive column values |
| embedded | embed the field |
| embeddedPrefix | column name prefix for embedded fields |
| autoCreateTime | track current time when creating, for int fields, it will track unix seconds, use value nano/milli to track unix nano/milli seconds, e.g: autoCreateTime:nano |
| autoUpdateTime | track current time when creating/updating, for int fields, it will track unix seconds, use value nano/milli to track unix nano/milli seconds, e.g: autoUpdateTime:milli |
| index | create index with options, use same name for multiple fields creates composite indexes, refer Indexes for details |
| uniqueIndex | same as index, but create uniqued index |
| check | creates check constraint, eg: check:age > 13, refer Constraints |
| <- | set field's write permission, <-:create create-only field, <-:update update-only field, <-:false no write permission, <- create and update permission |
| -> | set field's read permission, ->:false no read permission |
| - | ignore this field, - no read/write permission, -:migration no migrate permission, -:all no read/write/migrate permission |
| comment | add comment for field when migration |
GORM allows configure foreign keys, constraints, many2many table through tags for Associations, check out the Associations section for details
GORM optimizes many things to improve the performance, the default performance should good for most applications, but there are still some tips for how to improve it for your application.
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,
})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 warn %} NOTE Also refer how to enable interpolateparams for MySQL to reduce roundtrip https://github.com/go-sql-driver/mysql#interpolateparams {% endnote %}
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
By default GORM select all fields when querying, 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 10Query and process records with iteration or in batches
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`)"Increase data throughput through read/write splitting, check out Database Resolver
GORM allows eager loading relations in other SQL with Preload, for example:
type User struct {
gorm.Model
Username string
Orders []Order
}
type Order struct {
gorm.Model
UserID uint
Price float64
}
// Preload Orders when find users
db.Preload("Orders").Find(&users)
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1,2,3,4);
db.Preload("Orders").Preload("Profile").Preload("Role").Find(&users)
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1,2,3,4); // has many
// SELECT * FROM profiles WHERE user_id IN (1,2,3,4); // has one
// SELECT * FROM roles WHERE id IN (4,5,6); // belongs toPreload loads the association data in a separate query, Join Preload will loads association data using inner join, for example:
db.Joins("Company").Joins("Manager").Joins("Account").First(&user, 1)
db.Joins("Company").Joins("Manager").Joins("Account").First(&user, "users.name = ?", "jinzhu")
db.Joins("Company").Joins("Manager").Joins("Account").Find(&users, "users.id IN ?", []int{1,2,3,4,5})Join with conditions
db.Joins("Company", DB.Where(&Company{Alive: true})).Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;{% note warn %}
NOTE Join Preload works with one-to-one relation, e.g: has one, belongs to
{% endnote %}
clause.Associations can work with Preload similar like Select when creating/updating, you can use it to Preload all associations, for example:
type User struct {
gorm.Model
Name string
CompanyID uint
Company Company
Role Role
Orders []Order
}
db.Preload(clause.Associations).Find(&users)clause.Associations won't preload nested associations, but you can use it with Nested Preloading together, e.g:
db.Preload("Orders.OrderItems.Product").Preload(clause.Associations).Find(&users)GORM allows Preload associations with conditions, it works similar to Inline Conditions
// Preload Orders with conditions
db.Preload("Orders", "state NOT IN (?)", "cancelled").Find(&users)
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1,2,3,4) AND state NOT IN ('cancelled');
db.Where("state = ?", "active").Preload("Orders", "state NOT IN (?)", "cancelled").Find(&users)
// SELECT * FROM users WHERE state = 'active';
// SELECT * FROM orders WHERE user_id IN (1,2) AND state NOT IN ('cancelled');You are able to custom preloading SQL by passing in func(db *gorm.DB) *gorm.DB, for example:
db.Preload("Orders", func(db *gorm.DB) *gorm.DB {
return db.Order("orders.amount DESC")
}).Find(&users)
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1,2,3,4) order by orders.amount DESC;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)GORM provides Prometheus plugin to collect DBStats or user-defined metrics
https://github.com/go-gorm/prometheus
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
}))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
}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
}GORM provides First, Take, Last methods to retrieve a single object from the database, it adds LIMIT 1 condition when querying the database, and it will return the error ErrRecordNotFound if no record is found.
// Get the first record ordered by primary key
db.First(&user)
// SELECT * FROM users ORDER BY id LIMIT 1;
// Get one record, no specified order
db.Take(&user)
// SELECT * FROM users LIMIT 1;
// Get last record, ordered by primary key desc
db.Last(&user)
// SELECT * FROM users ORDER BY id DESC LIMIT 1;
result := db.First(&user)
result.RowsAffected // returns count of records found
result.Error // returns error or nil
// check error ErrRecordNotFound
errors.Is(result.Error, gorm.ErrRecordNotFound){% note warn %}
If you want to avoid the ErrRecordNotFound error, you could use Find like db.Limit(1).Find(&user), the Find method accepts both struct and slice data
{% endnote %}
The First and Last methods will find the first and last record (respectively) as ordered by primary key. They only work when a pointer to the destination struct is passed to the methods as argument or when the model is specified using db.Model(). Additionally, if no primary key is defined for relevant model, then the model will be ordered by the first field. For example:
var user User
var users []User
// works because destination struct is passed in
db.First(&user)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
// works because model is specified using `db.Model()`
result := map[string]interface{}{}
db.Model(&User{}).First(&result)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
// doesn't work
result := map[string]interface{}{}
db.Table("users").First(&result)
// works with Take
result := map[string]interface{}{}
db.Table("users").Take(&result)
// no primary key defined, results will be ordered by first field (i.e., `Code`)
type Language struct {
Code string
Name string
}
db.First(&Language{})
// SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1Objects can be retrieved using primary key by using Inline Conditions if the primary key is a number. When working with strings, extra care needs to be taken to avoid SQL Injection; check out Security section for details.
db.First(&user, 10)
// SELECT * FROM users WHERE id = 10;
db.First(&user, "10")
// SELECT * FROM users WHERE id = 10;
db.Find(&users, []int{1,2,3})
// SELECT * FROM users WHERE id IN (1,2,3);If the primary key is a string (for example, like a uuid), the query will be written as follows:
db.First(&user, "id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a")
// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";When the destination object has a primary value, the primary key will be used to build the condition, for example:
var user = User{ID: 10}
db.First(&user)
// SELECT * FROM users WHERE id = 10;
var result User
db.Model(User{ID: 10}).First(&result)
// SELECT * FROM users WHERE id = 10;// Get all records
result := db.Find(&users)
// SELECT * FROM users;
result.RowsAffected // returns found records count, equals `len(users)`
result.Error // returns error// Get first matched record
db.Where("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
// Get all matched records
db.Where("name <> ?", "jinzhu").Find(&users)
// SELECT * FROM users WHERE name <> 'jinzhu';
// IN
db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)
// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');
// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)
// SELECT * FROM users WHERE name LIKE '%jin%';
// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;
// Time
db.Where("updated_at > ?", lastWeek).Find(&users)
// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';
// BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';// Struct
db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;
// Map
db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;
// Slice of primary keys
db.Where([]int64{20, 21, 22}).Find(&users)
// SELECT * FROM users WHERE id IN (20, 21, 22);{% note warn %}
NOTE When querying with struct, GORM will only query with non-zero fields, that means if your field's value is 0, '', false or other zero values, it won't be used to build query conditions, for example:
{% endnote %}
db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu";To include zero values in the query conditions, you can use a map, which will include all key-values as query conditions, for example:
db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;For more details, see Specify Struct search fields.
When searching with struct, you can specify which particular values from the struct to use in the query conditions by passing in the relevant field name or the dbname to Where(), for example:
db.Where(&User{Name: "jinzhu"}, "name", "Age").Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
db.Where(&User{Name: "jinzhu"}, "Age").Find(&users)
// SELECT * FROM users WHERE age = 0;Query conditions can be inlined into methods like First and Find in a similar way to Where.
// Get by primary key if it were a non-integer type
db.First(&user, "id = ?", "string_primary_key")
// SELECT * FROM users WHERE id = 'string_primary_key';
// Plain SQL
db.Find(&user, "name = ?", "jinzhu")
// SELECT * FROM users WHERE name = "jinzhu";
db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;
// Struct
db.Find(&users, User{Age: 20})
// SELECT * FROM users WHERE age = 20;
// Map
db.Find(&users, map[string]interface{}{"age": 20})
// SELECT * FROM users WHERE age = 20;Build NOT conditions, works similar to Where
db.Not("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1;
// Not In
db.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)
// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");
// Struct
db.Not(User{Name: "jinzhu", Age: 18}).First(&user)
// SELECT * FROM users WHERE name <> "jinzhu" AND age <> 18 ORDER BY id LIMIT 1;
// Not In slice of primary keys
db.Not([]int64{1,2,3}).First(&user)
// SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)
// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';
// Struct
db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);
// Map
db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);For more complicated SQL queries. please also refer to Group Conditions in Advanced Query.
Select allows you to specify the fields that you want to retrieve from database. Otherwise, GORM will select all fields by default.
db.Select("name", "age").Find(&users)
// SELECT name, age FROM users;
db.Select([]string{"name", "age"}).Find(&users)
// SELECT name, age FROM users;
db.Table("users").Select("COALESCE(age,?)", 42).Rows()
// SELECT COALESCE(age,'42') FROM users;Also check out Smart Select Fields
Specify order when retrieving records from the database
db.Order("age desc, name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;
// Multiple orders
db.Order("age desc").Order("name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;
db.Clauses(clause.OrderBy{
Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},
}).Find(&User{})
// SELECT * FROM users ORDER BY FIELD(id,1,2,3)Limit specify the max number of records to retrieve
Offset specify the number of records to skip before starting to return the records
db.Limit(3).Find(&users)
// SELECT * FROM users LIMIT 3;
// Cancel limit condition with -1
db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
// SELECT * FROM users LIMIT 10; (users1)
// SELECT * FROM users; (users2)
db.Offset(3).Find(&users)
// SELECT * FROM users OFFSET 3;
db.Limit(10).Offset(5).Find(&users)
// SELECT * FROM users OFFSET 5 LIMIT 10;
// Cancel offset condition with -1
db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
// SELECT * FROM users OFFSET 10; (users1)
// SELECT * FROM users; (users2)Refer to Pagination for details on how to make a paginator
type result struct {
Date time.Time
Total int
}
db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result)
// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name` LIMIT 1
db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)
// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"
rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
defer rows.Close()
for rows.Next() {
...
}
rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
defer rows.Close()
for rows.Next() {
...
}
type Result struct {
Date time.Time
Total int64
}
db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)Selecting distinct values from the model
db.Distinct("name", "age").Order("name, age desc").Find(&results)Distinct works with Pluck and Count too
Specify Joins conditions
type result struct {
Name string
Email string
}
db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{})
// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id
rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
for rows.Next() {
...
}
db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)
// multiple joins with parameter
db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)You can use Joins eager loading associations with a single SQL, for example:
db.Joins("Company").Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;Join with conditions
db.Joins("Company", DB.Where(&Company{Alive: true})).Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;For more details, please refer to Preloading (Eager Loading).
You can also use Joins to join a derived table.
type User struct {
Id int
Age int
}
type Order struct {
UserId int
FinishedAt *time.Time
}
query := db.Table("order").Select("MAX(order.finished_at) as latest").Joins("left join user user on order.user_id = user.id").Where("user.age > ?", 18).Group("order.user_id")
db.Model(&Order{}).Joins("join (?) q on order.finished_at = q.latest", query).Scan(&results)
// SELECT `order`.`user_id`,`order`.`finished_at` FROM `order` join (SELECT MAX(order.finished_at) as latest FROM `order` left join user user on order.user_id = user.id WHERE user.age > 18 GROUP BY `order`.`user_id`) q on order.finished_at = q.latestScanning results into a struct works similarly to the way we use Find
type Result struct {
Name string
Age int
}
var result Result
db.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)
// Raw SQL
db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)Scopes allow you to re-use commonly used logic, the shared logic needs to be defined as type func(*gorm.DB) *gorm.DB
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 1000func Paginate(r *http.Request) func(db *gorm.DB) *gorm.DB {
return func (db *gorm.DB) *gorm.DB {
q := r.URL.Query()
page, _ := strconv.Atoi(q.Get("page"))
if page == 0 {
page = 1
}
pageSize, _ := strconv.Atoi(q.Get("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)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 org2.users;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 = 111GORM uses the database/sql's argument placeholders to construct the SQL statement, which will automatically escape arguments to avoid SQL injection
{% note warn %} NOTE The SQL from Logger is not fully escaped like the one executed, be careful when copying and executing it in SQL console {% endnote %}
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)// will be escaped
db.First(&user, "name = ?", userInput)
// SQL injection
db.First(&user, fmt.Sprintf("name = %v", userInput))When retrieving objects with number primary key by user's input, you should check the type of variable.
userInputID := "1=1;drop table users;"
// safe, return error
id,err := strconv.Atoi(userInputID)
if err != nil {
return error
}
db.First(&user, id)
// SQL injection
db.First(&user, userInputID)
// SELECT * FROM users WHERE 1=1;drop table users;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.
Serializer is an extensible interface that allows to customize how to serialize and deserialize data with databasae.
GORM provides some default serializers: json, gob, unixtime, here is a quick example of how to use it.
type User struct {
Name []byte `gorm:"serializer:json"`
Roles Roles `gorm:"serializer:json"`
Contracts map[string]interface{} `gorm:"serializer:json"`
JobInfo Job `gorm:"type:bytes;serializer:gob"`
CreatedTime int64 `gorm:"serializer:unixtime;type:time"` // store int as datetime into database
}
type Roles []string
type Job struct {
Title string
Location string
IsIntern bool
}
createdAt := time.Date(2020, 1, 1, 0, 8, 0, 0, time.UTC)
data := User{
Name: []byte("jinzhu"),
Roles: []string{"admin", "owner"},
Contracts: map[string]interface{}{"name": "jinzhu", "age": 10},
CreatedTime: createdAt.Unix(),
JobInfo: Job{
Title: "Developer",
Location: "NY",
IsIntern: false,
},
}
DB.Create(&data)
// INSERT INTO `users` (`name`,`roles`,`contracts`,`job_info`,`created_time`) VALUES
// ("\"amluemh1\"","[\"admin\",\"owner\"]","{\"age\":10,\"name\":\"jinzhu\"}",<gob binary>,"2020-01-01 00:08:00")
var result User
DB.First(&result, "id = ?", data.ID)
// result => User{
// Name: []byte("jinzhu"),
// Roles: []string{"admin", "owner"},
// Contracts: map[string]interface{}{"name": "jinzhu", "age": 10},
// CreatedTime: createdAt.Unix(),
// JobInfo: Job{
// Title: "Developer",
// Location: "NY",
// IsIntern: false,
// },
// }
DB.Where(User{Name: []byte("jinzhu")}).Take(&result)
// SELECT * FROM `users` WHERE `users`.`name` = "\"amluemh1\"A Serializer needs to implement how to serialize and deserialize data, so it requires to implement the the following interface
import "gorm.io/gorm/schema"
type SerializerInterface interface {
Scan(ctx context.Context, field *schema.Field, dst reflect.Value, dbValue interface{}) error
SerializerValuerInterface
}
type SerializerValuerInterface interface {
Value(ctx context.Context, field *schema.Field, dst reflect.Value, fieldValue interface{}) (interface{}, error)
}For example, the default JSONSerializer is implemented like:
// JSONSerializer json serializer
type JSONSerializer struct {
}
// Scan implements serializer interface
func (JSONSerializer) Scan(ctx context.Context, field *Field, dst reflect.Value, dbValue interface{}) (err error) {
fieldValue := reflect.New(field.FieldType)
if dbValue != nil {
var bytes []byte
switch v := dbValue.(type) {
case []byte:
bytes = v
case string:
bytes = []byte(v)
default:
return fmt.Errorf("failed to unmarshal JSONB value: %#v", dbValue)
}
err = json.Unmarshal(bytes, fieldValue.Interface())
}
field.ReflectValueOf(ctx, dst).Set(fieldValue.Elem())
return
}
// Value implements serializer interface
func (JSONSerializer) Value(ctx context.Context, field *Field, dst reflect.Value, fieldValue interface{}) (interface{}, error) {
return json.Marshal(fieldValue)
}And registered with the following code:
schema.RegisterSerializer("json", JSONSerializer{})After registering a serializer, you can use it with the serializer tag, for example:
type User struct {
Name []byte `gorm:"serializer:json"`
}You can use a registered serializer with tags, you are also allowed to create a customized struct that implements the above SerializerInterface and use it as a field type directly, for example:
type EncryptedString string
// ctx: contains request-scoped values
// field: the field using the serializer, contains GORM settings, struct tags
// dst: current model value, `user` in the below example
// dbValue: current field's value in database
func (es *EncryptedString) Scan(ctx context.Context, field *schema.Field, dst reflect.Value, dbValue interface{}) (err error) {
switch value := dbValue.(type) {
case []byte:
*es = EncryptedString(bytes.TrimPrefix(value, []byte("hello")))
case string:
*es = EncryptedString(strings.TrimPrefix(value, "hello"))
default:
return fmt.Errorf("unsupported data %#v", dbValue)
}
return nil
}
// ctx: contains request-scoped values
// field: the field using the serializer, contains GORM settings, struct tags
// dst: current model value, `user` in the below example
// fieldValue: current field's value of the dst
func (es EncryptedString) Value(ctx context.Context, field *schema.Field, dst reflect.Value, fieldValue interface{}) (interface{}, error) {
return "hello" + string(es), nil
}
type User struct {
gorm.Model
Password EncryptedString
}
data := User{
Password: EncryptedString("pass"),
}
DB.Create(&data)
// INSERT INTO `serializer_structs` (`password`) VALUES ("hellopass")
var result User
DB.First(&result, "id = ?", data.ID)
// result => User{
// Password: EncryptedString("pass"),
// }
DB.Where(User{Password: EncryptedString("pass")}).Take(&result)
// SELECT * FROM `users` WHERE `users`.`password` = "hellopass"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
Initialized bool
SkipHooks bool
SkipDefaultTransaction bool
DisableNestedTransaction bool
AllowGlobalUpdate bool
FullSaveAssociations bool
QueryFields bool
Context context.Context
Logger logger.Interface
NowFunc func() time.Time
CreateBatchSize int
}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` = 1PreparedStmt creates prepared statements when executing any SQL and caches them to speed up future calls, for example:
// 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
}Create a new DB without conditions with option NewDB, for example:
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 idCreate a new initialized DB, which is not Method Chain/Gortoutine Safe anymore, refer Method Chaining
tx := db.Session(&gorm.Session{Initialized: true})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)When using Transaction method inside a DB transaction, GORM will use SavePoint(savedPointName), RollbackTo(savedPointName) to give you the nested transaction support. You can disable it by using the DisableNestedTransaction option, for example:
db.Session(&gorm.Session{
DisableNestedTransaction: true,
}).CreateInBatches(&users, 100)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"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, "jinzhu@example.com"), (111, "jinzhu-2@example.com") ON DUPLICATE KEY SET email=VALUES(email);
// ...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 timeoutCtxGORM also provides shortcut method WithContext, here is the definition:
func (db *DB) WithContext(ctx context.Context) *DB {
return db.Session(&Session{Context: ctx})
}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.
NowFunc allows changing the function to get current time of GORM, for example:
db.Session(&Session{
NowFunc: func() time.Time {
return time.Now().Local()
},
})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),
})
}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 optionDefault 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)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 migrating table.
// Add table suffix when creating tables
db.Set("gorm:table_options", "ENGINE=InnoDB").AutoMigrate(&User{})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{})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{})Sharding plugin using SQL parser and replace for splits large tables into smaller ones, redirects Query into sharding tables. Give you a high performance database access.
https://github.com/go-gorm/sharding
- Non-intrusive design. Load the plugin, specify the config, and all done.
- Lighting-fast. No network based middlewares, as fast as Go.
- Multiple database support. PostgreSQL tested, MySQL and SQLite is coming.
- Allows you custom the Primary Key generator (Built in keygen, Sequence, Snowflake ...).
Config the sharding middleware, register the tables which you want to shard. See Godoc for config details.
import (
"fmt"
"gorm.io/driver/postgres"
"gorm.io/gorm"
"gorm.io/sharding"
)
dsn := "postgres://localhost:5432/sharding-db?sslmode=disable"
db, err := gorm.Open(postgres.New(postgres.Config{DSN: dsn}))
db.Use(sharding.Register(sharding.Config{
ShardingKey: "user_id",
NumberOfShards: 64,
PrimaryKeyGenerator: sharding.PKSnowflake,
}, "orders").Register(sharding.Config{
ShardingKey: "user_id",
NumberOfShards: 256,
PrimaryKeyGenerator: sharding.PKSnowflake,
// This case for show up give notifications, audit_logs table use same sharding rule.
}, Notification{}, AuditLog{}))Use the db session as usual. Just note that the query should have the Sharding Key when operate sharding tables.
// Gorm create example, this will insert to orders_02
db.Create(&Order{UserID: 2})
// sql: INSERT INTO orders_2 ...
// Show have use Raw SQL to insert, this will insert into orders_03
db.Exec("INSERT INTO orders(user_id) VALUES(?)", int64(3))
// This will throw ErrMissingShardingKey error, because there not have sharding key presented.
db.Create(&Order{Amount: 10, ProductID: 100})
fmt.Println(err)
// Find, this will redirect query to orders_02
var orders []Order
db.Model(&Order{}).Where("user_id", int64(2)).Find(&orders)
fmt.Printf("%#v\n", orders)
// Raw SQL also supported
db.Raw("SELECT * FROM orders WHERE user_id = ?", int64(3)).Scan(&orders)
fmt.Printf("%#v\n", orders)
// This will throw ErrMissingShardingKey error, because WHERE conditions not included sharding key
err = db.Model(&Order{}).Where("product_id", "1").Find(&orders).Error
fmt.Println(err)
// Update and Delete are similar to create and query
db.Exec("UPDATE orders SET product_id = ? WHERE user_id = ?", 2, int64(3))
err = db.Exec("DELETE FROM orders WHERE product_id = 3").Error
fmt.Println(err) // ErrMissingShardingKeyThe full example is here.
Query Raw SQL with Scan
type Result struct {
ID int
Name string
Age int
}
var result Result
db.Raw("SELECT id, name, age FROM users WHERE name = ?", 3).Scan(&result)
db.Raw("SELECT id, name, age FROM users WHERE name = ?", 3).Scan(&result)
var age int
db.Raw("SELECT SUM(age) FROM users WHERE role = ?", "admin").Scan(&age)
var users []User
db.Raw("UPDATE users SET name = ? WHERE age = ? RETURNING id, name", "jinzhu", 20).Scan(&users)Exec with Raw SQL
db.Exec("DROP TABLE users")
db.Exec("UPDATE orders SET shipped_at = ? WHERE id IN ?", time.Now(), []int64{1, 2, 3})
// Exec with SQL Expression
db.Exec("UPDATE users SET money = ? WHERE name = ?", gorm.Expr("money * ? + ?", 10000, 1), "jinzhu"){% note warn %} NOTE GORM allows cache prepared statement to increase performance, checkout Performance for details {% endnote %}
GORM supports named arguments with sql.NamedArg, map[string]interface{}{} or struct, for example:
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user)
// SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"
db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu2"}).First(&result3)
// SELECT * FROM `users` WHERE name1 = "jinzhu2" OR name2 = "jinzhu2" ORDER BY `users`.`id` LIMIT 1
// Named Argument with Raw SQL
db.Raw("SELECT * FROM users WHERE name1 = @name OR name2 = @name2 OR name3 = @name",
sql.Named("name", "jinzhu1"), sql.Named("name2", "jinzhu2")).Find(&user)
// SELECT * FROM users WHERE name1 = "jinzhu1" OR name2 = "jinzhu2" OR name3 = "jinzhu1"
db.Exec("UPDATE users SET name1 = @name, name2 = @name2, name3 = @name",
sql.Named("name", "jinzhunew"), sql.Named("name2", "jinzhunew2"))
// UPDATE users SET name1 = "jinzhunew", name2 = "jinzhunew2", name3 = "jinzhunew"
db.Raw("SELECT * FROM users WHERE (name1 = @name AND name3 = @name) AND name2 = @name2",
map[string]interface{}{"name": "jinzhu", "name2": "jinzhu2"}).Find(&user)
// SELECT * FROM users WHERE (name1 = "jinzhu" AND name3 = "jinzhu") AND name2 = "jinzhu2"
type NamedArgument struct {
Name string
Name2 string
}
db.Raw("SELECT * FROM users WHERE (name1 = @Name AND name3 = @Name) AND name2 = @Name2",
NamedArgument{Name: "jinzhu", Name2: "jinzhu2"}).Find(&user)
// SELECT * FROM users WHERE (name1 = "jinzhu" AND name3 = "jinzhu") AND name2 = "jinzhu2"Generate SQL and its arguments without executing, can be used to prepare or test generated SQL, Checkout Session for details
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}Returns generated SQL without executing.
GORM uses the database/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 guarantees, please only use it for debugging.
sql := DB.ToSQL(func(tx *gorm.DB) *gorm.DB {
return tx.Model(&User{}).Where("id = ?", 100).Limit(10).Order("age desc").Find(&[]User{})
})
sql //=> SELECT * FROM "users" WHERE id = 100 AND "users"."deleted_at" IS NULL ORDER BY age desc LIMIT 10Get result as *sql.Row
// Use GORM API build SQL
row := db.Table("users").Where("name = ?", "jinzhu").Select("name", "age").Row()
row.Scan(&name, &age)
// Use Raw SQL
row := db.Raw("select name, age, email from users where name = ?", "jinzhu").Row()
row.Scan(&name, &age, &email)Get result as *sql.Rows
// Use GORM API build SQL
rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows()
defer rows.Close()
for rows.Next() {
rows.Scan(&name, &age, &email)
// do something
}
// Raw SQL
rows, err := db.Raw("select name, age, email from users where name = ?", "jinzhu").Rows()
defer rows.Close()
for rows.Next() {
rows.Scan(&name, &age, &email)
// do something
}Checkout FindInBatches for how to query and process records in batch Checkout Group Conditions for how to build complicated SQL Query
Use ScanRows to scan a row into a struct, for example:
rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows() // (*sql.Rows, error)
defer rows.Close()
var user User
for rows.Next() {
// ScanRows scan a row into user
db.ScanRows(rows, &user)
// do something
}Run mutliple SQL in same db tcp connection (not in a transaction)
db.Connection(func(tx *gorm.DB) error {
tx.Exec("SET my.role = ?", "admin")
tx.First(&User{})
})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 1You can define your own Clause and use it with GORM, it needs to implements Interface
Check out examples for reference
For different databases, Clauses may generate different SQL, for example:
db.Offset(10).Limit(5).Find(&users)
// Generated for SQL Server
// SELECT * FROM "users" OFFSET 10 ROW FETCH NEXT 5 ROWS ONLY
// Generated for MySQL
// SELECT * FROM `users` LIMIT 5 OFFSET 10Which is supported because GORM allows database driver register Clause Builder to replace the default one, take the Limit as example
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...);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`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)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
})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, user3Gorm 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()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
}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 user1Save will save all fields when performing the Updating SQL
db.First(&user)
user.Name = "jinzhu 2"
user.Age = 100
db.Save(&user)
// UPDATE users SET name='jinzhu 2', age=100, birthday='2016-01-01', updated_at = '2013-11-17 21:34:10' WHERE id=111;When updating a single column with Update, it needs to have any conditions or it will raise error ErrMissingWhereClause, checkout Block Global Updates for details
When using the Model method and its value has a primary value, the primary key will be used to build the condition, for example:
// Update with conditions
db.Model(&User{}).Where("active = ?", true).Update("name", "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE active=true;
// User's ID is `111`:
db.Model(&user).Update("name", "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;
// Update with conditions and model value
db.Model(&user).Where("active = ?", true).Update("name", "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true;Updates supports update with struct or map[string]interface{}, when updating with struct it will only update non-zero fields by default
// Update attributes with `struct`, will only update non-zero fields
db.Model(&user).Updates(User{Name: "hello", Age: 18, Active: false})
// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;
// Update attributes with `map`
db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;{% note warn %}
NOTE When update with struct, GORM will only update non-zero fields, you might want to use map to update attributes or use Select to specify fields to update
{% endnote %}
If you want to update selected fields or ignore some fields when updating, you can use Select, Omit
// Select with Map
// User's ID is `111`:
db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello' WHERE id=111;
db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;
// Select with Struct (select zero value fields)
db.Model(&user).Select("Name", "Age").Updates(User{Name: "new_name", Age: 0})
// UPDATE users SET name='new_name', age=0 WHERE id=111;
// Select all fields (select all fields include zero value fields)
db.Model(&user).Select("*").Update(User{Name: "jinzhu", Role: "admin", Age: 0})
// Select all fields but omit Role (select all fields include zero value fields)
db.Model(&user).Select("*").Omit("Role").Update(User{Name: "jinzhu", Role: "admin", Age: 0})GORM allows hooks BeforeSave, BeforeUpdate, AfterSave, AfterUpdate, those methods will be called when updating a record, refer Hooks for details
func (u *User) BeforeUpdate(tx *gorm.DB) (err error) {
if u.Role == "admin" {
return errors.New("admin user not allowed to update")
}
return
}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);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, for example:
db.Model(&User{}).Update("name", "jinzhu").Error // gorm.ErrMissingWhereClause
db.Model(&User{}).Where("1 = 1").Update("name", "jinzhu")
// UPDATE users SET `name` = "jinzhu" WHERE 1=1
db.Exec("UPDATE users SET name = ?", "jinzhu")
// UPDATE users SET name = "jinzhu"
db.Session(&gorm.Session{AllowGlobalUpdate: true}).Model(&User{}).Update("name", "jinzhu")
// UPDATE users SET `name` = "jinzhu"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 errorGORM allows updates column with SQL expression, e.g:
// product's ID is `3`
db.Model(&product).Update("price", gorm.Expr("price * ? + ?", 2, 100))
// UPDATE "products" SET "price" = price * 2 + 100, "updated_at" = '2013-11-17 21:34:10' WHERE "id" = 3;
db.Model(&product).Updates(map[string]interface{}{"price": gorm.Expr("price * ? + ?", 2, 100)})
// UPDATE "products" SET "price" = price * 2 + 100, "updated_at" = '2013-11-17 21:34:10' WHERE "id" = 3;
db.Model(&product).UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))
// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = 3;
db.Model(&product).Where("quantity > 1").UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))
// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = 3 AND quantity > 1;And GORM also allows update with SQL Expression/Context Valuer with Customized Data Types, e.g:
// Create from customized data type
type Location struct {
X, Y int
}
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)},
}
}
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` = 1Update a table by using SubQuery
db.Model(&user).Update("company_name", db.Model(&Company{}).Select("name").Where("companies.id = users.company_id"))
// UPDATE "users" SET "company_name" = (SELECT name FROM companies WHERE companies.id = users.company_id);
db.Table("users as u").Where("name = ?", "jinzhu").Update("company_name", db.Table("companies as c").Select("name").Where("c.id = u.company_id"))
db.Table("users as u").Where("name = ?", "jinzhu").Updates(map[string]interface{}{}{"company_name": db.Table("companies as c").Select("name").Where("c.id = u.company_id")})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
// Update single column
db.Model(&user).UpdateColumn("name", "hello")
// UPDATE users SET name='hello' WHERE id = 111;
// Update multiple columns
db.Model(&user).UpdateColumns(User{Name: "hello", Age: 18})
// UPDATE users SET name='hello', age=18 WHERE id = 111;
// Update selected columns
db.Model(&user).Select("name", "age").UpdateColumns(User{Name: "hello", Age: 0})
// UPDATE users SET name='hello', age=0 WHERE id = 111;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}}GORM provides Changed method could be used in Before Update 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 Role changed
if tx.Statement.Changed("Role") {
return errors.New("role not allowed to change")
}
if tx.Statement.Changed("Name", "Admin") { // if Name or Role changed
tx.Statement.SetColumn("Age", 18)
}
// if any fields changed
if tx.Statement.Changed() {
tx.Statement.SetColumn("RefreshedAt", time.Now())
}
return nil
}
db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(map[string]interface{"name": "jinzhu2"})
// Changed("Name") => true
db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(map[string]interface{"name": "jinzhu"})
// Changed("Name") => false, `Name` not changed
db.Model(&User{ID: 1, Name: "jinzhu"}).Select("Admin").Updates(map[string]interface{
"name": "jinzhu2", "admin": false,
})
// Changed("Name") => false, `Name` not selected to update
db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(User{Name: "jinzhu2"})
// Changed("Name") => true
db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(User{Name: "jinzhu"})
// Changed("Name") => false, `Name` not changed
db.Model(&User{ID: 1, Name: "jinzhu"}).Select("Admin").Updates(User{Name: "jinzhu2"})
// Changed("Name") => false, `Name` not selected to updateTo change updating values in Before Hooks, you should use SetColumn unless it is a full updates with Save, for example:
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") {
user.Age += 20
tx.Statement.SetColumn("Age", user.Age)
}
}
db.Model(&user).Update("Name", "jinzhu")GORM 2.0 is a rewrite from scratch, it introduces some incompatible-API change and many improvements
Highlights
- Performance Improvements
- Modularity
- Context, Batch Insert, Prepared Statement Mode, DryRun Mode, Join Preload, Find To Map, Create From Map, FindInBatches supports
- Nested Transaction/SavePoint/RollbackTo SavePoint supports
- SQL Builder, Named Argument, Group Conditions, Upsert, Locking, Optimizer/Index/Comment Hints supports, SubQuery improvements, CRUD with SQL Expr and Context Valuer
- Full self-reference relationships support, Join Table improvements, Association Mode for batch data
- Multiple fields allowed to track create/update time, UNIX (milli/nano) seconds supports
- Field permissions support: read-only, write-only, create-only, update-only, ignored
- New plugin system, provides official plugins for multiple databases, read/write splitting, prometheus integrations...
- New Hooks API: unified interface with plugins
- New Migrator: allows to create database foreign keys for relationships, smarter AutoMigrate, constraints/checker support, enhanced index support
- New Logger: context support, improved extensibility
- Unified Naming strategy: table name, field name, join table name, foreign key, checker, index name rules
- Better customized data type support (e.g: JSON)
- GORM's developments moved to github.com/go-gorm, and its import path changed to
gorm.io/gorm, for previous projects, you can keep usinggithub.com/jinzhu/gormGORM V1 Document - Database drivers have been split into separate projects, e.g: github.com/go-gorm/sqlite, and its import path also changed to
gorm.io/driver/sqlite
go get gorm.io/gorm
// **NOTE** GORM `v2.0.0` released with git tag `v1.20.0`import (
"gorm.io/gorm"
"gorm.io/driver/sqlite"
)
func init() {
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{})
// Most CRUD API kept compatibility
db.AutoMigrate(&Product{})
db.Create(&user)
db.First(&user, 1)
db.Model(&user).Update("Age", 18)
db.Model(&user).Omit("Role").Updates(map[string]interface{}{"Name": "jinzhu", "Role": "admin"})
db.Delete(&user)
}The release note only cover major changes introduced in GORM V2 as a quick reference list
- Database operations support
context.Contextwith theWithContextmethod - Logger also accepts context for tracing
db.WithContext(ctx).Find(&users)To efficiently insert large number of records, pass a slice to the Create method. GORM will generate a single SQL statement to insert all the data and backfill primary key values, hook methods will be invoked too.
var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
db.Create(&users)
for _, user := range users {
user.ID // 1,2,3
}You can specify batch size when creating with CreateInBatches, e.g:
var users = []User{{Name: "jinzhu_1"}, ...., {Name: "jinzhu_10000"}}
// batch size 100
db.CreateInBatches(users, 100)Prepared Statement Mode creates prepared stmt and caches them to speed up future calls
// globally mode, all operations will create prepared stmt and cache to speed up
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{PrepareStmt: true})
// session mode, create prepares stmt and speed up current session operations
tx := db.Session(&Session{PrepareStmt: true})
tx.First(&user, 1)
tx.Find(&users)
tx.Model(&user).Update("Age", 18)Generates SQL without executing, can be used to check or test generated SQL
stmt := db.Session(&Session{DryRun: true}).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}Preload associations using INNER JOIN, and will handle null data to avoid failing to scan
db.Joins("Company").Joins("Manager").Joins("Account").Find(&users, "users.id IN ?", []int{1,2})Scan result to map[string]interface{} or []map[string]interface{}
var result map[string]interface{}
db.Model(&User{}).First(&result, "id = ?", 1)Create from map map[string]interface{} or []map[string]interface{}
db.Model(&User{}).Create(map[string]interface{}{"Name": "jinzhu", "Age": 18})
datas := []map[string]interface{}{
{"Name": "jinzhu_1", "Age": 19},
{"name": "jinzhu_2", "Age": 20},
}
db.Model(&User{}).Create(datas)Query and process records in batch
result := db.Where("age>?", 13).FindInBatches(&results, 100, func(tx *gorm.DB, batch int) error {
// batch processing
return nil
})db.Transaction(func(tx *gorm.DB) error {
tx.Create(&user1)
tx.Transaction(func(tx2 *gorm.DB) error {
tx.Create(&user2)
return errors.New("rollback user2") // rollback user2
})
tx.Transaction(func(tx2 *gorm.DB) error {
tx.Create(&user3)
return nil
})
return nil // commit user1 and user3
})tx := db.Begin()
tx.Create(&user1)
tx.SavePoint("sp1")
tx.Create(&user2)
tx.RollbackTo("sp1") // rollback user2
tx.Commit() // commit user1GORM supports use sql.NamedArg, map[string]interface{} as named arguments
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user)
// SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"
db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu2"}).First(&result3)
// SELECT * FROM `users` WHERE name1 = "jinzhu2" OR name2 = "jinzhu2" ORDER BY `users`.`id` LIMIT 1
db.Raw(
"SELECT * FROM users WHERE name1 = @name OR name2 = @name2 OR name3 = @name",
sql.Named("name", "jinzhu1"), sql.Named("name2", "jinzhu2"),
).Find(&user)
// SELECT * FROM users WHERE name1 = "jinzhu1" OR name2 = "jinzhu2" OR name3 = "jinzhu1"
db.Exec(
"UPDATE users SET name1 = @name, name2 = @name2, name3 = @name",
map[string]interface{}{"name": "jinzhu", "name2": "jinzhu2"},
)
// UPDATE users SET name1 = "jinzhu", name2 = "jinzhu2", name3 = "jinzhu"db.Where(
db.Where("pizza = ?", "pepperoni").Where(db.Where("size = ?", "small").Or("size = ?", "medium")),
).Or(
db.Where("pizza = ?", "hawaiian").Where("size = ?", "xlarge"),
).Find(&pizzas)
// SELECT * FROM pizzas WHERE (pizza = 'pepperoni' AND (size = 'small' OR size = 'medium')) OR (pizza = 'hawaiian' AND size = 'xlarge')// Where SubQuery
db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders)
// From SubQuery
db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age = ?", 18}).Find(&User{})
// SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE age = 18
// Update SubQuery
db.Model(&user).Update(
"price", db.Model(&Company{}).Select("name").Where("companies.id = users.company_id"),
)clause.OnConflict provides compatible Upsert support for different databases (SQLite, MySQL, PostgreSQL, SQL Server)
import "gorm.io/gorm/clause"
db.Clauses(clause.OnConflict{DoNothing: true}).Create(&users)
db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "id"}},
DoUpdates: clause.Assignments(map[string]interface{}{"name": "jinzhu", "age": 18}),
}).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 name="jinzhu", age=18; MySQL
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); MySQLdb.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`import "gorm.io/hints"
// Optimizer Hints
db.Clauses(hints.New("hint")).Find(&User{})
// SELECT * /*+ hint */ FROM `users`
// Index Hints
db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{})
// SELECT * FROM `users` USE INDEX (`idx_user_name`)
// Comment Hints
db.Clauses(hints.Comment("select", "master")).Find(&User{})
// SELECT /*master*/ * FROM `users`;Check out Hints for details
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)},
}
}
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",
Point: Point{X: 100, Y: 100},
})
// UPDATE `user_with_points` SET `name`="jinzhu",`point`=ST_PointFromText("POINT(100 100)") WHERE `id` = 1Check out Customize Data Types for details
Field permissions support, permission levels: read-only, write-only, create-only, update-only, ignored
type User struct {
Name string `gorm:"<-:create"` // allow read and create
Name string `gorm:"<-:update"` // allow read and update
Name string `gorm:"<-"` // allow read and write (create and update)
Name string `gorm:"->:false;<-:create"` // createonly
Name string `gorm:"->"` // readonly
Name string `gorm:"-"` // ignored
}type User struct {
CreatedAt time.Time // Set to current time if it is zero on creating
UpdatedAt int // Set to current unix seconds on updaing or if it is zero on creating
Updated int64 `gorm:"autoUpdateTime:nano"` // Use unix Nano seconds as updating time
Updated2 int64 `gorm:"autoUpdateTime:milli"` // Use unix Milli seconds as updating time
Created int64 `gorm:"autoCreateTime"` // Use unix seconds as creating time
}GORM provides multiple databases, read/write splitting support with plugin DB Resolver, which also supports auto-switching database/table based on current struct/table, and multiple sources、replicas supports with customized load-balancing logic
Check out Database Resolver for details
GORM provides plugin Prometheus to collect DBStats and user-defined metrics
Check out Prometheus for details
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
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
NamingStrategy: schema.NamingStrategy{TablePrefix: "t_", SingularTable: true},
})- Context support
- Customize/turn off the colors in the log
- Slow SQL log, default slow SQL time is 200ms
- Optimized the SQL log format so that it can be copied and executed in a database console
By default, all GORM write operations run inside a transaction to ensure data consistency, you can disable it during initialization to speed up write operations if it is not required
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
SkipDefaultTransaction: true,
})GORM optimizes support for custom types, so you can define a struct to support all databases
The following takes JSON as an example (which supports SQLite, MySQL, Postgres, refer: https://github.com/go-gorm/datatypes/blob/master/json.go)
import "gorm.io/datatypes"
type User struct {
gorm.Model
Name string
Attributes datatypes.JSON
}
db.Create(&User{
Name: "jinzhu",
Attributes: datatypes.JSON([]byte(`{"name": "jinzhu", "age": 18, "tags": ["tag1", "tag2"], "orgs": {"orga": "orga"}}`)),
}
// Query user having a role field in attributes
db.First(&user, datatypes.JSONQuery("attributes").HasKey("role"))
// Query user having orgs->orga field in attributes
db.First(&user, datatypes.JSONQuery("attributes").HasKey("orgs", "orga"))GORM allows select specific fields with Select, and in V2, GORM provides smart select mode if you are querying with a smaller struct
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 10Association Mode supports batch data, e.g:
// 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 unduplicated count of members in all user's team
db.Model(&users).Association("Team").Count()
// For `Append`, `Replace` with batch data, argument's length need to equal to data's length or will returns 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})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 user's account when deleting users
db.Select("Account").Delete(&users)We are trying to list big breaking changes or those changes can't be caught by the compilers, please create an issue or pull request here if you found any unlisted breaking changes
- GORM V2 prefer write tag name in
camelCase, tags insnake_casewon't works anymore, for example:auto_increment,unique_index,polymorphic_value,embedded_prefix, check out Model Tags - Tags used to specify foreign keys changed to
foreignKey,references, check out Associations Tags - Not support
sqltag
TableName will not allow dynamic table name anymore, the result of TableName will be cached for future
func (User) TableName() string {
return "t_user"
}Please use Scopes for dynamic tables, for example:
func UserTable(u *User) func(*gorm.DB) *gorm.DB {
return func(db *gorm.DB) *gorm.DB {
return db.Table("user_" + u.Role)
}
}
db.Scopes(UserTable(&user)).Create(&user)Previously tables could be created and dropped as follows:
db.CreateTable(&MyTable{})
db.DropTable(&MyTable{})Now you do the following:
db.Migrator().CreateTable(&MyTable{})
db.Migrator().DropTable(&MyTable{})A way of adding foreign key constraints was;
db.Model(&MyTable{}).AddForeignKey("profile_id", "profiles(id)", "NO ACTION", "NO ACTION")Now you add constraints as follows:
db.Migrator().CreateConstraint(&Users{}), "Profiles")
db.Migrator().CreateConstraint(&Users{}), "fk_users_profiles")which translates to the following sql code for postgres:
ALTER TABLE `Profiles` ADD CONSTRAINT `fk_users_profiles` FORIEGN KEY (`useres_id`) REFRENCES `users`(`id`))To reduce GC allocs, GORM V2 will share Statement when using method chains, and will only create new Statement instances for new initialized *gorm.DB or after a New Session Method, to reuse a *gorm.DB, you need to make sure it just after a New Session Method, for example:
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
// Safe for 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)
}
ctxDB := db.WithContext(ctx)
// Safe after a `New Session Method`
for i := 0; i < 100; i++ {
go ctxDB.Where(...).First(&user)
}
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
}Check out Method Chain for details
GORM V2 won't auto-reload default values created with database function after creating, checkout Default Values for details
GORM V1 will enable soft delete if the model has a field named DeletedAt, in V2, you need to use gorm.DeletedAt for the model wants to enable the feature, e.g:
type User struct {
ID uint
DeletedAt gorm.DeletedAt
}
type User struct {
ID uint
// field with different name
Deleted gorm.DeletedAt
}{% note warn %}
NOTE: gorm.Model is using gorm.DeletedAt, if you are embedding it, nothing needs to change
{% endnote %}
GORM V2 enabled BlockGlobalUpdate mode by default, to trigger a global update/delete, you have to use some conditions or use raw SQL or enable AllowGlobalUpdate mode, for example:
db.Where("1 = 1").Delete(&User{})
db.Raw("delete from users")
db.Session(&gorm.Session{AllowGlobalUpdate: true}).Delete(&User{})GORM V2 only returns ErrRecordNotFound when you are querying with methods First, Last, Take which is expected to return some result, and we have also removed method RecordNotFound in V2, please use errors.Is to check the error, e.g:
err := db.First(&user).Error
errors.Is(err, gorm.ErrRecordNotFound)Before/After Create/Update/Save/Find/Delete must be defined as a method of type func(tx *gorm.DB) error in V2, which has unified interfaces like plugin callbacks, if defined as other types, a warning log will be printed and it won't take effect, check out Hooks for details
func (user *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})
// Operations based on tx will runs inside same transaction without clauses of current one
var role Role
err := tx.First(&role, "name = ?", user.Role).Error
// SELECT * FROM roles WHERE name = "admin"
return err
}When updating with Update, Updates, You can use Changed method in Hooks BeforeUpdate, BeforeSave to check a field changed or not
func (user *User) BeforeUpdate(tx *gorm.DB) error {
if tx.Statement.Changed("Name", "Admin") { // if Name or Admin changed
tx.Statement.SetColumn("Age", 18)
}
if tx.Statement.Changed() { // if any fields changed
tx.Statement.SetColumn("Age", 18)
}
return nil
}
db.Model(&user).Update("Name", "Jinzhu") // update field `Name` to `Jinzhu`
db.Model(&user).Updates(map[string]interface{}{"name": "Jinzhu", "admin": false}) // update field `Name` to `Jinzhu`, `Admin` to false
db.Model(&user).Updates(User{Name: "Jinzhu", Admin: false}) // Update none zero fields when using struct as argument, will only update `Name` to `Jinzhu`
db.Model(&user).Select("Name", "Admin").Updates(User{Name: "Jinzhu"}) // update selected fields `Name`, `Admin`，`Admin` will be updated to zero value (false)
db.Model(&user).Select("Name", "Admin").Updates(map[string]interface{}{"Name": "Jinzhu"}) // update selected fields exists in the map, will only update field `Name` to `Jinzhu`
// Attention: `Changed` will only check the field value of `Update` / `Updates` equals `Model`'s field value, it returns true if not equal and the field will be saved
db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(map[string]interface{"name": "jinzhu2"}) // Changed("Name") => true
db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(map[string]interface{"name": "jinzhu"}) // Changed("Name") => false, `Name` not changed
db.Model(&User{ID: 1, Name: "jinzhu"}).Select("Admin").Updates(map[string]interface{"name": "jinzhu2", "admin": false}) // Changed("Name") => false, `Name` not selected to update
db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(User{Name: "jinzhu2"}) // Changed("Name") => true
db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(User{Name: "jinzhu"}) // Changed("Name") => false, `Name` not changed
db.Model(&User{ID: 1, Name: "jinzhu"}).Select("Admin").Updates(User{Name: "jinzhu2"}) // Changed("Name") => false, `Name` not selected to updatePlugin callbacks also need be defined as a method of type func(tx *gorm.DB) error, check out Write Plugins for details
When updating with struct, GORM V2 allows to use Select to select zero-value fields to update them, for example:
db.Model(&user).Select("Role", "Age").Update(User{Name: "jinzhu", Role: "", Age: 0})GORM V1 allows to use some settings to skip create/update associations, in V2, you can use Select to do the job, for example:
db.Omit(clause.Associations).Create(&user)
db.Omit(clause.Associations).Save(&user)
db.Select("Company").Save(&user)and GORM V2 doesn't allow preload with Set("gorm:auto_preload", true) anymore, you can use Preload with clause.Associations, e.g:
// preload all associations
db.Preload(clause.Associations).Find(&users)Also, checkout field permissions, which can be used to skip creating/updating associations globally
GORM V2 will use upsert to save associations when creating/updating a record, won't save full associations data anymore to protect your data from saving uncompleted data, for example:
user := User{
Name: "jinzhu",
BillingAddress: Address{Address1: "Billing Address - Address 1"},
ShippingAddress: Address{Address1: "Shipping Address - Address 1"},
Emails: []Email{
{Email: "jinzhu@example.com"},
{Email: "jinzhu-2@example.com"},
},
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, "jinzhu@example.com"), (111, "jinzhu-2@example.com") 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;In GORM V2, a JoinTable can be a full-featured model, with features like Soft Delete，Hooks, and define other fields, e.g:
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
AddressID int
CreatedAt time.Time
DeletedAt gorm.DeletedAt
}
func (PersonAddress) BeforeCreate(db *gorm.DB) error {
// ...
}
// PersonAddress must defined all required foreign keys, or it will raise error
err := db.SetupJoinTable(&Person{}, "Addresses", &PersonAddress{})After that, you could use normal GORM methods to operate the join table data, for example:
var results []PersonAddress
db.Where("person_id = ?", person.ID).Find(&results)
db.Where("address_id = ?", address.ID).Delete(&PersonAddress{})
db.Create(&PersonAddress{PersonID: person.ID, AddressID: address.ID})Count only accepts *int64 as the argument
some transaction methods like RollbackUnlessCommitted removed, prefer to use method Transaction to wrap your transactions
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
})Checkout Transactions for details
- Migrator will create database foreign keys by default
- Migrator is more independent, many API renamed to provide better support for each database with unified API interfaces
- AutoMigrate will alter column's type if its size, precision, nullable changed
- Support Checker through tag
check - Enhanced tag setting for
index
Checkout Migration for details
type UserIndex struct {
Name string `gorm:"check:named_checker,(name <> 'jinzhu')"`
Name2 string `gorm:"check:(age > 13)"`
Name4 string `gorm:"index"`
Name5 string `gorm:"index:idx_name,unique"`
Name6 string `gorm:"index:,sort:desc,collate:utf8,type:btree,length:10,where:name3 != 'jinzhu'"`
}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
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
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 processDelete a callback from callbacks
db.Callback().Create().Remove("gorm:create")
// delete callback `gorm:create` from Create callbacksReplace 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 processRegister 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)GORM has defined some callbacks to power current GORM features, check them out before starting your plugins
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