Gorm operation MySQL

Catalog

One 、gorm Introduce

Two 、gorm install

3、 ... and 、gorm Model definition

1、ORM Introduce

2、gorm Model definition

3、gorm Model label

4、 Define table name

5、gorm.Model

6、 Auto update time

Four 、gorm Connect to database

1、 To configure DSN (Data Source Name)

2、 Use gorm.Open Connect to database

3、gorm Debug mode

4、gorm Connection pool

---

One 、gorm Introduce

GORM yes Golang The popular database at present ORM Operation Library , Friendly to developers , It is very convenient and simple to use , The main use is to put struct Type and database table records , There is no need to write directly when operating the database Sql Code , Here we mainly introduce MySQL database .

GORM library github Address : https://github.com/go-gorm/gorm

Two 、gorm install

operation MySQL Two packages need to be installed ：

• MySQL Drive pack
• GORM package Use go get Command install dependency package

// install MySQL drive 
go get -u gorm.io/driver/mysql
// install gorm package 
go get -u gorm.io/gorm

Import package

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

3、 ... and 、gorm Model definition

1、ORM Introduce

ORM The framework operation database needs to define the model in advance , A model can be understood as a data model , As a medium for operating the database

Medium .

for example ：

• The data read from the database will be saved to the predefined model object first , Then we can get the data we want from the model object .
• Inserting data into the database is also a new model object , Then save the data you want to save to the model object first , Then save the model object to the database .

stay golang in gorm Model definition is through struct Realized , So we can get through gorm Library implementation struct The type and mysql Mapping of table data .

Tips ：gorm Be responsible for translating the reading and writing operations of the model into sql sentence , then gorm Then execute the database sql The result returned after the statement is transformed into the model object we define .

2、gorm Model definition

gorm Model definition is mainly in struct Add field label description based on type definition to realize , Let's look at a complete example . If there is a commodity list , The table structure is as follows

CREATE TABLE `food` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ' Self increasing ID, goods Id',
  `name` varchar(30) NOT NULL COMMENT ' Trade name ',
  `price` decimal(10,2) unsigned  NOT NULL COMMENT ' commodity price ',
  `type_id` int(10) unsigned NOT NULL COMMENT ' Commodity type Id',
  `createtime` int(10) NOT NULL DEFAULT 0 COMMENT ' Creation time ',
   PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

The model is defined as follows

// The field comment explains gorm Kuba struct The field is converted into a table. What does the field name look like .
type Food struct {
	Id         int  // The table field name is ：id
	Name       string // The table field name is ：name
	Price      float64 // The table field name is ：price
	TypeId     int  // The table field name is ：type_id
	
 // Use two backquotes after the field definition `` The wrapped part of the string is called the label definition , This is golang Basic grammar of , Different libraries define different labels , There are different meanings 
	CreateTime int64 `gorm:"column:createtime"`  // The table field name is ：createtime
}

Default gorm Yes struct The field name uses Snake Case The naming style is converted to mysql Table field name ( Need to convert to lowercase letters ).

according to gorm The default agreement of , The above example only needs to use gorm:"column:createtime" Labels are defined as CreateTime Field specifies the table field name , Others can use the default value .

Tips ：Snake Case Naming style , Is to underline between words （_） Separate , for example ： CreateTime Of Snake Case Style named create_time

3、gorm Model label

Through the example above , As you can see, through something like gorm:"column:createtime" Such tags define Syntax , Definition struct The column name of the field （ Table field name ）.

gorm Tag syntax ：gorm:" Tag definition "

Label definition section , Multiple label definitions can use semicolons （;） Separate , For example, define column names ：

gorm:"column: Name "

gorm Common tags are as follows ：

label	explain	Example
column	Specifies the column name	gorm:"column:createtime"
primaryKey	Specify primary key	gorm:"column:id; PRIMARY_KEY"
-	Ignore fields	gorm:"-"  You can ignore struct Field , Ignored fields do not participate in gorm Read and write operations

4、 Define table name

By definition struct Type of TableName Function implementation defines the table name of the model

Take the example above ：

// set a table name , You can give Food struct The type definition  TableName function , Returns a string as the table name 
func (v Food) TableName() string {
	return "food"
}

         Suggest : By default, table names are defined for the model , Sometimes the definition model is simply used to receive handwriting sql Result of query , At this time, there is no need to define the table name ; To pass by by hand gorm function Table() Specified table name , There is no need to define the model TableName function .

5、gorm.Model

GORM Define a gorm.Model Structure , It includes fields ID、CreatedAt、UpdatedAt、DeletedAt.

// gorm.Model  The definition of 
type Model struct {
  ID        uint           `gorm:"primaryKey"`
  CreatedAt time.Time
  UpdatedAt time.Time
  DeletedAt gorm.DeletedAt `gorm:"index"`
}

To embed it into our structure , Just include these fields , Similar to the effect of inheritance .

type User struct {
  gorm.Model //  The embedded gorm.Model Field of 
  Name string
}

6、 Auto update time

GORM Use by convention CreatedAt、UpdatedAt Tracking creation / Update time . If this field is defined ,GORM Creating 、 The current time will be automatically filled in when updating .

To use fields with different names , You can configure autoCreateTime、autoUpdateTime label

If you want to save UNIX（ Milli / Na ） Second time stamp , instead of time, Simply put time.Time It is amended as follows int that will do .

Example ：

type User struct {
  CreatedAt time.Time //  Default creation time field ,  When creating a , If the field value is zero , Fill in with the current time 
  UpdatedAt int       //  Default update time field ,  The value of the field is zero when it is created or when it is updated , Fill in with the current timestamp seconds 
  Updated   int64 `gorm:"autoUpdateTime:nano"` //  Custom field ,  Use the timestamp to fill in nanoseconds for update time 
  Updated   int64 `gorm:"autoUpdateTime:milli"` // Custom field ,  Fill the update time with timestamp milliseconds 
  Created   int64 `gorm:"autoCreateTime"`      // Custom field ,  Fill the creation time with timestamp seconds 
}

Four 、gorm Connect to database

gorm Support multiple databases , Here we mainly introduce mysql, Connect mysql There are two main steps :

1） To configure DSN (Data Source Name)

2） Use gorm.Open Connect to database

1、 To configure DSN (Data Source Name)

gorm Library usage dsn As a parameter for connecting to the database ,dsn Translated as data source name , Used to describe database connection information . Generally include the database connection address , account number , Information like passwords .

DSN Format ：

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

mysql Connect dsn Example ：

//mysql dsn Format 
// Parameters involved :
//username    Database account 
//password    Database password 
//host        Database connection address , It can be Ip Or domain name 
//port        Database port 
//Dbname      Database name 
username:password@tcp(host:port)/Dbname?charset=utf8&parseTime=True&loc=Local

// The example after filling in the parameters 
//username = root
//password = 123456
//host     = localhost
//port     = 3306
//Dbname   = tizi365
// Back K/V The meaning of the key value pair parameter is ：
//  charset=utf8  The client character set is utf8
//  parseTime=true  Support the database datetime and date Type conversion to golang Of time.Time type 
//  loc=Local  Use the system local time zone 
root:123456@tcp(localhost:3306)/tizi365?charset=utf8&parseTime=True&loc=Local

//gorm  Set up mysql Connection timeout parameters 
// When developing, you often need to set the database connection timeout parameter ,gorm It's through dsn Of timeout Parameter configuration 
// for example , Set up 10 The connection timed out in seconds ,timeout=10s
// The following is an example of completion 
root:123456@tcp(localhost:3306)/tizi365?charset=utf8&parseTime=True&loc=Local&timeout=10s

// Set the read / write timeout 
// readTimeout -  Read timeout ,0 It means no limit 
// writeTimeout -  Write timeout ,0 It means no limit 
root:123456@tcp(localhost:3306)/tizi365?charset=utf8&parseTime=True&loc=Local&timeout=10s&readTimeout=30s&writeTimeout=60s

2、 Use gorm.Open Connect to database

With the above configuration dsn Parameters , You can use gorm Connect to database , The following is an example of connecting to a database

package main

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

func main()  {
    // To configure MySQL Connection parameters 
	username := "root"  // account number 
	password := "123456" // password 
	host := "127.0.0.1" // Database address , It can be Ip Or domain name 
	port := 3306 // Database port 
	Dbname := "tizi365" // Database name 
	timeout := "10s" // Connection timeout ,10 second 
	
	// Under the splice dsn Parameters , dsn The format can refer to the syntax above , Use here Sprintf Dynamic splicing dsn Parameters , Because the general database connection parameters , We are all saved in the configuration file , Parameters need to be loaded from the configuration file , Then joining together dsn.
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8&parseTime=True&loc=Local&timeout=%s", username, password, host, port, Dbname, timeout)
	// Connect MYSQL,  get DB Type instance , Used for subsequent database read and write operations .
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic(" Failed to connect to database , error=" + err.Error())
	}
	// Delay closing database connection 
	defer db.Close()
}

3、gorm Debug mode

For the convenience of debugging , understand gorm How the operation is performed sql sentence , You need to open the debug log during development , such gorm Will print out every execution sql sentence .

Use Debug Function to execute the query

result := db.Debug().Where("username = ?", "tizi365").First(&u)

4、gorm Connection pool

In practice, concurrency is high , In order to improve the utilization of database connection , Avoid the performance consumption caused by repeatedly establishing database connections , Database connection pool technology is often used to maintain database connection .

gorm The built-in database connection pool is very simple to use. Just set the database connection pool parameters .

Database connection pool usage example ：

Definition tools package , Responsible for database initialization （ remarks ： With the help of connection pool , In general, when operating a database , The database connection can be encapsulated into a package ）

// Define a toolkit , Used to manage gorm Initialization of database connection pool .
package tools

// Defining the global db object , We perform database operations mainly through him .
var _db *gorm.DB

// Package initialization function ,golang characteristic , When each package is initialized, it will be executed automatically init function , This is used to initialize gorm.
func init() {
    ... Ignore dsn To configure , Please refer to the above example ...
    
    //  Statement err Variable , The following cannot be used := Assignment operator , otherwise _db Variables are treated as local variables , Cause external inaccessibility _db Variable 
    var err error
    // Connect MYSQL,  get DB Type instance , Used for subsequent database read and write operations .
    _db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
		panic(" Failed to connect to database , error=" + err.Error())
	}
     
    sqlDB, _ := db.DB()

    // Set database connection pool parameters 
    sqlDB.SetMaxOpenConns(100)   // Set the maximum number of connections to the database connection pool 
    sqlDB.SetMaxIdleConns(20)   // The maximum number of free connections allowed in the connection pool , without sql The number of connections that the task needs to execute is greater than 20, More than connections will be closed by the connection pool .
}

// obtain gorm db object , When other packages need to execute database queries , As long as through the tools.getDB() obtain db Object can .
// Don't worry about concurrency. Use the same db Objects will share the same connection ,db When an object calls its method, it will get a new connection from the database connection pool 
func GetDB() *gorm.DB {
	return _db
}

Examples of use ：

package main
// Import tools package 
import tools

func main() {
    // obtain DB
    db := tools.GetDB()
    
    // Perform database query operations 
    u := User{}
	// Automatic generation sql： SELECT * FROM `users`  WHERE (username = 'tizi365') LIMIT 1
	db.Where("username = ?", "tizi365").First(&u)
}

Be careful ： After using connection pool technology , Never use up db After the call db.Close Close database connection , This will cause the entire database connection pool to shut down , As a result, there are no available connections in the connection pool .