Zenn
🙌

Goのクエリビルダsquirrelとgoquを比較する

2021/12/27に公開
Go
SQL
tech

概要

以下のことを期待して、クエリビルダの導入を検討する

  • 生SQLのような表記揺れを防げる
  • 型に基づいてクエリを組み立てるのでtypoや不正な構文が発生しない
  • 末尾セミコロンのつけ間違えなどを防止できる
  • 条件の追加などが容易になる
  • (MySQL以外のDialectに対応できる)

以下の二つを候補に検討した

パフォーマンスの比較

機能面の比較

業務上で出てきたやや複雑なクエリのパターンをあげながら、squirrelとgoquでの記法や出力を比較する。

例1 : ユーザの存在確認

SELECT EXISTS ( SELECT id FROM users WHERE id = ? )

squirrel

subquery := sq.Select("id").From("users").Where(sq.Eq{"id": id})
sql, args, err := subquery.Prefix("SELECT EXISTS (").Suffix(")").ToSql()

// SELECT EXISTS ( SELECT id FROM users WHERE id = ? ) [hoge] <nil>

goqu

dialect := goqu.Dialect("mysql")
subquery := dialect.Select("id").From("users").Where(goqu.Ex{"id": goqu.Op{"eq": id}})
sql, args, err := dialect.Select(goqu.L("EXISTS ? ", subquery)).ToSQL()	

// SELECT EXISTS (SELECT `id` FROM `users` WHERE (`id` = 'hoge'))  [] <nil>
  • プレースホルダを有効にする
sql, args, err = dialect.Select(goqu.L("EXISTS ? ", subquery)).Prepared(true).ToSQL()

// SELECT EXISTS (SELECT `id` FROM `users` WHERE (`id` = ?))  [hoge] <nil>

例2 : メッセージの詳細と購読状況の取得

SELECT
	m.*,
	NOT EXISTS (
		SELECT id FROM subscription_users AS su WHERE su.id = ? AND su.message_id = m.id
	) AS is_subscribed
FROM
	messages AS m

squirrel

subquery := sq.Select("id").From("subscription_users AS su").
	Where(sq.And{sq.Eq{"su.id": id}, sq.Expr("su.message_id = m.id")})
sql, args, err := subquery.Prefix("NOT EXISTS (").Suffix(") AS is_subscribed").ToSql()
sql, _, err = sq.Select("m.*", sql).From("messages AS m").ToSql()

// SELECT m.*, NOT EXISTS ( SELECT id FROM subscription_users AS su WHERE (su.id = ? AND su.message_id = m.id) ) AS is_subscribed FROM messages AS m [hoge] <nil>

goqu

dialect := goqu.Dialect("mysql")
subquery := dialect.Select("id").From(goqu.T("subscription_users").As("su")).
	Where(goqu.And(
		goqu.Ex{"su.id": goqu.Op{"eq": id}},
		goqu.Ex{"su.message_id": goqu.Op{"eq": goqu.I("m.id")}},
	))
sql, args, err := dialect.Select("m.*", goqu.L("NOT EXISTS ? ", subquery).As("is_subscribed")).From(goqu.T("messages").As("m")).ToSQL()

// SELECT `m`.*, NOT EXISTS (SELECT `id` FROM `subscription_users` AS `su` WHERE ((`su`.`id` = 'hoge') AND (`su`.`message_id` = `m`.`id`)))  AS `is_subscribed` FROM `messages` AS `m` [] <nil>

例3 : 公開期間中のニュース一覧を取得

SELECT
	n.* 
FROM
	news AS n JOIN news_categories AS nc ON n.category_id = nc.id
WHERE
	(n.begin_at < NOW() OR n.begin_at IS NULL) AND (NOW() < n.end_at OR n.end_at IS NULL)
ORDER BY
	nc.priority DESC, n.priority DESC

squirrel

sql, args, err := sq.Select("n.*").From("news AS n").Join("news_categories AS nc ON n.category_id = nc.id").
	Where(sq.And{
		sq.Or{sq.Expr("n.begin_at < NOW()"), sq.Eq{"n.begin_at": nil}},
		sq.Or{sq.Expr("NOW() < n.end_at"), sq.Eq{"n.end_at": nil}},
	}).OrderBy("nc.priority DESC", "n.priority DESC").ToSql()

// SELECT n.* FROM news AS n JOIN news_categories AS nc ON n.category_id = nc.id WHERE ((n.begin_at < NOW() OR n.begin_at IS NULL) AND (NOW() < n.end_at OR n.end_at IS NULL)) ORDER BY nc.priority DESC, n.priority DESC [] <nil>

goqu

dialect := goqu.Dialect("mysql")
sql, args, err := dialect.Select("n.*").From(goqu.T("news").As("n")).Join(goqu.T("news_categories").As("nc"), goqu.On(goqu.Ex{"n.category_id": goqu.I("nc.id")})).
		Where(goqu.And(
			goqu.Or(goqu.Ex{"n.begin_at": goqu.Op{"lt": goqu.L("NOW()")}}, goqu.Ex{"n.begin_at": nil}),
			goqu.Or(goqu.Ex{"n.end_at": goqu.Op{"gt": goqu.L("NOW()")}}, goqu.Ex{"n.end_at": nil}),
		)).Order(goqu.I("nc.priority").Desc(), goqu.I("n.priority").Desc()).ToSQL()

// SELECT `n`.* FROM `news` AS `n` INNER JOIN `news_categories` AS `nc` ON (`n`.`category_id` = `nc`.`id`) WHERE (((`n`.`begin_at` < NOW()) OR (`n`.`begin_at` IS NULL)) AND ((`n`.`end_at` > NOW()) OR (`n`.`end_at` IS NULL))) ORDER BY `nc`.`priority` DESC, `n`.`priority` DESC [] <nil>

例4 : ユーザ情報のインサート

squirrel

sql, args, err := sq.Insert("users").Columns("id", "name", "status").Values(id, name, status).ToSql()

// INSERT INTO users (id,name,status) VALUES (?,?,?) [hoge fuga ok] <nil>

goqu

dialect := goqu.Dialect("mysql")
sql, args, err := dialect.Insert("users").Cols("id", "name", "status").Vals(goqu.Vals{id, name, status}).ToSQL()

// INSERT INTO `users` (`id`, `name`, `status`) VALUES ('hoge', 'fuga', 'ok') [] <nil>

例5 : ユーザ情報のアップデート

squirrel

sql, args, err := sq.Update("users").
		Set("name", name).
		Set("status", status).
		Where(sq.Eq{"id": id}).
		ToSql()

// UPDATE users SET name = ?, status = ? WHERE id = ? [fuga ok hoge] <nil>

goqu

dialect := goqu.Dialect("mysql")
sql, args, err := dialect.Update("users").Set(goqu.Record{
	"name":   name,
	"status": status,
}).Where(goqu.C("id").Eq(id)).ToSQL()

// UPDATE `users` SET `name`='fuga',`status`='ok' WHERE (`id` = 'hoge') [] <nil>

注意

  • 細かいユースケースについてはどちらもドキュメントが少なくissueやテストを見ながらの実装になりそう
  • goquでdialectを有効にするには下記のようにブランクインポートが必要
import (
	"github.com/doug-martin/goqu/v9"
	_ "github.com/doug-martin/goqu/v9/dialect/mysql"
)

結論

  • squirrelでは複雑なサブクエリに対応できない(できても可読性が悪い)ことがある
  • goquは複雑なユースケースに対応可能。構文を細かく型で指定しているのでコード量は比較的多く見える

Discussion