🆘

Go の sqlc ライブラリで動的な WHERE 句を設定できますか？

2023/10/03に公開

SQL

sqlc

tech

sqlc ライブラリで動的に WHERE 句を設定する方法を探しています。

環境

go 1.21.1
sqlc 1.22.0
mysql 8.0.33

sqlc について

sqlc ライブラリは、ユーザが予め定義した SQL とコメントから、DB アクセスを行うためのコードを自動生成してくれます。

https://github.com/sqlc-dev/sqlc

例えば、authors テーブルに対して、id を指定して検索する SQL を定義すると、以下のようなコードが生成されます。

ユーザが定義する SQL

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1;

自動生成されるコード

type Queries struct {
	db DBTX
}

const getAuthor = `-- name: GetAuthor :one
SELECT id, bio, birth_year FROM authors
WHERE id = $1
`

func (q *Queries) GetAuthor(ctx context.Context, id int) (Author, error) {
	row := q.db.QueryRowContext(ctx, getAuthor, id)
	var i Author
	err := row.Scan(&i.ID, &i.Bio, &i.BirthYear)
	return i, err
}

引用元: https://docs.sqlc.dev/en/latest/howto/select.html

呼び出し元は、Queries構造体のGetAuthorメソッドを呼び出すことで、DBアクセスを行うことができます。

WHERE 句や LIMIT などに外部からパラメータを動的に設定する場合、上記のように$1のようなプレースホルダを使うことができます。

何に悩んでいるか

例えば、以下のようなケースを考えます。

ケース1

リクエストされたパラメータに応じて、 NULL または NOT NULL の条件を設定したい
- 例えば、isNullFlg が１なら WHERE author.nullable_column IS NULL 、２なら WHERE author.nullable_column IS NOT NULL としたい

ケース2

パラメータに応じて、大小比較の条件を変更したい
- 例えば、typeFlg が gt なら WHERE author.id > $1 、le なら WHERE author.id <= $1 というように比較演算子を変更したい

過去、他のライブラリを使用して動的な WHERE 句を作成したこと(※)がありますが、sqlc においては、WHERE 句を外部から設定する方法が見つけられず、以下どちらかの方法で対応しようと考えています。（外部から WHERE 句を設定できる方法があれば、是非教えてくださいmm）

WHERE 句のパターンに応じて、SQL を定義する
SQL 側で WHERE 句を動的に設定する

1の場合、パターンに応じた SQL とメソッドを定義する必要があります。
ケース1, ケース2の組み合わせが必要な場合、2 * 2 = 4 パターンの SQL を定義する必要があります。

2の場合、SQL 側の CASE 句やIF文、ストアドプロシージャなどを使用して動的に設定できます。

CASE 句を使う場合

-- name: GetAuthor :one
SELECT * FROM author
WHERE 
    (CASE 
        WHEN $1 = 1 THEN nullable_column IS NULL
        ELSE $1 = 2 THEN nullable_column IS NOT NULL
    END)
AND 
    (CASE 
        WHEN comp_type = 'gt' AND id > comp_value THEN 1
        ELSE comp_type = 'le' AND id <= comp_value
    END)
;

一般的に、「sqlc を採用した＝ある程度 SQL を書けるメンバーがいる」という前提は成り立つと思うので、2でもいいと思いますが、実行計画への影響等、動的に WHERE 句を生成する弊害があるかもしれません。

sqlc での解決にはなっていませんが、自動生成されたコードとは別に、ユーザ独自の取得用メソッドを作ってもいいかもしれません。

WHERE 句の文字列を引数として受け取るメソッドを作成する

func (q *Queries) GetAuthor(ctx context.Context, id int, where string) error {
	getAuthor := "SELECT id, bio, birth_year FROM authors "

    if where != "" {
        getAuthor += where
    }

	q.db.QueryRowContext(ctx, getAuthor, id)
	var i Author
	err := row.Scan(&i.ID, &i.Bio, &i.BirthYear)
	return i, err
}

SELECT 対象のパラメータが変わった時に独自メソッド側でも変更が必要となることから、採用する時は、CI による自動テストとセットですね。

※ 例えば、sqlx ライブラリや、TypeScript の ORM ライブラリである TypeORM の Repository や QueryBuilder を使用

まとめ

sqlc でパラメータに応じた WHERE 句を設定する方法をご存知でしたら教えてください（2回目）
sqlc は、SQLをユーザが書ける、go の構造体への Marshal 用のコードも自動生成してくれるので、とても便利なライブラリなのでオススメです。

GitHubで編集を提案

環境

sqlc について

何に悩んでいるか

ケース1

ケース2

まとめ

Discussion