[Go 开源推荐] sqlc —— 从 SQL 中生成类型安全的 Go 代码

Go

sqlc: SQL 编译器

https://github.com/kyleconroy/sqlc

sqlc 从 SQL 生成 安全的全类型的 Go 代码。运作方式如下:

  1. 你编写 SQL 查询
  2. 运行 sqlc 生成 Go 代码,该代码为这些查询提供类型安全的接口
  3. 您编写应用程序代码,该代码调用 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 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。

原文地址:https://github.com/kyleconroy/sqlc

译文地址:https://learnku.com/go/t/41383

本文为协同翻译文章,如您发现瑕疵请点击「改进」按钮提交优化建议
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!