[Go 开源推荐] sqlc —— 从 SQL 中生成类型安全的 Go 代码
sqlc: SQL 编译器
sqlc 从 SQL 生成 安全的全类型的 Go 代码。运作方式如下:
- 你编写 SQL 查询
- 运行 sqlc 生成 Go 代码,该代码为这些查询提供类型安全的接口
- 您编写应用程序代码,该代码调用 sqlc 生成的方法。
说真的,就是这么简单。您不必再编写任何样板化的 SQL 查询代码。
入门
好了,宣传的够多了,让我们看看它的实际效果。
首先,你将以下 SQL 传递给 sqlc generate
:
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY,
name text NOT NULL,
bio text
);
-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;
-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;
-- name: CreateAuthor :one
INSERT INTO authors (
name, bio
) VALUES (
$1, $2
)
RETURNING *;
-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;
然后在应用程序代码中编写:
// 列出所有 authors
authors, err := db.ListAuthors(ctx)
if err != nil {
return err
}
fmt.Println(authors)
// 创建一个 author
insertedAuthor, err := db.CreateAuthor(ctx, db.CreateAuthorParams{
Name: "Brian Kernighan",
Bio: sql.NullString{String: "Co-author of The C Programming Language and The Go Programming Language", Valid: true},
})
if err != nil {
return err
}
fmt.Println(insertedAuthor)
// 获取刚刚插入的 author
fetchedAuthor, err := db.GetAuthor(ctx, insertedAuthor.ID)
if err != nil {
return err
}
// 打印 true
fmt.Println(reflect.DeepEqual(insertedAuthor, fetchedAuthor))
为了实现这一点,sqlc 生成可读的、惯用 的 Go 代码,否则您将自己编写这些代码。让我们看一下:
package db
import (
"context"
"database/sql"
)
type Author struct {
ID int64
Name string
Bio sql.NullString
}
const createAuthor = `-- name: CreateAuthor :one
INSERT INTO authors (
name, bio
) VALUES (
$1, $2
)
RETURNING id, name, bio
`
type CreateAuthorParams struct {
Name string
Bio sql.NullString
}
func (q *Queries) CreateAuthor(ctx context.Context, arg CreateAuthorParams) (Author, error) {
row := q.db.QueryRowContext(ctx, createAuthor, arg.Name, arg.Bio)
var i Author
err := row.Scan(&i.ID, &i.Name, &i.Bio)
return i, err
}
const deleteAuthor = `-- 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
}
const getAuthor = `-- name: GetAuthor :one
SELECT id, name, bio 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)
var i Author
err := row.Scan(&i.ID, &i.Name, &i.Bio)
return i, err
}
const listAuthors = `-- name: ListAuthors :many
SELECT id, name, bio FROM authors
ORDER BY name
`
func (q *Queries) ListAuthors(ctx context.Context) ([]Author, error) {
rows, err := q.db.QueryContext(ctx, listAuthors)
if err != nil {
return nil, err
}
defer rows.Close()
var items []Author
for rows.Next() {
var i Author
if err := rows.Scan(&i.ID, &i.Name, &i.Bio); err != nil {
return nil, err
}
items = append(items, i)
}
if err := rows.Close(); err != nil {
return nil, err
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}
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,
}
}
安装
macOS
brew install kyleconroy/sqlc/sqlc
Ubuntu
sudo snap install sqlc
go get
go get github.com/kyleconroy/sqlc/cmd/sqlc
下载
可以从 Equinox 稳定通道 或者最新的 GitHub release 上下载特定版本的二进制文件。
文档
see
本文中的所有译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。
推荐文章: