Golang 学习笔记-sqlc 代码生成

简介

文档：https://docs.sqlc.dev/en/stable/overview/install.html

安装

go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

使用方法

sqlc需要3个文件：

sqlc.yaml # sqlc的配置文件

schema.sql # 表结构SQL

query.sql # 增删改查SQL

sqlc配置文件

以PostgreSQL举例

version: "2"
sql:
  - engine: "postgresql" # 数据库
    queries: "query.sql" # query.sql文件的路径，该文件主要用于存储DML
    schema: "schema.sql" # schema.sql文件的路径,该文件主要用于存储DDL
    gen:
      go:
        package: "tutorial" # 模块的包名
        out: "tutorial" # 代码生成路径
        sql_package: "pgx/v5"

schema文件

-- 表结构文件
CREATE TABLE User
(
    id      SERIAL  PRIMARY KEY,
    username  varchar(32) not null,
    password  varchar(32) not null,
    status  int not null
);
comment on table User is '用户表';
comment on column User.username is '用户名';
comment on column User.password is '密码';
comment on column User.status is '状态';

query文件

-- name: <name> <command>
<name>: 定义方法名
<command>包含以下方法：
:one          查询并返回1条,如根据id获取指定用户信息
:many         查询并返回一个切片,如查询所有用户
:exec         执行sql且仅返回异常,如新增/修改/删除
:execresult   执行sql且返回结果及异常
:execrows     执行sql且返回受影响的行数
:execlastid   执行sql返回Result对象和异常，Result对象包含最后一次ID及受影响行数
:batchexec    批量执行，
:batchmany    批量查询，返回切片
:batchone     批量查询，返回单条

栗子

-- name: SelectUserByID :one
select id,username,password,status from User where id = $1;

-- name: SelectUser :many
select id,username,password,status from User;

-- name: DeleteUser :exec
delete from User where id = $1;

-- name: UpdateUser :exec
update User set status=$2 where id=$1;

-- name: CreateUser :exec
INSERT INTO User (username, password, status)
VALUES ($1, $2, $3)
RETURNING *;

生成代码

sqlc generate -f sqlc.yaml
# 他会在配置文件out指定的路径下生成以下三个文件，这三个文件不可手动修改
# 1. db.go        # one/many/exec 接口
# 2. models.go    # 将表结构转换成结构体
# 3. query.sql.go # 根据query.sql文件生成的方法

db.go

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.27.0

package tutorial

import (
    "context"

    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgconn"
)

type DBTX interface {
    Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
    Query(context.Context, string, ...interface{}) (pgx.Rows, error)
    QueryRow(context.Context, string, ...interface{}) pgx.Row
}

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

type Queries struct {
    db DBTX
}

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

models.go

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.27.0

package tutorial

// 用户表
type User struct {
    ID int32
    // 用户名
    Username string
    // 密码
    Password string
    // 状态
    Status int32
}

query.sql.go

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.27.0
// source: query.sql

package tutorial

import (
    "context"
)

const createUser = `-- name: CreateUser :exec
INSERT INTO Users (username, password, status)
VALUES ($1, $2, $3)
RETURNING id, username, password, status
`

type CreateUserParams struct {
    Username string
    Password string
    Status   int32
}

func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) error {
    _, err := q.db.Exec(ctx, createUser, arg.Username, arg.Password, arg.Status)
    return err
}

const deleteUser = `-- name: DeleteUser :exec
delete from Users where id = $1
`

func (q *Queries) DeleteUser(ctx context.Context, id int32) error {
    _, err := q.db.Exec(ctx, deleteUser, id)
    return err
}

const selectUser = `-- name: SelectUser :many
select id,username,password,status from Users
`

func (q *Queries) SelectUser(ctx context.Context) ([]User, error) {
    rows, err := q.db.Query(ctx, selectUser)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var items []User
    for rows.Next() {
        var i User
        if err := rows.Scan(
            &i.ID,
            &i.Username,
            &i.Password,
            &i.Status,
        ); err != nil {
            return nil, err
        }
        items = append(items, i)
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return items, nil
}

const selectUserByID = `-- name: SelectUserByID :one
select id,username,password,status from Users where id = $1
`

func (q *Queries) SelectUserByID(ctx context.Context, id int32) (User, error) {
    row := q.db.QueryRow(ctx, selectUserByID, id)
    var i User
    err := row.Scan(
        &i.ID,
        &i.Username,
        &i.Password,
        &i.Status,
    )
    return i, err
}

const updateUser = `-- name: UpdateUser :exec
update Users set status=$2 where id=$1
`

type UpdateUserParams struct {
    ID     int32
    Status int32
}

func (q *Queries) UpdateUser(ctx context.Context, arg UpdateUserParams) error {
    _, err := q.db.Exec(ctx, updateUser, arg.ID, arg.Status)
    return err
}

调用方法

func main() {
    ctx := context.Background()
    // 创建连接对象
    conn, _ := pgx.Connect(ctx, "host=127.0.0.1  port=5432 user=pqgotest password=123456 dbname=pqgotest sslmode=verify-full")
    defer conn.Close(ctx)
    db := tutorial.New(conn)
    // 赋值
    params := user.CreateUserParams{
        Username: "zhangsan",
        Password: "123456",
        Status:   0,
    }
    // 调用CreateUser方法新增用户
    _, err := db.CreateUser(c, params)
    if err != nil {
        return
    }
}