Fat Sir: Next, here’s a pie for you
Commander: Come on! A tidy note from ORM…
1 What is ORM? Why ORM?
ORM (object-relationl Mapping) is used to map an Object to a relational database.
In this way, we in the specific operation of the database, there is no need to deal with complex SQL statements, as long as the usual operation object operation it can be.
The main problem ORM addresses is the mapping of object relationships. The domain model and the relational model are based on the conceptual model respectively.
- The domain model is object oriented
- Relational models are relationship-oriented
In general, a persistent class corresponds to a table, and each instance of the class corresponds to one record in the table,
Each property of the class corresponds to each field of the table.
ORM technology features:
-
Improved development efficiency.
Since ORM can automatically map the fields and attributes of an Entity object to a Table in a database, we may actually no longer need a dedicated, large data access layer.
-
ORM provides a mapping to the database, no SQL coding directly, and the ability to retrieve data from the database as if it were an object.
The disadvantage of the ORM
The disadvantage of ORM is that it will sacrifice the execution efficiency of the program and will fix the thinking mode.
From the perspective of system structure, the system using ORM is generally a multi-layer system, and the efficiency will be reduced if the system has more layers. ORM is a fully object-oriented approach, and object-oriented approaches have some impact on performance.
2 ORM operation data
package main
import (
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
)
// UserInfo User information
// The orM creates the table from the structure by default. The ORM uses the Linux naming method, which is lowercase and underlined, and follows the name with an S
// Create the user_infOS table
type UserInfo struct {
gorm.Model
ID uint
Name string
Gender string
Hobby string
}
// TRUNCate TABLE table name
// The database needs to be created in advance such as mygorm
// parseTime Yes Indicates whether the query result is automatically parsed to time
// loc is the time zone setting for MySQL
// Charset is the encoding mode
func main(a) {
fmt.Println("try open mysql connection....")
db, err := gorm.Open("mysql"."root:123456@(localhost:3306)/mygorm? charset=utf8mb4&parseTime=True&loc=Local")
iferr ! =nil {
panic(err)
}
fmt.Println("successful")
defer db.Close()
// Automatic migration
If the table does not exist, create the table. If the table exists and the structure changes, update the table structure
db.AutoMigrate(&UserInfo{})
u1 := UserInfo{gorm.Model{}, 1."xiaozhu"."man"."playing"}
// Create a record
result := db.Create(&u1)
fmt.Println("result:", result.RowsAffected)
/ / query
var u = new(UserInfo)
// Query a record
db.First(u)
fmt.Printf("First: %#v\n", u)
// Query by condition
var uu UserInfo
db.Find(&uu, "name=?"."xiaozhu")
fmt.Printf("Find: %#v\n", uu)
/ / update
db.Model(&uu).Update("hobby"."sing")
// Delete, delete the record, does not delete the data in the table, but deleted_at will update the deletion time
db.Delete(&uu)
}
Copy the code- To use GORM, you must first create the database
- Gorm automatically creates data tables, and table structures can change dynamically
- Gorm creates tables that are named as transformations of structures in code. For example, if the structure is named UserInfo, the table is named user_infos
- Gorm makes it very easy to modify table structures
- Gorm is a fully object-oriented idea
3 Model Definition
A model is a standard struct, consisting of the basic data types of Go, custom types that implement the Scanner and Valuer interfaces, and their Pointers or aliases
Such as:
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
}
Copy the codeCustom model
Following existing conventions in GORM can reduce your configuration and code load.
type User struct {
gorm.Model / / embedded
Name string
Age sql.NullInt64
Birthday *time.Time
Email string `gorm:"type:varchar(100); uniqueIndex"`
Role string `gorm:"size:255"` // Set the field size to 255
MemberNumber *string `gorm:"unique; not null"` // Set the member number to be unique and not empty
Num int `gorm:"AUTO_INCREMENT"` // Set num to autoincrement
Address string `gorm:"index:addr"` // create index addr for address field
IgnoreMe int `gorm:"-"` // Ignore this field
}
Copy the codeField label
Tags are optional when declaring models. GORM supports the following tags: Tag names are case-insensitive, but camelCase style is recommended
| Tag name | instructions |
|---|---|
| column | Specify the DB column name |
| type | Column data type. The compatible general type is recommended. For example, all databases support bool, int, Uint, float, string, time, bytes and can be used together with other tags.not null,size.autoIncrement… likevarbinary(8)Specifying database data types in this way is also supported. When using the specified database data type, it needs to be the full database data type, such as:MEDIUMINT UNSIGNED not NULL AUTO_INSTREMENT |
| size | Specify the column size, for example:size:256 |
| primaryKey | Specifies the column primary key |
| unique | Specifying a unique column |
| default | Specifies the default value for the column |
| precision | Specifies the precision of the column |
| scale | Specify column size |
| not null | Specifies the column NOT NULL |
| autoIncrement | Specifies an automatic growth column |
| autoIncrementIncrement | Automatic step size that controls the interval between consecutive records |
| embedded | Nested fields |
| embeddedPrefix | The column name prefix of the embedded field |
| autoCreateTime | Tracing the current time at creation time, forintField, which tracks the second-level timestamp that you can usenano/milliTo track nanosecond and millisecond timestamps, for example:autoCreateTime:nano |
| autoUpdateTime | Track the current time when creating/updating, forintField, which tracks the second-level timestamp that you can usenano/milliTo track nanosecond and millisecond timestamps, for example:autoUpdateTime:milli |
| index | Create an index based on the parameter. Create a compound index if multiple fields use the same nameThe indexFor more details |
| uniqueIndex | withindexSame, but create a unique index |
| check | Create check constraints, for examplecheck:age > 13To see theThe constraintFor more details |
| <- | Set the field write permission,<-:createOnly the creation,<-:updateUpdate only,<-:falseNo write permission,<-Create and update permissions |
| -> | Set the field read permission,->:falseNo read permission |
| – | Ignore this field,-No read and write permission |
| comment | Annotations are added to fields at migration time |
Associated label
| The label | describe |
|---|---|
| foreignKey | Specifies the column of the current model as the foreign key of the join table |
| references | Specifies the column name of the reference table that will be mapped to the join table foreign key |
| polymorphic | Specify polymorphic types, such as model names |
| polymorphicValue | Specify polymorphic values, default table names |
| many2many | Specifies the name of the connection table |
| joinForeignKey | Specifies the foreign key column name of the join table that will be mapped to the current table |
| joinReferences | Specifies the foreign key column name of the join table that will be mapped to the reference table |
| constraint | Relational constraints such as:OnUpdate,OnDelete |
4 Primary key, table name, column name convention
Primary Key
By default, GORM uses a field named ID as the primary key of the table.
type User struct {
ID string // A field named 'ID' will default as the primary key of the table
Name string
}
Copy the code// Use 'AnimalID' as the primary key
type Animal struct {
AnimalID int64 `gorm:"primary_key"`
Name string
Age int64
}
Copy the codeTable Name
The default table name is the plural of the structure name, for example:
type User struct {} // The default table name is' users'
// Set the table name of User to 'profiles'
func (User) TableName(a) string {
return "profiles"
}
func (u User) TableName(a) string {
if u.Role == "admin" {
return "admin_users"
} else {
return "users"}}// Disable the plural form of the default table name. If set to true, the default table name of 'User' is' User '
db.SingularTable(true)
Copy the codeYou can also specify the Table name by using Table() :
// Create a table named 'deleted_users' using the User structure
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';
Copy the codeGORM also supports changing the default table name rule:
gorm.DefaultTableNameHandler = func (db *gorm.DB, defaultTableName string) string {
return "prefix_" + defaultTableName;
}
Copy the codeColumn Name
Column names are generated by underlining the field names
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`
}
Copy the codeColumn names can be specified using the structure tag:
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 co lumn name to `day_of_the_beast`
Age int64 `gorm:"column:age_of_the_beast"` // set column name to `age_of_the_beast`
}
Copy the codeTimestamp tracking
CreatedAt
If the model has a CreatedAt field, the value of that field will be when the record was first created.
db.Create(&user) // 'CreatedAt' will be the current time
The 'Update' method can be used to change the value of 'CreateAt'
db.Model(&user).Update("CreatedAt", time.Now())
Copy the codeUpdatedAt
If the model has an UpdatedAt field, the value of that field will be the time each record is updated.
db.Save(&user) // 'UpdatedAt' will be the current time
db.Model(&user).Update("name"."jinzhu") // 'UpdatedAt' will be the current time
Copy the codeDeletedAt
If the model has a DeletedAt field, calling Delete to Delete the record sets the DeletedAt field to the current time, and
Records are not deleted directly from the database.
5 CURD
Create a record
user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}
result := db.Create(&user) // Create a pointer to the data
user.ID // Returns the primary key to insert data
result.Error / / returns the error
result.RowsAffected // Return the number of inserted records
Copy the codeBulk insert
To insert a large number of records efficiently, pass a slice to the Create method. Pass the sliced data to the Create method, and GORM generates a single SQL statement to insert all the data and backfill the primary key values, and the hook method is called.
var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
db.Create(&users)
for _, user := range users {
user.ID / / 1, 2, 3
}
Copy the codeWhen creating a batch using CreateInBatches, you can also specify the number of batches to be created, for example:
var users = []User{{name: "jinzhu_1"},... , {Name:"jinzhu_10000"}}
// The quantity is 100
db.CreateInBatches(users, 100)
Copy the codeThe default value
You can define default values for fields with the label default, such as:
type User struct {
ID int64
Name string `gorm:"default:galeone"`
Age int64 `gorm:"default:18"`
}
Copy the codeWhen a record is inserted into the database, the default value is used to fill fields with a value of zero
The query
Retrieving a single object
GORM provides First, Take, and Last methods to retrieve individual objects from the database. When it queries the database and adds the LIMIT 1 condition, and no record is found, it returns the ErrRecordNotFound error
// Get the first record (primary key ascending order)
db.First(&user)
// SELECT * FROM users ORDER BY id LIMIT 1;
// Get a record without specifying the sort field
db.Take(&user)
// SELECT * FROM users LIMIT 1;
// Get the last record (primary key descending order)
db.Last(&user)
// SELECT * FROM users ORDER BY id DESC LIMIT 1;
result := db.First(&user)
result.RowsAffected // Returns the number of records found
result.Error // returns error
// Check the error ErrRecordNotFound
errors.Is(result.Error, gorm.ErrRecordNotFound)
Copy the codeIf you want to avoid ErrRecordNotFound errors, you can use Find, such as db.limit (1).find (&user), which accepts struct and slice data.
The First, Last methods sort the First/Last record by primary key, which is valid only if a struct is queried or provided with a model value. If the current model does not define a primary key, the First field is sorted, for example:
var user User
var users []User
/ / can
db.First(&user)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
/ / can
result := map[string]interface{}{}
db.Model(&User{}).First(&result)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
/ / no
result := map[string]interface{}{}
db.Table("users").First(&result)
// But can be used with Take
result := map[string]interface{}{}
db.Table("users").Take(&result)
// Sort by the first field
type Language struct {
Code string
Name string
}
db.First(&Language{})
// SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1
Copy the codeRetrieve with the primary key
If the primary key is of a numeric type, objects can also be retrieved by passing the primary key in an inline condition. If the primary key is a String, be careful to avoid SQL injection and see secure retrieval 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);
Copy the codeIf the primary key is a string like uuid, you need to write:
db.First(&user, "id = ?"."1b74413f-f3b8-409f-ac47-e8c062e3472a")
// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";
Copy the codeRetrieve all objects
// Get all records
result := db.Find(&users)
// SELECT * FROM users;
result.RowsAffected // Returns the number of records found, equivalent to 'len(users)'
result.Error // returns error
Copy the codeconditions
Condition of the String
// Get the first matching 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 ?"And []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';
Copy the codeStruct & Map conditions
// 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;
// Primary key slice condition
db.Where([]int64{20.21.22}).Find(&users)
// SELECT * FROM users WHERE id IN (20, 21, 22);
Copy the codeNote that GORM only queries non-zero value fields when using structures as conditional queries. This means that if you have a field value of 0, “”, false, or some other zero value, that field will not be used to build a query condition, such as:
db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu";
Copy the codeYou can use a map to build query criteria that use all values, such as:
db.Where(map[string]interface{} {"Name": "jinzhu"."Age": 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
Copy the codeOr view the specified structure query field for details
Specifies the structure query field
When querying with a structure, you can specify the fields to be queried using its field name or its dbNAME column name as arguments, 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;
Copy the codeInline condition
The usage is similar to Where
// SELECT * FROM users WHERE id = 23;
// Get records based on the primary key, if non-integer
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;
Copy the codeThe Not condition
Build the NOT condition, which is used similarly 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;
// Records not in the primary key slice
db.Not([]int64{1.2.3}).First(&user)
// SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;
Copy the codeOr conditions
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);
Copy the codeYou can also see the grouping criteria in advanced queries, which are used to write complex SQL
Select a specific field
Select the fields you want to retrieve from the database; by default, all fields are selected
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;
Copy the codeYou can also take a look at the smart selection field
Order
Specifies how records are sorted when retrieved from the database
db.Order("age desc, name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;
/ / multiple order
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)
Copy the codeLimit & Offset
Offset specifies the number of records to skip before starting to return records
db.Limit(3).Find(&users)
// SELECT * FROM users LIMIT 3;
// Eliminate the 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;
// Eliminate the Offset condition with -1
db.Offset(10).Find(&users1).Offset(- 1).Find(&users2)
// SELECT * FROM users OFFSET 10; (users1)
// SELECT * FROM users; (users2)
Copy the codeHow to write a Pagination machine
Group & Having
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`
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()
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)
Copy the codeDistinct
Select different values from the model
db.Distinct("name"."age").Order("name, age desc").Find(&results)
Copy the codeDistinct can also be used with Pluck, Count
Joins
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)
// Join multiple tables with parameters
db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?"."[email protected]").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?"."411111111111").Find(&user)
Copy the codeJoins preload
You can implement a single SQL pre-loaded associated record using Joins, 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`;
Copy the codeScan
Scan results to struct, similar to Find
type Result struct {
Name string
Age int
}
var result Result
db.Table("users").Select("name"."age").Where("name = ?"."Antonio").Scan(&result)
/ / the native SQL
db.Raw("SELECT name, age FROM users WHERE name = ?"."Antonio").Scan(&result)
Copy the codeHandling errors
GORM error handling is different from common Go code because GORM provides a chained API.
If any errors are encountered, GORM sets the Error field of * gorm.db, which you need to check like this:
if err := db.Where("name = ?"."jinzhu").First(&user).Error; err ! =nil {
// Handle error...
}
Copy the codeor
if result := db.Where("name = ?"."jinzhu").First(&user); result.Error ! =nil {
// Handle error...
}
Copy the codeErrRecordNotFound
GORM returns the ErrRecordNotFound error when the First, Last, or Take methods cannot find the record. If multiple errors occur, you can use errors.Is to determine if the error Is ErrRecordNotFound, for example:
// Check if the error is RecordNotFound
err := db.First(&user, 100).Error
errors.Is(err, gorm.ErrRecordNotFound)
Copy the codeTechnology is open, our mentality, should be more open. Embrace change, live in the sun, and strive to move forward.
More advanced usage and details can be found on the Chinese GROM website
Author: Nezha