preface

All back-end applications are inseparable from database operation. There are also some useful database operation components in Go. For example, Gorm is a good choice.

Here are Gorm's own examples:

• Full function ORM
• Association (Has One, Has Many, Belongs To, Many To Many, polymorphic, single table inheritance)
• Hook methods in Create, Save, Update, Delete, Find
• Supports preloading of Preload and Joins
• Transaction, nested transaction, Save Point, Rollback To Saved Point
• Context, precompiled mode, DryRun mode
• Batch insert, FindInBatches, Find/Create with Map, CRUD using SQL expression and Context Valuer
• SQL builder, Upsert, database lock, Optimizer/Index/Comment Hint, named parameter, subquery
• Composite primary key, index, constraint
• Auto Migration
• Custom Logger
• Flexible extensible plug-in API: Database Resolver, Prometheus
• Each feature has been tested
• Developer friendly

Of course, you may not use so many features of Gorm, but Gorm is a very excellent ORM framework in Go.

This paper does not explore the advantages and disadvantages of Gorm and other frameworks, but discusses the use of Gorm in practical development from the perspective of users.

Of course, Gorm's own official documents have been very detailed. If you have any doubts about the use of some Gorm in this article, please move to the official documents: https://gorm.io/zh_CN/docs/index.html

install

Execute the go get command on the console to install the dependency driver according to your actual use. Here, take MySQL as an example.

Gorm officially supports mysql, PostgreSQL, SQLite and SQL server

go get -u gorm.io/gorm
go get -u gorm.io/driver/mysql

You can introduce dependencies when using

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

Establish connection

Using Gorm to establish a database connection is actually very simple, but to make it easy to use, you need to spend some time. Here, we will show you how to connect from the simplest connection to the easy-to-use connection settings.

The most basic connection

func GetDb() *gorm.DB {
 // reference resources 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&parseTime=True&loc=Local"
 db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
 if err!= nil {
  return nil
 }
 return db
}

"Note:

1. Want to handle time correctly Time, you need to bring the parseTime parameter,
2. Use charset to specify the encoding. To support complete UTF-8 encoding, you need to change charset=utf8 to charset=utf8mb4

More parameter settings: https://github.com/go-sql-driver/mysql#parameters "

Set connection pool

Gorm also supports connection pools. Gorm uses database/sql to maintain connection pools

Use SetMaxIdleConns, SetMaxOpenConns and SetConnMaxLifetime to set the maximum number of idle connections, the maximum number of connections and the connection idle timeout parameters respectively.

func GetDb() *gorm.DB {
 // reference resources 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&parseTime=True&loc=Local"
 db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{

 })
 if err != nil {
  return nil
 }
 sqlDB, err := db.DB()
 if err != nil {
  log.Printf("database setup error %v", err)
 }
 sqlDB.SetMaxIdleConns(10)           //Maximum number of free connections
 sqlDB.SetMaxOpenConns(100)          //maximum connection
 sqlDB.SetConnMaxLifetime(time.Hour) //Set connection idle timeout
 return db
}

Global connection

For ease of use, we can use a global variable to save the connection of the database at the beginning. We can call it directly when using it without initializing the database again.

var db *gorm.DB

// GetDb get connection
func GetDb() *gorm.DB {
 return db
}

Change the previous function to initialize and assign value to db, and directly call GetDb function when using

func DbInit(){
 // reference resources 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&parseTime=True&loc=Local"
 tempDb, err := gorm.Open(mysql.Open(dsn), &gorm.Config{

 })
 if err != nil {
  return nil
 }
 sqlDB, err := tempDb.DB()
 if err != nil {
  log.Printf("database setup error %v", err)
 }
 sqlDB.SetMaxIdleConns(10)           //Maximum number of free connections
 sqlDB.SetMaxOpenConns(100)          //maximum connection
 sqlDB.SetConnMaxLifetime(time.Hour) //Set connection idle timeout
 db = tempDb
}

Utilization profile

Here, you actually find that Gorm can be used to establish a database connection, but is there any way to obtain connection parameters from the configuration file like Spring Boot? Chapter 3 just talks about how to use the method of reading the configuration file, so why not use it?

Stamp - > Go (III) go configuration file

Define database connection parameters in the configuration file

database:
  type: mysql
  host: localhost
  port: 3306
  username: root
  password: 123456
  dbname: test
  max_idle_conn: 10
  max_open_conn: 30
  conn_max_lifetime: 300

Define the corresponding structure

var Database *database

type conf struct {
 DB   database  `yaml:"database"`
}

type database struct {
 Type   string  `yaml:"type"`
 Host      string     `yaml:"host"`
 Port      string     `yaml:"port"`
 UserName   string   `yaml:"username"`
 Password   string   `yaml:"password"`
 DbName      string     `yaml:"dbname"`
 MaxIdleConn  int   `yaml:"max_idle_conn"`
 MaxOpenConn  int   `yaml:"max_open_conn"`
 ConnMaxLifetime int   `yaml:"conn_max_lifetime"`
}

For details on how to bind parameters, please click the stamp - > Go (III) Go configuration file

For a more intuitive feeling, the URI is extracted

//Get the link urifunc mysqluri() string {return FMT. Sprintf ('% s:%) s@tcp (%s:%s)/%s? charset=utf8&parseTime=true",  Database.UserName,  Database.Password,  Database.Host,  Database.Port,  Database.DbName)}

So that's what comes out.

var db *gorm.DB// GetDb get connection func GetDb() * Gorm DB {return DB} / / dbinit database connection pool initialization func dbinit() {FMT. Println (mysqluri()) Conn, err1: = Gorm. Open (mysql. Open (mysqluri()), & Gorm. Config {}) if err1= nil {  log.Printf("connect get failed.")   return } sqlDB, err := conn.DB() if err !=  nil {  log.Printf("database setup error %v", err) } sqlDB. Setmaxidleconns (database. Maxidleconn) / / maximum number of free connections sqlDB Setmaxopenconns (database. Maxopenconn) / / maximum connections sqlDB SetConnMaxLifetime(time.Duration(Database.ConnMaxLifetime) * time. Second) / / set the connection idle timeout db = conn}

If you want to initialize automatically when the project starts, change the DbInit method name to init. Otherwise, you need to call it in the main method to perform initialization.

For better development, we can customize Gorm's log

//Initialize database log newlogger: = logger New( log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer logger. Config{  SlowThreshold:             time.Second, // Slow SQL threshold  LogLevel:                  logger.Info, // Log level  IgnoreRecordNotFoundError: true,        // Ignore ErrRecordNotFound error for logger  Colorful:                  true,        // Disable color },)

Place it on the Gorm parameter as a parameter Open(mysql.Open(mySQLUri()), &gorm. Config{})

conn, err1 := gorm.Open(mysql.Open(mySQLUri()), &gorm.Config{ Logger: newLogger,})

use

Gorm's CURD is also relatively simple.

Define a structure User. Apart from the fields of the record, there are three fields: number, name and password

type User struct { Id    int64 `gorm:"primaryKey;column:id;"` Username  string  `gorm:"column:user_name;type:varchar(255);default:(-)" ` Password  string  `gorm:"column:password;type:varchar(255);default:(-)"` Deleted    gorm.DeletedAt `gorm:"column:deleted;type:timestamp;default:(-)"`         CreateTime time.Time      `gorm:"column:create_time;type:timestamp;default:(-)"` UpdateTime time.Time      `gorm:"column:update_time;type:timestamp;default:(-)"`}// TableName custom table name func (* user) TableName() string {return "users"}

explain:

1. Use primaryKey to specify the primary key
2. Use column:id to specify the column name in the database
3. Use Gorm Deletedat indicates that this field is a deletion flag. If Gorm. Is used Deletedat, the database column type must be in time format.
4. Use type:varchar(255) to indicate the field type
5. Use default: (-) to set the default value, - means no default value.
6. Use user Tablename table name database name. When Model is used to bind structure, Gorm will call this method by default. In addition, DB. Can also be used Table ("user") explicitly indicates the table name.

query

1. Get the first one and query the first one by default

// GetFirst SELECT * FROM users ORDER BY id LIMIT 1;func GetFirst() (user *User) { db := config.GetDb() db.Model(&user).First(&user) return }

1. Get last

// GetLast SELECT * FROM users ORDER BY id DESC LIMIT 1;func GetLast() (user *User) { db := config.GetDb() db.Model(&user).Last(&user) return}

1. Get by primary key

// GetById SELECT * FROM users WHERE id = 1; Func getbyid (ID Int64) (user * user) {DB: = config. Getdb() dB. Model (& user). Find (& user, ID) return} is equivalent to func getbyid (ID Int64) (user * user) {DB: = config. Getdb() dB. Model (& user). Where ("id =?", id). Find(&user) return}

1. Batch query by PK

// GetByIds SELECT * FROM users WHERE id IN (1,2,3); Func getbyids (IDS [] Int64) (user [] * user) {DB: = config. Getdb() dB. Model (& user). Find (& user, IDS) return} is equivalent to func getbyids (s [] Int64) (user [] * user) {DB: = config. Getdb() DB. Model (& user). Where ("ID in?", ids). Find(&user) return}

1. Get some parameters, for example, only get the name and password

// GetSomeParam SELECT username,password FROM users WHERE id = 1;func GetSomeParam(id int64) (user *User) { db := config.GetDb() db.Model(&user).Select("username", "password").Find(&user,id) return}

1. For paging query, you can use limit & offset for paging query

// GetPage SELECT * FROM users OFFSET 5 LIMIT 10;func GetPage(limit int,offset int) (user []*User) { db := config.GetDb() db.Model(&user).Limit(limit).Offset(offset).Find(&user) return}

1. order

// GetByOrder SELECT * FROM users ORDER BY id desc, username; Func getbyorder() (user [] * user) {DB: = config. Getdb() dB. Model (& user). Order ("ID DESC, username"). Find (& user) return} is equivalent to func getbyorder() (user [] * user) {DB: = config. Getdb() dB. Model (& user). Order ("ID desc"). Order ("username"). Find (& user) return}

For more information, move to: https://gorm.io/zh_CN/docs/query.html

newly added

1. Create a single

func Create(user *User)  { db := config.GetDb() db.Model(&user).Create(&user) return}

1. Save single (save)

func Save(user *User)  { db := config.GetDb() db.Model(&user).Save(&user) return}

The difference between create and Save: if the data to be inserted in Save exists, it will not be inserted. Create will execute the insertion in any case

1. Create multiple

func CreateBatch(user []*User)  { db := config.GetDb() db.Model(&user).Create(&user) return}

For more information, move to: https://gorm.io/zh_CN/docs/create.html

modify

1. Update individual fields

// UpdateUsername UPDATE users SET username = "lomtom" where id = 1func UpdateUsername(id int64,username string)  { db := config.GetDb() db.Model(&User{}).Where("id = ?",id).Update("username",username) return}

1. Full volume / multi column update (according to structure)

// UpdateByUser UPDATE `user` SET `id`=14,`user_name`='lomtom',`password`='123456',`create_time`='2021-09-26 14:22:21.271',`update_time`='2021-09-26 14:22:21.271' WHERE id = 14 AND `user`.`deleted` IS NULLfunc UpdateByUser(user *User)  { db := config.GetDb() db.Model(&User{}).Where("id = ?",user.Id).Updates(&user) return}

For more information, move to: https://gorm.io/zh_CN/docs/update.html

delete

1. Simple deletion (delete according to the id in user)

// DeleteByUser DELETE from users where id = 28;// DeleteByUser UPDATE `user` SET `deleted`='2021-09-26 14:25:33.368' WHERE `user`.`id` = 28 AND `user`.`deleted` IS NULLfunc DeleteByUser(user *User)  { db := config.GetDb() db.Model(&User{}).Delete(&user) return}

Note: Gorm is not added to the structure The fields marked with deletedat are deleted directly, and the deleted field will be updated, that is, soft deletion is realized

1. Delete by id

// DeleteById UPDATE `user` SET `deleted`='2021-09-26 14:29:55.15' WHERE `user`.`id` = 28 AND `user`.`deleted` IS NULLfunc DeleteById(id int64)  { db := config.GetDb() db.Model(&User{}).Delete(&User{},id) return}

affair

Similarly, Gorm has rich transaction support.

Anonymous transaction

DB. Can be used Transaction anonymous method to indicate that multiple operations are in a transaction. If err is returned, the transaction will be rolled back, and nil is returned, the transaction will be committed

func Transaction() error { db := config.GetDb() err := db.Transaction(func(tx *gorm.DB) error {  // Perform some DB operations in the transaction (from here on, you should use 'TX' instead of 'db') if err: = tx.create (& user {Username: "lomtom"}) Error;  err !=  Nil {/ / if any error is returned, the transaction will be rolled back. Return err} if err: = tx.delete (& user {}, 28) Error;  err !=  Nil {return err} / / return nil commit transaction return nil}) if err= nil {  return err } return nil}

Manual transaction

In dB Begin() indicates the beginning of a transaction. In case of an error, tx.Rollback() is used, and tx.Commit() is used for transaction submission

func Transaction1() error { db := config.GetDb() tx := db.Begin() defer func() {  if r := recover(); r != nil {   tx.Rollback()  } }() // Perform some DB operations in the transaction (from here on, you should use 'TX' instead of 'db') if err: = tx.create (& user {Username: "lomtom"}) Error;  err !=  Nil {/ / rollback transaction tx.rollback() return err} if err: = tx.delete (& user {}, 28) Error;  err !=  Nil {tx.rollback() return err} / / commit the transaction return tx.commit() Error}