“This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!”
For detailed official document translation, see SQLBoiler official document translation – the most useful Go ORM framework
preface
Recently, I investigated several star-heavy ORM frameworks on Github. Although they are fully functional, there are some problems in using them, such as syntax confusion, syntax redundancy, and most importantly, the need to get return values through Pointers. In the process of research, I found a mythboiler framework, SQLBoiler, which returns struct objects directly as a result of queries, optionally returns ERR, and has a fully functional and unambiguous SQL conditional constructor, which is a Go version of MybatisPlus.
However, SQLBoiler official does not provide Chinese documents, domestic technical communities do not have relevant introduction teaching, so while learning the official English documents while writing the introduction guide.
This article will only cover the SQLBoiler code generation process and simple add, delete, and change operations; the detailed functionality will be covered in subsequent articles.
Code generation
MySQL is used as the database here; other database configurations and dependencies may vary.
Environmental requirements
- Go above 1.13
- The serpentine nomenclature is used for list names
snake_case
Database table structure
CREATE TABLE `user` (
`id` bigint(0) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`nick_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT ' ',
`created_at` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
`updated_at` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
Copy the codeStep 1: Download and install the code generation plug-in
# Go 1.16And after:go install github.com/volatiletech/sqlboiler/v4@latest
go install github.com/volatiletech/sqlboiler/v4/drivers/sqlboiler-mysql@latest
# Go 1.15And before: GO111MODULE=ongo get -u -t github.com/volatiletech/sqlboiler/v4
GO111MODULE=on go get github.com/volatiletech/sqlboiler/v4/drivers/sqlboiler-mysql
Copy the codeNote: If you are using a different database, you need to change the name of mysql above to that of the corresponding database (if supported).
Step 2: Install the dependencies
go get github.com/volatiletech/sqlboiler/v4
go get github.com/volatiletech/null/v8
Copy the codeStep 3: Configure the file
SQLBoiler automatically recognizes the SQLBoiler. Toml configuration file in the root path. The contents are as follows:
wipe = true # Delete the files generated automatically before re-generating
no-tests = true # Do not generate test code
add-global-variants = true # Generate methods that use global data sources, i.e., methods with a G suffix
add-panic-variants = true # generation uses methods that panic when error is not nil, i.e. methods with a P suffix
no-context = true # No context parameters are required
[mysql]
dbname = "dbname"
host = "127.0.0.1"
user = "root"
pass = "root"
sslmode = "false"
Copy the codeNote: If you use other databases, the configuration may be different, see the official document for detailed configuration items.
Step 4: Generate code
To generate the code in the Image Models directory, type the following command on the command line:
sqlboiler mysql
Copy the code
Add, delete, check and change examples
Setting the data source
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/volatiletech/sqlboiler/v4/boil"
"github.com/volatiletech/sqlboiler/v4/queries/qm"
"log"
"sqlboiler_test/models"
)
func main(a) {
// Create the data source
db, err := sql.Open("mysql"."Root: a root @ TCP/tbname (127.0.0.1:3306)? parseTime=True")
iferr ! =nil {
log.Fatal(err)
}
// Set the global data source
boil.SetDB(db)
}
Copy the codeInsert
func InsertUserDemo(a) {
user := models.User{
Username: "username1",
Password: "123456",
NickName: "nickName1",
}
user.InsertGP(boil.Infer())
fmt.Printf("%+v", user)
}
/ / output
{ID:7 Username:username1 Password:123456 NickName:nickName1 CreatedAt:202107 -18 13:34:16.3409056 +0000 UTC UpdatedAt:202107 -18 13:34:16.3409056 +0000 UTC R:<nil> L:{}}
Copy the codeAs you can see, the insert takes just one line of user.insertgp (dial.infer ()), which automatically sets the values generated by ID, CreateAt, and UpdatedAt. Boil.Infer() is the intelligent selection insert field. Go zero value field will not be selected, but these fields will be set according to the value generated by the database after insertion (such as auto-increment primary key, default account, update time, creation time, etc.).
InsertGP() ‘s GP representation uses the global data source (G) and does not return error (P, direct panic if error is not nil). The R and L fields are automatically generated by the SQLBoiler plug-in and can now be ignored.
Delete
func DeleteUserDemo(a) {
// Delete according to user.id
user := models.User{ID: 1}
count := user.DeleteGP()
fmt.Println(count)
// Delete batches according to UserSlice
users := models.Users().AllGP()
count = users.DeleteAllGP()
fmt.Println(count)
// Delete according to the condition
count, _ = models.Users(models.UserWhere.Username.EQ("username1")).DeleteAllG()
fmt.Println(count)
}
Copy the codeThe deletion operation also requires a row, which can be deleted according to User struct, batch deletion according to UserSlice, or conditionally. The return value is the number of rows successfully deleted.
Select
func SelectUserDemo(a) {
// Query by condition (if no condition is specified, all conditions are queried)
users := models.Users(qm.Select(models.UserColumns.ID, models.UserColumns.NickName),
models.UserWhere.Username.EQ("username1")).AllGP()
fmt.Println(users)
// Query the quantity
count := models.Users(models.UserWhere.Username.EQ("username1")).CountGP()
fmt.Println(count)
// Query by primary key
user, err := models.FindUserG(1)
if err == sql.ErrNoRows {
// Business processing
}
fmt.Println(user)
}
Copy the codeYou can query records by condition, query quantity, and query by primary key. Note that FinUserG() returns SQL.errnorows when no record is queried, so it’s a good idea to check.
Qm.select () can be used to specify the columns to query.
Update
func UpdateUserDemo(a) {
// Update based on user.id
user := models.User{ID: 1, NickName: "nickName1001"}
count := user.UpdateGP(boil.Whitelist(models.UserColumns.NickName))
fmt.Println(count)
// Batch update according to UserSlice
users := models.Users().AllGP()
count = users.UpdateAllGP(models.M{models.UserColumns.NickName: "updateAllNickName"})
fmt.Println(count)
// Update according to the condition
count, _ = models.Users(models.UserWhere.NickName.EQ("updateAllNickName")).
UpdateAllG(models.M{models.UserColumns.NickName: "updateAllNickName2"})
fmt.Println(count)
}
Copy the codeSimilar to Delete operations, updates can be made based on User struct, batch updates can be made based on UserSlice, and updates can be made based on conditions.
Note here that when updating based on a User struct, it’s a good idea to use bole.whitelist () to specify which fields to update, since using Bole.infer () will update even a zero value.
Models. M{} indicates the updated fields and their corresponding values.
The complete code
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/volatiletech/sqlboiler/v4/boil"
"github.com/volatiletech/sqlboiler/v4/queries/qm"
"log"
"sqlboiler_test/models"
)
func main(a) {
// Create the data source
db, err := sql.Open("mysql"."Root: a root @ TCP/tbname (127.0.0.1:3306)? parseTime=True")
iferr ! =nil {
log.Fatal(err)
}
// Set the global data source
boil.SetDB(db)
//InsertUserDemo()
//DeleteUserDemo()
//SelectUserDemo()
//UpdateUserDemo()
}
func InsertUserDemo(a) {
user := models.User{
Username: "username1",
Password: "123456",
NickName: "nickName1",
}
user.InsertGP(boil.Infer())
fmt.Printf("%+v", user)
}
func DeleteUserDemo(a) {
// Delete according to user.id
user := models.User{ID: 1}
count := user.DeleteGP()
fmt.Println(count)
// Delete batches according to UserSlice
users := models.Users().AllGP()
count = users.DeleteAllGP()
fmt.Println(count)
// Delete according to the condition
count, _ = models.Users(models.UserWhere.Username.EQ("username1")).DeleteAllG()
fmt.Println(count)
}
func SelectUserDemo(a) {
// Query by condition (if no condition is specified, all conditions are queried)
users := models.Users(qm.Select(models.UserColumns.ID, models.UserColumns.NickName),
models.UserWhere.Username.EQ("username1")).AllGP()
fmt.Println(users[0])
// Query the quantity
count := models.Users(models.UserWhere.Username.EQ("username1")).CountGP()
fmt.Println(count)
// Query by primary key
user, err := models.FindUserG(1)
if err == sql.ErrNoRows {
// Business processing
}
fmt.Println(user)
}
func UpdateUserDemo(a) {
// Update based on user.id
user := models.User{ID: 1, NickName: "nickName1001"}
count := user.UpdateGP(boil.Whitelist(models.UserColumns.NickName))
fmt.Println(count)
// Batch update according to UserSlice
users := models.Users().AllGP()
count = users.UpdateAllGP(models.M{models.UserColumns.NickName: "updateAllNickName"})
fmt.Println(count)
// Update according to the condition
count, _ = models.Users(models.UserWhere.NickName.EQ("updateAllNickName")).
UpdateAllG(models.M{models.UserColumns.NickName: "updateAllNickName2"})
fmt.Println(count)
}
Copy the codeFor detailed official document translation, see SQLBoiler official document translation – the most useful Go ORM framework