What does go ORM do?

Time:2021-4-14

Fat Sir: next, I’ll give you a pie

Chief: come on!!
A note from ORM

What is ORM? Why ⽤ ORM?

What is ORM, or object relational mapping? Its purpose is to make a mapping between relational databases and objects,

In this way, we don’t need to deal with complex SQL statements when we operate the database. We just need to operate it as usual.

The main problems that ORM solves areMapping of object relations. Domain model and relational model are built on the basis of conceptual model.

  • Domain model is object oriented
  • The relation model is a ⾯ oriented relation

Generally, ⼀ persistent classes correspond to ⼀ tables, and each instance of the class corresponds to ⼀ records in the table,

Each property of the class corresponds to each field of the table.

ORM technology features:

  • Improve the development efficiency.

    Because ORM can map fields and attributes between entity objects and tables in database, we may not need a special and huge data access layer.

  • ORM provides the mapping to the database, and can get the data from the database like the operation object.

Disadvantages of ORM

The disadvantage of ORM is that it will sacrifice the execution efficiency of the program and fix the thinking mode.

From the perspective of system structure, the system adopting ORM is generally a multi-layer system. If there are more layers in the system, the efficiency will be reduced. ORM is a completely object-oriented approach, and object-oriented approach will also have an impact on performance.

2 ORM operation data

package main

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

//Userinfo user information
//ORM creates a table according to the structure by default. ORM uses the Linux naming method, that is, lowercase and underline, and adds s after the name
//User will be created_ Infos table
type UserInfo struct {
    gorm.Model
    ID     uint
    Name   string
    Gender string
    Hobby  string
}

//Truncate table name
//The database needs to be created in advance, such as mygorm
//Parsetime is whether the query result is parsed as time
//LOC is the time zone setting of MySQL
//Charset is the encoding mode
func main() {
    fmt.Println("try open mysql connection....")
    db, err := gorm.Open("mysql", "root:[email protected](localhost:3306)/mygorm?charset=utf8mb4&parseTime=True&loc=Local")
    if err != nil {
        panic(err)
    }
    fmt.Println("successful")
    defer db.Close()
    //Automatic migration
    //If the table does not exist, the table is created. If the table exists and the structure changes, the table structure is updated
    db.AutoMigrate(&UserInfo{})
    u1 := UserInfo{gorm.Model{}, 1, "xiaozhu", "man", "playing"}
    //Create a record
    result := db.Create(&u1)
    fmt.Println("result:", result.RowsAffected)
    //Inquiry
    var u = new(UserInfo)
    //Query a record
    db.First(u)
    fmt.Printf("First: %#v\n", u)
    //Query by criteria
    var uu UserInfo
    db.Find(&uu, "name=?", "xiaozhu")
    fmt.Printf("Find: %#v\n", uu)
    //Update
    db.Model(&uu).Update("hobby", "sing")
    //Delete. To delete a record here is not to delete the data in the data table, but to delete it_ At will update the deletion time
    db.Delete(&uu)
}
  • The database must be created before using Gorm
  • Gorm will automatically create data tables, and the table structure can change dynamically
  • The naming method of the table created by Gorm is the transformation of structure naming in the code. For example, if the structure is named as userinfo, the table will be named as user_ infos
  • It’s very easy for Gorm to modify the table structure
  • Gorm is a completely object-oriented idea

3 model definition

The model is a standard struct, which is implemented by go’s basic data typeScannerandValuerInterface and its pointer or alias

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
}

Custom model

Following the existing conventions of Gorm can reduce the amount of your configuration and code.

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 null
   Num          int     `gorm:"AUTO_ Increment "'// set num to auto increment type
   Address      string  `gorm:" index:addr "'// create an index named addr for the address field
   Ignoreme Int ` Gorm: '-' // ignore this field
}

Field label

When declaring a model, tag is optional. Gorm supports the following tags: tag name is not case sensitive, but it is recommended to usecamelCasestyle

Tag name explain
column Specify the DB column name
type For column data type, it is recommended to use a general type with good compatibility. For example, all databases support bool, int, uint, float, string, time and bytes, and can be used together with other tags, such as:not nullsize, autoIncrement… imagevarbinary(8)It is also supported to specify the database data type in this way. When using the specified database data type, it needs to be a complete database data type, such as:MEDIUMINT UNSIGNED not NULL AUTO_INSTREMENT
size Specify the column size, for example:size:256
primaryKey Specify column as primary key
unique Specifies that the column is unique
default Specifies the default value for the column
precision Specifies the precision of the column
scale Specifies the column size
not null Specifies that the column is not null
autoIncrement Specifies that the column is auto growing
autoIncrementIncrement Automatic step size to control the interval between successive records
embedded Nested fields
embeddedPrefix Prefix of column name of embedded field
autoCreateTime Track the current time on creation, forintField, which tracks the second level timestamp, and you can use thenano/milliTo track nanosecond and millisecond timestamps, for example:autoCreateTime:nano
autoUpdateTime Track current time when creating / updating, forintField, which tracks the second level timestamp, and you can use thenano/milliTo track nanosecond and millisecond timestamps, for example:autoUpdateTime:milli
index Create an index according to the parameters. If multiple fields use the same name, create a composite index to view theIndexesGet details
uniqueIndex AndindexSame, but creates a unique index
check Create check constraints, such ascheck:age > 13, viewconstraintGet details
<- Set the permission of field writing,<-:createOnly create<-:updateOnly update<-:falseNo write permission<-Create and update permissions
-> Set the field read permission,->:falseNo read access
Ignore the field,-No access
comment Add comments to fields during migration

Associated label

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 value and default table name
many2many Specifies the connection table name
joinForeignKey Specifies the foreign key column name of the join table, which will be mapped to the current table
joinReferences Specifies the foreign key column name of the join table, which will be mapped to the reference table
constraint Relationship constraints, such as:OnUpdateOnDelete

4. Agreement of primary key, table name and column name

Primary key

Gorm makes the field named ID as the primary key of the table by default.

type User struct { 

 ID string // the field named 'ID' will be the primary key of the table by default 

 Name string 

 }
//Make 'animalid' as the primary key 

type Animal struct { 

AnimalID int64 `gorm:"primary_key"` 

Name string 

Age int64 

}

Table name

By default, the table name is the plural of the structure name

Type user struct {} // the default table name is ` users` 

//Set the user's table name to ` profiles` 

func (User) TableName() string { 

    return "profiles" 

} 

func (u User) TableName() string { 

    if u.Role == "admin" { 

        return "admin_users" 

    } else { 

        return "users" 

    } 

} 

//The plural form of the default table name is not allowed. If it is set to true, the default table name of 'user' is' user '` 

db.SingularTable(true)

You can also specify the table name through table():

//Make the ⽤ user structure create a file named ` deleted '_ 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';

Gorm also holds the rule of changing the default table name:

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

Column name

Column names are divided by field names and underscores

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 make the ⽤ struct tag specify the 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 co lumn 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

If the model has a created at field, the value of this field will be the time when the record was first created.

db.Create (& user) // 'createdat' will be the current time 

//You can change the value of 'create at' by using the 'Update' method 

db.Model(&user).Update("CreatedAt", time.Now())

UpdatedAt

If the model has an updated at field, the value of this field will be the time of each update of the record.

db.Save (& user) // 'updatedat' will be the current time 

db.Model (& user). Update ("name", "Jinzhu") // 'updatedat' will be the current time

DeletedAt

If the model has a deletedat field, when you call delete to delete the record, the deletedat field will be set to the current time

Instead of deleting records directly from the database.

5 CURD

Create a record

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

result :=  db.Create (& user) // created by data pointer

user.ID              //Returns the primary key of the inserted data
result.Error         //Return error
result.RowsAffected  //Returns the number of inserted records

Batch insert

To insert a large number of records effectively, insert aslicePass on toCreatemethod. Passing the slice data to the Create method, Gorm will generate a single SQL statement to insert all the data, backfill the primary key value, and call the hook method.

var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
db.Create(&users)

for _, user := range users {
  user.ID // 1,2,3
}

useCreateInBatchesWhen creating, you can also specify the number to create, for example:

var users = []User{{name: "jinzhu_1"}, ...., {Name: "jinzhu_10000"}}

//The quantity is 100
db.CreateInBatches(users, 100)

Default value

You can use the labeldefaultDefine default values for fields, such as:

type User struct {
  ID   int64
  Name string `gorm:"default:galeone"`
  Age  int64  `gorm:"default:18"`
}

When inserting a record into a database, the default value isWill be used forFill value isZero valueFields for

query

Retrieve a single object

Gorm providesFirstTakeLastMethod to retrieve a single object from the database. When querying the database, it addsLIMIT 1Condition, and no record is found, it returnsErrRecordNotFounderror

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

//Gets a record without a sort field specified
db.Take(&user)
// SELECT * FROM users LIMIT 1;

//Get the last record (primary key descending)
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 errrecordnotfound error
errors.Is(result.Error, gorm.ErrRecordNotFound)

If you want to avoid itErrRecordNotFoundError, you can useFindFor exampledb.Limit(1).Find(&user)FindMethod can accept the data of struct and slice.

First, LastMethod will sort by primary key to find the first / last record, which is valid only when struct is used to query or model value is provided. If the primary key is not defined in the current model, it will sort by the first field, for example:

var user User
var users []User

//Yes
db.First(&user)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1

//Yes
result := map[string]interface{}{}
db.Model(&User{}).First(&result)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1

//No way
result := map[string]interface{}{}
db.Table("users").First(&result)

//But it can be used with take
result := map[string]interface{}{}
db.Table("users").Take(&result)

//Sort by first field
type Language struct {
  Code string
  Name string
}
db.First(&Language{})
// SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1

Search with primary key

If the primary key is a numeric type, you can also use theInline conditionPass in the primary key to retrieve the object. If the primary key is of string type, be careful to avoid SQL injection. Check thesecurityGet 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 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";

Retrieve 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

condition

String condition
//Get the first matching record
db.Where("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;

//Get all matching 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 & Map condition

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

be carefulWhen using structure as a conditional query, Gorm only queries non-zero value fields. This means that if your field value is0''falseOr something elseZero value, which will not be used to build query criteria, for example:

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

You can use map to build query conditions, which will use all the values, such as:

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

Or viewSpecify structure query fieldsGet details

Specify structure query fields

When a structure is used for query, you can use its field name or its dbname column name as a parameter to specify the query field. 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;

Inline condition

Usage andWheresimilar

// SELECT * FROM users WHERE id = 23;
//Get the record according to the primary key, if it is a non integer primary key
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;

Not condition

Construct not condition, usage andWheresimilar

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

Or condition

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

You can also view theGrouping conditions, which is used to write complex SQL

Select specific fields

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;

You can have a lookIntelligent 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 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 & Offset

LimitSpecifies the maximum number of records to getOffsetSpecifies 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 by - 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 offset condition by - 1
db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
// SELECT * FROM users OFFSET 10; (users1)
// SELECT * FROM users; (users2)

seePaginationLearn how to write a pager

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)

Distinct

Select different values from the model

db.Distinct("name", "age").Order("name, age desc").Find(&results)

DistinctYou can also cooperatePluck, Countuse

Joins

Specify the joins condition

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)

//Multi table join 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)

Jones preload

You can useJoinsImplement a single SQL preload associated record, 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`;

Scan

Scan results to struct, usage andFindsimilar

type Result struct {
  Name string
  Age  int
}

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

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

Handling error

Gorm’s error handling is different from the common go code, because Gorm provides a chained API.

If any errors are encountered, Gorm sets the*gorm.DBOfErrorField, you need to check it like this:

if err := db.Where("name = ?", "jinzhu").First(&user).Error; err != nil {
  //Processing error
}

perhaps

if result := db.Where("name = ?", "jinzhu").First(&user); result.Error != nil {
  //Processing error
}

ErrRecordNotFound

WhenFirstLastTakeMethod, Gorm returnsErrRecordNotFoundWrong. If multiple errors occur, you canerrors.IsJudge whether the error isErrRecordNotFound, for example:

//Check if the error is recordnotfound
err := db.First(&user, 100).Error
errors.Is(err, gorm.ErrRecordNotFound)

Technology is open, and our mentality should be even more open. Embrace change, live in the sun, and strive to move forward.

More advanced usage and details can be foundChinese grom website

Author: Little Devil boy Nezha

This work adoptsCC agreementReprint must indicate the author and the link of this article