AdSense

網頁

2021/11/29

Golang 使用sqlc 轉譯SQL為go程式碼

sqlc函式庫是可用來將SQL語言轉譯為Go程式碼的命令列工具。


下面範例使用sqlc產生存取employee資料表的Go程式碼。

範例環境：

  • Go 1.17
  • sqlc v1.11
  • PostgreSQL 14


安裝sqlc

在命令列輸入go install github.com/kyleconroy/sqlc/cmd/sqlc@latest安裝sqlc。

$ go install github.com/kyleconroy/sqlc/cmd/sqlc@latest
go: downloading github.com/kyleconroy/sqlc v1.11.0
go: downloading github.com/spf13/cobra v1.2.1
go: downloading gopkg.in/yaml.v3 v3.0.0-20210107192922-496545a6307b
go: downloading github.com/jinzhu/inflection v1.0.0
go: downloading github.com/pingcap/parser v0.0.0-20210914110036-002913dd28ec
go: downloading github.com/pganalyze/pg_query_go/v2 v2.1.0
go: downloading github.com/antlr/antlr4 v0.0.0-20200209180723-1177c0b58d07
go: downloading github.com/pingcap/errors v0.11.5-0.20210425183316-da1aaba5fb63
go: downloading github.com/pingcap/log v0.0.0-20210906054005-afc726e70354
go: downloading gopkg.in/natefinch/lumberjack.v2 v2.0.0

若Go版本在1.17以前則輸入go get github.com/kyleconroy/sqlc/cmd/sqlc安裝sqlc。

或在Mac可用Homebrew安裝輸入brew install sqlc


建立Go專案

參考「Golang hello world」建立一個簡單的Go專案，本範例module命名為abc.com/demo。


建立sqlc.yaml

在專案根目錄建立sqlc.yaml內容如下：

sqlc.yaml

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

  • version - sqlc的版本
  • packages - 定義生成程式碼的各項參數。
    • name - 生成程式碼檔的package名稱。
    • path - 生成程式碼檔的輸出的目錄。
    • engine - 使用的資料庫，支援postgresql及mysql，預設為postgresql。
    • schema - 資料表定義SQL(DDL)所在檔案或目錄。
    • queries - 查詢SQL(DQL, DML)所在檔案或目錄。

執行sqlc命令時預設讀取所在目錄的sqlc.yaml或sqlc.json配置檔來生成Go程式碼。


建立schema.sql

建立schema.sql並放在/sqlc目錄，內容為建立employee資料表的DDL SQL。sqlc需要讀取DDL來生成程式碼，即為sqlc.yaml中schema屬性指定的檔案或目錄。

schema.sql

CREATE TABLE IF NOT EXISTS employee (
   id bigserial PRIMARY KEY,            -- 編號
   name varchar(60) UNIQUE NOT NULL,    -- 名稱
   age integer,                         -- 年齡
   created_at timestamp NOT NULL        -- 建立時間
);


建立query.sql

建立query.sql並放在/sqlc目錄，內容為employee資料表的查詢SQL，sqlc依此來生成對應的程式碼，即為sqlc.yaml中queries屬性指定的檔案或目錄。

SQL前必須使用sqlc的query annotations註解來指定生成的名稱(name)及操作命令(command)。

-- name: <name> <command>

例如下面第一個SQL的生成的方法名稱為GetById，:one代表回傳一個結果；
第二個SQL的生成的方法名稱為GetAll，:many代表回傳多個結果；
第三個SQL的生成的方法名稱為Insert，:exec會返回error；
第四個SQL的生成的方法名稱為Update，:execrows會返回修改筆數。

query.sql

-- name: GetById :one
SELECT * FROM employee
WHERE id = $1 LIMIT 1;

-- name: GetAll :many
SELECT * FROM employee;

-- name: Insert :exec
INSERT INTO employee (name, age, created_at) 
VALUES ($1, $2, $3);

-- name: Update :execrows
Update employee
SET
    name = $1,
    age = $2
WHERE
    id = $3;


執行sqlc命令

完成sqlc.yaml、schema.sql及query.sql後在sqlc所在目錄執行sqlc generate命令來生成Go程式碼。

~/../go-demo$ sqlc generate

執行後不會顯示任何訊息，而生成的檔案會出現在sqlc.yaml的path指定的目錄，也就是/db目錄。sqlc會產生db.go、models.go、query.sql.go共3個Go檔案。

  • db.go - 用來建立Queries查詢物件。
  • models.go - schema.sql對應的struct。
  • query.sql.go - query.sql對應的Queries查詢方法。

db.go

// Code generated by sqlc. DO NOT EDIT.

package db

import (
    "context"
    "database/sql"
)

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,
    }
}

models.go

// Code generated by sqlc. DO NOT EDIT.

package db

import (
    "database/sql"
    "time"
)

type Employee struct {
    ID        int64
    Name      string
    Age       sql.NullInt32
    CreatedAt time.Time
}

query.sql.go

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

package db

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

const getAll = `-- name: GetAll :many
SELECT id, name, age, created_at FROM employee
`

func (q *Queries) GetAll(ctx context.Context) ([]Employee, error) {
    rows, err := q.db.QueryContext(ctx, getAll)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var items []Employee
    for rows.Next() {
        var i Employee
        if err := rows.Scan(
            &i.ID,
            &i.Name,
            &i.Age,
            &i.CreatedAt,
        ); 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
}

const getById = `-- name: GetById :one
SELECT id, name, age, created_at FROM employee
WHERE id = $1 LIMIT 1
`

func (q *Queries) GetById(ctx context.Context, id int64) (Employee, error) {
    row := q.db.QueryRowContext(ctx, getById, id)
    var i Employee
    err := row.Scan(
        &i.ID,
        &i.Name,
        &i.Age,
        &i.CreatedAt,
    )
    return i, err
}

const insert = `-- name: Insert :exec
INSERT INTO employee (name, age, created_at)
VALUES ($1, $2, $3)
`

type InsertParams struct {
    Name      string
    Age       sql.NullInt32
    CreatedAt time.Time
}

func (q *Queries) Insert(ctx context.Context, arg InsertParams) error {
    _, err := q.db.ExecContext(ctx, insert, arg.Name, arg.Age, arg.CreatedAt)
    return err
}

const update = `-- name: Update :execrows
Update employee
SET
    name = $1,
    age = $2
WHERE
    id = $3
`

type UpdateParams struct {
    Name string
    Age  sql.NullInt32
    ID   int64
}

func (q *Queries) Update(ctx context.Context, arg UpdateParams) (int64, error) {
    result, err := q.db.ExecContext(ctx, update, arg.Name, arg.Age, arg.ID)
    if err != nil {
        return 0, err
    }
    return result.RowsAffected()
}

專案目錄結構如下。

/
├──db/
│   ├── db.go
│   ├── models.go
│   └── query.sql.go
├──sqlc/
│   ├── query.sql
│   └── schema.sql
├── go.mod
├── go.sum
├── main.go
└── sqlc.yaml


測試

在本機啟動postgres資料庫及建立employee資料表及插入資料。

在專案根目錄輸入go get github.com/lib/pq下載postgres driver。

~/../go-demo$ go get github.com/lib/pq
go: downloading github.com/lib/pq v1.10.4
go get: added github.com/lib/pq v1.10.4

在main.go撰寫程式碼如下，呼叫Queries.GetAll()查詢employee的資料。

main.go

package main

import (
    "context"
    "database/sql"
    "fmt"

    sqlcDb "abc.com/demo/db"

    _ "github.com/lib/pq"
)

const (
    HOST     = "localhost"
    PORT     = "5432"
    DATABASE = "postgres"
    USER     = "admin"
    PASSWORD = "12345"
    SSL      = "disable"
)

func OpenDB(ctx context.Context) *sql.DB {
    driver := "postgres"
    dsn := fmt.Sprintf(
        "host=%s port=%s user=%s password=%s dbname=%s sslmode=%s",
        HOST, PORT, USER, PASSWORD, DATABASE, SSL)

    db, err := sql.Open(driver, dsn)
    if err != nil {
        panic("open database error")
    }
    return db
}

func main() {
    ctx := context.Background()
    emps, err := GetAll(ctx)
    if err != nil {
        panic(err)
    }
    fmt.Println(emps)
}

func GetAll(ctx context.Context) ([]sqlcDb.Employee, error) {
    db := OpenDB(ctx)
    queries := sqlcDb.New(db)
    emps, err := queries.GetAll(ctx)
    if err != nil {
        return nil, err
    }
    return emps, nil
}

func GetById(ctx context.Context, id int64) (*sqlcDb.Employee, error) {
    db := OpenDB(ctx)
    qr := sqlcDb.New(db)
    emp, err := qr.GetById(ctx, id)
    if err != nil {
        return nil, err
    }
    return &emp, nil
}

func Insert(ctx context.Context, name string, age int) error {
    db := OpenDB(ctx)
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    params := sqlcDb.InsertParams{
        Name: name,
        Age:  sql.NullInt32{int32(age), true},
    }
    qr := sqlcDb.New(tx)
    err = qr.Insert(ctx, params)
    if err != nil {
        return err
    }

    tx.Commit()
    if err != nil {
        return err
    }
    return nil
}

func Update(ctx context.Context, id int64, name string, age int) (int64, error) {
    db := OpenDB(ctx)
    tx, err := db.Begin()
    if err != nil {
        return 0, err
    }
    defer tx.Rollback()

    params := sqlcDb.UpdateParams{
        ID:   id,
        Name: name,
        Age:  sql.NullInt32{int32(age), true},
    }
    qr := sqlcDb.New(tx)
    rows, err := qr.Update(ctx, params)
    if err != nil {
        return 0, err
    }

    tx.Commit()
    if err != nil {
        return 0, err
    }
    return rows, nil
}

執行印出結果如下。

[{1 john {33 true} 2021-11-24 22:07:21.697726 +0000 +0000} {2 mary {28 true} 2021-11-24 22:07:21.700125 +0000 +0000}]

github。


標籤： ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)

AdSense