LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@kanedaq

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

Last updated at Posted at 2023-06-21

あるオンラインサロンでチーム開発に参加しています。
私はチーム03のバックエンド側メンバーに加わりました。
チーム03のバックエンドは、Go+Gin+sqlc、DBは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テーブルからの検索結果は、以下の通りです。

image.png

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

image.png


[
    {
        "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ビューからの検索結果は、以下の通りです。

image.png

実装(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のレスポンスは以下のようになりました。
複合型のキーがスネークケースですが、それ以外は良いですね。

image.png


[
    {
        "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ビューからの検索結果は、以下の通りです。

image.png

実装(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のレスポンスは以下のようになりました。
複合型のキーがキャメルケースになり、レスポンスの全てのキーをキャメルケースで統一できました。

image.png

まとめ

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

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

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

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?