Go to Qiita Advent Calendar 2024 Top
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】(6) PostgreSQLの複合型の配列の更新について、もう少し煮詰める

Last updated at Posted at 2023-07-09

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

本シリーズのリンク

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

本記事で行うこと

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

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

参考URL（感謝します）

データベース側での準備

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

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


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を使って、リクエストボディに以下の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されたというレスポンスが返ってきました。

image.png

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

image.png

続いて、リクエストボディに以下の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されたというレスポンスが返ってきました。

image.png

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

image.png

まとめ

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