本シリーズのリンク

チーム開発参加の記録【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に飛ばしてみた

本記事で行うこと

本シリーズの4番目の記事に、ストアドプロシージャのメリットについて書きました。
以下、記事のスクリーンショットです。

こんな風に書きましたが、Bunで複数のクエリーをまとめてDBサーバーに送信することにより、ネットワーク通信を減らせること気づきましたので、記事にします。

Goコード

最初に、今回書いたソースコード全体を載せてしまいます。
5番目の記事のコードを少しアレンジしました。

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

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

go.modは5番目の記事と全く同じなので割愛します。

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

	// 親テーブルに1件UPSERTするクエリー
	upsert_stmt := db.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").
		String()

	// 子テーブルに3件INSERT

	// 1件目のINSERTクエリー
	insert_stmt1 := db.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").
		String()

	// 2件目のINSERTクエリー
	insert_stmt2 := db.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").
		String()

	// 3件目のINSERTは、isGoodのときは成功させて、!isGoodのときは失敗させる
	var city string
	if isGood {
		city = "San Francisco" // 親テーブルに存在する値を外部キーに設定
	} else {
		city = "Hayward" // 親テーブルに存在しない値を外部キーに設定
	}
	insert_stmt3 := db.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").
		String()

	// これまでの4つのクエリーを1つの文字列にまとめる
	stmt := upsert_stmt + ";\n" +
		insert_stmt1 + ";\n" + 
		insert_stmt2 + ";\n" +
		insert_stmt3

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

	// 1つにまとめたクエリーを実行
	if _, err := tx.NewRaw(stmt).Exec(ctx); err != nil {
		tx.Rollback()
		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}

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

	// トランザクションが成功したら、親テーブルと子テーブルを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"))
}

トランザクション処理

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

複数のクエリーを1つの文字列にまとめる

5番目の記事では、トランザクション内でUPSERTを1回、INSERTを3回実行しました。
これらのクエリーを1つ1つアプリから実行するのではなく、まずは複数のクエリーを1つの文字列にまとめてしまいます。

	// 親テーブルに1件UPSERTするクエリー
	upsert_stmt := db.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").
		String()

	// 子テーブルに3件INSERT

	// 1件目のINSERTクエリー
	insert_stmt1 := db.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").
		String()

	// 2件目のINSERTクエリー
	insert_stmt2 := db.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").
		String()

	// 3件目のINSERTは、isGoodのときは成功させて、!isGoodのときは失敗させる
	var city string
	if isGood {
		city = "San Francisco" // 親テーブルに存在する値を外部キーに設定
	} else {
		city = "Hayward" // 親テーブルに存在しない値を外部キーに設定
	}
	insert_stmt3 := db.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").
		String()

	// これまでの4つのクエリーを1つの文字列にまとめる
	stmt := upsert_stmt + ";\n" +
		insert_stmt1 + ";\n" +
		insert_stmt2 + ";\n" +
		insert_stmt3

まとめたクエリーであるstmt変数をデバッガで見ると、以下の内容になっていました
（トランザクション成功パターンのクエリーです）。

INSERT INTO cities ("name", "location") VALUES ('San Francisco', '(-194.0, 53.0)') ON CONFLICT (name) DO UPDATE SET location = EXCLUDED.location;
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-01');
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-02');
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-03')

トランザクション開始

トランザクションの開始・終了は、Go言語側で行うことにしました。

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

クエリー実行

1つにまとめたクエリーをトランザクション内で実行します。
失敗したらロールバックします。

	// 1つにまとめたクエリーを実行
	if _, err := tx.NewRaw(stmt).Exec(ctx); err != nil {
		tx.Rollback()
		return c.JSON(http.StatusInternalServerError, httpError{Error: err.Error()})
	}

コミット

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

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

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

ここは5番目の記事と全く同じです。

	// トランザクションが成功したら、親テーブルと子テーブルを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)

動かしてみる

それでは、テーブルが空の状態で動かしてみます。
APIの仕様は5番目の記事と同じです。

失敗パターン

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

PostgreSQLの失敗パターン

Postmanのスナップショット：

Bunが出力したログ：

[bun]  08:13:13.181   BEGIN                 3.402ms  BEGIN
[bun]  08:13:13.182   SELECT                1.376ms  INSERT INTO cities ("name", "location") VALUES ('San Francisco', '(-194.0, 53.0)') ON CONFLICT (name) DO UPDATE SET location = EXCLUDED.location;
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-01');
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-02');
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]  08:13:13.183   ROLLBACK                185µs  ROLLBACK

libSQLの失敗パターン

Postmanのスナップショット：

Bunが出力したログ：

[bun]  08:15:10.430   BEGIN                 2.937ms  BEGIN
[bun]  08:15:10.432   SELECT                1.921ms  INSERT INTO cities ("name", "location") VALUES ('San Francisco', '(-194.0, 53.0)') ON CONFLICT (name) DO UPDATE SET location = EXCLUDED.location;
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-01');
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-02');
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('Hayward', 43, 57, 0, '2023-01-03')    *errors.errorString: SQLite error: FOREIGN KEY constraint failed 
[bun]  08:15:10.432   ROLLBACK                601µs  ROLLBACK

成功パターン

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

PostgreSQLの成功パターン

Postmanのスナップショット：

Bunが出力したログ：

[bun]  08:17:16.824   BEGIN                   420µs  BEGIN
[bun]  08:17:16.825   SELECT                  915µs  INSERT INTO cities ("name", "location") VALUES ('San Francisco', '(-194.0, 53.0)') ON CONFLICT (name) DO UPDATE SET location = EXCLUDED.location;
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-01');
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-02');
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-03')
[bun]  08:17:16.829   COMMIT                3.752ms  COMMIT
[bun]  08:17:16.831   SELECT                  914µs  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]  08:19:41.996   BEGIN                 1.114ms  BEGIN
[bun]  08:19:41.997   SELECT                  645µs  INSERT INTO cities ("name", "location") VALUES ('San Francisco', '(-194.0, 53.0)') ON CONFLICT (name) DO UPDATE SET location = EXCLUDED.location;
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-01');
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-02');
INSERT INTO weather ("city", "temp_lo", "temp_hi", "prcp", "date") VALUES ('San Francisco', 43, 57, 0, '2023-01-03')
[bun]  08:19:42.001   COMMIT                3.513ms  COMMIT
[bun]  08:19:42.003   SELECT                1.707ms  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を使って、複数のクエリーをまとめてDBサーバーに送信して実行することにより、ネットワーク通信を減らすことができました。
ストアドプロシージャは開発者体験が悪く、あまり書きたくないので、Go言語でも通信を減らせるのはうれしいです。

それと、sqlcでは複数のクエリーをまとめて送信は出来ないんじゃないでしょうか？
そうだとしたら、sqlcからBunに乗り換える理由が1つ増えました。