本シリーズのリンク

チーム開発参加の記録【2023-10～2024-03】(1) Go言語用ORM「Bun」をDBファーストで使う試み（SQLite使用）
チーム開発参加の記録【2023-10～2024-03】(2) Go言語からTursoを使ってみた
チーム開発参加の記録【2023-10～2024-03】(3) slogを使ってリクエスト・レスポンス情報をログ出力してみた
チーム開発参加の記録【2023-10～2024-03】(4) Go言語用ORM「Bun」をDBファーストで使う試み（PostgreSQL使用）
チーム開発参加の記録【2023-10～2024-03】(5) Go言語用ORM「Bun」でトランザクション、UPSERT、JOINを使ってみた
チーム開発参加の記録【2023-10～2024-03】(6) Go言語用ORM「Bun」で複数のクエリーをまとめてDBサーバーで実行
チーム開発参加の記録【2023-10～2024-03】(7) slogでErrorレベルのログをSlackに飛ばしてみた

本記事で行うこと

これまでの記事で、Go言語用ORM「Bun」を使って基本的なCRUD操作を試しました。
次はもう少し複雑な処理を書いてみよう、ということで、本記事で以下の3つを試します。

トランザクション
UPSERT
テーブルのJOIN

データベースはPostgreSQLと、libSQL（SQLiteのフォーク）の両方を使用します。

テーブルを作る

それでは作業に移ります。
DBファーストで行きますので、まずDDLを書きます。
このページのCREATE TABLE文を、PostgreSQLとlibSQLの両方で動くように書き直しました（本番プロジェクトではこんなことはせず、各DBに最適なDDLを書きます）。
ただし、libSQL用にはSTRICTオプションを付け加えます。

PostgreSQL用

CREATE TABLE cities (
        name     TEXT  PRIMARY KEY,
        location TEXT
);

CREATE TABLE weather (
        city      TEXT REFERENCES CITIES(name),
        temp_lo   INT,           -- 最低気温
        temp_hi   INT,           -- 最高気温
        prcp      REAL,          -- 降水量
        date      TEXT
);

libSQL用

CREATE TABLE cities (
        name     TEXT  PRIMARY KEY,
        location TEXT
) STRICT;

CREATE TABLE weather (
        city      TEXT REFERENCES CITIES(name),
        temp_lo   INT,           -- 最低気温
        temp_hi   INT,           -- 最高気温
        prcp      REAL,          -- 降水量
        date      TEXT
) STRICT;

EchoとBunを使って、トランザクション操作するWeb APIサーバーを作ってみる

それでは、Go言語からBunを使っていきます。
最初に、今回書いたソースコード全体を載せてしまいます。

ディレクトリ構造とファイル一覧

Project Root
  ├── app.go
  └── go.mod

app.go

app.go

package main

import (
	"context"
	"database/sql"
	"net/http"

	"github.com/labstack/echo/v4"
	"github.com/labstack/echo/v4/middleware"
	_ "github.com/tursodatabase/libsql-client-go/libsql"
	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/pgdialect"
	"github.com/uptrace/bun/dialect/sqlitedialect"
	"github.com/uptrace/bun/driver/pgdriver"
	"github.com/uptrace/bun/extra/bundebug"
)

var (
	postgresDb *bun.DB
	libsqlDb   *bun.DB
)

func postgresGood(c echo.Context) error {
	return transaction(postgresDb, c, true)
}

func libsqlGood(c echo.Context) error {
	return transaction(libsqlDb, c, true)
}

func postgresBad(c echo.Context) error {
	return transaction(postgresDb, c, false)
}

func libsqlBad(c echo.Context) error {
	return transaction(libsqlDb, c, false)
}

func transaction(db *bun.DB, c echo.Context, isGood bool) error {
	type (
		dummyModel struct{}

		httpError struct {
			Error string `json:"error"`
		}
	)

	ctx := context.TODO()

	// トランザクション開始
	tx, err := db.BeginTx(ctx, &sql.TxOptions{})
	if err != nil {
		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}
	committed := false
	defer func() {
		if !committed {
			tx.Rollback()
		}
	}()

	// 親テーブルに1件UPSERT

	//// MERGE文はlibSQLで使えなかったので、INSERT ... ON CONFLICTに書き直した。
	//if _, err := tx.NewMerge().
	//	Model((*dummyModel)(nil)).
	//	ModelTableExpr("cities").
	//	With("data_source", tx.NewSelect().
	//		ColumnExpr("'San Francisco'  AS name").
	//		ColumnExpr("'(-194.0, 53.0)' AS location"),
	//	).
	//	Using("data_source").
	//	On("cities.name = data_source.name").
	//	WhenUpdate("MATCHED", func(q *bun.UpdateQuery) *bun.UpdateQuery {
	//		return q.Set("location = data_source.location")
	//	}).
	//	WhenInsert("NOT MATCHED", func(q *bun.InsertQuery) *bun.InsertQuery {
	//		return q.Value("name", "data_source.name").
	//			Value("location", "data_source.location")
	//	}).
	//	Exec(ctx); err != nil {
	//
	//	return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	//}

	// INSERT ... ON CONFLICT
	if _, err := tx.NewInsert().
		Model((*dummyModel)(nil)).
		ModelTableExpr("cities").
		Value("name", "?", "San Francisco").
		Value("location", "?", "(-194.0, 53.0)").
		On("CONFLICT (name) DO UPDATE").
		Set("location = EXCLUDED.location").
		Exec(ctx); err != nil {

		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}

	// 子テーブルに3件INSERT

	// 1件目
	if _, err := tx.NewInsert().
		Model((*dummyModel)(nil)).
		ModelTableExpr("weather").
		Value("city", "?", "San Francisco").
		Value("temp_lo", "?", 43).
		Value("temp_hi", "?", 57).
		Value("prcp", "?", 0.0).
		Value("date", "?", "2023-01-01").
		Exec(ctx); err != nil {

		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}

	// 2件目
	if _, err := tx.NewInsert().
		Model((*dummyModel)(nil)).
		ModelTableExpr("weather").
		Value("city", "?", "San Francisco").
		Value("temp_lo", "?", 43).
		Value("temp_hi", "?", 57).
		Value("prcp", "?", 0.0).
		Value("date", "?", "2023-01-02").
		Exec(ctx); err != nil {

		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}

	// 3件目のINSERTは、isGoodのときは成功させて、!isGoodのときは失敗させる
	var city string
	if isGood {
		city = "San Francisco" // 親テーブルに存在する値を外部キーに設定
	} else {
		city = "Hayward" // 親テーブルに存在しない値を外部キーに設定
	}
	if _, err := tx.NewInsert().
		Model((*dummyModel)(nil)).
		ModelTableExpr("weather").
		Value("city", "?", city).
		Value("temp_lo", "?", 43).
		Value("temp_hi", "?", 57).
		Value("prcp", "?", 0.0).
		Value("date", "?", "2023-01-03").
		Exec(ctx); err != nil {

		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}

	// コミット
	if err := tx.Commit(); err != nil {
		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}
	committed = true

	// トランザクションが成功したら、親テーブルと子テーブルをjoinして、結果表をレスポンスにして返す
	type resultSetType struct {
		Name   string   `json:"name"`
		TempLo *int64   `json:"tempLo"`
		TempHi *int64   `json:"tempHi"`
		Prcp   *float32 `json:"prcp"`
		Date   *string  `json:"date"`
	}
	resultSet := make([]resultSetType, 0)
	if err := db.NewSelect().
		Column("cities.name").
		Column("weather.temp_lo").
		Column("weather.temp_hi").
		Column("weather.prcp").
		Column("weather.date").
		Table("cities").
		Join("LEFT OUTER JOIN weather").
		JoinOn("cities.name = weather.city").
		Order("weather.date").
		Scan(ctx, &resultSet); err != nil {

		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}

	return c.JSON(http.StatusOK, resultSet)
}

func main() {
	// PostgreSQL初期設定
	dsn := "postgres://postgres:secret@localhost:5432/postgres?sslmode=disable"
	sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN(dsn)))

	postgresDb = bun.NewDB(sqldb, pgdialect.New())
	postgresDb.AddQueryHook(bundebug.NewQueryHook(
		bundebug.WithVerbose(true),
		bundebug.FromEnv("BUNDEBUG"),
	))

	// libSQL初期設定
	dbUrl := "http://127.0.0.1:8080"
	libdb, err := sql.Open("libsql", dbUrl)
	if err != nil {
		panic(err)
	}
	libdb.SetMaxOpenConns(1)

	libsqlDb = bun.NewDB(libdb, sqlitedialect.New())
	libsqlDb.AddQueryHook(bundebug.NewQueryHook(
		bundebug.WithVerbose(true),
		bundebug.FromEnv("BUNDEBUG"),
	))

	// EchoでAPIサーバー
	e := echo.New()
	e.Use(middleware.Logger())
	e.Use(middleware.Recover())

	// Routing
	postgresApi := e.Group("/postgres")
	libsqlApi := e.Group("/libsql")

	// 成功するトランザクション
	postgresApi.POST("/good", postgresGood)
	libsqlApi.POST("/good", libsqlGood)

	// 途中で失敗するトランザクション
	postgresApi.POST("/bad", postgresBad)
	libsqlApi.POST("/bad", libsqlBad)

	e.Logger.Fatal(e.Start(":1323"))
}

go.mod

module exercise_bun

go 1.21

require (
	github.com/labstack/echo/v4 v4.11.3
	github.com/tursodatabase/libsql-client-go v0.0.0-20231216154754-8383a53d618f
	github.com/uptrace/bun v1.1.16
	github.com/uptrace/bun/dialect/pgdialect v1.1.16
	github.com/uptrace/bun/dialect/sqlitedialect v1.1.16
	github.com/uptrace/bun/driver/pgdriver v1.1.16
	github.com/uptrace/bun/extra/bundebug v1.1.16
)

require (
	github.com/antlr/antlr4/runtime/Go/antlr/v4 v4.0.0-20230512164433-5d1fd1a340c9 // indirect
	github.com/fatih/color v1.15.0 // indirect
	github.com/golang-jwt/jwt v3.2.2+incompatible // indirect
	github.com/jinzhu/inflection v1.0.0 // indirect
	github.com/klauspost/compress v1.15.15 // indirect
	github.com/labstack/gommon v0.4.0 // indirect
	github.com/libsql/sqlite-antlr4-parser v0.0.0-20230802215326-5cb5bb604475 // indirect
	github.com/mattn/go-colorable v0.1.13 // indirect
	github.com/mattn/go-isatty v0.0.19 // indirect
	github.com/tmthrgd/go-hex v0.0.0-20190904060850-447a3041c3bc // indirect
	github.com/valyala/bytebufferpool v1.0.0 // indirect
	github.com/valyala/fasttemplate v1.2.2 // indirect
	github.com/vmihailenco/msgpack/v5 v5.3.5 // indirect
	github.com/vmihailenco/tagparser/v2 v2.0.0 // indirect
	golang.org/x/crypto v0.14.0 // indirect
	golang.org/x/exp v0.0.0-20220722155223-a9213eeb770e // indirect
	golang.org/x/net v0.17.0 // indirect
	golang.org/x/sys v0.13.0 // indirect
	golang.org/x/text v0.13.0 // indirect
	golang.org/x/time v0.3.0 // indirect
	mellium.im/sasl v0.3.1 // indirect
	nhooyr.io/websocket v1.8.7 // indirect
)

トランザクション処理

それでは、トランザクション処理を行うtransaction()の中身について、一つ一つ見ていきます。

トランザクション開始

Bunの公式ページを参考にしてコーディングしました。
コミットされなかったら、関数を抜けるときにロールバックするようにしました。

	// トランザクション開始
	tx, err := db.BeginTx(ctx, &sql.TxOptions{})
	if err != nil {
		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}
	committed := false
	defer func() {
		if !committed {
			tx.Rollback()
		}
	}()

上記コードからBunが生成したSQLです。

トランザクション開始：

BEGIN

ロールバック：

ROLLBACK

親テーブルへのUPSERT

本記事執筆時点で、libSQLではMERGE文が使えなかったので、PostgreSQLでもlibSQLでも動くようにINSERT ON CONFLICTを使用しました。

	// INSERT ... ON CONFLICT
	if _, err := tx.NewInsert().
		Model((*dummyModel)(nil)).
		ModelTableExpr("cities").
		Value("name", "?", "San Francisco").
		Value("location", "?", "(-194.0, 53.0)").
		On("CONFLICT (name) DO UPDATE").
		Set("location = EXCLUDED.location").
		Exec(ctx); err != nil {

		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}

上記コードからBunが生成したSQLを、整形して見やすくしました。
プライマリーキーのname列には「San Francisco」という値が入ります。

INSERT INTO cities (
    "name",
    "location"
)
VALUES (
    'San Francisco',
    '(-194.0, 53.0)'
)
ON CONFLICT (name) DO
UPDATE SET
    location = EXCLUDED.location

子テーブルへのINSERT

3件INSERTします。

1件目・2件目

INSERT文が成功するように記述します。

	// 1件目
	if _, err := tx.NewInsert().
		Model((*dummyModel)(nil)).
		ModelTableExpr("weather").
		Value("city", "?", "San Francisco").
		Value("temp_lo", "?", 43).
		Value("temp_hi", "?", 57).
		Value("prcp", "?", 0.0).
		Value("date", "?", "2023-01-01").
		Exec(ctx); err != nil {

		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}

	// 2件目
	if _, err := tx.NewInsert().
		Model((*dummyModel)(nil)).
		ModelTableExpr("weather").
		Value("city", "?", "San Francisco").
		Value("temp_lo", "?", 43).
		Value("temp_hi", "?", 57).
		Value("prcp", "?", 0.0).
		Value("date", "?", "2023-01-02").
		Exec(ctx); err != nil {

		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}

上記の1件目のコードからBunが生成したSQLを、整形して見やすくしました。

INSERT INTO weather (
    "city",
    "temp_lo",
    "temp_hi",
    "prcp",
    "date"
)
VALUES (
    'San Francisco',
    43,
    57,
    0,
    '2023-01-01'
)

3件目

引数isGoodがtrueの時は成功するように、falseの時は失敗するように記述します。

	// 3件目のINSERTは、isGoodのときは成功させて、!isGoodのときは失敗させる
	var city string
	if isGood {
		city = "San Francisco" // 親テーブルに存在する値を外部キーに設定
	} else {
		city = "Hayward" // 親テーブルに存在しない値を外部キーに設定
	}
	if _, err := tx.NewInsert().
		Model((*dummyModel)(nil)).
		ModelTableExpr("weather").
		Value("city", "?", city).
		Value("temp_lo", "?", 43).
		Value("temp_hi", "?", 57).
		Value("prcp", "?", 0.0).
		Value("date", "?", "2023-01-03").
		Exec(ctx); err != nil {

		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}

上記コードからBunが生成した、失敗するときのSQLを、整形して見やすくしました。
外部キーのcity列に、親テーブルにない値を設定しています。

INSERT INTO weather (
    "city",
    "temp_lo",
    "temp_hi",
    "prcp",
    "date"
)
VALUES (
    'Hayward',
    43,
    57,
    0,
    '2023-01-03'
)

コミット

ここまでエラーが起きなかったら、トランザクションをコミットします。

	if err := tx.Commit(); err != nil {
		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}
	committed = true

上記コードからBunが生成したSQLです。

COMMIT

親テーブルと子テーブルをJOIN

処理が成功したか見たいので、親テーブルと子テーブルをjoinして、結果表をレスポンスにして返します。

	// トランザクションが成功したら、親テーブルと子テーブルをjoinして、結果表をレスポンスにして返す
	type resultSetType struct {
		Name   string   `json:"name"`
		TempLo *int64   `json:"tempLo"`
		TempHi *int64   `json:"tempHi"`
		Prcp   *float32 `json:"prcp"`
		Date   *string  `json:"date"`
	}
	resultSet := make([]resultSetType, 0)
	if err := db.NewSelect().
		Column("cities.name").
		Column("weather.temp_lo").
		Column("weather.temp_hi").
		Column("weather.prcp").
		Column("weather.date").
		Table("cities").
		Join("LEFT OUTER JOIN weather").
		JoinOn("cities.name = weather.city").
		Order("weather.date").
		Scan(ctx, &resultSet); err != nil {

		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}

	return c.JSON(http.StatusOK, resultSet)

上記コードからBunが生成したSQLを、整形して見やすくしました。

SELECT
    "cities"."name",
    "weather"."temp_lo",
    "weather"."temp_hi",
    "weather"."prcp",
    "weather"."date"
FROM
    "cities"
LEFT OUTER JOIN
    weather
ON
    (cities.name = weather.city)
ORDER BY
    "weather"."date"

動かしてみる

それでは、テーブルが空の状態で動かしてみます。

API一覧

APIのメソッドは、すべてPOSTです。

PATH	DB	トランザクションの成否
/postgres/good	PostgreSQL用	成功
/libsql/good	libSQL用	成功
/postgres/bad	PostgreSQL用	失敗
/libsql/bad	libSQL用	失敗

失敗パターン

失敗パターンから動かしてみます。
処理後のログを見ると、トランザクションがコミットされずにロールバックされています。
また、処理後のテーブルはすべて空のままでした。

PostgreSQLの失敗パターン

Postmanのスナップショット：

Bunが出力したログ：

[bun]  22:54:46.494   BEGIN                   300µs  BEGIN
[bun]  22:54:46.494   INSERT                  399µs  INSERT INTO cities ("name", "location") VALUES ('San Francisco', '(-194.0, 53.0)') ON CONFLICT (name) DO UPDATE SET location = EXCLUDED.location
[bun]  22:54:46.494   INSERT                  390µs  INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-01')
[bun]  22:54:46.495   INSERT                  254µs  INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-02')
[bun]  22:54:46.495   INSERT                  443µs  INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('Hayward', 43, 57, 0, '2023-01-03')       pgdriver.Error: ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey" (SQLSTATE=23503) 
[bun]  22:54:46.495   ROLLBACK                178µs  ROLLBACK

libSQLの失敗パターン

Postmanのスナップショット：

Bunが出力したログ：

[bun]  22:57:18.346   BEGIN                 1.133ms  BEGIN
[bun]  22:57:18.346   INSERT                  472µs  INSERT INTO cities ("name", "location") VALUES ('San Francisco', '(-194.0, 53.0)') ON CONFLICT (name) DO UPDATE SET location = EXCLUDED.location
[bun]  22:57:18.346   INSERT                  440µs  INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-01')
[bun]  22:57:18.347   INSERT                  467µs  INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-02')
[bun]  22:57:18.347   INSERT                  415µs  INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('Hayward', 43, 57, 0, '2023-01-03')       *errors.errorString: failed to execute SQL: INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('Hayward', 43, 57, 0, '2023-01-03')
SQLite error: FOREIGN KEY constraint failed 
[bun]  22:57:18.348   ROLLBACK                328µs  ROLLBACK

成功パターン

次は成功パターンです。
処理後のログを見ると、トランザクションがコミットまで進んでいます。
また、APIのレスポンスを見て、データがテーブルに挿入されていることが確認できました。

なお、親テーブルへのUPSERT処理について、本記事ではINSERT部分しか動作確認しませんでしたが、UPDATE部分も成功することを確認しました。

PostgreSQLの成功パターン

Postmanのスナップショット：

Bunが出力したログ：

[bun]  23:04:04.636   BEGIN                   384µs  BEGIN
[bun]  23:04:04.637   INSERT                  513µs  INSERT INTO cities ("name", "location") VALUES ('San Francisco', '(-194.0, 53.0)') ON CONFLICT (name) DO UPDATE SET location = EXCLUDED.location
[bun]  23:04:04.637   INSERT                  280µs  INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-01')
[bun]  23:04:04.637   INSERT                  239µs  INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-02')
[bun]  23:04:04.637   INSERT                  179µs  INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-03')
[bun]  23:04:04.641   COMMIT                3.292ms  COMMIT
[bun]  23:04:04.642   SELECT                1.187ms  SELECT "cities"."name", "weather"."temp_lo", "weather"."temp_hi", "weather"."prcp", "weather"."date" FROM "cities" LEFT OUTER JOIN weather ON (cities.name = weather.city) ORDER BY "weather"."date"

libSQLの成功パターン

Postmanのスナップショット：

Bunが出力したログ：

[bun]  23:06:20.489   BEGIN                 1.156ms  BEGIN
[bun]  23:06:20.491   INSERT                1.652ms  INSERT INTO cities ("name", "location") VALUES ('San Francisco', '(-194.0, 53.0)') ON CONFLICT (name) DO UPDATE SET location = EXCLUDED.location
[bun]  23:06:20.491   INSERT                  488µs  INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-01')
[bun]  23:06:20.492   INSERT                  424µs  INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-02')
[bun]  23:06:20.492   INSERT                  497µs  INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-03')
[bun]  23:06:20.499   COMMIT                6.422ms  COMMIT
[bun]  23:06:20.500   SELECT                1.341ms  SELECT "cities"."name", "weather"."temp_lo", "weather"."temp_hi", "weather"."prcp", "weather"."date" FROM "cities" LEFT OUTER JOIN weather ON (cities.name = weather.city) ORDER BY "weather"."date"

まとめ

Bunでトランザクション（コミットとロールバック）、UPSERT、テーブルのJOINを試しました。
PostgreSQL、libSQLともに、期待通りに動きました。