从SQL生成可直接调用的go接口-sqlc

24 Mar, 2022

在 Go 语言中编写数据库操作代码真的非常痛苦！database/sql标准库提供的都是比较底层的接口。我们需要编写大量重复的代码。大量的模板代码不仅写起来烦，而且还容易出错。有时候字段类型修改了一下，可能就需要改动很多地方；添加了一个新字段，之前使用select *查询语句的地方都要修改。如果有些地方有遗漏，可能就会造成运行时panic。即使使用 ORM 库，这些问题也不能完全解决！这时候，sqlc来了！sqlc可以根据我们编写的 SQL 语句生成类型安全的、地道的 Go 接口代码，我们要做的只是调用这些方法。

sqlc generates fully type-safe idiomatic Go code from SQL. Here’s how it works:

编写SQL语句
使用 sqlc生成我们所需要的go查询接口
使用这些接口与数据库交互

快速比较

database/sql
- 快
- 易出错，且runtime才能捕获问题
gorm （Golang 的库）
- CRUD已被实现，所需要的代码少
- 需要用gorm的函数来写查询代码（新的学习成本）
- 高负载下运行慢
sqlx
- 速度快，易用
- 同样易出错，且在runtime 才能捕获问题
sqlc（choose)
- 快，易用，自动生成代码
- 编写代码时即可发现SQL的错误
- 能支持PG

安装sqlc

拉取镜像

docker pull kjconroy/sqlc

初始化配置文件

docker run -rm -v 你的项目路径:/src -w /src kjconroy/sqlc init

执行命令

docker run -rm -v D:\repository\simplebank:/src -w /src kjconroy/sqlc init

运行之后会在D:\repository\simplebank生成sqlc.yaml文件

编辑配置文件。

写入以下内容，配置文件中的目录都要存在

version: 1
packages:
  - path: "./db/sqlc" # 生成go 代码的位置
    name: "db"  # 生成 go package 的名字
    engine: "postgresql" # 使用的数据库引擎
    schema: "./db/migration/" # 迁移表的sql语句 我们使用migrate中的up文件
    queries: "./db/query" # CRUD的sql
    emit_json_tags: true  # 添加json在生成的struct中
    emit_prepared_queries: false 
    emit_interface: false
    emit_exact_table_names: false

在./db/query/account.sql中写入crud语句

-- 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 :one
update accounts
set balance =$2
where id = $1
returning *;

-- name: DeleteAccount :exec
delete
from accounts
where id = $1;

-- 修改sql语句中的展位符号名称
-- 生成的参数结构体字段名称就是我们自定义的名称了。

-- name: AddAccountBalance :one
update accounts
set balance = balance+ $2
where id = $1
returning *;
-- name: AddAccountBalance :one
update accounts
set balance = balance+ sqlc.arg(amount)
where id = sqlc.arg(id)
returning *;

我们可以看到这和我们自己写sql并无不同，最大的区别就是每一句sql上面都会有一个注释

name: 后面的是我们要生成的那个go查询接口的方法名，再后后面的one、many、exec都有不同的含义：

one：只有一个返回值
many：多个返回值
exec：没有返回值

生成CRUD代码，执行

docker run --rm -v D:\repository\simplebank:/src -w /src kjconroy/sqlc generate

在./db/sqlc中会生成CRUD代码

现在可以在db/sqlc文件夹下查看生成的go code

其实除了我们需要的account.sql.go,entry.sql.go,transfer.sql.go，还会生成三个.go文件，可以简单看一下里面都是些什么内容：

db.go初始化了一个Queries结构，我们需要传入一个自己的db连接对象

// Code generated by sqlc. DO NOT EDIT.

package db

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
	PrepareContext(context.Context, string) (*sql.Stmt, error)
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}

func New(db DBTX) *Queries {
	return &Queries{db: db}
}

type Queries struct {
	db DBTX
}

func (q *Queries) WithTx(tx *sql.Tx) *Queries {
	return &Queries{
		db: tx,
	}
}

models.go：就是将我们每个表的字段都做了一次结构体的封装

// Code generated by sqlc. DO NOT EDIT.

package db

import (
	"time"
)

type Account struct {
	ID        int64     `json:"id"`
	Owner     string    `json:"owner"`
	Balance   int64     `json:"balance"`
	Currency  string    `json:"currency"`
	CreatedAt time.Time `json:"created_at"`
}

querier.go：定义一个接口，封装所有的sql查询接口

// Code generated by sqlc. DO NOT EDIT.

package db

import (
	"context"
)

type Querier interface {
	CreateAccount(ctx context.Context, arg CreateAccountParams) (Account, error)
	DeleteAccount(ctx context.Context, id int64) error
	GetAccount(ctx context.Context, id int64) (Account, error)
	ListAccounts(ctx context.Context, arg ListAccountsParams) ([]Account, error)
	UpdateAccount(ctx context.Context, arg UpdateAccountParams) (Account, error)
}

var _ Querier = (*Queries)(nil)

account.sql.go：用go实现了我们刚才写的那些sql语句，一些输入和输出结构都用了struct来定义

// Code generated by sqlc. DO NOT EDIT.
// source: account.sql

package db

import (
	"context"
)

const addAccountBalance = `-- name: AddAccountBalance :one
update accounts
set balance = balance+ $1
where id = $2
returning id, owner, balance, currency, created_at
`

type AddAccountBalanceParams struct {
	Amount int64 `json:"amount"`
	ID     int64 `json:"id"`
}

func (q *Queries) AddAccountBalance(ctx context.Context, arg AddAccountBalanceParams) (Account, error) {
	row := q.db.QueryRowContext(ctx, addAccountBalance, arg.Amount, arg.ID)
	var i Account
	err := row.Scan(
		&i.ID,
		&i.Owner,
		&i.Balance,
		&i.Currency,
		&i.CreatedAt,
	)
	return i, err
}
... 剩下的省略

至此我们就完全可以用go来与数据库实现交互了，sqlc的优势也很明显了，我们只需要写sql，而不需要关心go如何与sql进行交互的

同时sqlc还支持了语法错误的判断，而不存在我们在运行程序是因为sql出错而panic的情况.

使用

package main

import (
	"context"
	"database/sql"
	"log"
	"testing"

	_ "github.com/lib/pq"
)

const (
	DB_DRIVER = "postgres"
	DB_SOURCE = "postgresql://postgres:postgres@localhost:25432/simple_bank?sslmode=disable"
)

var (
	testQueries *Queries // 全局的queries 数据库测试都要用到
	testDB      *sql.DB
)

func main() {

	// 连接数据库
	var err error
	testDB, err = sql.Open(DB_DRIVER, DB_SOURCE)
	if err != nil {
		log.Fatalln("cannot connect to db :", err)
	}
	testQueries = New(testDB) // 生成测试的queries

	account, err := testQueries.CreateAccount(context.Background(),
		CreateAccountParams{
			Owner:    "jimyag",
			Balance:  1111,
			Currency: "USD",
		})
	if err != nil {
		log.Fatalln("cannot create account")
	}
	log.Print(account)
}

参考

Installing sqlc — sqlc 1.12.0 documentation

sqlc使用说明 - 码农教程 (manongjc.com)

sqlc-地鼠文档 (topgoer.cn)

#SQL #教程 #工具