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?

チーム開発参加の記録【2023-10~2024-03】(6) Go言語用ORM「Bun」で複数のクエリーをまとめてDBサーバーで実行

Last updated at Posted at 2023-12-23

本シリーズのリンク

本記事で行うこと

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

image.png

こんな風に書きましたが、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のスナップショット:

image.png

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のスナップショット:

image.png

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のスナップショット:

image.png

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のスナップショット:

image.png

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つ増えました。

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