【新規ツール探し】Sql から Go コードを生成する「sqlc」

記事作成日：2022/11/30

情報

名前	URL
Github	https://github.com/kyleconroy/sqlc
公式サイト	https://docs.sqlc.dev/en/latest/index.html
デモサイト	sqlc - Playground
開発母体	kyleconroy 氏
version	1.16.0
言語	Go
価格	無料
ライセンス	MIT

情報
何ができるもの？
使い方
- チュートリアル
- アプリを動かす
利用シーン
登場背景
気にすること

何ができるもの？

sql から db コードが生成できます。

Database and language support — sqlc 1.16.0 documentation

使い方

Installing sqlc — sqlc 1.16.0 documentation

go install github.com/kyleconroy/sqlc/cmd/sqlc@latest

../../go/pkg/mod/github.com/pingcap/log@v0.0.0-20210906054005-afc726e70354/log.go:26:2: unrecognized import path "gopkg.in/natefinch/lumberjack.v2": reading https://gopkg.in/natefinch/lumberjack.v2?go-get=1: 502 Bad Gateway
        server response: Cannot obtain refs from GitHub: cannot talk to GitHub: Get https://github.com/natefinch/lumberjack.git/info/refs?service=git-upload-pack: net/http: request canceled (Client.Timeout exceeded while awaiting headers)

このエラーでコケたので docker をつかいます。

docker pull kjconroy/sqlc
docker run --rm -v $(pwd):/src -w /src kjconroy/sqlc generate

チュートリアル

Getting started with MySQL — sqlc 1.16.0 documentation

DDL / DML を用意し

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = ? LIMIT 1;

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;

-- name: CreateAuthor :execresult
INSERT INTO authors (
  name, bio
) VALUES (
  ?, ?
);

-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = ?;

CREATE TABLE authors (
  id   BIGINT  NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name text    NOT NULL,
  bio  text
);

アプリを動かす

こんなディレクトリ構成で

❯ lt
.
├── app
├── app.go
├── db
│  └── schema.sql
├── go.mod
├── go.sum
├── query.sql
├── schema.sql
├── sqlc.yaml
└── tutorial
   ├── db.go
   ├── models.go
   └── query.sql.go

mysql を起動。

docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=dbname -v ./db/:/docker-entrypoint-initdb.d/ mysql

サンプルコードを実行

package main

import (
    "context"
    "database/sql"
    "log"
    "reflect"

    "tutorial.sqlc.dev/app/tutorial"

    _ "github.com/go-sql-driver/mysql"
)

func run() error {
    ctx := context.Background()

    db, err := sql.Open("mysql", "root:password@/dbname")
    if err != nil {
        return err
    }

    queries := tutorial.New(db)

    // list all authors
    authors, err := queries.ListAuthors(ctx)
    if err != nil {
        return err
    }
    log.Println(authors)

    // create an author
    result, err := queries.CreateAuthor(ctx, tutorial.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
    }

    insertedAuthorID, err := result.LastInsertId()
    if err != nil {
        return err
    }
    log.Println(insertedAuthorID)

    // get the author we just inserted
    fetchedAuthor, err := queries.GetAuthor(ctx, insertedAuthorID)
    if err != nil {
        return err
    }

    // prints true
    log.Println(reflect.DeepEqual(insertedAuthorID, fetchedAuthor.ID))
    return nil
}

func main() {
    if err := run(); err != nil {
        log.Fatal(err)
    }
}

❯ go run app.go
2022/11/30 10:48:39 []
2022/11/30 10:48:39 1
2022/11/30 10:48:39 true

❯ go run app.go
2022/11/30 10:50:08 [{1 Brian Kernighan {Co-author of The C Programming Language and The Go Programming Language true}}]
2022/11/30 10:50:08 2
2022/11/30 10:50:08 true

❯ go run app.go
2022/11/30 10:50:27 [{1 Brian Kernighan {Co-author of The C Programming Language and The Go Programming Language true}} {2 Brian Kernighan {Co-author of The C Programming Language and The Go Programming Language true}}]
2022/11/30 10:50:27 3
2022/11/30 10:50:27 true

❯ go run app.go
2022/11/30 10:50:28 [{1 Brian Kernighan {Co-author of The C Programming Language and The Go Programming Language true}} {2 Brian Kernighan {Co-author of The C Programming Language and The Go Programming Language true}} {3 Brian Kernighan {Co-author of The C Programming Language and The Go Programming Language true}}]
2022/11/30 10:50:28 4
2022/11/30 10:50:28 true

利用シーン

MySQL or Postgres と Go を組み合わせてる場合に、コードを自動生成したい時。

こうかいておけば

-- name: CreateAuthor :execresult
INSERT INTO authors (
  name, bio
) VALUES (
  ?, ?
);

これが自動生成されるので楽ちん。

func (q *Queries) CreateAuthor(ctx context.Context, arg CreateAuthorParams) (sql.Result, error) {
    return q.db.ExecContext(ctx, createAuthor, arg.Name, arg.Bio)
}