1. 安装sqlc
go get github.com/kyleconroy/sqlc/cmd/sqlc
2. 基本使用
建立基本项目结构
mkdir sqlc-demo
cd sqlc-demo
go mod init sqlc-demo
在sqlc-demo中建立如下目录结构：
.
├── db
│ ├── queries
│ ├── schema
│ └── sqlc
└── go.mod
其中query中存储查询语句，schema中存储数据库表结构，sqlc中存储生成的代码。
基本表结构
sqlc-demo/db/schema/table.sql
CREATE TABLE "accounts" (
"id" bigserial PRIMARY KEY,
"owner" varchar NOT NULL,
"balance" bigint NOT NULL,
"currency" varchar NOT NULL,
"created_at" timestamptz NOT NULL DEFAULT (now())
);
CREATE TABLE "entries" (
"id" bigserial PRIMARY KEY,
"account_id" bigint NOT NULL,
"amount" bigint NOT NULL,
"created_at" timestamptz NOT NULL DEFAULT (now())
);
CREATE TABLE "transfers" (
"id" bigserial PRIMARY KEY,
"from_account_id" bigint NOT NULL,
"to_account_id" bigint NOT NULL,
"amount" bigint NOT NULL,
"created_at" timestamptz NOT NULL DEFAULT (now())
);
配置文件
sqlc-demo/sqlc.yaml
version: 1
packages:
- name: "db" # 生成文件的包名
path: "db/sqlc" # 生成的文件位置
engine: "postgresql" # 数据库驱动引擎
schema: "./db/schema/" # 数据库表结构
queries: "./db/queries" # 查询语句位置
查询语句
现在有了表结构和配置文件，我们可以开始根据业务需要书写查询语句了。
sqlc-demo/db/queries/account.sql
-- name: CreateAccount :one
INSERT INTO accounts (
owner,
balance,
currency
) VALUES (
$1, $2, $3
) RETURNING *;
在上面这个查询语句中，第一行注释是为sqlc做的标记，告诉sqlc，要生成的函数名字为CreateAccount, 返回单个对象。
生成代码
sqlc-demo/Makefile
sqlc:
sqlc generate
.PHONY: sqlc
查看sqlc-demo/db/sqlc/文件夹下，生成了三个文件：
.
├── db
│ ├── queries
│ │ └── account.sql
│ ├── schema
│ │ └── table.sql
│ └── sqlc
│ ├── account.sql.go
│ ├── db.go
│ └── models.go
├── go.mod
├── Makefile
└── sqlc.yaml
其中，account.sql.go中包含有生成的CreateAccount函数:
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
}
这样，我们就可以非常方便的通过golang函数调用的方式完成操作数据库的任务。
使用
func run() error {
ctx := context.Background()
psql_db, err := sql.Open("postgres", "user=pqgotest dbname=pqgotest sslmode=verify-full")
if err != nil {
return err
}
queries := db.New(psql_db)
// create an author
insertedAccount, err := queries.CreateAccount(ctx, db.CreateAccountParams{
Owner: "name",
Balance: 0,
Currency: "RMB",
})
if err != nil {
return err
}
log.Println(insertedAccount)
return nil
3. 注释
要想让sqlc为你生成代码，需要在查询语句的上方写上一行注释，其格式为：
-- name: <name> <command>
其中, 是你想要生成函数的名字，则是对生成函数的返回做简单的设置
:exec
:exec使函数只执行查询语句，但不返回任何值（除error外）
-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;
func (q *Queries) DeleteAuthor(ctx context.Context, id int64) error {
_, err := q.db.ExecContext(ctx, deleteAuthor, id)
return err
}
:execresult:execresult使函数返回一个sql.Result接口，其接口定义如下：
type Result interface {
// LastInsertId returns the integer generated by the database
// in response to a command. Typically this will be from an
// "auto increment" column when inserting a new row. Not all
// databases support this feature, and the syntax of such
// statements varies.
LastInsertId() (int64, error)
// RowsAffected returns the number of rows affected by an
// update, insert, or delete. Not every database or database
// driver may support this.
RowsAffected() (int64, error)
}
-- name: DeleteAllAuthors :execresult
DELETE FROM authors;
func (q *Queries) DeleteAllAuthors(ctx context.Context) (sql.Result, error) {
return q.db.ExecContext(ctx, deleteAllAuthors)
}
:execrows:execrows使函数返回受该查询语句影响的行
-- name: DeleteAllAuthors :execrows
DELETE FROM authors;
func (q *Queries) DeleteAllAuthors(ctx context.Context) (int64, error) {
_, err := q.db.ExecContext(ctx, deleteAllAuthors)
// ...
}
:many:many使函数返回一个切片
-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;
func (q *Queries) ListAuthors(ctx context.Context) ([]Author, error) {
rows, err := q.db.QueryContext(ctx, listAuthors)
// ...
}
:one:one使函数返回单个对象
-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;
func (q *Queries) GetAuthor(ctx context.Context, id int64) (Author, error) {
row := q.db.QueryRowContext(ctx, getAuthor, id)
// ...
}
4. 配置文件
version: "1"
packages:
- name: "db" # 生成代码的包名
path: "internal/db" # 生成代码的位置
queries: "./sql/query/" # 查询语句的位置
schema: "./sql/schema/" # 数据库表的位置
engine: "postgresql" # 数据库引擎
emit_prepared_queries: true # 开启预查询支持，默认为false
emit_interface: false # 如果为true，则会生成一个Querier接口，包含所有查询方法，默认为false
emit_exact_table_names: false # 如果为true，则结构体会与表名相同，否则将会对复数表名进行单数化，默认为false
emit_empty_slices: false # 如果为true，则:many标签若查询为空会返回[]而不是nil, 默认为false
emit_exported_queries: false # 如果为true，则生成的函数为可导出的
emit_json_tags: true # 如果为true，则为生成的结构体增加json标签
json_tags_case_style: "camel" # json标签的风格，"camel"：camelCase，"pascal":PascalCase, "snake": snake_case
output_db_file_name: "db.go" # 自定义db文件的名字
output_models_file_name: "models.go" # 自定义model文件的名字
output_querier_file_name: "querier.go" # 自定义querier文件的名字