Introduction to GORM usage of ORM framework in Go language

Install

Install GORM with the following command:

$ go get -u /gorm

You may have seen it use itgo get -u /jinzhu/gormCommand to install GORM, this is the old version of v1, which is now outdated and is not recommended. New version v2 has been moved to/go-gorm/gormUnder the warehouse.

Start quickly

The following sample code will help you quickly get started with the use of GORM:

package main
import (
	"/driver/sqlite"
	"/gorm"
)
// Product defines a structure to map database tablestype Product struct {
	
	Code  string
	Price uint
}
func main() {
	// Establish a database connection	db, err := ((""), &{})
	if err != nil {
		panic("failed to connect database")
	}
	// Migrate table structure	(&Product{})
	// Add data	(&Product{Code: "D42", Price: 100})
	// Find data	var product Product
	(&product, 1)                 // find product with integer primary key
	(&product, "code = ?", "D42") // find product with code D42
	// Update data - update product's price to 200	(&product).Update("Price", 200)
	// Update data - update multiple fields	(&product).Updates(Product{Price: 200, Code: "F42"}) // non-zero fields
	(&product).Updates(map[string]interface{}{"Price": 200, "Code": "F42"})
	// Delete data - delete product	(&product, 1)
}

Tip: This is usedSQLiteDatabase driver, need to passgo get -u /driver/sqliteCommand to install.

Save the above code inand execute.

$ go run 

After the execution is completed, we will get it in the current directory.SQLite database file.

① Enter the SQLite command line.

② View existing database tables.

③ Set the output mode when querying table data later to left-align by column.

④ Query the data present in the table.

Students who have experience using ORM frameworks can understand the above code even if I don’t explain it.

This sample code can basically summarize the usage routines of the GORM framework:

• Define the structure map table structure:ProductStructures are called "models" in GORM. A model corresponds to a database table and a structure instance object corresponds to a database table record.

• Connect to the database: GORMMethods establish a connection with the database. Only after the connection is established can the database be CRUD operations.

• Automatic migration table structure: callMethods can be automatically created in the databaseProductThe database table mapped by the structure, and, whenProductIf the structure field is changed, if the migration code is executed again, GORM will automatically adjust the table structure, which is very convenient. However, I do not recommend using this feature in production projects. Because database table operations are all high-risk operations, they must be reviewed and approved by multiple people before they can be executed. Although the GORM automatic migration function will not have any problems in theory, it is better to be cautious in online operations. I personally think it is more appropriate to use it only in small projects or projects where data is not so important.

• CRUD operation: After migrating the database, you have the database table and you can perform CRUD operation.

Some students may have a question, there is no similarity in the above example code.defer ()Actively close the connection operation, so when will the database connection be closed?

In fact, GORM maintains a database connection pool and initializes it.dbAfter that, all connections are managed by the underlying library. Without manual intervention from programmers, GORM will automatically close the connection at the right time. GORM framework authorjinzhuThere are also source code warehousesIssueI have replied to questions from netizens. Interested students can click to view.

Next, I will explain the use of GORM in detail.

Declaration model

GORM uses a model to map a database table, and the model is standard Gostruct, implemented by Go's basic data typeScannerandValuerThe custom type of the interface and its pointer or alias are composed of.

For example:

type User struct {
	ID           uint
	Name         string
	Email        *string
	Age          uint8
	Birthday     *
	MemberNumber 
	ActivatedAt  
	CreatedAt    
	UpdatedAt    
}

We can usegormField tags to control the type, column size, default value and other properties of database table fields, such as usingcolumnField tags to map field names in the database.

type User struct {
	
	Name         string         `gorm:"column:name"`
	Email        *string        `gorm:"column:email"`
	Age          uint8          `gorm:"column:age"`
	Birthday     *     `gorm:"column:birthday"`
	MemberNumber  `gorm:"column:member_number"`
	ActivatedAt     `gorm:"column:activated_at"`
}
func (u *User) TableName() string {
	return "user"
}

Not specifiedcolumnIn the case of field tags, GORM uses the field name by default.snake_caseAs a column name.

GORM uses the structure name by defaultsnake_casesAs the table name, it is implemented as the structureTableNameMethods can customize table names.

I prefer the practice of "explicit over implicit", so the database name and table name will be displayed and written out.

Because we do not use the automatic migration function, other field tags cannot be used, so I will not introduce them one by one. Interested students can check the official documents for learning.

UserThere is a nested structure in the structure, it is a model provided by GORM by defaultstruct, used to simplify user model definition.

GORM tends to be convention-preferred than configuration, by default, useIDAs the primary key, useCreatedAt、UpdatedAt、DeletedAtThe creation, update, and deletion time of field tracking records. These fields are defined inmiddle:

type Model struct {
	ID        uint `gorm:"primarykey"`
	CreatedAt 
	UpdatedAt 
	DeletedAt DeletedAt `gorm:"index"`
}

Since we do not use the automatic migration function, we need to manually write SQL statements to create them.userDatabase table structure:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT '' COMMENT 'username',
  `email` varchar(255) NOT NULL DEFAULT '' COMMENT 'Mail',
  `age` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'age',
  `birthday` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Birthday',
  `member_number` varchar(50) COMMENT 'Member number',
  `activated_at` datetime COMMENT 'Activation time',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_email` (`email`),
  INDEX `idx_deleted_at`(`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User Table';

The field types in the database must correspond to the field types of the model in Go, and incompatible types may cause errors.

Connect to the database

GORM officially supports database types: MySQL, PostgreSQL, SQLite, SQL Server and TiDB.

Here we use the most common MySQL as an example to illustrate how GORM connects to a database.

In the sample code that started quickly in the previous article, when we used the SQLite database, we installed itsqliteDriver. To connect to MySQL, you need to usemysqldrive.

Defined in GORMThe interface is used to regulate database connection operations. We call the program that implements this interface "driver". For each database, there are corresponding drivers, which are independent of the GORM library and need to be introduced separately.

The code for connecting to the MySQL database is as follows:

package main
import (
	"fmt"
	"/driver/mysql"
	"/gorm"
)
func ConnectMySQL(host, port, user, pass, dbname string) (*, error) {
	dsn := ("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		user, pass, host, port, dbname)
	return ((dsn), &{})
}

It can be found that this code is exactly the same as the code connecting to the SQLite database, which is the benefit of interface-oriented programming.

first,Receive a stringdsn, DSN full nameData Source Name, translated as the database source name. DSN defines the connection information of a database, including user name, password, database IP, database port, database character set, database time zone and other information. DSN follows a specific format:

username:password@protocol(address)/dbname?param=value

Through the information contained in the DSN,mysqlThe driver can know how to connect to the MySQL database.

The return is exactly oneobject, pass it toAfter the method, we will get*Object, this object can be used to operate the database.

GORM usagedatabase/sqlTo maintain the database connection pool, we can set the following parameters for the connection pool:

func SetConnect(db *) error {
	sqlDB, err := ()
	if err != nil {
		return err
	}
	(100)                 // Set the maximum number of open connections to the database	(100)                 // Set the maximum number of idle connections	(10 * ) // Set the maximum survival time of idle connection	return nil
}

Now that the database connection has been established, we can operate on the database.

create

AvailableCreateMethod to create a database record:

now := ()
email := "u1@"
user := User{Name: "user1", Email: &email, Age: 18, Birthday: &now}
// INSERT INTO `user` (`created_at`,`updated_at`,`deleted_at`,`name`,`email`,`age`,`birthday`,`member_number`,`activated_at`) VALUES ('2023-05-22 22:14:47.814','2023-05-22 22:14:47.814',NULL,'user1','u1@',18,'2023-05-22 22:14:47.812',NULL,NULL)
result := (&user) // Create through pointers to data("user: %+v\n", user) // Automatic filling("affected rows: %d\n", )
("error: %v\n", )

To create a record, we need to instantiate it firstUserobject, then pass its pointer tomethod.

After the method is executed, a*Object.

It will be automatically filled with the true value returned after creating the database record.

You can get the number of rows that affect this operation.

You can know if there is an error in executing SQL.

Here I will(&user)This sentenceORMThe native SQL statements generated by the code are placed in comments for your comparison and learning. And, in the subsequent examples I will do the same.

CreateThe method not only supports creating a single record, it also supports batch operations, creating multiple records at a time:

now = ()
email2 := "u2@"
email3 := "u3@"
users := []User{
	{Name: "user2", Email: &email2, Age: 19, Birthday: &now},
	{Name: "user3", Email: &email3, Age: 20, Birthday: &now},
}
// INSERT INTO `user` (`created_at`,`updated_at`,`deleted_at`,`name`,`email`,`age`,`birthday`,`member_number`,`activated_at`) VALUES ('2023-05-22 22:14:47.834','2023-05-22 22:14:47.834',NULL,'user2','u2@',19,'2023-05-22 22:14:47.833',NULL,NULL),('2023-05-22 22:14:47.834','2023-05-22 22:14:47.834',NULL,'user3','u3@',20,'2023-05-22 22:14:47.833',NULL,NULL)
result = (&users)

The main logic of the code remains unchanged, and only a single one is neededUserChange the instance toUserJust slice it. GORM uses a SQL statement to complete batch creation of records.

Query

Query records are the scenarios we use most in daily development. GORM provides a variety of methods to support SQL query operations.

useFirstThe method can query the first record:

var user User
// SELECT * FROM `user` WHERE `user`.`deleted_at` IS NULL ORDER BY `user`.`id` LIMIT 1
result := (&user)

FirstMethod receives a model pointer through the model'sTableNameThe method can get the database table name and then useSELECT *Statements query records from the database.

From the generated SQL, you can findFirstMethod query data by default according to the primary keyIDSort ascending order and only filter the deletion time asNULLdata, useLIMITKeywords to limit the number of data strips.

useLastThe method can query the last data, and the sorting rule is the primary keyIDDescending order:

var lastUser User
// SELECT * FROM `user` WHERE `user`.`deleted_at` IS NULL ORDER BY `user`.`id` DESC LIMIT 1
result = (&lastUser)

useWhereMethods can add query conditions:

var users []User
// SELECT * FROM `user` WHERE name != 'unknown' AND `user`.`deleted_at` IS NULL
result = ("name != ?", "unknown").Find(&users)

No single data query is available here, so use it insteadFindMethod to query all records that meet the criteria.

The query methods described above are all throughSELECT *Query all fields in the database table, we can useSelectMethod specifies the fields to be queried:

var user2 User
// SELECT `name`,`age` FROM `user` WHERE `user`.`deleted_at` IS NULL ORDER BY `user`.`id` LIMIT 1
result = ("name", "age").First(&user2)

useOrderMethods can customize the sorting rules:

var users2 []User
// SELECT * FROM `user` WHERE `user`.`deleted_at` IS NULL ORDER BY id desc
result = ("id desc").Find(&users2)

GORM also provides the rightLimit & OffsetSupport:

var users3 []User
// SELECT * FROM `user` WHERE `user`.`deleted_at` IS NULL LIMIT 2 OFFSET 1
result = (2).Offset(1).Find(&users3)

use-1CancelLimit & OffsetRestrictions:

var users4 []User
var users5 []User
// SELECT * FROM `user` WHERE `user`.`deleted_at` IS NULL LIMIT 2 OFFSET 1; (users4)
// SELECT * FROM `user` WHERE `user`.`deleted_at` IS NULL; (users5)
result = (2).Offset(1).Find(&users4).Limit(-1).Offset(-1).Find(&users5)

This code will execute two query statements. The reason why multiple SQLs can be executed in this way is because each method returns*Object, this is also a programming skill.

useCountMethods can count the number of records:

var count int64
// SELECT count(*) FROM `user` WHERE `user`.`deleted_at` IS NULL
result = (&User{}).Count(&count)

Sometimes we encounter more complex businesses, we may need to use SQL subqueries. The subqueries can be nested in another query. GORM allows*Generate subqueries when an object is used as a parameter:

var avgages []float64
// SELECT AVG(age) as avgage FROM `user` WHERE `user`.`deleted_at` IS NULL GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `user` WHERE name LIKE 'user%')
subQuery := ("AVG(age)").Where("name LIKE ?", "user%").Table("user")
result = (&User{}).Select("AVG(age) as avgage").Group("name").Having("AVG(age) > (?)", subQuery).Find(&avgages)

HavingThe method signature is as follows:

func (db *DB) Having(query interface{}, args ...interface{}) (tx *DB) 

The second parameter is a genericinterface{}, so not only can you receive strings, GORM determines its type as*When , a subquery is constructed.

renew

In order to explain the update operation, we need to query a record first, and the subsequent update operation is based on this queryUserObject:

var user User
// SELECT * FROM `user` WHERE `user`.`deleted_at` IS NULL ORDER BY `user`.`id` LIMIT 1
result := (&user)

Update operation onlyUserThe property of the object and then call(&user)The method can be completed:

 = "John"
 = 20
// UPDATE `user` SET `created_at`='2023-05-22 22:14:47.814',`updated_at`='2023-05-22 22:24:34.201',`deleted_at`=NULL,`name`='John',`email`='u1@',`age`=20,`birthday`='2023-05-22 22:14:47.813',`member_number`=NULL,`activated_at`=NULL WHERE `user`.`deleted_at` IS NULL AND `id` = 1
result = (&user)

When updating the operation,UserThe object must be guaranteedIDThe attribute has a value, otherwise it will become a creation operation.

SaveThe method saves all fields, even if the field is a zero value of the corresponding type.

In addition to usingSaveMethod updates all fields, we can also useUpdateMethod updates the specified field:

// UPDATE `user` SET `name`='Jianghushinian',`updated_at`='2023-05-22 22:24:34.215' WHERE `user`.`deleted_at` IS NULL AND `id` = 1
result = (&user).Update("name", "Jianghushinian")

UpdateOnly support updating a single field. If you want to update multiple fields, you can use it.Updatesmethod:

// UPDATE `user` SET `updated_at`='2023-05-22 22:29:35.19',`name`='JiangHu' WHERE `user`.`deleted_at` IS NULL AND `id` = 1
result = (&user).Updates(User{Name: "JiangHu", Age: 0})

Notice,UpdatesMethods andSaveThere is a big difference in the method, it only updates the non-zero value field.AgeThe field has a zero value, so it will not be updated.

If you have to update the zero value field, you can use the aboveSaveMethod, you can alsoUserChange the structure tomap[string]interface{}Type ofmapObject:

// UPDATE `user` SET `age`=0,`name`='JiangHu',`updated_at`='2023-05-22 22:29:35.623' WHERE `user`.`deleted_at` IS NULL AND `id` = 1
result = (&user).Updates(map[string]interface{}{"name": "JiangHu", "age": 0})

In addition, when updating data, you can also use itTo implement SQL expressions:

// UPDATE `user` SET `age`=age + 1,`updated_at`='2023-05-22 22:24:34.219' WHERE `user`.`deleted_at` IS NULL AND `id` = 1
result = (&user).Update("age", ("age + ?", 1))

("age + ?", 1)Method calls will be converted toage=age + 1SQL expressions.

delete

AvailableDeleteMethod to delete the number record:

var user User
// UPDATE `user` SET `deleted_at`='2023-05-22 22:46:45.086' WHERE name = 'JiangHu' AND `user`.`deleted_at` IS NULL
result := ("name = ?", "JiangHu").Delete(&user)

For deletion operations, GORM uses a logical deletion policy by default and does not physically delete records.

soDeleteWhen the method deletes the data, it actually executes SQLUPDATEOperation, notDELETEoperate.

Willdeleted_atThe field is updated to the current time, indicating that the current data has been deleted. This is also why the generated SQL statements are automatically appended when explaining query and update in the previous article.deleted_at IS NULLWhere the reasons for the condition.

This realizes logical deletion, and the data still exists in the database, but it will be filtered out when querying and updating.

After the record is deleted, we cannot directly query the log that has been logically deleted through the following code:

// SELECT * FROM `user` WHERE name = 'JiangHu' AND `user`.`deleted_at` IS NULL ORDER BY `user`.`id` LIMIT 1
result = ("name = ?", "JiangHu").First(&user)
if err := ; err != nil {
	(err) // record not found
}

This will get an errorrecord not found。

However, GORM providesUnscopedMethods, you can bypass logical deletion:

// SELECT * FROM `user` WHERE name = 'JiangHu' ORDER BY `user`.`id` LIMIT 1
result = ().Where("name = ?", "JiangHu").First(&user)

The above code can query the records that have been deleted logically, and the generated SQL statement does not contain it.deleted_at IS NULLWhere conditions.

For more important data, it is recommended to use logical deletion, so that data can be restored when needed and facilitate fault tracking.

However, if you explicitly want to physically delete a record, you can use itUnscopedmethod:

// DELETE FROM `user` WHERE name = 'JiangHu' AND `user`.`id` = 1
result = ().Where("name = ?", "JiangHu").Delete(&user)

Related

In daily development, in most cases, not only a single table is operated, but also a multi-table with associated relationships.

Here we take the three most common tables in a blog system, "Article Table, Comment Table, and Tag Table" as an example, and explain how GORM operates the association table.

The most common associations are involved here: one-to-many and many-to-many. An article can have multiple comments, so the article and comment are a one-to-many relationship; an article can have multiple tags, and each tag can also contain multiple posts, so the article and tag are a many-to-many relationship.

The model is defined as follows:

type Post struct {
	
	Title    string     `gorm:"column:title"`
	Content  string     `gorm:"column:content"`
	Comments []*Comment `gorm:"foreignKey:PostID;constraint:OnUpdate:CASCADE,OnDelete:SET NULL;references:ID"`
	Tags     []*Tag     `gorm:"many2many:post_tags"`
}
func (p *Post) TableName() string {
	return "post"
}
type Comment struct {
	
	Content string `gorm:"column:content"`
	PostID  uint   `gorm:"column:post_id"`
	Post    *Post
}
func (c *Comment) TableName() string {
	return "comment"
}
type Tag struct {
	
	Name string  `gorm:"column:name"`
	Post []*Post `gorm:"many2many:post_tags"`
}
func (t *Tag) TableName() string {
	return "tag"
}

In the model definition,PostArticle model usageCommentsandTagsSave the associated comments and tags separately, and these two fields are not saved in the database table.

CommentsField tag usageforeignKeyLet's indicateCommentsForeign keys in the table and useconstraintThe constraints are specified,referencesIndicateCommentsTable foreign key referencePostTable ofIDField.

In fact, foreign keys are no longer recommended in production environments, and there are no database-level foreign key constraints between each table. When doing CRUD operations, business constraints are all performed through the code level. To demonstrate the foreign key and cascade operation functions of GORM, these structure tags are defined here.

TagsField tag usagemany2manyTo indicate the name of a many-to-many association table.

forCommentModel,PostIDFields are foreign keys to save。PostFields are also not stored in the database, which is very common in ORM frameworks.

Next, I will also explain the CRUD operations of the association table one by one.

create

createPostIt will be automatically created when it is associated with itCommentsandTags：

var post Post
post = Post{
	Title:   "post1",
	Content: "content1",
	Comments: []*Comment{
		{Content: "comment1", Post: &post},
		{Content: "comment2", Post: &post},
	},
	Tags: []*Tag{
		{Name: "tag1"},
		{Name: "tag2"},
	},
}
result := (&post)

Here is a post objectpost, and contains two comments and two tags.

NoticeCommentofPostField referenced&post, not specifiedPostIDForeign key field, GORM can handle it correctly.

The above code will generate and execute the following SQL statements in turn:

BEGIN TRANSACTION;
INSERT INTO `tag` (`created_at`,`updated_at`,`deleted_at`,`name`) VALUES ('2023-05-22 22:56:52.923','2023-05-22 22:56:52.923',NULL,'tag1'),('2023-05-22 22:56:52.923','2023-05-22 22:56:52.923',NULL,'tag2') ON DUPLICATE KEY UPDATE `id`=`id`
INSERT INTO `post` (`created_at`,`updated_at`,`deleted_at`,`title`,`content`) VALUES ('2023-05-22 22:56:52.898','2023-05-22 22:56:52.898',NULL,'post1','content1') ON DUPLICATE KEY UPDATE `id`=`id`
INSERT INTO `comment` (`created_at`,`updated_at`,`deleted_at`,`content`,`post_id`) VALUES ('2023-05-22 22:56:52.942','2023-05-22 22:56:52.942',NULL,'comment1',1),('2023-05-22 22:56:52.942','2023-05-22 22:56:52.942',NULL,'comment2',1) ON DUPLICATE KEY UPDATE `post_id`=VALUES(`post_id`)
INSERT INTO `post_tags` (`post_id`,`tag_id`) VALUES (1,1),(1,2) ON DUPLICATE KEY UPDATE `post_id`=`post_id`
COMMIT;

It can be found that comments that form a one-to-many relationship with the article and labels that form a many-to-many relationship with the article will be created, and GORM maintains its association, and all of these operations are completed under one transaction.

In addition, the previous articleSaveThe method not only can update records, but it actually supports creating records whenPostThe object does not have a primary keyIDhour,SaveThe method will create a new record:

var post3 Post
post3 = Post{
	Title:   "post3",
	Content: "content3",
	Comments: []*Comment{
		{Content: "comment33", Post: &post3},
	},
	Tags: []*Tag{
		{Name: "tag3"},
	},
}
result = (&post3)

The SQL generated by the above code is as follows:

BEGIN TRANSACTION;
INSERT INTO `tag` (`created_at`,`updated_at`,`deleted_at`,`name`) VALUES ('2023-05-22 23:17:53.189','2023-05-22 23:17:53.189',NULL,'tag3') ON DUPLICATE KEY UPDATE `id`=`id`
INSERT INTO `post` (`created_at`,`updated_at`,`deleted_at`,`title`,`content`) VALUES ('2023-05-22 23:17:53.189','2023-05-22 23:17:53.189',NULL,'post3','content3') ON DUPLICATE KEY UPDATE `id`=`id`
INSERT INTO `comment` (`created_at`,`updated_at`,`deleted_at`,`content`,`post_id`) VALUES ('2023-05-22 23:17:53.19','2023-05-22 23:17:53.19',NULL,'comment33',0) ON DUPLICATE KEY UPDATE `post_id`=VALUES(`post_id`)
INSERT INTO `post_tags` (`post_id`,`tag_id`) VALUES (0,0) ON DUPLICATE KEY UPDATE `post_id`=`post_id`
COMMIT;

Query

You can use the following method according toPostofIDQuery the associatedComments：

var (
	post     Post
	comments []*Comment
)
 = 1
// SELECT * FROM `comment` WHERE `comment`.`post_id` = 1 AND `comment`.`deleted_at` IS NULL
err := (&post).Association("Comments").Find(&comments)

Note⚠️: Pass toAssociationThe parameters of the method areComments, that is,PostFields defined in the model, not the model name of the commentComment. Don't make any mistakes in this, otherwise an error will be reported when executing SQL.

Postis the source model, primary keyIDCan't be empty.AssociationThe method specifies the associated field name, inPostUse of associated comments in the modelCommentsexpress. Last usedFindMethod to query associated comments.

InquiryPostWhen we can preload the associatedComments：

post2 := Post{}
result := ("Comments").Preload("Tags").First(&post2)
(post2)
for i, comment := range  {
	(i, comment)
}
for i, tag := range  {
	(i, tag)
}

We can use it as usualFirstA method queryPostRecord and use it togetherPreloadMethod to specify the preloaded associated field name, so in queryPostWhen recording, all records in the associated field table will be queryed and assigned to the associated field.

The above code will be executed as follows SQL:

BEGIN TRANSACTION;
SELECT * FROM `post` WHERE `post`.`deleted_at` IS NULL ORDER BY `post`.`id` LIMIT 1
SELECT * FROM `comment` WHERE `comment`.`post_id` = 1 AND `comment`.`deleted_at` IS NULL
SELECT * FROM `post_tags` WHERE `post_tags`.`post_id` = 1
SELECT * FROM `tag` WHERE `tag`.`id` IN (1,2) AND `tag`.`deleted_at` IS NULL
COMMIT;

GORM queries all associated records through multiple SQL statements and associates them.CommentsandTagsAssign toPostThe corresponding fields of the model.

When encountering multi-table queries, we usually useJOINTo connect multiple tables:

type PostComment struct {
	Title   string
	Comment string
}
postComment := PostComment{}
post3 := Post{}
 = 3
// SELECT ,  AS comment FROM `post` LEFT JOIN comment ON comment.post_id =  WHERE `post`.`deleted_at` IS NULL AND `post`.`id` = 3
result := (&post3).Select(",  AS comment").Joins("LEFT JOIN comment ON comment.post_id = ").Scan(&postComment)

useSelectMethod to specify the fields to be queried, usingJoinsMethod to implementJOINFunction, final useScanMethods can scan the query results topostCommentIn the object.

For one-to-many relationships,JoinsThe method also supports preloading:

var comments2 []*Comment
// SELECT `comment`.`id`,`comment`.`created_at`,`comment`.`updated_at`,`comment`.`deleted_at`,`comment`.`content`,`comment`.`post_id`,`Post`.`id` AS `Post__id`,`Post`.`created_at` AS `Post__created_at`,`Post`.`updated_at` AS `Post__updated_at`,`Post`.`deleted_at` AS `Post__deleted_at`,`Post`.`title` AS `Post__title`,`Post`.`content` AS `Post__content` FROM `comment` LEFT JOIN `post` `Post` ON `comment`.`post_id` = `Post`.`id` AND `Post`.`deleted_at` IS NULL WHERE `comment`.`deleted_at` IS NULL
result = ("Post").Find(&comments2)
for i, comment := range comments2 {
	(i, comment)
	(i, )
}

JOINPreloading of functions does not require explicit usePreloadTo indicate, justJoinsIn the method, we specify one-to-many relationship modelPostJust useFindQueryCommentRecord.

According to the generated SQL, you can find that the query main table iscomment, the secondary table ispost. And the fields of the secondary table have been renamed toModel name__field nameFormat, such asPost__title(Offtopic: If you have used Python's Django ORM framework, you should feel familiar with this double-underlined name field).

renew

Just like when explaining the update of a single table, we need to query a record to demonstrate the update operation:

var post Post
// SELECT * FROM `post` WHERE `post`.`deleted_at` IS NULL ORDER BY `post`.`id` LIMIT 1
result := (&post)

You can use the following method to replace itPostRelatedComments：

comment := Comment{
	Content: "comment3",
}
err := (&post).Association("Comments").Replace([]*Comment{&comment})

Still useAssociationMethod SpecificationPostRelatedComments，ReplaceMethods are used to complete replacement operations.

Pay attention here,ReplaceThe method returns the result no longer*object, but return directlyerror。

Generate SQL as follows:

BEGIN TRANSACTION;
INSERT INTO `comment` (`created_at`,`updated_at`,`deleted_at`,`content`,`post_id`) VALUES ('2023-05-23 09:07:42.852','2023-05-23 09:07:42.852',NULL,'comment3',1) ON DUPLICATE KEY UPDATE `post_id`=VALUES(`post_id`)
UPDATE `post` SET `updated_at`='2023-05-23 09:07:42.846' WHERE `post`.`deleted_at` IS NULL AND `id` = 1
UPDATE `comment` SET `post_id`=NULL WHERE `comment`.`id` <> 8 AND `comment`.`post_id` = 1 AND `comment`.`deleted_at` IS NULL
COMMIT;

delete

useDeleteWhen deleting article tables, the data of the associated tables will not be deleted:

var post Post
// UPDATE `post` SET `deleted_at`='2023-05-23 09:09:58.534' WHERE id = 1 AND `post`.`deleted_at` IS NULL
result := ("id = ?", 1).Delete(&post)

For records with associated relationships, the same is true when deleting them.UPDATEOperation and do not affect the associated data.

If you want to delete the relationship with the article when deleting a comment, you can useAssociationmethod:

// UPDATE `comment` SET `post_id`=NULL WHERE `comment`.`post_id` = 6 AND `comment`.`id` IN (NULL) AND `comment`.`deleted_at` IS NULL
err := (&post2).Association("Comments").Delete()

Transactions

GORM provides support for transactions, which is necessary in complex business logic.

To perform a series of operations in a transaction, you can useTransactionMethod implementation:

func TransactionPost(db *) error {
	return (func(tx *) error {
		post := Post{
			Title: "Hello World",
		}
		if err := (&post).Error; err != nil {
			return err
		}
		comment := Comment{
			Content: "Hello World",
			PostID:  ,
		}
		if err := (&comment).Error; err != nil {
			return err
		}
		return nil
	})
}

existTransactionThe code inside the method will be processed in a transaction.TransactionThe method receives a function whose parameters aretx *, all database operations in the transaction should use thistxInsteaddb。

In the function that executes the transaction, if any error is returned, the entire transaction will be rolled back,nilThen the transaction is committed.

In addition to usingTransactionAutomatically manage transactions, we can also manage transactions manually:

func TransactionPostWithManually(db *) error {
	tx := ()
	post := Post{
		Title: "Hello World Manually",
	}
	if err := (&post).Error; err != nil {
		()
		return err
	}
	comment := Comment{
		Content: "Hello World Manually",
		PostID:  ,
	}
	if err := (&comment).Error; err != nil {
		()
		return err
	}
	return ().Error
}

()Used to start a transaction and returntx, this should be used for later transaction operationstxObject. If you encounter an error during the transaction, you can use()Roll back the transaction, if there is no problem, you can finally use it()Submit transaction.

Note: Manual transactions, once the transaction starts, you should usetxProcess database operations.

hook

GORM also supports the Hook function. Hook is a function called before and after creation, query, update, delete and other operations, and is used to manage the life cycle of an object.

The function signature of the hook method isfunc(*) error, for example, the following hook function is triggered before creating an operation:

func (u *User) BeforeCreate(tx *) (err error) {
	 = ()
	if  == "admin" {
		return ("invalid name")
	}
	return nil
}

For example, weUserModel definitionBeforeCreateHook, this is createdUserBefore the object, GORM will automatically call this function, and theUserObject creationUUIDAnd username legality verification function.

The hook functions supported by GORM and the execution timing are as follows:

Native SQL

Although we use ORM frameworks to convert native SQL writing to object-oriented programming, support for native SQL is a must-have feature of ORM frameworks.

Can be usedRawMethods execute native query SQL and add resultsScanTo the model:

var userRes UserResult
(`SELECT id, name, age FROM user WHERE id = ?`, 3).Scan(&userRes)
("affected rows: %d\n", )
()
(userRes)

Native SQL also supports the use of expressions:

var sumage int
(`SELECT SUM(age) as sumage FROM user WHERE member_number ?`, ("IS NULL")).Scan(&sumage)

In addition, we can also useExecExecute any native SQL:

("UPDATE user SET age = ? WHERE id IN ?", 18, []int64{1, 2})
// Use expressions(`UPDATE user SET age = ? WHERE name = ?`, ("age * ? + ?", 1, 2), "Jianghu")
// Delete the table("DROP TABLE user")

useExecThe execution result cannot be obtained, and it can be used to operate on the table, such as adding and deleting tables, etc.

Supports use when writing SQL@nameSyntax naming parameters:

("UPDATE user SET age = ? WHERE id IN ?", 18, []int64{1, 2})
// Use expressions(`UPDATE user SET age = ? WHERE name = ?`, ("age * ? + ?", 1, 2), "Jianghu")
// Delete the table("DROP TABLE user")

useDryRunThe mode can directly obtain native SQL generated by GORM without executing it, making it convenient for subsequent use:

var post Post
("title LIKE @name OR content LiKE @name", ("name", "%Hello%")).Find(&post)
var user User
// SELECT * FROM user WHERE name1 = "Jianghu" OR name2 = "shinian" OR name3 = "Jianghu"
("SELECT * FROM user WHERE name1 = @name OR name2 = @name2 OR name3 = @name",
   ("name", "Jianghu"), ("name2", "shinian")).Find(&user)

DryRunThe pattern can be translated as empty run, meaning that it does not execute real SQL, which is very useful when debugging.

debug

We have basically explained the common functions of GORM. Finally, let’s introduce how to debug when encountering problems in daily development.

I have summarized the GORM debugging method as follows 5 points:

• Global logging

Remember when connecting to the databaseIs the second parameter of the method? We passed an empty configuration at that time&{}, This optional parameter can change some default functional configurations of GORM, such as we can set the log level toInfo, so that all executed SQL statements can be printed on the console:

db, err := ((dsn), &{
	Logger:(),
})

• Print Slow Query SQL

Sometimes a certain segment of ORM code executes very slowly. We can detect slow query statements in SQL by turning on the slow query log:

func ConnectMySQL(host, port, user, pass, dbname string) (*, error) {
	slowLogger := (
		(, "\r\n", ),
		{
			// Set the slow query time threshold to 3ms (default: 200 * )			SlowThreshold: 3 * ,
			// Set log level			LogLevel: ,
		},
	)
	dsn := ("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		user, pass, host, port, dbname)
	return ((dsn), &{
		Logger: slowLogger,
	})
}

• Print the specified SQL

useDebugCan print SQL that is executed by the current ORM statement:

().First(&User{}) 

• Open the DryRun model globally

When connecting to the database, we can turn on the "Empty Run" mode globally:

db, err := ((dsn), &{ DryRun: true, }) 

After turning on the DryRun model, no SQL statement will be truly executed, which is convenient for testing.

• Locally open the DryRun model

In the currentSessionThe "Empty Run" model is partially enabled, and SQL and its parameters can be generated without performing operations, which are used to prepare or test generated SQL:

var user User
stmt := (&{DryRun: true}).First(&user, 1).Statement
(()) // => SELECT * FROM `users` WHERE `id` = $1 ORDER BY `id`
()         // => []interface{}{1}

Summarize

This article explains GORM, the most popular ORM framework in the Go language, and introduces how to write models, how to connect to databases, and the most commonly used CRUD operations. It also explains the one-to-many and many-to-many relationship operations in the association table. We also introduced the essential function "transactions". GORM also provides hook functions to facilitate us to insert some custom logic before and after CRUD operations. Finally, we also introduce how to use native SQL and how to debug.

As long as you have a solid foundation in native SQL, it will not be too laborious to learn the ORM framework, and we also have various debugging methods to print the SQL generated by GORM to facilitate troubleshooting.

The above is the detailed introduction to the use of ORM framework GORM in Go language. For more information about Go ORM framework GORM, please pay attention to my other related articles!