Go (5) Go do not know how to use Gorm?

This is the 10th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Author: lomtom

Personal website: Lomtom.top,

Personal public account: Bosiao Yuan

Your support is my biggest motivation.

preface

All back-end applications need database operations, and there are some useful database operations components in Go, such as Gorm, which is a good choice.

Here are the advantages of Gorm’s own example:

A fully functional ORM
(Has One, Has Many, Belongs To, Many To Many, polymorphism, single table inheritance)
Hook methods in Create, Save, Update, Delete, and Find
Supports preloading of preloads and Joins
Transactions, nested transactions, Save Point, Rollback To Saved Point
Context, precompiled mode, DryRun mode
Batch batches, FindInBatches, Find/Create with Map, CRUD using SQL expressions, Context Valuer
SQL builder, Upsert, database lock, Optimizer/Index/Comment Hint, named parameter, subquery
Compound primary key, index, constraint
Auto Migration
Custom Logger
Flexible extensibility API: Database Resolver, Prometheus…
Each feature has gone through a lot of testing
Developer friendly

Of course, you may not use gorm as much as you do, but gorM is an excellent ORM framework for Go.

This article also does not explore the pros and cons of Gorm compared to other frameworks, but from the perspective of users, together with Gorm in the actual development of the use.

IO /zh_CN/docs/…

The installation

Run the go get command on the console to install dependencies. The driver is installed based on actual use. MySQL is used as an example.

Gorm supports the following databases: MySQL, PostgreSQL, SQlite, and SQL Server

go get -u gorm.io/gorm
go get -u gorm.io/driver/mysql
Copy the code

Just introduce dependencies at use time

import (
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)
Copy the code

Establish a connection

Using Gorm to establish a database connection is actually very simple, but to do good, it needs to pay attention to, here, will lead you how to from the simplest connection to good connection Settings.

The most basic connection

func GetDb(a) *gorm.DB {
	// See https://github.com/go-sql-driver/mysql#dsn-data-source-name for details
	dsn := "User: pass @ TCP/dbname (127.0.0.1:3306)? charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	iferr! =nil {
		return nil
	}
	return db
}
Copy the code

Note:

To properly handle time. time, you need to take the parseTime argument,

To support the full UTF-8 encoding, you need to change charset= UTF8 to charset= UTf8MB4

More parameter Settings: github.com/go-sql-driv…

Setting a Connection Pool

Gorm also supports connection pooling, and Gorm maintains connection pooling using DATABASE/SQL

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

func GetDb(a) *gorm.DB {
	// See https://github.com/go-sql-driver/mysql#dsn-data-source-name for details
	dsn := "User: pass @ TCP/dbname (127.0.0.1:3306)? charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{

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

Global connection

For ease of use, we can use a global variable to hold the connection to the database at the beginning and call it when we use it, without having to initialize the database again.

var db *gorm.DB

// GetDb gets the connection
func GetDb(a) *gorm.DB {
	return db
}
Copy the code

Instead of initializing db and assigning values to db, call GetDb directly when used

func DbInit(a){
	// See https://github.com/go-sql-driver/mysql#dsn-data-source-name for details
	dsn := "User: pass @ TCP/dbname (127.0.0.1:3306)? charset=utf8mb4&parseTime=True&loc=Local"
	tempDb, err := gorm.Open(mysql.Open(dsn), &gorm.Config{

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

Using configuration files

At this point, you can actually use Gorm to set up database connections pretty well, but is there a way like Spring Boot to get connection parameters from configuration files?

-> Go (3) 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
Copy the code

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"`}Copy the code

How to bind parameters, please stamp -> Go (3) Go configuration file

For a more intuitive feel, extract the URI

// Get the link URI
func mySQLUri(a) string {
	return fmt.Sprintf("%s:%s@tcp(%s:%s)/%s? charset=utf8&parseTime=true",
		Database.UserName,
		Database.Password,
		Database.Host,
		Database.Port,
		Database.DbName)
}
Copy the code

So this is what you end up with.

var db *gorm.DB

// GetDb gets the connection
func GetDb(a) *gorm.DB {
	return db
}

// DbInit Initializes the database connection pool
func DbInit(a) {
	fmt.Println(mySQLUri())
	conn, err1 := gorm.Open(mysql.Open(mySQLUri()), &gorm.Config{})
	iferr1 ! =nil {
		log.Printf("connect get failed.")
		return
	}
	sqlDB, err := conn.DB()
	iferr ! =nil {
		log.Printf("database setup error %v", err)
	}
	sqlDB.SetMaxIdleConns(Database.MaxIdleConn)                                     // Maximum number of idle connections
	sqlDB.SetMaxOpenConns(Database.MaxOpenConn)                                     // Maximum number of connections
	sqlDB.SetConnMaxLifetime(time.Duration(Database.ConnMaxLifetime) * time.Second) // Set connection idle timeout
	db = conn
}
Copy the code

If you want automatic initialization at project startup, change the DbInit method name to init; otherwise, you need to call the initialization yourself in the main method.

For better development, we can customize Gorm logs

// Initialize the 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},)Copy the code

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

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

use

Gorm’s CURD leaves are relatively simple.

Define a structure User that has three fields, number, name, and password, except for the fields of the record

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 specifies a custom TableName
func (*User) TableName(a) string {
	return "users"
}
Copy the code

Description:

useprimaryKeySpecify the primary key
usecolumn:idSpecifies the column name in the database
usegorm.DeletedAtIdentifies the field as a delete flag if it is usedgorm.DeletedAt, the database column type must be in time format.
usetype:varchar(255)Specify field type
usedefault:(-)Set the default values,-Indicates that there is no default value.
useUser.TableNameTable name Database name when usedModelWhen you bind a structure,GormThis method is called by default, but can be used otherwisedb.Table("user")Explicitly indicate the table name.

The query

Gets the first one, which is queried by default

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

Get the last one

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

Obtained 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 tofunc GetById(id int64) (user *User) {
	db := config.GetDb()
	db.Model(&user).Where("id = ?",id).Find(&user)
	return
}
Copy the code

Batch query using primary keys

// 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 tofunc GetByIds(s []int64) (user []*User) {
	db := config.GetDb()
	db.Model(&user).Where("id in ?",ids).Find(&user)
	return
}
Copy the code

Gets partial parameters, such as only 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
}
Copy the code

Paging queries can be usedLimit & OffsetPaging 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
}
Copy the code

order

// GetByOrder SELECT * FROM users ORDER BY id desc, username;
func GetByOrder(a) (user []*User) {
	db := config.GetDb()
	db.Model(&user).Order("id desc,username").Find(&user)
	return} is equivalent tofunc GetByOrder(a) (user []*User) {
	db := config.GetDb()
	db.Model(&user).Order("id desc").Order("username").Find(&user)
	return
}
Copy the code

IO /zh_CN/docs/…

new

Create a single

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

Save a single

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

Save does not insert data if it exists. Create does insert data in all cases

Create multiple

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

IO /zh_CN/docs/…

Modify the

Updating a single field

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

Full/multi-column update (based on 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 NULL
func UpdateByUser(user *User)  {
	db := config.GetDb()
	db.Model(&User{}).Where("id = ?",user.Id).Updates(&user)
	return
}
Copy the code

IO /zh_CN/docs/…

delete

Simply delete (according touserDelete id from

// 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'. 'IS NULL
func DeleteByUser(user *User)  {
	db := config.GetDb()
	db.Model(&User{}).Delete(&user)
	return
}
Copy the code

Structure did not add gorm.DeletedAt mark of the field, directly deleted, added will update the deleted field, that is, to achieve soft deletion

According to theidTo delete

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

The transaction

Gorm also has rich transaction support.

Anonymous transaction

The db.Transaction anonymous method can be used to indicate that multiple operations are in a Transaction, returning ERR will be rolled back, and returning nil will commit the Transaction

func Transaction(a) error {
	db := config.GetDb()
	err := db.Transaction(func(tx *gorm.DB) error {
		// Perform some DB operations in a transaction (from here you should use 'tx' instead of 'db')
		if err := tx.Create(&User{Username: "lomtom"}).Error; err ! =nil {
			// Returning any errors will roll back the transaction
			return err
		}
		if err := tx.Delete(&User{}, 28).Error; err ! =nil {
			return err
		}
		// Return nil commit transaction
		return nil
	})
	iferr ! =nil {
		return err
	}
	return nil
}

Copy the code

Manual transaction

Db.begin () indicates the start of a transaction, error using tx.rollback (), transaction commit using tx.mit ()

func Transaction1(a) error {
	db := config.GetDb()
	tx := db.Begin()
	defer func(a) {
		if r := recover(a); r ! =nil {
			tx.Rollback()
		}
	}()
	// Perform some DB operations in a transaction (from here you should use 'tx' instead of 'db')
	if err := tx.Create(&User{Username: "lomtom"}).Error; err ! =nil {
		// Rollback the 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
}
Copy the code

Go (5) Go do not know how to use Gorm?

preface

The installation

Establish a connection

The most basic connection

Setting a Connection Pool

Global connection

Using configuration files

use

The query

new

Modify the

delete

The transaction

Anonymous transaction

Manual transaction

Related Posts

NET Core service deployment in ARM64 server

Algorithm – the best time to buy and sell moon cakes

This MySQL bug is an eye-opener for me!