GoLong’s learning path (16) Basic tool GORM (operate database Mysql) (create database, insert data Insert and query data select))

GORM is similar to Mybatis in Java, which is great news for developers and supports mainstream databases.

Article directory

• Install
• • method 1
  • Method 2
• Link to Mysql database
• • Link existing database link
• Create table
• create
• • Create a record with specified fields
  • Batch insert
  • Create hook
  • Create based on Map
• Inquire
• • Search based on primary key
  • • Primary key is number
    • Primary key is a string
  • Query all objects
  • • Conditional query
    • • Conditional query using `Struct` & `Map` conditions
    • Specify structure (table) query fields
    • NOT condition
    • Or condition
  • Select a specific field (Select)
  • Order
  • Limit & Offset
  • Group By & Having
  • Distinct
  • Joins
  • Joins preloading
  • • Derived table of joins
  • Scan

Installation

Method 1

import _ "gorm.io/gorm"

Method 2

Operation under DOS window

go get -u gorm.io/gorm

Link to Mysql database

import (
  "gorm.io/driver/mysql"
  "gorm.io/gorm"
)

func main() {<!-- -->
  // Refer to https://github.com/go-sql-driver/mysql#dsn-data-source-name for details
  dsn := "user:pass@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4 & amp;parseTime=True & amp;loc=Local"
  db, err := gorm.Open(mysql.Open(dsn), & amp;gorm.Config{<!-- -->})
}
}

At this time, the configuration is the default configuration structure. In order to cope with differences in database versions, the official documentation provides another way to view Lianjia data.

db, err := gorm.Open(mysql.New(mysql.Config{<!-- -->
  DSN: "gorm:gorm@tcp(127.0.0.1:3306)/gorm?charset=utf8 & amp;parseTime=True & amp;loc=Local", // DSN data source name
  DefaultStringSize: 256, //Default length of string type field
  DisableDatetimePrecision: true, // Disable datetime precision, which is not supported by databases before MySQL 5.6
  DontSupportRenameIndex: true, // When renaming the index, delete and create a new one. Databases before MySQL 5.7 and MariaDB do not support renaming indexes.
  DontSupportRenameColumn: true, // Use `change` to rename columns. Databases before MySQL 8 and MariaDB do not support renaming columns.
  SkipInitializeWithVersion: false, // Automatically configure according to the current MySQL version
}), & amp;gorm.Config{<!-- -->})

Link to existing database link

GORM allows initializing *gorm.DB via an existing database connection

import (
  "database/sql"
  "gorm.io/driver/mysql"
  "gorm.io/gorm"
)

sqlDB, err := sql.Open("mysql", "mydb_dsn")
gormDB, err := gorm.Open(mysql.New(mysql.Config{<!-- -->
  Conn: sqlDB,
}), & amp;gorm.Config{<!-- -->})

Create table

This example is not related to the following examples.

import (
"gorm.io/gorm"
"time"
)

type User struct {<!-- -->
gorm.Model
Username string
Password string
Date time.Time
}
//Here, a table name is returned.
func (*User) TableName() string {<!-- -->
return "user_info"
}

func main() {<!-- -->
dsn := "root:123456@tcp(127.0.0.1:3306)/dome?charset=utf8mb4 & amp;parseTime=True & amp;loc=Local"
db, err := gorm.Open(mysql.Open(dsn), & amp;gorm.Config{<!-- -->})
if err != nil {<!-- -->
fmt.Println("Link failed")
} else {<!-- -->
fmt.Println("Database created successfully")
}
//This is how to create a table
db.AutoMigrate( & amp;entiy.User{<!-- -->})
}

Create

Create corresponds to insert.

Single record insertion

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

result := db.Create( & amp;user) // Create through data pointer

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

Multiple record insertion

users := []*User{<!-- -->
    User{<!-- -->Name: "Jinzhu", Age: 18, Birthday: time.Now()},
    User{<!-- -->Name: "Jackson", Age: 19, Birthday: time.Now()},
}

result := db.Create(users) // Pass slice to insert multiple rows of data

result.Error // return error
result.RowsAffected // Returns the number of inserted records

Create a record with specified fields

Create records and assign specified fields

db.Select("Name", "Age", "CreatedAt").Create( & amp;user)
// INSERT INTO `users` (`name`,`age`,`created_at`) VALUES ("jinzhu", 18, "2020-07-04 11:05:21.775")

Create record and ignore ‘Qmit’ field

db.Omit("Name", "Age", "CreatedAt").Create( & amp;user)
// INSERT INTO `users` (`birthday`,`updated_at`) VALUES ("2020-01-01 00:00:00.000", "2020-07-04 11:05:21.775")

Batch insert

To insert a large number of records efficiently, pass a slice to the Create method.

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

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

But when encountering a lot of data, such batch insertion consumes a lot of resources. Therefore, the official document can set the number of inserts at one time (CreateInBatches)

var users = []User{<!-- -->{<!-- -->Name: "jinzhu_1"}, ...., {<!-- -->Name: \ "jinzhu_10000"}}

// batch size 100
db.CreateInBatches(users, 100)

Of course this is a case of logical processing. But if we want them to perform batch processing on the entire project, it is not appropriate. So we can choose to set it when initializing the database

db, err := gorm.Open(sqlite.Open("gorm.db"), & amp;gorm.Config{<!-- -->
  CreateBatchSize: 1000,
})

db := db.Session( & amp;gorm.Session{<!-- -->CreateBatchSize: 1000})

users = [5000]User{<!-- -->{<!-- -->Name: "jinzhu", Pets: []Pet{<!-- -->pet1, pet2, pet3}} ...}

db.Create(&users)
// INSERT INTO users xxx (5 batches)
// INSERT INTO pets xxx (15 batches)

Create hook

What is a hook? In fact, it is a code implemented before and after the main program called a hook.

GORM allows users to define hooks to implement pre-save BeforeSave, pre-create BeforeCreate, post-save AfterSave, and post-create AfterCreate .

func (u *User) BeforeCreate(tx *gorm.DB) (err error) {<!-- -->
  u.UUID = uuid.New()

    if u.Role == "admin" {<!-- -->
        return errors.New("invalid role")
    }
    return
}

Create from Map

GORM supports creation from map[string]interface{} and []map[string]interface{}

db.Model( & amp;User{<!-- -->}).Create(map[string]interface{<!-- -->}{<!-- -->
  "Name": "jinzhu", "Age": 18,
})

// batch insert from `[]map[string]interface{}{}`
db.Model( & amp;User{<!-- -->}).Create([]map[string]interface{<!-- -->}{<!-- -->
  {<!-- -->"Name": "jinzhu_1", "Age": 18},
  {<!-- -->"Name": "jinzhu_2", "Age": 20},
})

When creating a from map, hooks are not called, associations are not saved, and primary key values are not backfilled.

Query

What about this query? Unlike writing query statements ourselves, GORM encapsulates some operations. So we need to call.

GORM provides First, Take, Last methods to retrieve individual objects from the database. When querying the database it adds a LIMIT 1 condition and when no record is found, it returns an 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( & amp;user)
result.RowsAffected // Returns the number of records found
result.Error // returns error or nil

// Check for ErrRecordNotFound errors
errors.Is(result.Error, gorm.ErrRecordNotFound)

If you want to avoid ErrRecordNotFound errors, you can use Find, such as db.Limit(1).Find( & amp;user), Find method can accept data from struct and slice.

Using Find without limit for a single object, db.Find( & amp;user) will query the entire table and return only the first object, which is low performance and uncertain.

The First and Last methods will find the first record and the last record sorted by primary key.

This method only works if the target struct is a pointer or the model is specified via db.Model().

Search based on primary key

If the primary key is of numeric type, you can use inline conditions to retrieve the object. When working with strings, extra care is required to avoid SQL injection

The primary key is a number

db.First( & amp;user, 10)
// SELECT * FROM users WHERE id = 10;

db.First( &user, "10")
// SELECT * FROM users WHERE id = 10;

db.Find( & amp;users, []int{<!-- -->1,2,3})
// SELECT * FROM users WHERE id IN (1,2,3);

The primary key is a string

db.First( & amp;user, "id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a")
// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";

When the target object has a primary key value, the primary key is used to build the query conditions.

var user = User{<!-- -->ID: 10}
db.First(&user)
// SELECT * FROM users WHERE id = 10;

var resultUser
db.Model(User{<!-- -->ID: 10}).First( & amp;result)
// SELECT * FROM users WHERE id = 10;

If you use gorm’s specific field type (e.g. gorm.DeletedAt), it will run a different query to retrieve the object. (Recommended, this is very useful)

type User struct {<!-- -->
  ID string `gorm:"primarykey;size:16"`
  Name string `gorm:"size:24"`
  DeletedAt gorm.DeletedAt `gorm:"index"`
}

var user = User{<!-- -->ID: 15}
db.First(&user)
// SELECT * FROM `users` WHERE `users`.`id` = '15' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1

Query all objects

//Get all records
result := db.Find( & amp;users)
// SELECT * FROM users;

result.RowsAffected // returns found records count, equals `len(users)`
result.Error // returns error

Conditional query

where: Query the first matching

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

where: Query all matching

//Get all matched records
db.Where("name <> ?", "jinzhu").Find( & amp;users)
// SELECT * FROM users WHERE name <> 'jinzhu';

IN: Query all the
Note: You must pay attention to inserting a slice

// IN
db.Where("name IN ?", []string{<!-- -->"jinzhu", "jinzhu 2"}).Find( & amp;users)
// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');

LIKE: fuzzy query

// LIKE
db.Where("name LIKE ?", "%jin%").Find( & amp;users)
// SELECT * FROM users WHERE name LIKE '%jin%';

AND

// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find( & amp;users)
// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

Time

// Time
db.Where("updated_at > ?", lastWeek).Find( & amp;users)
// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

BETWEEN

// BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find( & amp;users)
// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

Note: If the object has a primary key set, the query conditions will not overwrite the value of the primary key. , instead use And to join the condition

var user = User{<!-- -->ID: 10}
db.Where("id = ?", 20).First( & amp;user)
// SELECT * FROM users WHERE id = 10 and id = 20 ORDER BY id ASC LIMIT 1

This query will give record not found error. So when making this query statement, you need to set the primary key such as id to nil (null)

Conditional query using Struct & amp; Map conditions

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

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

// Slice of primary keys
db.Where([]int64{<!-- -->20, 21, 22}).Find( & amp;users)
// SELECT * FROM users WHERE id IN (20, 21, 22);

Note that there is a problem when using struct queries: When using struct queries, GORM will only query non-zero fields, which means that if the value of your field is 0, “”, false or other zero values, it will Will not be used to build query conditions. But fields with values can be queried.
Example:

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

This problem can be solved through map query:

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

Specify structure (table) query fields

To put it simply, in some cases, we need the values of certain fields in a specific table. At this time we can search through the structure.

db.Where( & amp;User{<!-- -->Name: "jinzhu"}, "name", "Age").Find( & amp;users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;

db.Where( & amp;User{<!-- -->Name: "jinzhu"}, "Age").Find( & amp;users)
// SELECT * FROM users WHERE age = 0;

NOT condition

Similar to where

db.Not("name = ?", "jinzhu").First( & amp;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( & amp;users)
// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");

// Struct
db.Not(User{<!-- -->Name: "jinzhu", Age: 18}).First( & amp;user)
// SELECT * FROM users WHERE name <> "jinzhu" AND age <> 18 ORDER BY id LIMIT 1;

// Not In slice of primary keys
db.Not([]int64{<!-- -->1,2,3}).First( & amp;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( & amp;users)
// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';

// Struct
db.Where("name = 'jinzhu'").Or(User{<!-- -->Name: "jinzhu 2", Age: 18}).Find( & amp;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( & amp;users)
// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);

Select specific fields (Select)

We only want some specific field values we need to appear in sql. At this point we need to make settings.

db.Select("name", "age").Find( & amp;users)
// SELECT name, age FROM users;

db.Select([]string{<!-- -->"name", "age"}).Find( & amp;users)
// SELECT name, age FROM users;

db.Table("users").Select("COALESCE(age,?)", 42).Rows()
// SELECT COALESCE(age,'42') FROM users;

Order

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

//Multiple orders
db.Order("age desc").Order("name").Find( & amp;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( & amp;User{<!-- -->})
// SELECT * FROM users ORDER BY FIELD(id,1,2,3)

Limit & amp; Offset

• Limit specifies the maximum number of records to retrieve
• Offset specifies the number of records to skip before starting to return records

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

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

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

db.Limit(10).Offset(5).Find( & amp;users)
// SELECT * FROM users OFFSET 5 LIMIT 10;

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

Group By & Having

Model: Process the model (struck)
Table: table processing

type result struct {<!-- -->
  Date time.Time
  Total int
}

db.Model( & amp;User{<!-- -->}).Select("name, sum(age) as total").Where("name LIKE ?", "group%\ ").Group("name").First( & amp;result)
// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name` LIMIT 1


db.Model( & amp;User{<!-- -->}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find( & amp;result)
// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows ()
defer rows.Close()
for rows.Next() {<!-- -->
  ...
}

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having ("sum(amount) > ?", 100).Rows()
defer rows.Close()
for rows.Next() {<!-- -->
  ...
}

type Result struct {<!-- -->
  Date time.Time
  Total int64
}
db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum( amount) > ?", 100).Scan( & amp;results)

Distinct

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

Joins

Specify connection conditions

type result struct {<!-- -->
  Name string
  Email string
}

db.Model( & amp;User{<!-- -->}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id\ ").Scan( & amp;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( & amp; results)

Multiple connections with parameters

// multiple joins with parameter
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( & amp;user)

Joins preloading

db.Joins("Company").Find( & amp;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`;

// inner join
db.InnerJoins("Company").Find( & amp;users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` INNER JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;

Join conditional link

db.Joins("Company", db.Where( & amp;Company{<!-- -->Alive: true})).Find( & amp;users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;

Derived table of joins

type User struct {<!-- -->
    ID int
    Age int
}

type Order struct {<!-- -->
    UserId int
    FinishedAt *time.Time
}

query := db.Table("order").Select("MAX(order.finished_at) as latest").Joins("left join user user on order.user_id = user.id"). Where("user.age > ?", 18).Group("order.user_id")

db.Model( & amp;Order{<!-- -->}).Joins("join (?) q on order.finished_at = q.latest", query).Scan( & amp;results)
// SELECT `order`.`user_id`,`order`.`finished_at` FROM `order` join (SELECT MAX(order.finished_at) as latest FROM `order` left join user user on order.user_id = user.id WHERE user.age > 18 GROUP BY `order`.`user_id`) q on order.finished_at = q.latest

Scan

Scan the results into the structure, this method is not much different from Find

type Result struct {<!-- -->
  Name string
  Age int
}

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

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