1. 作用
根据编写的sql语句生成go语言的调用代码
sqlc官网
2. 安装sqlc
安装方式：
| 平台 |
安装指令 |
| macOS |
brew install sqlc |
| ubuntu |
sudo snap install sqlc |
| Go>=1.17 |
go install github.com/kyleconroy/sqlc/cmd/sqlc@latest |
| Go<1.17 |
go get github.com/kyleconroy/sqlc/cmd/sqlc |
| Docker |
docker pull kjconroy/sqlc |
| downloads |
https://github.com/kyleconroy/sqlc/releases |
如果是安装文件方式安装，定义全局使用，可以将执行文件拷贝到/usr/local/bin下即可；也可以配置环境变量到/etc/profile中
3. 使用教程
根目录下的结构：
1
2
3
4
|
.
├── query.sql #编写生成代码的sql语句文件
├── schema.sql #表结构文件
└── sqlc.yaml #sqlc默认配置文件
|
查看sqlc.yaml内容配置：
1
2
3
4
5
6
7
|
version: 1
packages:
- path: "tutorial" # 指定生成文件到指定的目录名称
name: "tutorial" # 定义生成go文件的包名
engine: "mysql" # mysql,postgresql,sqlite
schema: "schema.sql" # 指定表结构文件
queries: "query.sql" # 指定查询语句文件
|
1
2
3
4
5
6
7
8
9
10
|
version: "2"
sql:
- schema: "./schema.sql"
queries: "./query.sql"
engine: "mysql"
gen:
go:
package: "authors"
out: "author"
emit_json_tags: true
|
schema.sql文件示例：
1
2
3
4
5
|
CREATE TABLE authors (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name text NOT NULL,
bio text
);
|
紧接着编写一个query.sql文件，用于绑定sql语句与代码的调用关系
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = ? LIMIT 1;
-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;
-- name: CreateAuthor :execresult
INSERT INTO authors (
name, bio
) VALUES (
?, ?
);
-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = ?;
#字段说明
-- name: funcation_name :one/many/execresult
# 对应的代码 one：返回一个数据对象
# many: 返回一个列表结合slice
# exec: 只返回执行错误的error
# execrows: 返回受影响的行数，和执行错误
# execlastid: 返回最后一个影响的行数id值，和执行的错误
# execresult: 返回执行结果，和执行错误
|
根据以上定义的内容，生成代码：
生成的文件结构
1
2
3
4
|
├── author
│ ├── db.go
│ ├── models.go
│ └── query.sql.go
|
1.全量查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
bio text NOT NULL,
birth_year int NOT NULL
);
-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = ?;
-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY id;
|
2.选择查询
1
2
3
4
5
6
7
8
9
10
11
12
13
|
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
bio text NOT NULL,
birth_year int NOT NULL
);
-- name: GetBioForAuthor :one
SELECT bio FROM authors
WHERE id = ?;
-- name: GetInfoForAuthor :one
SELECT bio, birth_year FROM authors
WHERE id = ?;
|
3.多元素参数查询写法
1
2
3
4
5
6
7
8
9
|
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
bio text NOT NULL,
birth_year int NOT NULL
);
-- name: ListAuthorsByIDs :many
SELECT * FROM authors
WHERE id IN (sqlc.slice('ids')); # 集合参数，需要sqlc.slice(paramName)处理
|
4.统计查询
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
hometown text NOT NULL
);
-- name: CountAuthors :one
SELECT count(*) FROM authors;
-- name: CountAuthorsByTown :many
SELECT hometown, count(*) FROM authors
GROUP BY 1
ORDER BY 1;
|
5.1.数据插入写法
1
2
3
4
5
6
7
|
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
bio text NOT NULL
);
-- name: CreateAuthor :exec
INSERT INTO authors (bio) VALUES (?);
|
5.2.带返回参数的数据插入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
-- Example queries for sqlc
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY,
name text NOT NULL,
bio text
);
-- name: CreateAuthor :one
INSERT INTO authors (
name, bio
) VALUES (
?, ?
)
RETURNING *;
-- name: CreateAuthorAndReturnId :one
INSERT INTO authors (
name, bio
) VALUES (
?, ?
)
RETURNING id;
|
6.更新数据操作
1
2
3
4
5
6
7
8
9
|
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
bio text NOT NULL
);
-- name: UpdateAuthorBios :exec
UPDATE authors SET bio = ?;
-- name: UpdateAuthor :exec
UPDATE authors SET bio = ?
WHERE id = ?;
|
7.删除数据
1
2
3
4
5
6
7
|
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
bio text NOT NULL
);
-- name: DeleteAuthor :exec
DELETE FROM authors WHERE id = ?;
|
执行生成代码
