Golang后端大师班学习笔记 — 4.从SQL中生成Golang CRUD代码

GolangCRUD

CRUD

CCreateRReadUUpdateDDelete

GolangCRUD

low-leveldatabase/sql

在官方文档 https://pkg.go.dev/database/sql#DB.QueryContext 中，可以看到如下代码示例：

package main

import (
	"context"
	"database/sql"
	"log"
	"time"
)

var (
	ctx context.Context
	db  *sql.DB
)

func main() {
	id := 123
	var username string
	var created time.Time
	err := db.QueryRowContext(ctx, "SELECT username, created_at FROM users WHERE id=?", id).Scan(&username, &created)
	switch {
	case err == sql.ErrNoRows:
		log.Printf("no user with id %d\n", id)
	case err != nil:
		log.Fatalf("query error: %v\n", err)
	default:
		log.Printf("username is %q, account created on %s\n", username, created)
	}
}

QueryRowContext()SQLdb.QueryRowContext(ctx, "SELECT username, created_at FROM users WHERE id=?", id).Scan(&username, &created)

这种方法的主要优点是：

运行速度快
代码编写起来简单

缺点是：

SQL

high-level

Golang

优点:

CRUDGORM

缺点:

GORMbenchmarksGORM3-5

示例代码：

user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}

result := db.Create(&user)

db.First(&user)

3. SQLX

优点：

速度几乎和标准库一样快，使用也非常简单
字段映射是通过查询文本或结构标签完成的

Select()StructScan()structdatabase/sql

缺点:

错误只会在运行时捕获

示例代码:

err = db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")
    if err != nil {
        fmt.Println(err)
        return
    }
    usa, singsing, honkers := places[0], places[1], places[2]
    
    fmt.Printf("%#v\n%#v\n%#v\n", usa, singsing, honkers)
    // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}
    // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}
    // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}

    // Loop through rows using only one struct
    place := Place{}
    rows, err := db.Queryx("SELECT * FROM place")
    for rows.Next() {
        err := rows.StructScan(&place)
        if err != nil {
            log.Fatalln(err)
        } 
        fmt.Printf("%#v\n", place)
    }

4. SQLC

优点:

database/sqldatabase/sqlSQLGolang

缺点：

mysqlpostgres

mysqlpostgresSQLCSQLXGORMSQLC

SQLC

1. 安装SQLC

SQLC

brew install sqlc

sqlc versionsqlc help

compile SQLgenerate生成SQLGolanginitsqlc.yaml

SQLCGolang

银行simplebanksqlc init

sqlc init

vscodesqlc.yamlsqlc.yaml

nameGodbpathGolangdbsqlcpath./db/sqlcqueriesSQLdbqueryqueries./db/query/schema./db/migration/enginepostgresqlemit_json_tagstrueJSON

version: 1
packages:
  - path: "./db/sqlc"
    name: "db"
    engine: "postgresql"
    schema: "./db/migration/"
    queries: "./db/query/"
    emit_json_tags: true

打开终端，执行

sqlc generate

会发现如下错误：

error parsing queries: no queries contained in paths /goproject/simplebank/db/query

query

Makefilesqlc

postgres:
	docker run --name postgres14 -e POSTGRES_PASSWORD=123456 -e POSTGRES_USER=root -p 5432:5432 -d postgres:14-alpine

createdb:
	docker exec -it postgres14 createdb --username=root --owner=root simple_bank

dropdb:
	docker exec -it postgres14 dropdb simple_bank

migrateup:
	migrate --path db/migration --database="postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose up

migratedown:
	migrate --path db/migration --database="postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose down

sqlc:
	sqlc generate

.PHONY: postgres, createdb, dropdb, migrateup, migratedown, sqlc

SQLaccountdb/queryaccount.sqlSQLCaccount.sqlINSERT-- name: CreateAuthor :oneSQLCGolangCreateAccountoneAccount

-- name: CreateAccount :one
INSERT INTO accounts (
  owner, 
  balance,
  currency
) VALUES (
  $1, $2, $3
)
RETURNING *;

RETURNING *Account

make sqlc

db/sqlcaccount.sql.godb.gomodels.go

models.goJSONdb.goaccount.sql.gopackagedbcreateAccountRETURNING *RETURNING id, owner, balance, currency, created_at

CreateAccountParams

type CreateAccountParams struct {
	Owner    string `json:"owner"`
	Balance  int64  `json:"balance"`
	Currency string `json:"currency"`
}

CreateAccountQueriesAccountCreateAccountParams

func (q *Queries) CreateAccount(ctx context.Context, arg CreateAccountParams) (Account, error) {
	row := q.db.QueryRowContext(ctx, createAccount, arg.Owner, arg.Balance, arg.Currency)
	var i Account
	err := row.Scan(
		&i.ID,
		&i.Owner,
		&i.Balance,
		&i.Currency,
		&i.CreatedAt,
	)
	return i, err
}

这时，我们看到代码里面有红色下划线报错：

是因为，我们还没有为项目初始化模块，在项目下打开终端，执行

go mod init simplebank

account.sql.go

database/sqlSQLSQL

SQLCgomake sqlc

3. READ 读取操作

SQLCGetListaccount.sql

-- 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;

LIMITOFFSETmake sqlcaccount.sql.goGetAccountListAccountsSELECT *SELECT id, owner, balance, currency, created_at

4. UPDATE 更新操作

SQLCUPDATEaccount.sql

-- 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 :exec
UPDATE accounts SET balance = $2 WHERE id = $1;

make sqlcaccount.sql.goUpdateAccount

func (q *Queries) UpdateAccount(ctx context.Context, arg UpdateAccountParams) error {
	_, err := q.db.ExecContext(ctx, updateAccount, arg.ID, arg.Balance)
	return err
}

SQL:exec:oneRETURNING *

-- name: UpdateAccount :one
UPDATE accounts SET balance = $2 WHERE id = $1
RETURNING *;

make sqlcaccount.sql.goUpdateAccount

5. DELETE 删除操作

SQLCaccount.sql

-- name: DeleteAccount :exec
DELETE FROM accounts WHERE id = $1;

make sqlcaccount.sql.goDeleteAccountCRUDentriestransfersCRUD