Basic configuration: GORM use, database connection and configuration and database operations

Gorm is a developer-friendly ORM library written by Golang. In the previous configuration section we have used GORM to connect to our mysql database. In this section we’ll talk more about how to configure GORM.

Gorm supports a variety of database connections. Currently, the official list of support libraries are: MySQL, PostgreSQL, SQLite, SQL Server database connections. In the blog site we are developing, we have chosen to use MySQL as the back-end database.

Database connection

MySQL > connect to MySQL database

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

Mysql > connect to mysql with TCP socket string

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{})
Copy the code

User name: password @tcp(database IP or domain name: port)/ database name Charset = database encoding &parseTime=True&loc=Local. In order for the database to support emoji, i.e. emoticons in wechat chat, such as 🙂, we need to set the Charset to UTF8MB4. The table encoding and the string field encoding must be set to UTF8MB4 when designing the database table to support the 4-byte UTF8 encoding.

Mysql.Config{} and gorm.Config{} also support more detailed configuration, here will not be in-depth introduction, preliminary learning does not need to learn all things at once, it is difficult to remember so much, later need to use it according to the requirements of the configuration function.

In order for mysql to work well, we also need to set the maximum number of idle connections to the connection object, the maximum number of open connections to the database, and the life cycle of each connection.

  sqlDB, err := db.DB()
	iferr ! =nil {
		return err
	}
	sqlDB.SetMaxIdleConns(1000)
	sqlDB.SetMaxOpenConns(100000)
	sqlDB.SetConnMaxLifetime(- 1)
Copy the code

• Db.db () is to get the DB connection object
• SetMaxIdleConns sets the maximum number of idle connections
• SetMaxOpenConns Sets the maximum number of open connections to the database
• SetConnMaxLifetime Indicates the lifetime of each connection

These configurations are very useful when the database has a large number of reads and writes. They can ensure that the database can still work properly even when a large number of concurrent reads and writes are performed.

Automatic migration table

Another powerful feature of GORM is automatic table migration. Enable automatic migration mode to keep mysql tables up to date.

In the previous section, we created the five table models and mentioned that we could use the AutoMigrate function for automatic migration, so now we add them to automatic migration mode. Let’s reopen config/config.go and add the following code to InitDB() :

db.AutoMigrate(&model.Admin{}, &model.Article{}, &model.ArticleData{}, &model.Attachment{}, &model.Category{})
Copy the code

The InitDB() function is as follows:

func InitDB(setting *mysqlConfig) error {
	var db *gorm.DB
	var err error
	url := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=utf8mb4&parseTime=True&loc=Local",
		setting.User, setting.Password, setting.Host, setting.Port, setting.Database)
	setting.Url = url
	db, err = gorm.Open(mysql.Open(url), &gorm.Config{})
	iferr ! =nil {
		return err
	}

	sqlDB, err := db.DB()
	iferr ! =nil {
		return err
	}

	sqlDB.SetMaxIdleConns(1000)
	sqlDB.SetMaxOpenConns(100000)
	sqlDB.SetConnMaxLifetime(- 1)

	db.AutoMigrate(&model.Admin{}, &model.Article{}, &model.ArticleData{}, &model.Attachment{}, &model.Category{})

	DB = db

	return nil
}
Copy the code

OK, as long as we run this project, it will perform automatic migration first to ensure that the database table fields are up to date.

Write data

In the last section, we defined the model structure and did not read or write it. For convenience, we added a Save() function to each model to manage their creation and update operations:

admin.go

func (admin *Admin) Save(db *gorm.DB) error {
	if admin.Id == 0 {
		admin.CreatedTime = time.Now().Unix()
	}
	admin.UpdatedTime = time.Now().Unix()

	iferr := db.Save(admin).Error; err ! =nil {
		return err
	}

	return nil
}
Copy the code

The Save() function takes a pointer to * gorm.db and returns an error. We have to pass db *gorm. db because we can’t use db variables in config.go directly. If we use db variables in config.go directly, it will cause cyclic dependencies, which golang does not allow. Error is returned to verify that the execution succeeded. If the execution succeeded, the return value is nil. If the execution failed, the return value is the cause of the failure.

The Save() function is an internal method of the Admin model. This method operates on a pointer to Admin, which means that the Admin pointer is already a pointer. Data cannot be inserted or updated.

category.go

func (category *Category) Save(db *gorm.DB) error {
	if category.Id == 0 {
		category.CreatedTime = time.Now().Unix()
	}
	category.UpdatedTime = time.Now().Unix()

	iferr := db.Save(category).Error; err ! =nil {
		return err
	}

	return nil
}
Copy the code

If the value is zero, it is considered to be an insert. Therefore, we need to assign CreatedTime to the current timestamp. Meanwhile, each Save is considered to be an update operation. So you also need to assign UpdatedTime to the current timestamp to indicate when the data was updated.

article.go

func (article *Article) Save(db *gorm.DB) error {
	if article.Id == 0 {
		article.CreatedTime = time.Now().Unix()
	}
	article.UpdatedTime = time.Now().Unix()

	iferr := db.Save(article).Error; err ! =nil {
		return err
	}

	return nil
}
Copy the code

When we save the articles table, we do not see the insertion and update of the article_data table. That’s because gorM internally automatically handles this insert and update operation based on their foreign key relationships. Because our Article model defines the ArticleData field, it is a one-to-one relationship.

attachment.go

func (attachment *Attachment) Save(db *gorm.DB) error {
	if attachment.Id == 0 {
		attachment.CreatedTime = time.Now().Unix()
	}
	attachment.UpdatedTime = time.Now().Unix()

	iferr := db.Save(attachment).Error; err ! =nil {
		return err
	}

	attachment.GetThumb()

	return nil
}

func (attachment *Attachment) GetThumb(a) {
	// If it is a remote address, the thumbnail is the same as the original address
	if strings.HasPrefix(attachment.FileLocation, "http") {
		attachment.Logo = attachment.FileLocation
		attachment.Thumb = attachment.FileLocation
	} else {
		pfx := "/uploads/"
		attachment.Logo = pfx + attachment.FileLocation
		paths, fileName := filepath.Split(attachment.FileLocation)
		attachment.Thumb = pfx + paths + "thumb_" + fileName
	}
}
Copy the code

For the Save operation of the Attachment model, we have also defined the GetThumb() function, which is also the internal method of the Attachment model. It will automatically organize the display data of Logo and Thumb field according to the upload path and display path defined by us. Because *Attachment is a pointer, the value of the Attachment variable can be directly modified through the pointer after the Save operation. Therefore, after the Save() operation is performed, Attachment.GetThumb() will immediately handle the Logo and Thumb fields.

At this point, we can use the Save() function to Save the data for each model. The Save() function automatically determines whether the data is inserted or updated, and then performs the insert and update operations.