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

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

# 本シリーズのリンク

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

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

# 本記事で行うこと

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

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

# 参考URL（感謝します）
- [あまり知られていないPostgreSQLの機能](https://postd.cc/postgresql-unknown-features/)

# データベース側での準備

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

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

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

```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
);
```

## 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文を使用しています。

```sql

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

```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

```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で以下を実行して、ジェネレーター生成ファイルを作ります。

```sh

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

```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](https://www.postman.com/)を使って、リクエストボディに以下のJSONを渡してAPIを呼んでみます。

```json

[
	{
		"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されたというレスポンスが返ってきました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/89d3aa8d-4947-723b-1c03-c4653b5d5dc7.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/911b8808-3514-ba21-c965-05551b8b721a.png)

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

```json

[
	{
		"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されたというレスポンスが返ってきました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/1b9f5538-0163-3286-a105-6b96568419e5.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/196e6c21-534e-be2e-5f31-2bfc70d32508.png)

# まとめ

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