あるオンラインサロンでチーム開発に参加しています。
私はチーム03のバックエンド側メンバーに加わりました。
チーム03のバックエンドは、Go+Gin+sqlc、DBはPostgreSQLを使うことになりました。
チーム開発に参加しながら、私の学習の軌跡を記事にしていきます。

本シリーズのリンク

チーム開発参加の記録【2023-06～2023-08】(1) Go+Ginで画像をダウンロード／アップロードするAPIを作る
チーム開発参加の記録【2023-06～2023-08】(2) sqlc + jackc/pgx/v5（v5.4.0）を使ってみた
チーム開発参加の記録【2023-06～2023-08】(3) sqlc + jackc/pgx/v5（v5.4.1）からPostgreSQLの複合型の配列を使ってみた
チーム開発参加の記録【2023-06～2023-08】(4) sqlc + jackc/pgx/v5 からPostgreSQLの複合型の配列を更新してみた
チーム開発参加の記録【2023-06～2023-08】(5) gocronでスケジュール処理し、定期的にバッチジョブを起動してみた
チーム開発参加の記録【2023-06～2023-08】(6) PostgreSQLの複合型の配列の更新について、もう少し煮詰める

※ 本記事のソースコードは主に学習・検証目的で書いたものであり、プロダクトにそのまま使用できる品質を目指していません。

本記事で行うこと

前回の記事では、sqlcからPostgreSQLの基本データ型を使ってみました。
今回のプロジェクトでDB設計を行ったところ、複合型の配列を使いたい場面が出てきましたので、sqlcからSELECT文を試しました。

前提

使用したsqlcのバージョンは、v1.18.0です。
sqlcの公式ページによれば、以下のDBデータ型等がsqlcから使えるそうです。

Arrays
Dates and Time
Enums
Null
UUIDs
JSON

複合型（Composite Types）については記載がありませんが、まずは使えるか試してみます。

データベースの準備

あらかじめ以下のSQLを実行して、type_hoge複合型とbarテーブルをCREATEし、データを2件INSERTしました。


CREATE TYPE type_hoge AS (
    col_int INTEGER,
    col_varchar VARCHAR
);

CREATE TABLE bar
(
    col_serial SERIAL NOT NULL,
    col_hoge_array type_hoge[] NOT NULL
);

INSERT INTO bar
(
    col_hoge_array
)
VALUES
(
    ARRAY[ROW(10, 'aaa'), ROW(20, 'bbb'), ROW(30, 'ccc')]::type_hoge[]
),
(
    ARRAY[ROW(100, 'yyy'), ROW(200, 'zzz')]::type_hoge[]
);

barテーブルからの検索結果は、以下の通りです。

3通りの実装

それでは、Go+Gin+sqlcを使用して、barテーブルの全データを返すAPIを実装します。

ディレクトリ構成＆ファイル一覧


Project Root
  ├── db/
  │     ├── dto/
  │     │     └── hoge_dto.go    → 実装(2)と実装(3)で使用
  │     ├── query/
  │     │     ├── query.sql
  │     │     └── schema.sql
  │     └── sqlc/
  │            ├── db.go
  │            ├── models.go
  │            └── query.sql.go
  ├── app.go
  ├── go.mod
  └── sqlc.yaml

db/sqlc/配下のファイルは、sqlcのコードジェネレーターによって生成されるファイルで、人が以下の3ファイルを用意して「sqlc generate」コマンドを実行することにより生成されます。

db/query/schema.sql
db/query/query.sql
sqlc.yaml

実装(1)・実装(2)・実装(3)で共通するソースコードを準備

APIの実装が共通する内容となります。

共通コード：app.go

app.go


package main

import (
	"context"
	db "exercise/db/sqlc"
	"net/http"
	"os"

	"github.com/gin-gonic/gin"
	"github.com/jackc/pgx/v5/pgxpool"
)

type httpError struct {
	Error string `json:"error"`
}

var pool *pgxpool.Pool

func ListBar(c *gin.Context) {
	q := db.New(pool)
	list, err := q.ListBar(context.Background())
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
		return
	}
	c.JSON(http.StatusOK, list)
}

func main() {
	connString := "user=postgres password=secret host=localhost port=5432 dbname=your_database sslmode=disable"
	var err error
	pool, err = pgxpool.New(context.Background(), connString)
	if err != nil {
		print(err.Error())
		os.Exit(1)
	}
	defer pool.Close()

	router := gin.Default()
	router.GET("/bar", ListBar)

	router.Run("0.0.0.0:8080")
}

共通コード：go.mod

pgx/v5のバージョンは、2023-06-18にリリースされたv5.4.1です。
https://github.com/jackc/pgx/blob/master/CHANGELOG.md

go.mod


module exercise

go 1.20

require github.com/gin-gonic/gin v1.9.1

require (
	github.com/jackc/pgpassfile v1.0.0 // indirect
	github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
	github.com/jackc/puddle/v2 v2.2.0 // indirect
	github.com/kr/text v0.2.0 // indirect
	github.com/rogpeppe/go-internal v1.10.0 // indirect
	golang.org/x/sync v0.1.0 // indirect
)

require (
	github.com/bytedance/sonic v1.9.1 // indirect
	github.com/chenzhuoyu/base64x v0.0.0-20221115062448-fe3a3abad311 // indirect
	github.com/gabriel-vasile/mimetype v1.4.2 // indirect
	github.com/gin-contrib/sse v0.1.0 // indirect
	github.com/go-playground/locales v0.14.1 // indirect
	github.com/go-playground/universal-translator v0.18.1 // indirect
	github.com/go-playground/validator/v10 v10.14.0 // indirect
	github.com/goccy/go-json v0.10.2 // indirect
	github.com/jackc/pgx/v5 v5.4.1
	github.com/json-iterator/go v1.1.12 // indirect
	github.com/klauspost/cpuid/v2 v2.2.4 // indirect
	github.com/leodido/go-urn v1.2.4 // indirect
	github.com/mattn/go-isatty v0.0.19 // indirect
	github.com/modern-go/concurrent v0.0.0-20180306012644-bacd9c7ef1dd // indirect
	github.com/modern-go/reflect2 v1.0.2 // indirect
	github.com/pelletier/go-toml/v2 v2.0.8 // indirect
	github.com/twitchyliquid64/golang-asm v0.15.1 // indirect
	github.com/ugorji/go/codec v1.2.11 // indirect
	golang.org/x/arch v0.3.0 // indirect
	golang.org/x/crypto v0.10.0 // indirect
	golang.org/x/net v0.10.0 // indirect
	golang.org/x/sys v0.9.0 // indirect
	golang.org/x/text v0.10.0 // indirect
	google.golang.org/protobuf v1.30.0 // indirect
	gopkg.in/yaml.v3 v3.0.1 // indirect
)

実装(1)

結論から書くと、実装(1)は失敗でした。
本記事執筆時点で、sqlcはPostgreSQLの複合型をサポートしていないかもしれません。
どのように失敗するか、記録しておきます。

実装(1)のdb/query/schema.sql

db/query/schema.sql


CREATE TYPE type_hoge AS (
    col_int INTEGER,
    col_varchar VARCHAR
);

CREATE TABLE bar
(
    col_serial SERIAL NOT NULL,
    col_hoge_array type_hoge[] NOT NULL
);

実装(1)のdb/query/query.sql

db/query/query.sql


-- name: ListBar :many
SELECT
    *
FROM
    bar;

実装(1)のsqlc.yaml

sqlc.yaml


version: "2"
sql:
  - engine: "postgresql"
    queries: "./db/query/query.sql"
    schema: "./db/query/schema.sql"
    gen:
      go:
        out: "./db/sqlc"
        package: "db"
        sql_package: "pgx/v5"
        emit_json_tags: true
        json_tags_case_style: "camel"

実装(1)のdb/sqlc/*.go（ジェネレーターで生成）

ここまでで、以下のファイルが用意できていますので、

db/query/schema.sql
db/query/query.sql
sqlc.yaml

Project Rootで以下を実行します。


sqlc generate

すると、db/sqlc/配下に以下の3つのgoファイルが生成されました。

db/sqlc/db.go

db/sqlc/db.go


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

package db

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

db/sqlc/models.go

PostgreSQLの複合型の配列が、Go言語側ではstringの配列になってしまいました。

db/sqlc/models.go


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

package db

import ()

type Bar struct {
	ColSerial    int32    `json:"colSerial"`
	ColHogeArray []string `json:"colHogeArray"`
}

db/sqlc/query.sql.go

db/sqlc/query.sql.go


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

package db

import (
	"context"
)

const listBar = `-- name: ListBar :many
SELECT
    col_serial, col_hoge_array
FROM
    bar
`

func (q *Queries) ListBar(ctx context.Context) ([]Bar, error) {
	rows, err := q.db.Query(ctx, listBar)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []Bar
	for rows.Next() {
		var i Bar
		if err := rows.Scan(&i.ColSerial, &i.ColHogeArray); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

実装(1)の実行結果

APIのレスポンスは以下のようになりました。
エラーで落ちるわけではありませんが、複合型の配列が文字列の配列になってしまいました。


[
    {
        "colSerial": 1,
        "colHogeArray": [
            "(10,aaa)",
            "(20,bbb)",
            "(30,ccc)"
        ]
    },
    {
        "colSerial": 2,
        "colHogeArray": [
            "(100,yyy)",
            "(200,zzz)"
        ]
    }
]

DB設計で複合型の配列の使用をやめて、基本型の複数の配列にバラすなり、JSON型にデータを突っ込むなりすれば簡単ではありますが、DB設計を変更するのは最後の手段にしたいので、別の改善策を探っていきます。

実装(2)：PostgreSQLのJSONB型を経由する

PostgreSQLのテーブル定義はそのままにして、新たにビューをCREATEします。
sqlcはPostgreSQLのJSONB型を扱えますので、view_barビューで複合型の配列をJSONB型に変換します。
schema.sqlの修正だけでなく、PostgreSQL側でもCREATE VIEW文を発行する必要があります。

実装(2)のdb/query/schema.sql

db/query/schema.sql


CREATE TYPE type_hoge AS (
    col_int INTEGER,
    col_varchar VARCHAR
);

CREATE TABLE bar
(
    col_serial SERIAL NOT NULL,
    col_hoge_array type_hoge[] NOT NULL
);

CREATE VIEW view_bar AS
SELECT
    col_serial,
    TO_JSONB(col_hoge_array) AS col_hoge_array
FROM
    bar;

実装(2)のdb/query/query.sql

view_barビューから問い合わせるように変更します。

db/query/query.sql


-- name: ListBar :many
SELECT
    *
FROM
    view_bar;

view_barビューからの検索結果は、以下の通りです。

実装(2)のdb/dto/hoge_dto.go

新たにhoge_dto.goファイルを生成します。
view_barビューの検索結果のjsonデータに合わせて、スネークケースで型を作ります。

db/dto/hoge_dto.go


package dto

type HogeDto struct {
	ColInt    int32  `json:"col_int"`
	ColString string `json:"col_varchar"`
}

type HogeArrayDto []HogeDto

実装(2)のsqlc.yaml

overridesを追加し、view_barビューのcol_hoge_array列の型と、Go言語側のHogeArrayDto型をマッピングします。

sqlc.yaml


version: "2"
sql:
  - engine: "postgresql"
    queries: "./db/query/query.sql"
    schema: "./db/query/schema.sql"
    gen:
      go:
        out: "./db/sqlc"
        package: "db"
        sql_package: "pgx/v5"
        emit_json_tags: true
        json_tags_case_style: "camel"
        overrides:
          - column: "view_bar.col_hoge_array"
            go_type:
              import: "exercise/db/dto"
              package: "dto"
              type: "HogeArrayDto"

実装(2)のdb/sqlc/*.go（ジェネレーターで生成）

ここまでで、以下のファイルが用意できていますので、

db/query/schema.sql
db/query/query.sql
sqlc.yaml

Project Rootで以下を実行します。


sqlc generate

すると、db/sqlc/配下の以下の3つのgoファイルが再生成されました。

db/sqlc/db.go

db/sqlc/db.go


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

package db

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

db/sqlc/models.go

view_barビューのJSONB型が、Go言語側でHogeArrayDto型にマッピングされました。

db/sqlc/models.go


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

package db

import (
	dto "exercise/db/dto"
)

type Bar struct {
	ColSerial    int32    `json:"colSerial"`
	ColHogeArray []string `json:"colHogeArray"`
}

type ViewBar struct {
	ColSerial    int32            `json:"colSerial"`
	ColHogeArray dto.HogeArrayDto `json:"colHogeArray"`
}

db/sqlc/query.sql.go

db/sqlc/query.sql.go


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

package db

import (
	"context"
)

const listBar = `-- name: ListBar :many
SELECT
    col_serial, col_hoge_array
FROM
    view_bar
`

func (q *Queries) ListBar(ctx context.Context) ([]ViewBar, error) {
	rows, err := q.db.Query(ctx, listBar)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ViewBar
	for rows.Next() {
		var i ViewBar
		if err := rows.Scan(&i.ColSerial, &i.ColHogeArray); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

実装(2)の実行結果

APIのレスポンスは以下のようになりました。
複合型のキーがスネークケースですが、それ以外は良いですね。


[
    {
        "colSerial": 1,
        "colHogeArray": [
            {
                "col_int": 10,
                "col_varchar": "aaa"
            },
            {
                "col_int": 20,
                "col_varchar": "bbb"
            },
            {
                "col_int": 30,
                "col_varchar": "ccc"
            }
        ]
    },
    {
        "colSerial": 2,
        "colHogeArray": [
            {
                "col_int": 100,
                "col_varchar": "yyy"
            },
            {
                "col_int": 200,
                "col_varchar": "zzz"
            }
        ]
    }
]

APIレスポンスのキーの一部がスネークケースでも良ければ、実装(2)で終わりにして良いでしょう。

実装(3)：スネークケースだったjsonのキーをキャメルケースにする

実装(3)は泥臭いですし、もっと良い実装があるかもしれません。
人が編集するファイルで実装(2)と内容が違うのは、「db/query/schema.sql」と「db/dto/hoge_dto.go」の2つです。

実装(3)のdb/query/schema.sql

CREATE VIEW文に、jsonのキーをキャメルケースに泥臭く変換するロジックを加えます。
PostgreSQL側でも、view_barビューを作り直す必要があります。

db/query/schema.sql


CREATE TYPE type_hoge AS (
    col_int INTEGER,
    col_varchar VARCHAR
);

CREATE TABLE bar
(
    col_serial SERIAL NOT NULL,
    col_hoge_array type_hoge[] NOT NULL
);

CREATE VIEW view_bar AS
SELECT
    col_serial,
    (SELECT
         TO_JSONB(ARRAY_AGG(
                     JSONB_BUILD_OBJECT('colInt', TO_JSONB(hoge)->'col_int') ||
                     JSONB_BUILD_OBJECT('colVarchar', TO_JSONB(hoge)->'col_varchar')))
     FROM
         UNNEST(col_hoge_array) AS hoge
    ) AS col_hoge_array
FROM
    bar;

view_barビューからの検索結果は、以下の通りです。

実装(3)のdb/dto/hoge_dto.go

view_barビューの検索結果のjsonデータに合わせて、最終jsonがキャメルケースになるように型を作ります。

db/dto/hoge_dto.go


package dto

type HogeDto struct {
    ColInt    int32  `json:"colInt"`
    ColString string `json:"colVarchar"`
}

type HogeArrayDto []HogeDto

実装(3)のdb/sqlc/*.go（ジェネレーターで生成）

上記2ファイルの編集が終わったら、Project Rootで以下を実行して、ジェネレーター生成ファイルを作り直します。


sqlc generate

すると、db/sqlc/配下の以下の3つのgoファイルが再生成されました。

db/sqlc/db.go

db/sqlc/db.go


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

package db

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

db/sqlc/models.go

db/sqlc/models.go


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

package db

import (
	dto "exercise/db/dto"
)

type Bar struct {
	ColSerial    int32    `json:"colSerial"`
	ColHogeArray []string `json:"colHogeArray"`
}

type ViewBar struct {
	ColSerial    int32            `json:"colSerial"`
	ColHogeArray dto.HogeArrayDto `json:"colHogeArray"`
}

db/sqlc/query.sql.go

db/sqlc/query.sql.go


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

package db

import (
	"context"
)

const listBar = `-- name: ListBar :many
SELECT
    col_serial, col_hoge_array
FROM
    view_bar
`

func (q *Queries) ListBar(ctx context.Context) ([]ViewBar, error) {
	rows, err := q.db.Query(ctx, listBar)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ViewBar
	for rows.Next() {
		var i ViewBar
		if err := rows.Scan(&i.ColSerial, &i.ColHogeArray); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

実装(3)の実行結果

APIのレスポンスは以下のようになりました。
複合型のキーがキャメルケースになり、レスポンスの全てのキーをキャメルケースで統一できました。

まとめ

本記事ではSELECT文のみでしたが、PostgreSQLの複合型の配列を、JSON経由でsqlcで扱うことができました。

いずれsqlcでも、PostgreSQLの複合型が正式にサポートされると予想しています。→参考ページ。
その時は本記事のAPI自体のソースコードは修正の必要がなく、実装(2)・実装(3)でCREATEしたview_barビューをDROPし、Go言語側のファイルを微修正すれば良いということになります。

INSERT文やUPDATE文は、別の機会に試そうと思います。

チーム開発参加の記録【2023-06～2023-08】(3) sqlc + jackc/pgx/v5（v5.4.1）からPostgreSQLの複合型の配列を使ってみた

本シリーズのリンク

本記事で行うこと

前提

データベースの準備

3通りの実装

ディレクトリ構成＆ファイル一覧

実装(1)・実装(2)・実装(3)で共通するソースコードを準備

共通コード：app.go

共通コード：go.mod

実装(1)

実装(1)のdb/query/schema.sql

実装(1)のdb/query/query.sql

実装(1)のsqlc.yaml

実装(1)のdb/sqlc/*.go（ジェネレーターで生成）

実装(1)の実行結果

実装(2)：PostgreSQLのJSONB型を経由する

実装(2)のdb/query/schema.sql

実装(2)のdb/query/query.sql

実装(2)のdb/dto/hoge_dto.go

実装(2)のsqlc.yaml

実装(2)のdb/sqlc/*.go（ジェネレーターで生成）

実装(2)の実行結果

実装(3)：スネークケースだったjsonのキーをキャメルケースにする

実装(3)のdb/query/schema.sql

実装(3)のdb/dto/hoge_dto.go

実装(3)のdb/sqlc/*.go（ジェネレーターで生成）

実装(3)の実行結果

まとめ