チーム開発参加の記録【2023-06～2023-08】(4) sqlc + jackc/pgx/v5 からPostgreSQLの複合型の配列を更新してみた

あるオンラインサロンでチーム開発に参加しています。
私はチーム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の複合型の配列をSELECT文で使ってみました。
本記事ではSELECT文に加えて、複合型の配列へのINSERT文とUPDATE文も試します。

前提

前回の記事と同じです。

データベース側での準備

TYPEとTABLEの作成

以下のSQLを実行して、type_fuga複合型とbazテーブルをCREATEします。

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

CREATE TABLE baz
(
    id INTEGER NOT NULL,
    col_fuga_array type_fuga[] NOT NULL
);

ビューの作成

前回の記事と同じ理由で、ビューをCREATEします。

CREATE VIEW view_baz AS
SELECT
    id,
    (SELECT
         TO_JSONB(ARRAY_AGG(
                     JSONB_BUILD_OBJECT('colInt', TO_JSONB(fuga)->'col_int') ||
                     JSONB_BUILD_OBJECT('colVarchar', TO_JSONB(fuga)->'col_varchar')))
     FROM
         UNNEST(col_fuga_array) AS fuga
    ) AS col_fuga_array
FROM
    baz;

ストアドプロシージャの作成

本記事執筆時点で、sqlcの公式ページに複合型（Composite Types）の記載がなく、まだ複合型をサポートしていない様子です。
本記事の結論を先に書いてしまいますと、ストアドプロシージャを利用してsqlc未サポートの機能を補います。

PostgreSQL側で以下のSQLを実行して、ストアドプロシージャをCREATEします。
sqlcはJSONB型をサポートしていますので、複合型の配列の代わりにJSONB型パラメーターをインターフェースとして利用し、ストアドプロシージャ内部で複合型の配列に変換しています。

INSERT文を行うストアドプロシージャ

CREATE OR REPLACE PROCEDURE insert_baz(
    id INTEGER,
    col_fuga_array JSONB
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    fuga_array type_fuga[];
BEGIN
    FOR i IN 0..JSONB_ARRAY_LENGTH(col_fuga_array) - 1 LOOP
        fuga_array = ARRAY_APPEND(fuga_array,
            ROW((col_fuga_array->i->'colInt')::INTEGER,
                 col_fuga_array->i->>'colVarchar')::type_fuga);
    END LOOP;

    INSERT INTO baz
    (
        id,
        col_fuga_array
    )
    VALUES
    (
        insert_baz.id,
        fuga_array
    );
END
$$;

UPDATE文を行うストアドプロシージャ

CREATE OR REPLACE PROCEDURE update_baz(
    id INTEGER,
    col_fuga_array JSONB
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    fuga_array type_fuga[];
BEGIN
    FOR i IN 0..JSONB_ARRAY_LENGTH(col_fuga_array) - 1 LOOP
        fuga_array = ARRAY_APPEND(fuga_array,
            ROW((col_fuga_array->i->'colInt')::INTEGER,
                 col_fuga_array->i->>'colVarchar')::type_fuga);
    END LOOP;

    UPDATE baz
    SET
        col_fuga_array = fuga_array
    WHERE
        baz.id = update_baz.id;
END
$$;

Go側の実装

それでは、Go+Gin+sqlcを使用して、bazテーブルへSELECT、INSERT、UPDATEを行うAPIを実装します。

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

Project Root
  ├── db/
  │     ├── dto/
  │     │     └── fuga_dto.go
  │     ├── query/
  │     │     ├── query.sql
  │     │     └── schema.sql
  │     └── sqlc/
  │            ├── db.go
  │            ├── models.go
  │            └── query.sql.go
  ├── app.go
  ├── go.mod
  └── sqlc.yaml

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

• db/query/schema.sql
• db/query/query.sql
• db/dto/fuga_dto.go
• sqlc.yaml

各ソースコード

db/query/schema.sql

db/query/schema.sql

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

CREATE TABLE baz
(
    id INTEGER NOT NULL,
    col_fuga_array type_fuga[] NOT NULL
);

CREATE VIEW view_baz AS
SELECT
    id,
    (SELECT
         TO_JSONB(ARRAY_AGG(
                     JSONB_BUILD_OBJECT('colInt', TO_JSONB(fuga)->'col_int') ||
                     JSONB_BUILD_OBJECT('colVarchar', TO_JSONB(fuga)->'col_varchar')))
     FROM
         UNNEST(col_fuga_array) AS fuga
    ) AS col_fuga_array
FROM
    baz;

CREATE OR REPLACE PROCEDURE insert_baz(
    id INTEGER,
    col_fuga_array JSONB
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    fuga_array type_fuga[];
BEGIN
    FOR i IN 0..JSONB_ARRAY_LENGTH(col_fuga_array) - 1 LOOP
            fuga_array = ARRAY_APPEND(fuga_array,
                ROW((col_fuga_array->i->'colInt')::INTEGER,
                     col_fuga_array->i->>'colVarchar')::type_fuga);
    END LOOP;

    INSERT INTO baz
    (
        id,
        col_fuga_array
    )
    VALUES
    (
        insert_baz.id,
        fuga_array
    );
END
$$;

CREATE OR REPLACE PROCEDURE update_baz(
    id INTEGER,
    col_fuga_array JSONB
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    fuga_array type_fuga[];
BEGIN
    FOR i IN 0..JSONB_ARRAY_LENGTH(col_fuga_array) - 1 LOOP
            fuga_array = ARRAY_APPEND(fuga_array,
                ROW((col_fuga_array->i->'colInt')::INTEGER,
                     col_fuga_array->i->>'colVarchar')::type_fuga);
    END LOOP;

    UPDATE baz
    SET
        col_fuga_array = fuga_array
    WHERE
        baz.id = update_baz.id;
END
$$;

db/query/query.sql

SELECT文はビューを利用し、INSERT文とUPDATE文はストアドプロシージャを利用します。
そのいずれも、sqlc側に複合型の配列を隠し、JSONB型を使ってもらうようにしています。

db/query/query.sql

-- name: SelectBaz :many
SELECT
    *
FROM
    view_baz;

-- name: InsertBaz :exec
CALL insert_baz(
    @id,
    @col_fuga_array
);

-- name: UpdateBaz :exec
CALL update_baz(
    @id,
    @col_fuga_array
);

db/dto/fuga_dto.go

前回の記事からの変更点として、複合型の中身のcolIntとcolVarcharは、NULLも入れられる型にしました。

db/dto/fuga_dto.go

package dto

import "github.com/jackc/pgx/v5/pgtype"

type FugaDto struct {
	ColInt    pgtype.Int4 `json:"colInt"`
	ColString pgtype.Text `json:"colVarchar"`
}

type FugaArrayDto []FugaDto

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"
        overrides:
          - column: "view_baz.col_fuga_array"
            go_type:
              import: "exercise/db/dto"
              package: "dto"
              type: "FugaArrayDto"

db/sqlc/*.go（ジェネレーターで生成）

ここまでのファイルの編集が終わったら、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 Baz struct {
	ID           int32    `json:"id"`
	ColFugaArray []string `json:"colFugaArray"`
}

type ViewBaz struct {
	ID           int32            `json:"id"`
	ColFugaArray dto.FugaArrayDto `json:"colFugaArray"`
}

• 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 insertBaz = `-- name: InsertBaz :exec
CALL insert_baz(
    $1,
    $2
)
`

type InsertBazParams struct {
	ID           int32  `json:"id"`
	ColFugaArray []byte `json:"colFugaArray"`
}

func (q *Queries) InsertBaz(ctx context.Context, arg InsertBazParams) error {
	_, err := q.db.Exec(ctx, insertBaz, arg.ID, arg.ColFugaArray)
	return err
}

const selectBaz = `-- name: SelectBaz :many
SELECT
    id, col_fuga_array
FROM
    view_baz
`

func (q *Queries) SelectBaz(ctx context.Context) ([]ViewBaz, error) {
	rows, err := q.db.Query(ctx, selectBaz)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ViewBaz
	for rows.Next() {
		var i ViewBaz
		if err := rows.Scan(&i.ID, &i.ColFugaArray); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const updateBaz = `-- name: UpdateBaz :exec
CALL update_baz(
    $1,
    $2
)
`

type UpdateBazParams struct {
	ID           int32  `json:"id"`
	ColFugaArray []byte `json:"colFugaArray"`
}

func (q *Queries) UpdateBaz(ctx context.Context, arg UpdateBazParams) error {
	_, err := q.db.Exec(ctx, updateBaz, arg.ID, arg.ColFugaArray)
	return err
}

app.goとgo.mod

app.goで、SELECT、INSERT、UPDATEを行う3つのAPIを実装します。
自動生成されたquery.sql.goを見ながら、どう実装するか考えました。

• app.go

app.go

package main

import (
	"context"
	"encoding/json"
	"exercise/db/dto"
	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"`
}

type httpMessage struct {
	Message string `json:"message"`
}

type bazRequestBody struct {
	ID           int32            `json:"id"`
	ColFugaArray dto.FugaArrayDto `json:"colFugaArray"`
}

var pool *pgxpool.Pool

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

func InsertBaz(c *gin.Context) {
	var req bazRequestBody
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
		return
	}
	colFugaArray, err := json.Marshal(req.ColFugaArray)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
		return
	}
	q := db.New(pool)
	arg := db.InsertBazParams{
		ID:           req.ID,
		ColFugaArray: colFugaArray,
	}
	err = q.InsertBaz(context.Background(), arg)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
		return
	}
	c.JSON(http.StatusOK, httpMessage{Message: "ok"})
}

func UpdateBaz(c *gin.Context) {
	var req bazRequestBody
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
		return
	}
	colFugaArray, err := json.Marshal(req.ColFugaArray)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
		return
	}
	q := db.New(pool)
	arg := db.UpdateBazParams{
		ID:           req.ID,
		ColFugaArray: colFugaArray,
	}
	err = q.UpdateBaz(context.Background(), arg)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
		return
	}
	c.JSON(http.StatusOK, httpMessage{Message: "ok"})
}

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("/select", SelectBaz)
	router.POST("/insert", InsertBaz)
	router.PUT("/update", UpdateBaz)

	router.Run("0.0.0.0:8080")
}

• go.mod

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
)

実行結果

最初はbazテーブルが空ですので、まずデータをINSERTします。

INSERT API

Postmanを使ってAPIを2回呼んで、データを2件登録してみました。

1回目のリクエストボディ

{
    "id": 1,
    "colFugaArray": [
        {
            "colInt": 10,
            "colVarchar": null
        },
        {
            "colInt": null,
            "colVarchar": "bbb"
        },
        {
            "colInt": 30,
            "colVarchar": "ccc"
        }
    ]
}

2回目のリクエストボディ

{
    "id": 2,
    "colFugaArray": [
        {
            "colInt": 10,
            "colVarchar": "aaa"
        },
        {
            "colInt": 20,
            "colVarchar": "bbb"
        },
        {
            "colInt": 30,
            "colVarchar": "ccc"
        },
        {
            "colInt": 40,
            "colVarchar": "ddd"
        }
    ]
}

Postmanのスクリーンショット

PostgreSQL側を見ると、bazテーブルに2件INSERTされたことが確認できました。

UPDATE API

id=2のデータを更新してみます。

リクエストボディ

{
    "id": 2,
    "colFugaArray": [
        {
            "colInt": null,
            "colVarchar": "AAA"
        },
        {
            "colInt": 200,
            "colVarchar": null
        },
        {
            "colInt": 300,
            "colVarchar": "CCC"
        }
    ]
}

Postmanのスクリーンショット

PostgreSQL側を見ると、id=2のデータが更新されたことが確認できました。

SELECT API

PostmanでAPIを呼んで、bazデータを全件取得してみました。

意図したレスポンスが得られました。

まとめ

本記事執筆時点で、sqlcはPostgreSQLの複合型をサポートしていないようですが、PostgreSQLのビューとストアドプロシージャを使って足りない機能を補えました。
sqlcは若いプロダクトで、まだ足りない機能があるかもしれませんが、ビュー、ストアドプロシージャ、ストアドファンクションと併用すれば、たいていのことは出来そうな感触を得ました。