Original text connection: https://blog.csdn.net/u010525...

install

go get -u github.com/jinzhu/gorm

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

type Product struct {
  gorm.Model    // gorm.Model helps us define some fields, such as ID and created_at wait
  Code string
  Price uint
}

func main() {
  db, err := gorm.Open("sqlite3", "test.db")
  if err != nil {
    panic("failed to connect database")
  }
  defer db.Close()

  //Automatically check whether the Product structure changes, and migrate if it changes
  db.AutoMigrate(&Product{})

  // increase
  db.Create(&Product{Code: "L1212", Price: 1000})

  // check
  var product Product
  db.First(&product, 1) // Found product with id 1
  db.First(&product, "code = ?", "L1212") // Find the product with code l1212

  // Change - the price of the updated product is 2000
  db.Model(&product).Update("Price", 2000)

  // Delete - delete product
  db.Delete(&product)
}

Model definition

Models are generally ordinary structures of Golang, basic data types of Go, or pointers. sql.Scanner and driver Valuer, and also supports interfaces.

example:

type User struct {
  gorm.Model
  Name         string
  Age          sql.NullInt64
  Birthday     *time.Time
  Email        string  `gorm:"type:varchar(100);unique_index"`
  Role         string  `gorm:"size:255"` //Set the size of the field to 255 bytes
  MemberNumber *string `gorm:"unique;not null"` // Set the memberNumber field to be unique and not empty
  Num          int     `gorm:"AUTO_INCREMENT"` // Set Num Field Auto increment
  Address      string  `gorm:"index:addr"` // Create an index named 'addr' for Address
  IgnoreMe     int     `gorm:"-"` //Ignore this field
}

Structure label

A tag is an optional tag when declaring a model. GORM supports the following tags:

Supported structure labels

Associated structure label

For more information, see the association section

Agreed Gorm Model

gorm.Model is a structure containing some basic fields, including ID, CreatedAt, UpdatedAt and DeletedAt.

You can use it to embed it into your model, or you can combine it to build your own model.

// gorm.Model definition
type Model struct {
  ID        uint `gorm:"primary_key"`
  CreatedAt time.Time
  UpdatedAt time.Time
  DeletedAt *time.Time
}

// Inject the fields' ID ',' createdat ',' updatedat 'and' deletedat 'into the' User 'model
type User struct {
  gorm.Model
  Name string
}

// Don't use Gorm Model model
type User struct {
  ID   int
  Name string
}

ID as primary key

GORM uses ID as the primary key name by default.

type User struct {
  ID   string // The field name 'ID' will be used as the default primary key name
}

// Set the field 'AnimalID' as the default primary key
type Animal struct {
  AnimalID int64 `gorm:"primary_key"`
  Name     string
  Age      int64
}

Plural table name

Table names are plural forms of structure names

type User struct {} // The default table name is ` users`

// Set the table name of 'User' to 'profiles'`
func (User) TableName() string {
  return "profiles"
}

func (u User) TableName() string {
    if u.Role == "admin" {
        return "admin_users"
    } else {
        return "users"
    }
}

// If the disable table name plural property is set to true, the table name of 'user' will be 'user'`
db.SingularTable(true)

Specify table name

// Create 'Delete' with 'User' structure_ Users ` table
db.Table("deleted_users").CreateTable(&User{})

var deleted_users []User
db.Table("deleted_users").Find(&deleted_users)
 SELECT * FROM deleted_users;

db.Table("deleted_users").Where("name = ?", "jinzhu").Delete()
 DELETE FROM deleted_users WHERE name = 'jinzhu';

Modify default table name

You can use any rule for table names by defining the DefaultTableNameHandler field.

gorm.DefaultTableNameHandler = func (db *gorm.DB, defaultTableName string) string  {
    return "prefix_" + defaultTableName;
}

Serpentine listing

The column name is the serpentine lowercase (lowercase letters underlined) form of the field name

type User struct {
  ID        uint      // Field name is ` id`
  Name      string    // Field name is ` name`
  Birthday  time.Time // The field name is ` birthday`
  CreatedAt time.Time // The field name is ` created_at`
}

// Override column name
type Animal struct {
    AnimalId    int64     `gorm:"column:beast_id"`         // Set column name to ` beast_id`
    Birthday    time.Time `gorm:"column:day_of_the_beast"` // Set column name to ` day_of_the_beast`
    Age         int64     `gorm:"column:age_of_the_beast"` // Set column name to ` age_of_the_beast`
}

Timestamp tracking

CreatedAt

For a model with a CreatedAt field, it will be set to the current time when the record was first created.

db.Create(&user) // Set 'CreatedAt' to the current time

// You can use the 'Update' method to change the default time
db.Model(&user).Update("CreatedAt", time.Now())

UpdatedAt

For the model with UpdatedAt field, it will be set to the current time when the record is updated.

db.Save(&user) // Set 'UpdatedAt' to the current time

db.Model(&user).Update("name", "jinzhu") // Set 'UpdatedAt' to the current time

DeletedAt

For models with DeletedAt field, when their instances are deleted, they are not deleted from the database, but the DeletedAt field is set to the current time. Refer to Soft Delete.

Connect to database
In order to connect to the database, you must first import the database driver. For example:

import _ "github.com/go-sql-driver/mysql"

GORM already contains some drivers. In order to remember their import paths, you can import mysql drivers as follows

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

Supported databases

MySQL

Note: in order to correctly handle time Time, you need to include parseTime as a parameter.

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

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

PostgreSQL

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

func main() {
  db, err := gorm.Open("postgres", "host=myhost port=myport user=gorm dbname=gorm password=mypassword")
  defer db.Close()
}

Sqlite3

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

func main() {
  db, err := gorm.Open("sqlite3", "/tmp/gorm.db")
  defer db.Close()
}

SQL Server

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

func main() {
  db, err := gorm.Open("mssql", "sqlserver://username:password@localhost:1433?database=dbname")
  defer db.Close()
}

Unsupported database
GORM officially supports the above four databases. You can write support for unsupported databases. Refer to [GORM conversations] ( https://github.com/jinzhu/gor...)

Add, delete, modify and query API

Create record

user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}

db.NewRecord(user) // =>Return 'true' because the primary key is empty

db.Create(&user)

db.NewRecord(user) // =>Create after 'user' and return 'false'

Default value

You can define the default value of the field through the label, for example:

type Animal struct {
    ID   int64
    Name string `gorm:"default:'galeone'"`
    Age  int64
}

Then SQL will exclude those fields with no value or zero value. After the record is inserted into the database, gorm will load the values of these fields from the database. (null fields in the database are loaded from the default values)

var animal = Animal{Age: 99, Name: ""}
db.Create(&animal)
// INSERT INTO animals("age") values('99');
// SELECT name from animals WHERE ID=111; //  The returned primary key is 111
// animal.Name => 'galeone'

Note that all fields containing zero values, such as 0, '', false or other zero values, will not be saved to the database, but the default value of this field will be used. You should consider using pointer types or other values to avoid this situation:

// Use pointer value
type User struct {
  gorm.Model
  Name string
  Age  *int `gorm:"default:18"`
}

// Use scanner/valuer
type User struct {
  gorm.Model
  Name string
  Age  sql.NullInt64 `gorm:"default:18"`
}

Set the field value in the hook

If you want to update the value of the field in the BeforeCreate function, you should use scope Setcolumn, for example:

func (user *User) BeforeCreate(scope *gorm.Scope) error {
  scope.SetColumn("ID", uuid.New())
  return nil
}

Create additional options

// Add additional options for inserting SQL statements
db.Set("gorm:insert_option", "ON CONFLICT").Create(&product)
// INSERT INTO products (name, code) VALUES ("name", "code") ON CONFLICT;

query

// Get the first record, sort by primary key
db.First(&user)
 SELECT * FROM users ORDER BY id LIMIT 1;

// Get a record without specifying sorting
db.Take(&user)
 SELECT * FROM users LIMIT 1;

// Get the last record, sort by primary key
db.Last(&user)
 SELECT * FROM users ORDER BY id DESC LIMIT 1;
var users []User
// Get all records
db.Find(&users)
 SELECT * FROM users;

// Query by primary key (only applicable if the primary key is numeric)
db.First(&user, 10)
 SELECT * FROM users WHERE id = 10;

Where

Primordial sql
/ Get the first matching record
db.Where("name = ?", "jinzhu").First(&user)
 SELECT * FROM users WHERE name = 'jinzhu' limit 1;

// Get all matching records
db.Where("name = ?", "jinzhu").Find(&users)
 SELECT * FROM users WHERE name = 'jinzhu';

// <>
db.Where("name <> ?", "jinzhu").Find(&users)

// IN
db.Where("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)

// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)

// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)

// Time
db.Where("updated_at > ?", lastWeek).Find(&users)

// BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)

Struct & Map

// Struct
db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
 SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1;

// Map
db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
 SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

// Multiple primary key slice query
db.Where([]int64{20, 21, 22}).Find(&users)
 SELECT * FROM users WHERE id IN (20, 21, 22);

Note: when querying through struct, GORM will query the non-zero values of these fields, which means that your fields contain 0, '', false or other zero values and will not appear in the query statement, for example:

db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)
 SELECT * FROM users WHERE name = "jinzhu";

You can consider applying pointer types or scanner/valuer to avoid this situation.

// Use pointer type
type User struct {
  gorm.Model
  Name string
  Age  *int
}

// Using scanner/valuer
type User struct {
  gorm.Model
  Name string
  Age  sql.NullInt64
}

Not

Similar to Where query:

db.Not("name", "jinzhu").First(&user)
 SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1;

// Not included
db.Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&users)
 SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");

//Not in primary key slice
db.Not([]int64{1,2,3}).First(&user)
 SELECT * FROM users WHERE id NOT IN (1,2,3);

db.Not([]int64{}).First(&user)
 SELECT * FROM users ORDER BY ID LIMIT 1;

// Native SQL
db.Not("name = ?", "jinzhu").First(&user)
 SELECT * FROM users WHERE NOT(name = "jinzhu");

// Struct
db.Not(User{Name: "jinzhu"}).First(&user)
 SELECT * FROM users WHERE name <> "jinzhu";

Or

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"}).Find(&users)
 SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

// Map
db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users)
 SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

Intra row condition query

Similar to Where query.

It should be noted that when using chained call to pass in inline condition queries, these queries will not be passed to subsequent intermediate methods.

// Query by primary key (only applicable if the primary key is numeric)
db.First(&user, 23)
 SELECT * FROM users WHERE id = 23 LIMIT 1;
// Non numeric primary key query
db.First(&user, "id = ?", "string_primary_key")
 SELECT * FROM users WHERE id = 'string_primary_key' LIMIT 1;

// Native 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;

Additional Query options

// Add additional options for SQL queries
db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10)
 SELECT * FROM users WHERE id = 10 FOR UPDATE;

FirstOrInit

Get the first matching record, or start a new record under the given conditions (only applicable to struct and map conditions).

// Not found
db.FirstOrInit(&user, User{Name: "non_existing"})
 user -> User{Name: "non_existing"}

// Found
db.Where(User{Name: "Jinzhu"}).FirstOrInit(&user)
 user -> User{Id: 111, Name: "Jinzhu", Age: 20}
db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"})
 user -> User{Id: 111, Name: "Jinzhu", Age: 20}

Attrs

If no record is found, the struct is initialized with parameters

// Not found
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
 SELECT * FROM USERS WHERE name = 'non_existing';
 user -> User{Name: "non_existing", Age: 20}

db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
 SELECT * FROM USERS WHERE name = 'non_existing';
 user -> User{Name: "non_existing", Age: 20}

// Found
db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 30}).FirstOrInit(&user)
 SELECT * FROM USERS WHERE name = jinzhu';
 user -> User{Id: 111, Name: "Jinzhu", Age: 20}

Assign

Whether the data is queried or not, the parameter is assigned to struct

// Not found
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)
 user -> User{Name: "non_existing", Age: 20}

// Found
db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 30}).FirstOrInit(&user)
 SELECT * FROM USERS WHERE name = jinzhu';
 user -> User{Id: 111, Name: "Jinzhu", Age: 30}

FirstOrCreate

Get the first matching record, or create a record with a given condition (only applicable to struct and map conditions).

// Not found
db.FirstOrCreate(&user, User{Name: "non_existing"})
 INSERT INTO "users" (name) VALUES ("non_existing");
 user -> User{Id: 112, Name: "non_existing"}

// Found
db.Where(User{Name: "Jinzhu"}).FirstOrCreate(&user)
 user -> User{Id: 111, Name: "Jinzhu"}

Note: Attrs and Assign are used similarly.
Note the difference between FirstOrCreate and FirstOrInit:
The creation or update of FirstOrCreate will trigger a callback, but FirstOrInit will not.
The source code is shown in the figure below

Advanced query

Subquery

Use * Gorm Expr for sub query

db.Where("amount > ?", DB.Table("orders").Select("AVG(amount)").Where("state = ?", "paid").QueryExpr()).Find(&orders)
// SELECT * FROM "orders"  WHERE "orders"."deleted_at" IS NULL AND (amount > (SELECT AVG(amount) FROM "orders"  WHERE (state = 'paid')));

Query field

Specify the fields to retrieve from the database, all of which are selected 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;

Order

When using Order to query records from the database, when the second parameter is set to true, the previously defined conditions will be overwritten.

db.Order("age desc, name").Find(&users)
 SELECT * FROM users ORDER BY age desc, name;

// Multiple sort criteria
db.Order("age desc").Order("name").Find(&users)
 SELECT * FROM users ORDER BY age desc, name;

// Reorder
db.Order("age desc").Find(&users1).Order("age", true).Find(&users2)
 SELECT * FROM users ORDER BY age desc; (users1)
 SELECT * FROM users ORDER BY age; (users2)

Limit

Specify the maximum number of records to query, and use - 1 to cancel the LIMIT condition

db.Limit(3).Find(&users)
 SELECT * FROM users LIMIT 3;

// Cancel the LIMIT restriction with - 1
db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
 SELECT * FROM users LIMIT 10; (users1)
 SELECT * FROM users; (users2)

Offset

Specifies the number of records to skip before starting to return records.

db.Offset(3).Find(&users)
 SELECT * FROM users OFFSET 3;

// Cancel OFFSET restriction with - 1
db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
 SELECT * FROM users OFFSET 10; (users1)
 SELECT * FROM users; (users2)

Count

Get the number of model records

db.Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Find(&users).Count(&count)
 SELECT * from USERS WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (users)
 SELECT count(*) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (count)

db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
 SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)

db.Table("deleted_users").Count(&count)
 SELECT count(*) FROM deleted_users;

Note: when using Count in the query chain, it must be placed in the last position because it will override the SELECT query condition.

Group query group and Having

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
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()
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)

Specify Association condition Joins

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 association queries
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)

Pluck

Use plug to query a single column from the model as a collection. If you want to query multiple columns, you should use Scan instead.

var ages []int64
db.Find(&users).Pluck("age", &ages)

var names []string
db.Model(&User{}).Pluck("name", &names)

db.Table("deleted_users").Pluck("name", &names)

// Requesting more than one column? Do it like this:
db.Select("name, age").Scan(&users)

Scan

Put the Scan query results into another structure.

type Result struct {
    Name string
    Age  int
}

var result Result
db.Table("users").Select("name, age").Where("name = ?", 3).Scan(&result)

// Raw SQL
db.Raw("SELECT name, age FROM users WHERE name = ?", 3).Scan(&result)

Note: what is the difference between Scan and Find?
My understanding is that Find is used for and the struct of the model itself; Scan can be an external custom structure.

to update

Update all fields
The Save method will include all fields when performing the SQL update operation, even if they have not been modified.

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;

Only the specified fields are updated

If you only want to Update the modified fields, you can use the Update, Updates method.

Note: when using structure update, GORM will only update those non empty fields:

// If a single property is changed, update it
db.Model(&user).Update("name", "hello")
 UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;

// Update a single attribute with a combination condition
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;

// Using 'map' to update multiple attributes will only update those changed fields
db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})
 UPDATE users SET name='hello', age=18, actived=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

// Using 'struct' to update multiple attributes will only update those modified and non empty fields
db.Model(&user).Updates(User{Name: "hello", Age: 18})
 UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

// Warning: when using structure update, GORM will only update those non empty fields
// For example, in the following updates, nothing will be updated, because like "" 0, false is the null value of these field types
db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})

Select / exclude only updated fields

If you only want to update or ignore some fields during the update operation, you can use the Select, Omit method.

db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})
 UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;

db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})
 UPDATE users SET age=18, actived=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

Update column hook method

The above update operation will execute the BeforeUpdate and AfterUpdate methods of the model to update the UpdatedAt timestamp and save its association. If you don't want to perform these operations, you can use the UpdateColumn and UpdateColumns methods.
Note: the following methods will not trigger the hook function:

// Update single attribute, similar with `Update`
db.Model(&user).UpdateColumn("name", "hello")
 UPDATE users SET name='hello' WHERE id = 111;

// Update multiple attributes, similar with `Updates`
db.Model(&user).UpdateColumns(User{Name: "hello", Age: 18})
 UPDATE users SET name='hello', age=18 WHERE id = 111;

Batch update

During batch update, the hook function will not be executed

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);

// Using struct updates will only apply to non-zero values, or use map[string]interface {}
db.Model(User{}).Updates(User{Name: "hello", Age: 18})
 UPDATE users SET name='hello', age=18;

// Use 'RowsAffected' to get the number of records affected by the update
db.Model(User{}).Updates(User{Name: "hello", Age: 18}).RowsAffected

With expressions SQL to update
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" = '2';

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" = '2';

DB.Model(&product).UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))
 UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = '2';

DB.Model(&product).Where("quantity > 1").UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))
 UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = '2' AND quantity > 1;

Update value in hook function

If you want to use the BeforeUpdate and BeforeSave hook functions to modify the updated values, you can use scope Setcolumn method, for example:

func (user *User) BeforeSave(scope *gorm.Scope) (err error) {
  if pw, err := bcrypt.GenerateFromPassword(user.Password, 0); err == nil {
    scope.SetColumn("EncryptedPassword", pw)
  }
}

Additional update options

What does OPTION do???

// Add additional SQL options to the update SQL statement
db.Model(&user).Set("gorm:update_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Update("name", "hello")
 UPDATE users SET name='hello', updated_at = '2013-11-17 21:34:10' WHERE id=111 OPTION (OPTIMIZE FOR UNKNOWN);

delete

Warning: when deleting a record, you need to make sure that the primary key of the record has a value. GORM will use the primary key to delete the record. If the primary key field is empty, GORM will delete all records in the model.

// To delete an existing record, you'd better ensure that the email primary key is not empty before deleting
db.Delete(&email)
 DELETE from emails where id=10;

// Add additional options for deleting SQL statements
db.Set("gorm:delete_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Delete(&email)
 DELETE from emails where id=10 OPTION (OPTIMIZE FOR UNKNOWN);

Batch delete
Delete all matching 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%";

Soft delete

If there is a DeletedAt field in the model, it will automatically have the ability of soft deletion! When deleting, the data will not be permanently deleted from the database, but the value of DeletedAt will be updated to the current time.
That is, the UPDATE statement is actually executed during soft deletion.

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;

// When querying records, soft deletion records will be ignored
db.Where("age = 20").Find(&user)
 SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;

// Find soft delete records using the Unscoped method
db.Unscoped().Where("age = 20").Find(&users)
 SELECT * FROM users WHERE age = 20;

// Use the Unscoped method to permanently delete records
db.Unscoped().Delete(&order)
 DELETE FROM orders WHERE id=10;

Association relationship

Belongs To

The belongs to association establishes a one-to-one connection with another model so that the model declares that each instance "belongs" to an instance of another model.

For example, if your application contains users and user data, and each user data is assigned to only one user

type User struct {
  gorm.Model
  Name string
}

// `Profile ` belongs to 'User', and 'UserID' is a foreign key
type Profile struct {
  gorm.Model
  UserID int
  User   User
  Name   string
}

Foreign key

In order to define a dependency relationship, a foreign key must exist. The default foreign key uses the owner type name plus its primary key.

Like the above example, in order to declare that a model belongs to User, its foreign key should be UserID.

GORM provides a method to customize foreign keys, for example:

type User struct {
    gorm.Model
    Name string
}

type Profile struct {
    gorm.Model
  Name      string
  User      User `gorm:"foreignkey:UserRefer"` // When User is assigned to a Profile using UserRefer as the foreign key > >, the User ID assigned to userref
  UserRefer string
}

Associated foreign key

For dependencies, GORM usually uses the owner's primary key as the foreign key value. In the above example, it is the User's ID.

When you assign a profile to a user, GORM will save the ID value of the user table into the UserID field of the user profile.

You can change the label Association_ Foreign key to change it, for example:

type User struct {
    gorm.Model
  Refer int
    Name string
}

type Profile struct {
    gorm.Model
  Name      string
  User      User `gorm:"association_foreignkey:Refer"` // use Refer as the associated foreign key
  UserRefer string
}

Note: the difference between an associated foreign key and a foreign key: an associated foreign key does not have to be the primary key of an associated object;
foreignkey in gorm tag refers to the field corresponding to the primary key of the associated object in this object; association_foreignkey is the relationship field between the value object and the associated object. This field must exist in the associated object. After being specified, there should be a field named "associated object name + associated field name" in this object.

Has One

has one association is also a one-to-one connection with another model, but the semantics (and results) are somewhat different. This association indicates that each instance of a model contains or owns an instance of another model.

For example, if your application contains users and credit cards, and each user can only have one credit card.

// The user has a credit card, CredtCardID foreign key
type User struct {
    gorm.Model
    CreditCard   CreditCard
  CreditCardID uint
}

type CreditCard struct {
    gorm.Model
    Number   string
}

Foreign key

For a one-to-one relationship, a foreign key field must also exist. The owner will save the primary key to the field associated with the model.

The name of this field is usually generated by the type of belongs to model plus its primary key. In the above example, it is CreditCardID

When you give a user a credit card, it will save a credit card ID to the creditcard ID field.

If you want to use another field to save this relationship, you can change it by using the label foreignkey, for example:

type User struct {
  gorm.Model
  CreditCard CreditCard `gorm:"foreignkey:CardRefer"`
  CardRefer uint
}

type CreditCard struct {
    gorm.Model
    Number string
}

Associated foreign key

Usually, the owner will save the primary key to the foreign key of belogns to model. You can save other fields instead, just like the following example using Number.

type User struct {
  gorm.Model
  CreditCard CreditCard `gorm:"association_foreignkey:Number"`
  CardRefer uint 
  // CardNumber string // ??
}

type CreditCard struct {
    gorm.Model
    Number string
}

Polymorphic Association

Support polymorphic one-to-many and one-to-one associations.

 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
  }

Note: polymorphism and many to many are explicitly not supported and will throw an error.

Use one-to-one

You can find the has one association through Related.

var card CreditCard
db.Model(&user).Related(&card, "CreditCard")
 SELECT * FROM credit_cards WHERE user_id = 123; // 123 is the primary key of the user table
// CreditCard is the field name of the user table, which means that the user's credit card relationship is obtained and written to the variable card.
// As in the above example, if the field name is the same as the variable type name, it can be omitted, such as:
db.Model(&user).Related(&card)

One to many Has Many

has many association is to create a one to many relationship with another model. Unlike has one, the owner can have 0 or more model instances.

For example, if your application contains users and credit cards, and each user has multiple credit cards.

// The user has multiple credit cards, and the UserID is a foreign key
type User struct {
    gorm.Model
    CreditCards []CreditCard
}

type CreditCard struct {
    gorm.Model
    Number   string
    UserID  uint
}

Foreign key

In order to define a one to many relationship, a foreign key must exist. By default, the name of the foreign key is the name of the owner type plus its primary key.

As in the above example, in order to define a model belonging to User, the foreign key should be UserID.

Using other field names as foreign keys, you can customize it through foreign key, for example:

type User struct {
    gorm.Model
    CreditCards []CreditCard `gorm:"foreignkey:UserRefer"`
}

type CreditCard struct {
    gorm.Model
    Number    string
      UserRefer uint
}

Foreign key Association

GORM usually uses the owner's primary key as the value of the foreign key. In the above example, it is the User's ID.

When you assign a credit card to a user, GORM will save the user ID to the UserID field of the credit card table.

You can pass the Association_ Foreign key to change it, for example:

type User struct {
    gorm.Model
  MemberNumber string
    CreditCards  []CreditCard `gorm:"foreignkey:UserMemberNumber;association_foreignkey:MemberNumber"`
}

type CreditCard struct {
    gorm.Model
    Number           string
  UserMemberNumber string
}

Polymorphic Association

Support polymorphic one-to-many and one-to-one associations.

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
  }

Use one to many

You can find the has many relationship through Related.

db.Model(&user).Related(&emails)
 SELECT * FROM emails WHERE user_id = 111; // 111 is the primary key of the user table

Many To Many

Many to many adds an intermediate table to the two models.

For example, if your application contains users and languages, one user will speak multiple languages, and many users will speak a specific language.