あるオンラインサロンでチーム開発に参加しています。
私はチーム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の複合型の配列の更新について、もう少し煮詰める

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

本記事で行うこと

第4回の記事で、まだsqlcがサポートしていない複合型の配列の更新を、ストアドプロシージャで補いました。
その後の個人ワークで、sqlcもストアドプロシージャも使わずに、Go言語側でpgxを使って同じことができるか試してみました。
その結果、pgxを使えば何でも出来そうだけど、複合型の配列の更新部分でSQL文を動的に組み立てざるを得ないかな？という結論に至りました。
ストアドプロシージャは静的SQLで行けたので、私はそっちを選びたいと思いました。

そこで本記事も第4回と同様に、sqlcで出来なかったことを、PostgreSQLの機能であるストアドプロシージャ／ストアドファンクションで補う方針で行きます。
※ 第4回ではストアドプロシージャを使いましたが、本記事では値を返すストアドファンクションを試します。

参考URL（感謝します）

あまり知られていないPostgreSQLの機能

データベース側での準備

PostgreSQLで以下のSQLを実行します。

type_hoge複合型と、fooテーブルをCREATE

今回のUPSERT処理の対象である、fooテーブルをCREATEします。
fooテーブルはtype_hoge複合型の配列の列を持ちます。


CREATE TYPE type_hoge AS
(
    a INTEGER,
    b INTEGER
);

CREATE TABLE foo
(
    id INTEGER NOT NULL PRIMARY KEY,
    col_composite_array type_hoge[] NOT NULL
);

upsert_fooストアドファンクション

upsert_fooファンクション内でfooテーブルをUPSERTします。

upsert_fooファンクションの引数として、UPSERTしたい複数件のデータの配列を、JSONB型で渡します。
- sqlcがサポートしていない複合型の配列でなく、サポートしているJSONB型を使用します。
upsert_fooファンクションの戻り値として、INSERTされた件数とUPDATEされた件数を返すようにしました。
- 戻り値の型をresult_upsert_fooテーブルとしてCREATEします。当初はテーブルでなく複合型で作っていたのですが（CREATE TYPE）、sqlcが認識してくれなかったので、テーブルでCREATEし直しました。
- UPSERTを行うのに、PostgreSQL 15からMERGE文が使えるようになりましたが、INSERTされた件数とUPDATEされた件数を取得する方法が分からなかったので、従来からあるINSERT ... ON CONFLICT文を使用しています。


CREATE TABLE result_upsert_foo
(
    inserted INTEGER NOT NULL,
    updated  INTEGER NOT NULL
);

CREATE OR REPLACE FUNCTION upsert_foo(
    data JSONB
)
    RETURNS result_upsert_foo
    LANGUAGE plpgsql
    volatile
AS
$$
DECLARE
    composite_array type_hoge[];
    xmx INTEGER;
    rec result_upsert_foo%ROWTYPE DEFAULT ROW(0, 0);
BEGIN
    FOR i IN 0..JSONB_ARRAY_LENGTH(data) - 1 LOOP
        composite_array = ARRAY[]::type_hoge[];
        FOR j IN 0..JSONB_ARRAY_LENGTH(data->i->'col_composite_array') - 1 LOOP
            composite_array = ARRAY_APPEND(composite_array,
                ROW(data->i->'col_composite_array'->j->'a',
                    data->i->'col_composite_array'->j->'b')::type_hoge);
        END LOOP;

        INSERT INTO foo
        (
            id,
            col_composite_array
        )
        VALUES
        (
            (data->i->'id')::INTEGER,
            composite_array
        )
        ON CONFLICT (id) DO
        UPDATE
        SET
            col_composite_array = EXCLUDED.col_composite_array
        RETURNING
            xmax
        INTO xmx;

        IF xmx = 0 THEN
            rec.inserted = rec.inserted + 1;
        ELSE
            rec.updated = rec.updated + 1;
        END IF;
    END LOOP;
    RETURN rec;
END
$$;

Go側の実装

Go+Gin+sqlcを使用して、fooテーブルへUPSERT処理を行うAPIを実装します。

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


Project Root
  ├── db/
  │     ├── 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

各ソースコード

db/query/schema.sql


CREATE TYPE type_hoge AS
(
    a INTEGER,
    b INTEGER
);

CREATE TABLE foo
(
    id INTEGER NOT NULL PRIMARY KEY,
    col_composite_array type_hoge[] NOT NULL
);

CREATE TABLE result_upsert_foo
(
    inserted INTEGER NOT NULL,
    updated  INTEGER NOT NULL
);

CREATE OR REPLACE FUNCTION upsert_foo(
    data JSONB
)
    RETURNS result_upsert_foo
    LANGUAGE plpgsql
    volatile
AS
$$
DECLARE
    composite_array type_hoge[];
    xmx INTEGER;
    rec result_upsert_foo%ROWTYPE DEFAULT ROW(0, 0);
BEGIN
    FOR i IN 0..JSONB_ARRAY_LENGTH(data) - 1 LOOP
        composite_array = ARRAY[]::type_hoge[];
        FOR j IN 0..JSONB_ARRAY_LENGTH(data->i->'col_composite_array') - 1 LOOP
            composite_array = ARRAY_APPEND(composite_array,
                ROW(data->i->'col_composite_array'->j->'a',
                    data->i->'col_composite_array'->j->'b')::type_hoge);
        END LOOP;

        INSERT INTO foo
        (
            id,
            col_composite_array
        )
        VALUES
        (
            (data->i->'id')::INTEGER,
            composite_array
        )
        ON CONFLICT (id) DO
        UPDATE
        SET
            col_composite_array = EXCLUDED.col_composite_array
        RETURNING
            xmax
        INTO xmx;

        IF xmx = 0 THEN
            rec.inserted = rec.inserted + 1;
        ELSE
            rec.updated = rec.updated + 1;
        END IF;
    END LOOP;
    RETURN rec;
END
$$;

db/query/query.sql

ストアドファンクションを呼び出して、UPSERT処理を行います。
ファンクションの戻り値のinserted列はINSERTされた件数、updated列はUPDATEされた件数です。

db/query/query.sql


/* name: UpsertFoo :one */
SELECT
    inserted,
    updated
FROM
    upsert_foo(@data);

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"

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 ()

type Foo struct {
	ID                int32    `json:"id"`
	ColCompositeArray []string `json:"colCompositeArray"`
}

type ResultUpsertFoo struct {
	Inserted int32 `json:"inserted"`
	Updated  int32 `json:"updated"`
}

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 upsertFoo = `-- name: UpsertFoo :one
SELECT
    inserted,
    updated
FROM
    upsert_foo($1)
`

func (q *Queries) UpsertFoo(ctx context.Context, data []byte) (ResultUpsertFoo, error) {
	row := q.db.QueryRow(ctx, upsertFoo, data)
	var i ResultUpsertFoo
	err := row.Scan(&i.Inserted, &i.Updated)
	return i, err
}

app.go

Web APIを実装します。以下の仕様にしました。

upsert API（POST）：リクエストボディのJSONをそのままストアドファンクションに引き渡してUPSERT処理してもらい、レスポンスとしてINSERTされた件数とUPDATEされた件数を返します。

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 upsertFoo(c *gin.Context) {
	data, err := c.GetRawData()
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
		return
	}

	q := db.New(pool)
	resultSet, err := q.UpsertFoo(context.Background(), data)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
		return
	}
	c.JSON(http.StatusOK, resultSet)
}

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.POST("/upsert", upsertFoo)

	router.Run("0.0.0.0:8080")
}

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
)

実行結果

fooテーブルが空の状態からスタートします。
Postmanを使って、リクエストボディに以下のJSONを渡してAPIを呼んでみます。


[
	{
		"id": 1,
		"col_composite_array": [
			{
				"a": 11,
				"b": 12
			},
			{
				"a": 13,
				"b": 14
			}
		]
	},
	{
		"id": 2,
		"col_composite_array": [
			{
				"a": 21,
				"b": 22
			},
			{
				"a": 23,
				"b": 24
			}
		]
	}
]

2件INSERTされたというレスポンスが返ってきました。

PostgreSQL側でも2件のINSERTを確認できました。

続いて、リクエストボディに以下のJSONを渡してAPIを呼んでみます。
既存の2件をUPDATEし、加えてもう1件INSERTする内容です。


[
	{
		"id": 1,
		"col_composite_array": [
			{
				"a": 101,
				"b": 102
			},
			{
				"a": 103,
				"b": 104
			}
		]
	},
	{
		"id": 2,
		"col_composite_array": [
			{
				"a": 201,
				"b": 202
			},
			{
				"a": 203,
				"b": 204
			}
		]
	},
	{
		"id": 3,
		"col_composite_array": [
			{
				"a": 301
			},
			{
				"b": 304
			}
		]
	}
]

1件INSERTされ、2件UPDATEされたというレスポンスが返ってきました。

PostgreSQL側でも処理を確認できました。

まとめ

Go言語側で書くなら動的にSQL文を組み立てざるを得ない処理でも、ストアドプロシージャ／ストアドファンクションを使えば静的SQLで行ける場合があることがわかりました。私は動的SQLは、回避できるなら回避したいです。
本シリーズを通して、sqlcは感覚がHasuraと近いという感想を持ちました。
- Hasuraは人がSQLやストアドプロシージャ等を書くと、DB操作するAPIを自動生成してくれます。
- 一方sqlcは人がAPIをコーディングしますが、本シリーズのAPIはシンプルな記述しかしておらず、コーディングの手間がかかっていません。
- いざとなったらGo言語でごりごりロジックを書ける柔軟性があり、技術選択する上でHasuraよりも安心感があると思いました。

チーム開発参加の記録【2023-06～2023-08】(6) PostgreSQLの複合型の配列の更新について、もう少し煮詰める

本シリーズのリンク

本記事で行うこと

参考URL（感謝します）

データベース側での準備

type_hoge複合型と、fooテーブルをCREATE

upsert_fooストアドファンクション

Go側の実装

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

各ソースコード

db/query/schema.sql

db/query/query.sql

sqlc.yaml

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

app.go

go.mod

実行結果

まとめ