Golang 学习笔记-sqlc 代码生成

25 阅读2分钟

简介

文档:docs.sqlc.dev/en/stable/o…

安装

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

使用方法

sqlc需要3个文件:

  1. sqlc.yaml # sqlc的配置文件
  2. schema.sql # 表结构SQL
  3. 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
	}
}