SoFunction
Updated on 2025-03-04

Examples of preloading and soft deletion of golang gorm

1. Orm reads and writes data

1. Create

1.1. Create a record

user := User{Name: "Jinzhu", Age: 18, Birthday: ()}
(user) // => Returns `true` when the primary key is empty`(&user)
(user) // => Return `false` after creating `user`

1.2. Default value

You can define the default values ​​in the gorm tag, and insert SQL will ignore these fields with default values ​​and their value is empty, and after inserting the record into the database, gorm will load the values ​​of these fields from the database.

type Animal struct {
    ID   int64
    Name string `gorm:"default:'galeone'"`
    Age  int64
}
var animal = Animal{Age: 99, Name: ""}
(&animal)
// INSERT INTO animals("age") values('99');
// SELECT name from animals WHERE ID=111; // Return primary key is 111//  => 'galeone'

1.3. Set the primary key in Callbacks

If you want to set the value of the main field in the BeforeCreate callback, you can use, for example:

func (user *User) BeforeCreate(scope *) error {
  ("ID", ())
  return nil
}

1.4. Extended creation options

// Add extended SQL options to Intert statement("gorm:insert_option", "ON CONFLICT").Create(&product)
// INSERT INTO products (name, code) VALUES ("name", "code") ON CONFLICT;

2. Query

// Get the first record and sort by primary key(&user)
//// SELECT * FROM users ORDER BY id LIMIT 1;
// Get the last record and sort it by primary key(&user)
//// SELECT * FROM users ORDER BY id DESC LIMIT 1;
// Get all records(&users)
//// SELECT * FROM users;
// Use primary key to get records(&user, 10)
//// SELECT * FROM users WHERE id = 10;

2.1. Where query conditions (simple SQL)

// Get the first matching record("name = ?", "jinzhu").First(&user)
//// SELECT * FROM users WHERE name = 'jinzhu' limit 1;
// Get all matching records("name = ?", "jinzhu").Find(&users)
//// SELECT * FROM users WHERE name = 'jinzhu';
("name <> ?", "jinzhu").Find(&users)
// IN
("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)
// LIKE
("name LIKE ?", "%jin%").Find(&users)
// AND
("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
// Time
("updated_at > ?", lastWeek).Find(&users)
("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)

2.2. Where Query Conditions (Struct & Map)

Note: When using struct query, GORM will query only those fields with values

// Struct
(&User{Name: "jinzhu", Age: 20}).First(&user)
//// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1;
// Map
(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
//// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;
// Slice of primary key([]int64{20, 21, 22}).Find(&users)
//// SELECT * FROM users WHERE id IN (20, 21, 22);

2.3. Not conditional query

("name", "jinzhu").First(&user)
//// SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1;
// Not In
("name", []string{"jinzhu", "jinzhu 2"}).Find(&users)
//// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");
// Not In slice of primary keys
([]int64{1,2,3}).First(&user)
//// SELECT * FROM users WHERE id NOT IN (1,2,3);
([]int64{}).First(&user)
//// SELECT * FROM users;
// Plain SQL
("name = ?", "jinzhu").First(&user)
//// SELECT * FROM users WHERE NOT(name = "jinzhu");
// Struct
(User{Name: "jinzhu"}).First(&user)
//// SELECT * FROM users WHERE name <> "jinzhu";

2.4. Query with inline conditions

Note: When using primary key query, you should carefully check whether the passed value is a valid primary key to avoid SQL injection

// Press the primary key to get(&amp;user, 23)
//// SELECT * FROM users WHERE id = 23 LIMIT 1;
// Simple SQL(&amp;user, "name = ?", "jinzhu")
//// SELECT * FROM users WHERE name = "jinzhu";
(&amp;users, "name &lt;&gt; ? AND age &gt; ?", "jinzhu", 20)
//// SELECT * FROM users WHERE name &lt;&gt; "jinzhu" AND age &gt; 20;
// Struct
(&amp;users, User{Age: 20})
//// SELECT * FROM users WHERE age = 20;
// Map
(&amp;users, map[string]interface{}{"age": 20})
//// SELECT * FROM users WHERE age = 20;

2.5. Or condition query

("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)
//// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';
// Struct
("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&users)
//// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';
// Map
("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users)

2.6. Query chain

Gorm has a linkable API, you can use it like this

("name <> ?","jinzhu").Where("age >= ? and role <> ?",20,"admin").Find(&users)
//// SELECT * FROM users WHERE name <> 'jinzhu' AND age >= 20 AND role <> 'admin';
("role = ?", "admin").Or("role = ?", "super_admin").Not("name = ?", "jinzhu").Find(&users)

2.7. Extended query options

// Add extended SQL options to Select statements("gorm:query_option", "FOR UPDATE").First(&amp;amp;user, 10)
//// SELECT * FROM users WHERE id = 10 FOR UPDATE;

2.8. FirstOrInit

Get the first matching record, or initialize a new record with the given condition (only for struct, map conditions)

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

2.9. Attrs

If no record is found, the structure is initialized using parameters

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

2.10. Assign

Assign the parameter to the result, regardless of whether it is found or not

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

2.11. FirstOrCreate

Get the first matching record, or create a new record with the given condition (only for struct, map condition)

// Unfound
(&user, User{Name: "non_existing"})
//// INSERT INTO "users" (name) VALUES ("non_existing");
//// user -> User{Id: 112, Name: "non_existing"}
// Found
(User{Name: "Jinzhu"}).FirstOrCreate(&user)
//// user -> User{Id: 111, Name: "Jinzhu"}

2.12. Attrs

If no record is found, then the structure is assigned to the parameters

// Unfound
(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
//// SELECT * FROM users WHERE name = 'non_existing';
//// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
//// user -> User{Id: 112, Name: "non_existing", Age: 20}
// Found
(User{Name: "jinzhu"}).Attrs(User{Age: 30}).FirstOrCreate(&user)
//// SELECT * FROM users WHERE name = 'jinzhu';
//// user -> User{Id: 111, Name: "jinzhu", Age: 20}

2.13. Assign

Assign it to the record, regardless of whether it is found or not, and saved back to the database.

// Unfound
(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)
//// SELECT * FROM users WHERE name = 'non_existing';
//// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
//// user -> User{Id: 112, Name: "non_existing", Age: 20}
// Found
(User{Name: "jinzhu"}).Assign(User{Age: 30}).FirstOrCreate(&user)
//// SELECT * FROM users WHERE name = 'jinzhu';
//// UPDATE users SET age=30 WHERE id = 111;
//// user -> User{Id: 111, Name: "jinzhu", Age: 30}

2.14. Select

Specifies the fields to retrieve from the database, and by default, all fields are selected;

("name, age").Find(&users)
//// SELECT name, age FROM users;
([]string{"name", "age"}).Find(&users)
//// SELECT name, age FROM users;
("users").Select("COALESCE(age,?)", 42).Rows()
//// SELECT COALESCE(age,'42') FROM users;

2.15. Order

Specify the order when retrieving records from the database, set the reorder totrueTo override the defined conditions

("age desc, name").Find(&users)
//// SELECT * FROM users ORDER BY age desc, name;
// Multiple orders
("age desc").Order("name").Find(&users)
//// SELECT * FROM users ORDER BY age desc, name;
// ReOrder
("age desc").Find(&users1).Order("age", true).Find(&users2)
//// SELECT * FROM users ORDER BY age desc; (users1)
//// SELECT * FROM users ORDER BY age; (users2)

2.16. Limit

Specify the number of records to retrieve

(3).Find(&users)
//// SELECT * FROM users LIMIT 3;
// Cancel limit condition with -1
(10).Find(&users1).Limit(-1).Find(&users2)
//// SELECT * FROM users LIMIT 10; (users1)
//// SELECT * FROM users; (users2)

2.17. Offset

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

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

eg:

type Info struct {
	Id      int    `json:"id"`
	Code    string `json:"code"`
	HwCode  string `json:"hw_code"`
	Name    string `json:"name"`
	Des     string `json:"des"`
	Created int64  `json:"created"`
	Updated int64  `json:"updated"`
	BrandId int    `json:"brand_id"`
}
func (Info) TableName() string {
	return "bike_color"
}
func (o object) QueryInfo2(id int) *Info {
	r := new(Info)
	("id = ?",id).Find(&amp;r)
	return r
}
// Listfunc (o object) QueryList2(page, PageSize int) (list []Info, total int) {
	((page-1)*PageSize).Limit(PageSize).Find(&amp;list)
	total = len(list)
	return
}

2.18. Count

Get the number of records in the model

("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)
(&User{}).Where("name = ?", "jinzhu").Count(&count)
//// SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)
("deleted_users").Count(&count)
//// SELECT count(*) FROM deleted_users;

2.19. Group & Having

rows, err := ("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
for () {
    ...
}
rows, err := ("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
for () {
    ...
}
type Result struct {
    Date  
    Total int64
}
("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)

2.20. Join

Specify connection conditions

rows, err := ("users").Select(", ").Joins("left join emails on emails.user_id = ").Rows()
for () {
    ...
}
("users").Select(", ").Joins("left join emails on emails.user_id = ").Scan(&amp;results)
// Multiple connections and parameters("JOIN emails ON emails.user_id =  AND  = ?", "jinzhu@").Joins("JOIN credit_cards ON credit_cards.user_id = ").Where("credit_cards.number = ?", "411111111111").Find(&amp;user)

2.21. Pluck

Take a single column in the model as a map query. If you want to query multiple columns, you can use itScan

var ages []int64
(&amp;users).Pluck("age", &amp;ages)
var names []string
(&amp;User{}).Pluck("name", &amp;names)
("deleted_users").Pluck("name", &amp;names)
// To return multiple columns, do this:("name, age").Find(&amp;users)

2.22. Scan

Scan the results into another structure.

type Result struct {
    Name string
    Age  int
}
var result Result
("users").Select("name, age").Where("name = ?", 3).Scan(&result)
// Raw SQL
("SELECT name, age FROM users WHERE name = ?", 3).Scan(&result)

2.23. Scopes

Pass the current database connection tofunc(*DB) *DB, can be used to dynamically add conditions

func AmountGreaterThan1000(db *) * {
    return ("amount &gt; ?", 1000)
}
func PaidWithCreditCard(db *) * {
    return ("pay_mode_sign = ?", "C")
}
func PaidWithCod(db *) * {
    return ("pay_mode_sign = ?", "C")
}
func OrderStatus(status []string) func (db *) * {
    return func (db *) * {
        return (AmountGreaterThan1000).Where("status in (?)", status)
    }
}
(AmountGreaterThan1000, PaidWithCreditCard).Find(&amp;orders)
// Find all credit card orders and amounts greater than 1000(AmountGreaterThan1000, PaidWithCod).Find(&amp;orders)
// Find all COD orders and amounts greater than 1000(OrderStatus([]string{"paid", "shipped"})).Find(&amp;orders)
// Find all paid and ship orders

2.24. Specify the table name

// Create a `deleted_users` table using User structure definition("deleted_users").CreateTable(&amp;User{})
var deleted_users []User
("deleted_users").Find(&amp;deleted_users)
//// SELECT * FROM deleted_users;
("deleted_users").Where("name = ?", "jinzhu").Delete()
//// DELETE FROM deleted_users WHERE name = 'jinzhu';

3. Preload

("Orders").Find(&users)
//// SELECT * FROM users;
//// SELECT * FROM orders WHERE user_id IN (1,2,3,4);
("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');
("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');
("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 to

3.1. Customize preload SQL

You can passfunc(db *) *(andScopesThe same way to use it) Customize preloaded SQL, for example:

("Orders", func(db *) * {
    return (" DESC")
}).Find(&amp;users)
//// SELECT * FROM users;
//// SELECT * FROM orders WHERE user_id IN (1,2,3,4) order by  DESC;

3.2. Nested preloading

("").Find(&amp;users)
("Orders", "state = ?", "paid").Preload("").Find(&amp;users)

4. Update

4.1. Update all fields

SaveWill include all fields when performing update SQL, even if it has not changed

(&user)
 = "jinzhu 2"
 = 100
(&user)
//// UPDATE users SET name='jinzhu 2', age=100, birthday='2016-01-01', updated_at = '2013-11-17 21:34:10' WHERE id=111;

4.2. Update the change field

If you want to update only the changed fields, you can useUpdate, Updates

// Update individual properties (if changed)(&amp;user).Update("name", "hello")
//// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;
//Update a single attribute using a combination condition(&amp;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;
//Update multiple properties with `map`, only those changed fields will be updated(&amp;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;
//Update multiple properties with `struct`, only these changed and non-blank fields will be updated(&amp;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 struct update, FORM will only update fields with non-null values// For the following updates, nothing will be updated to "", 0, false is the blank value of its type(&amp;user).Updates(User{Name: "", Age: 0, Actived: false})

4.3. Update the selected field

If you just want to update or ignore certain fields when updating, you can useSelect, Omit

(&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;
(&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;

4.4. Update the change field without Callbacks

The above update operation will execute the modelBeforeUpdate, AfterUpdateMethod, update itUpdatedAttimestamp, save itsAssociations, if you don't want to call them, you can useUpdateColumn, UpdateColumns

// Update a single property, similar to `Update`(&amp;user).UpdateColumn("name", "hello")
//// UPDATE users SET name='hello' WHERE id = 111;
// Update multiple attributes, similar to "Update"(&amp;user).UpdateColumns(User{Name: "hello", Age: 18})
//// UPDATE users SET name='hello', age=18 WHERE id = 111;

4.5. Batch Updates Batch Updates

CallbacksNot running during batch update

("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);
//Update with struct only works for non-zero values, or use map[string]interface{}(User{}).Updates(User{Name: "hello", Age: 18})
//// UPDATE users SET name='hello', age=18;
// Use `RowsAffected` to get the update record count(User{}).Updates(User{Name: "hello", Age: 18}).RowsAffected

4.6. Update using SQL expressions

(&product).Update("price", ("price * ? + ?", 2, 100))
//// UPDATE "products" SET "price" = price * '2' + '100', "updated_at" = '2013-11-17 21:34:10' WHERE "id" = '2';
(&product).Updates(map[string]interface{}{"price": ("price * ? + ?", 2, 100)})
//// UPDATE "products" SET "price" = price * '2' + '100', "updated_at" = '2013-11-17 21:34:10' WHERE "id" = '2';
(&product).UpdateColumn("quantity", ("quantity - ?", 1))
//// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = '2';
(&product).Where("quantity > 1").UpdateColumn("quantity", ("quantity - ?", 1))
//// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = '2' AND quantity > 1;

4.7. Change update value in Callbacks

If you want to useBeforeUpdate, BeforeSaveChange the updated value in the callback, you can use,For example

func (user *User) BeforeSave(scope *) (err error) {
  if pw, err := (, 0); err == nil {
    ("EncryptedPassword", pw)
  }
}

4.8. Additional update options

// Add extra SQL options to Update statement(&amp;amp;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);

5. Delete/Soft Delete

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

// Delete existing records(&amp;email)
//// DELETE from emails where id=10;
// Add extra SQL options to the Delete statement("gorm:delete_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Delete(&amp;email)
//// DELETE from emails where id=10 OPTION (OPTIMIZE FOR UNKNOWN);

5.1. Batch Delete

Delete all matching records

("email LIKE ?", "%jinzhu%").Delete(Email{})
//// DELETE from emails where email LIKE "%jinhu%";
(Email{}, "email LIKE ?", "%jinzhu%")
//// DELETE from emails where email LIKE "%jinhu%";

5.2. Soft Deletion

If the model hasDeletedAtField, it will automatically get soft delete function! Then invokingDeleteWhen not permanently deleted from the database, only fields areDeletedAtThe value of 'is set to the current time.

(&amp;user)
//// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;
// Batch Delete("age = ?", 20).Delete(&amp;User{})
//// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;
// Soft deleted records will be ignored during query("age = 20").Find(&amp;user)
//// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;
// Use Unscoped to find soft deleted records().Where("age = 20").Find(&amp;users)
//// SELECT * FROM users WHERE age = 20;
// Use Unscoped to permanently delete records().Delete(&amp;order)
//// DELETE FROM orders WHERE id=10;

6. Relationship

By default, when a record is created/updated, GORM will save its association, if the association has a primary key, GORM will call Update to save it, otherwise it will be created.

user := User{
    Name:            "jinzhu",
    BillingAddress:  Address{Address1: "Billing Address - Address 1"},
    ShippingAddress: Address{Address1: "Shipping Address - Address 1"},
    Emails:          []Email{
                                        {Email: "jinzhu@"},
                                        {Email: "jinzhu-2@example@"},
                   },
    Languages:       []Language{
                     {Name: "ZH"},
                     {Name: "EN"},
                   },
}
(&user)
//// BEGIN TRANSACTION;
//// INSERT INTO "addresses" (address1) VALUES ("Billing Address - Address 1");
//// INSERT INTO "addresses" (address1) VALUES ("Shipping Address - Address 1");
//// INSERT INTO "users" (name,billing_address_id,shipping_address_id) VALUES ("jinzhu", 1, 2);
//// INSERT INTO "emails" (user_id,email) VALUES (111, "jinzhu@");
//// INSERT INTO "emails" (user_id,email) VALUES (111, "jinzhu-2@");
//// INSERT INTO "languages" ("name") VALUES ('ZH');
//// INSERT INTO user_languages ("user_id","language_id") VALUES (111, 1);
//// INSERT INTO "languages" ("name") VALUES ('EN');
//// INSERT INTO user_languages ("user_id","language_id") VALUES (111, 2);
//// COMMIT;
(&user)

6.1. Skip save associations when creating/updating

By default, GORM also saves its associations, you can set itgorm:save_associationsforfalseSkip it.

("gorm:save_associations", false).Create(&user)
("gorm:save_associations", false).Save(&user)

6.2. Tag settings skip saving association

You can use tags to configure your struct so that associations are not saved when created/update

type User struct {
  
  Name      string
  CompanyID uint
  Company   Company `gorm:"save_associations:false"`
}
type Company struct {
  
  Name string
}

The above is the detailed content of golang gorm's preloading and soft-deletion data operation examples. For more information about golang gorm's preloading soft-deletion data, please pay attention to my other related articles!