Golang后端大师班学习笔记 — 4.从SQL中生成Golang CRUD代码

本节将学习使用Golang来做CRUD操作。

这里的CRUD指的是什么？

• C 是 Create，代表新建或向数据库插入新记录
• R 是 Read, 从数据库中检索记录
• U 是 Update，改变数据库中记录的内容
• D 是 Delete，从数据库中删除记录。

在Golang中，有几种实现 CRUD 操作的方法。

1. 使用 low-level 标准库 database/sql

在官方文档 https://pkg.go.dev/database/sql#DB.QueryContext 中，可以看到如下代码示例：

package main

import (
	"context"
	"database/sql"
	"log"
	"time"
)

var (
	ctx context.Context
	db  *sql.DB
)

func main() {
	id := 123
	var username string
	var created time.Time
	err := db.QueryRowContext(ctx, "SELECT username, created_at FROM users WHERE id=?", id).Scan(&username, &created)
	switch {
	case err == sql.ErrNoRows:
		log.Printf("no user with id %d\n", id)
	case err != nil:
		log.Fatalf("query error: %v\n", err)
	default:
		log.Printf("username is %q, account created on %s\n", username, created)
	}
}

这里只使用QueryRowContext()函数，并传入原始的SQL查询的参数，db.QueryRowContext(ctx, "SELECT username, created_at FROM users WHERE id=?", id).Scan(&username, &created)，然后将结果保存到目标变量中。

这种方法的主要优点是：

• 运行速度快
• 代码编写起来简单

缺点是：

• 必须手动将SQL字段映射到变量，非常容易出错，如果变量的顺序不匹配，或者忘记将一些参数传递给函数调用，错误就只会在运行时出现。

2. 使用 high-level 的 GORM

它是 Golang 的对象关系映射库。

优点:

• 使用起来简单，CRUD操作都已经内部实现了，产生的代码会很短，只需要声明模型，并调用 GORM 提供的函数就可以了。

缺点:

• 必须学习如何使用 GORM 提供的函数实现查询，特别是复杂的联表查询
• 当流量大的时候速度会变慢，网上有些测试(benchmarks) GORM 比标准库慢 3-5 倍

示例代码：

user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}

result := db.Create(&user)

db.First(&user)

3. SQLX

优点：

• 速度几乎和标准库一样快，使用也非常简单
• 字段映射是通过查询文本或结构标签完成的

它提供一些函数，比如：Select()或StructScan(),它们会自动将结果扫描到struct结构的字段中，因此，不需要像使用database/sql那样手动进行映射，这将有助于缩短代码，并减少潜在的错误，但是我们写的代码还是比较长的。

缺点:

• 错误只会在运行时捕获

示例代码:

err = db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")
    if err != nil {
        fmt.Println(err)
        return
    }
    usa, singsing, honkers := places[0], places[1], places[2]
    
    fmt.Printf("%#v\n%#v\n%#v\n", usa, singsing, honkers)
    // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}
    // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}
    // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}

    // Loop through rows using only one struct
    place := Place{}
    rows, err := db.Queryx("SELECT * FROM place")
    for rows.Next() {
        err := rows.StructScan(&place)
        if err != nil {
            log.Fatalln(err)
        } 
        fmt.Printf("%#v\n", place)
    }

4. SQLC

优点:

• 运行速度快，像 database/sql 一样，因为生成的代码就是使用database/sql；使用简单
• 只需要编写 SQL 查询语句，就会自动生成Golang代码
• 有任何错误都会立即捕获，而不需要等到运行时才知道

缺点：

• 目前只支持 mysql 和 postgres 数据库

建议： 如果使用mysql 或 postgres ，选择 SQLC ，否则，选择 SQLX。对性能要求不高的应用，使用 GORM 。课程中使用了SQLC

安装和使用SQLC

1. 安装SQLC

首先，打开SQLC的官网，https://sqlc.dev/,找到文档的链接，https://docs.sqlc.dev/en/latest/overview/install.html，这里使用的是MAC，所以用如下命令安装：

brew install sqlc

安装后，可以使用sqlc version，查看安装的版本；sqlc help，查看命令帮助。

• compile 编译命令，用于检查SQL语法和类型错误
• generate 最重要的命令，生成 ,它将为我们检查语法错误，并从SQL语句中生成Golang 代码
• init 用来创建一个空的 sqlc.yaml 配置文件

2. 使用SQLC生成Golang代码

让我们进入之前的银行项目目录simplebank(https://blog.csdn.net/8665048/article/details/124006088),运行 sqlc init

sqlc init

在 vscode 中，就可以看到它创建了一个 sqlc.yaml 文件

打开文档，https://docs.sqlc.dev/en/latest/tutorials/getting-started-postgresql.html，可以看到：

我们复制它，替换自动生成的sqlc.yaml文件内容。
其中，

• name表示，将生成的Go包名字是什么，把它改成db
• path 指定存放生成的 Golang 代码的目录，在我们的项目db目录下，新建子目录sqlc,这里的path，修改为 ./db/sqlc
• queries 指定在哪里查找 SQL 查询语句，在我们的项目db目录下，新建子目录query,这里的queries, 修改为 ./db/query/
• schema,包含数据库迁移文件的目录，这里，我们改成 ./db/migration/
• engine 表示我们使用什么数据库，这里是 postgresql, 不去动它。
• 另外，增加 emit_json_tags，设置为 true, 将 JSON 标记添加到生成的结构体中。
  改好的配置文件内容如下：

version: 1
packages:
  - path: "./db/sqlc"
    name: "db"
    engine: "postgresql"
    schema: "./db/migration/"
    queries: "./db/query/"
    emit_json_tags: true

目录结构如下：

打开终端，执行

sqlc generate

会发现如下错误：

error parsing queries: no queries contained in paths /goproject/simplebank/db/query

因为，query 目录里还没有查询语句文件，稍后我们来写。

现在，先在Makefile文件里添加一个新的命令 sqlc, 它将帮助我们的团队成员，在一个地方找到所有用于开发的命令。改完如下：

postgres:
	docker run --name postgres14 -e POSTGRES_PASSWORD=123456 -e POSTGRES_USER=root -p 5432:5432 -d postgres:14-alpine

createdb:
	docker exec -it postgres14 createdb --username=root --owner=root simple_bank

dropdb:
	docker exec -it postgres14 dropdb simple_bank

migrateup:
	migrate --path db/migration --database="postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose up

migratedown:
	migrate --path db/migration --database="postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose down

sqlc:
	sqlc generate

.PHONY: postgres, createdb, dropdb, migrateup, migratedown, sqlc

接下来，编写第一个SQL语句来创建一个account,在项目的db/query目录下，新建一个account.sql文件，在 SQLC 的文档中找到这段，复制到 account.sql 文件中

这是一条基础的INSERTSQL语句，需要注意的是上面的注释-- name: CreateAuthor :one,该注释将会让 SQLC 如何为此SQL语句生成 Golang 的函数名称，这里我们改成CreateAccount，one表示返回1个Account对象。改完如下：

-- name: CreateAccount :one
INSERT INTO accounts (
  owner, 
  balance,
  currency
) VALUES (
  $1, $2, $3
)
RETURNING *;

最后 RETURNING * 表示创建Account后，返回所有字段的内容。
然后，我们在终端执行：

make sqlc

可以看到它执行成功了，没有错误，这时，可以在项目的db/sqlc目录里生成好了3个文件，account.sql.go、db.go和models.go。

• 可以看到models.go里面的3个结构体，映射我们的数据表，JSON标签也有了，注释也有了，注释用的是之前我们建表时里面的注释，并且结构体的命名自动把复数变成了单数。
• db.go里面定义了DB操作的接口方法。
• account.sql.go 其中的 package名称db,是之前我们配置指定的，其中的createAccount把之前写的RETURNING *变成了 RETURNING id, owner, balance, currency, created_at,这样可以让查询语句更清晰。

CreateAccountParams结构体有了我们在创建新账户时需要的所有字段。

type CreateAccountParams struct {
	Owner    string `json:"owner"`
	Balance  int64  `json:"balance"`
	Currency string `json:"currency"`
}

CreateAccount方法定义了一个Queries为接收者，返回Account或者错误,主要参数是CreateAccountParams

func (q *Queries) CreateAccount(ctx context.Context, arg CreateAccountParams) (Account, error) {
	row := q.db.QueryRowContext(ctx, createAccount, arg.Owner, arg.Balance, arg.Currency)
	var i Account
	err := row.Scan(
		&i.ID,
		&i.Owner,
		&i.Balance,
		&i.Currency,
		&i.CreatedAt,
	)
	return i, err
}

这时，我们看到代码里面有红色下划线报错：

是因为，我们还没有为项目初始化模块，在项目下打开终端，执行

go mod init simplebank

再看account.sql.go,所有的报错提示已经没有了。

可以看到，生成的代码，最终是使用database/sql，而不需要我们手动拼接这些参数，赞。而且，它会在生成代码之前检查SQL语句的语法，以避免写SQL时出现低级错误。

特别注意，我们不要手动修改SQLC生成的go文件内容，因为，我们每次运行make sqlc时，这些文件都会重新生成，如果我们在这里修改了内容，它会被重新覆盖掉。

3. READ 读取操作

在SQLC的官方文档中，可以看到，2个基本的数据查询操作：Get和List

把它复制到我们的account.sql文件中，并做修改，如下：

-- name: CreateAccount :one
INSERT INTO accounts (
  owner, 
  balance,
  currency
) VALUES (
  $1, $2, $3
)
RETURNING *;

-- name: GetAccount :one
SELECT * FROM accounts
WHERE id = $1 LIMIT 1;

-- name: ListAccounts :many
SELECT * FROM accounts
ORDER BY id
LIMIT $1
OFFSET $2;

获取列表数据时，不需要一次把所有的记录查询出来，这里做分页处理，增加了LIMIT和OFFSET，之后，我们在终端运行make sqlc重新生成代码，再次打开account.sql.go，可以看到多生成了GetAccount和ListAccounts,SELECT *也被替换成了SELECT id, owner, balance, currency, created_at

4. UPDATE 更新操作

打开SQLC关于UPDATE的文档，https://docs.sqlc.dev/en/latest/howto/update.html，可以看到：

把它复制到我们的account.sql文件中，并做修改，如下：

-- name: CreateAccount :one
INSERT INTO accounts (
  owner, 
  balance,
  currency
) VALUES (
  $1, $2, $3
)
RETURNING *;

-- name: GetAccount :one
SELECT * FROM accounts
WHERE id = $1 LIMIT 1;

-- name: ListAccounts :many
SELECT * FROM accounts
ORDER BY id
LIMIT $1
OFFSET $2;

-- name: UpdateAccount :exec
UPDATE accounts SET balance = $2 WHERE id = $1;

再次运行make sqlc,之后account.sql.go又多了个UpdateAccount

func (q *Queries) UpdateAccount(ctx context.Context, arg UpdateAccountParams) error {
	_, err := q.db.ExecContext(ctx, updateAccount, arg.ID, arg.Balance)
	return err
}

有时候，我们需要得到更新后的结果，就需要把更新后的结果返回出来，这里再修改一下SQL语句，:exec改为:one，并在最后增加RETURNING *,如下：

-- name: UpdateAccount :one
UPDATE accounts SET balance = $2 WHERE id = $1
RETURNING *;

重新生成代码，make sqlc,可以看到account.sql.go里面的UpdateAccount有返回值了。

5. DELETE 删除操作

打开SQLC关于删除的文档链接，https://docs.sqlc.dev/en/latest/howto/delete.html，可以看到：

复制它到我们的account.sql文件中，并做修改，如下：

-- name: DeleteAccount :exec
DELETE FROM accounts WHERE id = $1;

之后，运行make sqlc,account.sql.go文件中已经有了新增的DeleteAccount，这样一个完整的CRUD便完成了。还有两个表entries和transfers，可以作为练习，自己实现一下CRUD。

下一节，我们将学习如何，Golang使用随机数据为数据库的CRUD写单元测试。