Goでtext/templateを使ってSQLを組み立てる

今、VちゃんではGoでコードを書いており、その中でDBにアクセスするコードではクエリビルダーは使わずSQLを直書きしている

書き込み系のSQLは基本的にシンプルになるので特に問題ない
しかし、読み取り系の特定条件での検索をするようなSQLを書くときにはSQLが複雑になり、そのままSQLを文字列として扱うだけでなく、特定のJOINを追加したりWHEREを追加したり、ということが起こる
こういった検索クエリは検索エンジンに投げることが一般的に多いと思うが、今の自分のアプリケーションは検索エンジンを入れているわけではないのでここをSQLで頑張る必要がある

まずはtext/templateの使い方

import (
    "bytes"
    "fmt"
    "testing"
    "text/template"
)

func TestExample(t *testing.T) {
    var buf bytes.Buffer
    tmpl := `
{{ if .flag }}
true
{{ else }}
false
{{ end }}
`
    data := map[string]interface{}{
        "flag": true,
    }
    tm := template.Must(template.New("tmpl").Parse(tmpl))
    if err := tm.Execute(&buf, data); err != nil {
        t.Fatal(err)
    }
    fmt.Println(buf.String())
}

出力例

true

{{ if .flag }} などを書いているところの改行はそのまま出力されるので注意 html/template というのもあるのでたまにimport間違えたりするので注意

複数条件は以下のように書く

func TestExample(t *testing.T) {
    var buf bytes.Buffer
    tmpl := `
{{ if and .flag1 .flag2 }}
true
{{ end }}
`
    data := map[string]interface{}{
        "flag1": true,
        "flag2": true,
    }
    tm := template.Must(template.New("tmpl").Parse(tmpl))
    if err := tm.Execute(&buf, data); err != nil {
        t.Fatal(err)
    }
    fmt.Println(buf.String())
}

and の後に条件を並べる
and に否定条件などを入れる場合は and (not .flag1) (not flag2) みたいにかっこで囲っておく

ドキュメントを見ると比較演算子を使えたりいくつかの演算子がtemplate内での独自構文で提供されている

golang.org

SQLを書く

実際にSQLを書いていく

SELECT
    vtuber.id
FROM
    vtuber
WHERE
    vtuber.deleted_at IS NULL

これは実際のGoのアプリケーションでsqlxで実行されるSQLをシンプルにしたもの
VTuberの一覧を取得しているSQLである
一覧を取得するときにはここに検索条件が追加される
例えばある個性が設定されているVTuberのみ取得するSQLにしてみよう

SELECT
    vtuber.id
FROM
    vtuber
INNER JOIN vtuber_personality
    ON vtuber_personality.vtuber_id = vtuber.id
WHERE
    vtuber.deleted_at IS NULL
    AND vtuber_personality.personality_id IN (:personalities)

個性の検索をつけることで関連テーブルへのJOIN、WHEREでの絞り込みが追加された

更に配信スタイルでの絞り込みも行ってみよう

SELECT
    vtuber.id
FROM
    vtuber
INNER JOIN vtuber_personality
    ON vtuber_personality.vtuber_id = vtuber.id
INNER JOIN vtuber_video_style
    ON vtuber_video_style.vtuber_id = vtuber.id
WHERE
    vtuber.deleted_at IS NULL
    AND vtuber_personality.personality_id IN (:personalities)
    AND vtuber_video_style.vtuber_style_id IN (:video_styles)
GROUP BY vtuber.id
HAVING COUNT(*) = :condition_count

条件に全て合致していることを確認するためにGROUP BYが追加された

こんな感じで検索のクエリは条件が追加されるたびに少しづつ複雑さを増していく
じゃあこのSQLをアプリケーション内で実行しようと思ったときにいくつかの選択肢が浮かぶ

• SQLを文字列連結で動的に変える
• クエリビルダーを使う
• SQL自体は変わらないようにSQL内で条件分岐を活用する（WHEREの条件などで頑張る）

それぞれの方法は良し悪しあるかと思うが今回はこのSQLをtext/templateパッケージを使用して組み立ててみようと思う

text/templateでSQLを組み立てる

上記の条件を元にSQLが組み立てられるようにしたtemplateはこのようになる

SELECT
    vtuber.id
FROM
    vtuber
{{ if .personalities }}
INNER JOIN vtuber_personality
    ON vtuber_personality.vtuber_id = vtuber.id
{{ end }}
{{ if .vtuberGroups }}
INNER JOIN vtuber_video_style
    ON vtuber_video_style.vtuber_id = vtuber.id
{{ end }}
WHERE
    vtuber.deleted_at IS NULL
{{ if .personalities }}
    AND vtuber_personality.personality_id IN (:personalities)
{{ end }}
{{ if .vtuberGroups }}
    AND vtuber_video_style.vtuber_style_id IN (:video_styles)
{{ end }}
{{ if or .personalities .vtuberGroups }}
GROUP BY vtuber.id
HAVING COUNT(*) = :condition_count
{{ end }}

Goで実行するとするとこんな感じ

func TestExample(t *testing.T) {
    tmpl := `
SELECT
  vtuber.id
FROM
  vtuber
{{ if .personalities }}
INNER JOIN vtuber_personality
  ON vtuber_personality.vtuber_id = vtuber.id
{{ end }}
{{ if .vtuberGroups }}
INNER JOIN vtuber_video_style
  ON vtuber_video_style.vtuber_id = vtuber.id
{{ end }}
WHERE
  vtuber.deleted_at IS NULL
{{ if .personalities }}
  AND vtuber_personality.personality_id IN (:personalities)
{{ end }}
{{ if .vtuberGroups }}
  AND vtuber_video_style.vtuber_style_id IN (:video_styles)
{{ end }}
{{ if or .personalities .vtuberGroups }}
GROUP BY vtuber.id
HAVING COUNT(*) = :condition_count
{{ end }}
`

    var buf bytes.Buffer
    data := map[string]interface{}{
        "personalities": []string{"p1"},
        "vtuberGroups": []string{"g1"},
    }
    tm := template.Must(template.New("tmpl").Parse(tmpl))
    if err := tm.Execute(&buf, data); err != nil {
        t.Fatal(err)
    }

    fmt.Println(buf.String())
}

出力されるSQLはこちら
実際にはこの buf.String() をsqlxなどの実行クエリとして流し込むことになる

SELECT
    vtuber.id
FROM
    vtuber

INNER JOIN vtuber_personality
    ON vtuber_personality.vtuber_id = vtuber.id


INNER JOIN vtuber_video_style
    ON vtuber_video_style.vtuber_id = vtuber.id

WHERE
    vtuber.deleted_at IS NULL

    AND vtuber_personality.personality_id IN (:personalities)


    AND vtuber_video_style.vtuber_style_id IN (:video_styles)


GROUP BY vtuber.id
HAVING COUNT(*) = :condition_count

改行が変に入るのが気になるところ
何も指定しなかった場合、さらにこんな感じになる

SELECT
    vtuber.id
FROM
    vtuber


WHERE
    vtuber.deleted_at IS NULL

そんなときは {{ if .personalities -}}～～～{{- end }} のように波かっこに改行やスペースを削除したいところにハイフンを記述してあげればマシになる

golang.org

SELECT
    vtuber.id
FROM
    vtuber
{{ if .personalities -}}
INNER JOIN vtuber_personality
    ON vtuber_personality.vtuber_id = vtuber.id
{{- end }}
{{ if .vtuberGroups -}}
INNER JOIN vtuber_video_style
    ON vtuber_video_style.vtuber_id = vtuber.id
{{- end }}
WHERE
    vtuber.deleted_at IS NULL
{{ if .personalities -}}
    AND vtuber_personality.personality_id IN (:personalities)
{{- end }}
{{ if .vtuberGroups -}}
    AND vtuber_video_style.vtuber_style_id IN (:video_styles)
{{- end }}
{{ if or .personalities .vtuberGroups -}}
GROUP BY vtuber.id
HAVING COUNT(*) = :condition_count
{{- end }}

これを実行すると

SELECT
    vtuber.id
FROM
    vtuber
INNER JOIN vtuber_personality
    ON vtuber_personality.vtuber_id = vtuber.id
INNER JOIN vtuber_video_style
    ON vtuber_video_style.vtuber_id = vtuber.id
WHERE
    vtuber.deleted_at IS NULL
AND vtuber_personality.personality_id IN (:personalities)
AND vtuber_video_style.vtuber_style_id IN (:video_styles)
GROUP BY vtuber.id
HAVING COUNT(*) = :condition_count

うんうん、条件指定した場合は綺麗になった

条件未指定の場合は？

SELECT
    vtuber.id
FROM
    vtuber


WHERE
    vtuber.deleted_at IS NULL

まあまあ改行入ってしまう
ここは微妙なところ

所感

• template内に全ての条件が入っているので条件の全容を把握しやすい
• ただしちょっと見にくい
• パッとコピーしてSQL流して試すとかはやりにくい
• シンタックスハイライトが効かないかもね、SQLかつtext/templateの構文が混ざるので