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】(4) sqlc + jackc/pgx/v5 からPostgreSQLの複合型の配列を更新してみた

Last updated at Posted at 2023-06-28

あるオンラインサロンでチーム開発に参加しています。
私はチーム03のバックエンド側メンバーに加わりました。
チーム03のバックエンドは、Go+Gin+sqlc、DBは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のスクリーンショット

image.png

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

image.png

UPDATE API

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

リクエストボディ


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

Postmanのスクリーンショット

image.png

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

image.png

SELECT API

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

image.png

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

まとめ

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

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?