gorp の クエリはテンプレートに書いたほうがわかりやすいと思いました。

最近は主に TypeScript を書いてます。楽しいです。

という話は置いておきまして、gorpを使ったバックエンドのコードレビューしていたときに、 SQLがあちこちに散らばってしまい可読性が悪くなってるのを見かけました。

gorpでは生SQLをかけるのでSQLが短ければ直感的にわかりやすいというメリットはありますが、 結合や検索条件が複雑になるに連れコードは関数やファイルで分割されSQLはどんどん散り散りになっていきます。

私は十行足らずのSQLを表現するために数十行の Goのコードを書きたくありませんし、保守できる自信もありません。

前置きが長くなりましたが、表題の通り Go の標準テンプレート機能を使ってSQLを書いたほうが 直感的にわかりやすいだろうと思ったので、期待通りに動作するか検証してみます。

検証環境を作ったので手元で試したい人は Clone してください。 使い方は後述。使うライブラリのバージョンとかは go.mod を参照のこと。 https://github.com/righ/gorp-tips.git

準備が面倒で SQLBoiler の記事で使ったデータを流用してるので 設定が雑でも許してください。

👐 準備

で環境を起動するとアプリ用のコンテナと MySQL のコンテナが起動するので docker exec -it gorp /bin/bash でアプリのコンテナに入ります。

すでにレコードも入ってるので特にもうすることはないです。一応 MySQL に接続してみます。

大丈夫そうですね。

実行するプログラムは src/ に入ってるので先に移動しておきましょう。

💪 実装

この記事では、上で準備したテーブルを雑にJOIN(LEFT JOIN)して検索して標準出力に表示するプログラムを作っていきます。

今回使ういくつかのプログラムはリポジトリパターンを採用しており、DBをいじる処理は repositories/ 配下に書き、コントローラー経由で呼び出しています。 それぞれのプログラムはコントローラーに異なるリポジトリを指定することで異なる処理を実行するという具合です。

package controllers

import (
	"context"

	_ "github.com/go-sql-driver/mysql"

	"gorp-tips/models"
	"gorp-tips/repositories"
)

func GetJets(ctx context.Context, repo repositories.JetRepository, req models.Request) ([]models.Result, error) {
	return repo.GetJets(ctx, req)
}

まぁ難しい話はさておき、重要なポイントはDBアクセスはリポジトリ(repositories/)が担当して、 実行プログラムがそれを使い分けているだけなので以下ではそれらに注目してみていくことにしましょう。(そのために分けたというのもある)

😇 まずはふつうに

とりあえず、何も考えず SQLを組み立てて実装してみます。左右がプログラムで下が実行結果です。(以下すべてこの構成)

• main_without_template.go

  package main
  
  import (
  	"context"
  	"database/sql"
  	"flag"
  	"fmt"
  	"gorp-tips/controllers"
  	"gorp-tips/models"
  	"gorp-tips/repositories"
  
  	"github.com/go-gorp/gorp"
  	_ "github.com/go-sql-driver/mysql"
  	"github.com/labstack/gommon/log"
  )
  
  func initDb() *gorp.DbMap {
  	db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
  	dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "utf8mb4"}}
  	models.MapStructsToTables(dbmap)
  	return dbmap
  }
  
  func main() {
  	dbmap := initDb()
  	defer dbmap.Db.Close()
  
  	var (
  		age       = flag.Int("age", 0, "jet age")
  		pilotName = flag.String("pilot_name", "", "pilot name")
  		jetName   = flag.String("jet_name", "", "jet name")
  		language  = flag.String("language", "", "language")
  	)
  	flag.Parse()
  	req := models.Request{
  		Age:       *age,
  		PilotName: *pilotName,
  		JetName:   *jetName,
  		Language:  *language,
  	}
  	repo := repositories.NewJetRepository0(dbmap)
  	results, err := controllers.GetJets(context.Background(), repo, req)
  	if err != nil {
  		log.Fatal(err)
  		return
  	}
  	for _, record := range results {
  		fmt.Printf("%+v\n", record)
  	}
  
  }
  
  

• jet_repository0.go

  package repositories
  
  import (
  	"context"
  	"strings"
  
  	"github.com/go-gorp/gorp"
  	"github.com/labstack/gommon/log"
  
  	"gorp-tips/models"
  )
  
  type JetRepository interface {
  	GetJets(ctx context.Context, req models.Request) ([]models.Result, error)
  }
  
  type jetRepository0 struct {
  	exec gorp.SqlExecutor
  }
  
  func NewJetRepository0(exec gorp.SqlExecutor) JetRepository {
  	return &jetRepository0{
  		exec: exec,
  	}
  }
  
  func (r *jetRepository0) GetJets(ctx context.Context, req models.Request) ([]models.Result, error) {
  	query := "SELECT jets.name AS jetName, jets.age AS jetAge, jets.color AS jetColor, pilots.name AS pilotName, languages.language "
  	query += "FROM jets "
  	query += "JOIN pilots ON pilots.id = jets.pilot_id "
  	query += "LEFT JOIN pilot_languages ON pilot_languages.pilot_id = jets.pilot_id "
  	query += "LEFT JOIN languages ON languages.id = pilot_languages.language_id "
  	conds, variables := makeCondition(req)
  	if conds != "" {
  		query += "WHERE " + conds
  	}
  	query += " ORDER BY jets.age, jets.id"
  	log.Debug(query)
  
  	var results []models.Result
  	if _, err := r.exec.Select(&results, query, variables); err != nil {
  		log.Error(err)
  		return nil, err
  	}
  	return results, nil
  }
  
  func makeCondition(req models.Request) (string, map[string]interface{}) {
  	conds := []string{}
  	context := map[string]interface{}{}
  
  	if req.Age > 0 {
  		conds = append(conds, "jets.age = :age")
  		context["age"] = req.Age
  	}
  	if req.PilotName != "" {
  		conds = append(conds, "pilots.name LIKE :pilot_name")
  		context["pilot_name"] = "%" + req.PilotName + "%"
  	}
  	if req.JetName != "" {
  		conds = append(conds, "jets.name LIKE :jet_name")
  		context["jet_name"] = "%" + req.JetName + "%"
  	}
  	if req.Language != "" {
  		conds = append(conds, "languages.language = :language")
  		context["language"] = req.Language
  	}
  
  	return strings.Join(conds, " AND "), context
  }
  
  

info

• Language だけアドレスが表示されているのは language が LEFT OUTER で結合される(つまりnullableな)ので ポインタ型にする必要があったのです。構造体内のポインタ型を Printf でうまく表示する方法がみつからなかったのでこのようになってます。
• 大文字から始まる JetRepository は構造体ではなくインタフェースです。 リポジトリ初期化関数の返却値やコントローラーの仮引数の型として使うことで抽象化しています。

標準入力で受け取った値を makeCondition で組み立て、WHERE条件に渡すことでフィルタリングしています。 検索に必要なフィールドは固定でJOINしていますがパフォーマンスを考慮するなら JOIN の組み立ても分岐するべきでしょう。

今回の要件はシンプルなのでそこまで複雑ではないですがすでに少し辛さを感じます。検索条件を組み立てるSQLが分かれてしまっているのと、 文字列結合時に前後のスペースを意識しないといけないのが地味に精神に来ます。

📖 テンプレートに書いてみる

ということで実行するSQLを テンプレートファイルに切り出してみます。

まず SQL を書いたGoテンプレートがこちら。

SELECT
  jets.name AS jetName,
  jets.age AS jetAge,
  jets.color AS jetColor,
  pilots.name AS pilotName,
  languages.language
FROM jets
JOIN pilots ON pilots.id = jets.pilot_id
LEFT JOIN pilot_languages ON pilot_languages.pilot_id = jets.pilot_id
LEFT JOIN languages ON languages.id = pilot_languages.language_id
WHERE TRUE
  {{ if ne .Age 0 -}}
  AND jets.age = :age
  {{- end}}
  {{if ne .PilotName "" -}}
  AND pilots.name LIKE :pilot_name
  {{- end}}
  {{if ne .JetName "" -}}
  AND jets.name LIKE :jet_name
  {{- end}}
  {{if ne .Language "" -}}
  AND languages.language = :language
  {{- end}}
ORDER BY jets.age, jets.id
;

パラメータの有無によってかき分けたいところは {{}} で囲んで条件分岐しています。 Go テンプレートの記法は Package template とかを見ましょう

続いてこのSQLを読み出して描画する関数 GetSQL を以下のように定義してみます。

package db

import (
	"bytes"
	"text/template"
)

func GetSQL(filename string, req interface{}) string {
	var buf bytes.Buffer
	t := template.Must(template.ParseFiles("sql/" + filename))
	t.Execute(&buf, req)
	return buf.String()
}

見てわかるようにテンプレートを読み出して描画したものをバッファに突っ込んでいるだけです。

この関数を用いて先程と同様のプログラムを書いてみますね。ほぼリポジトリにしか違いがないので右側にだけ注目してもらえればよいです。

• main_with_template.go

  package main
  
  import (
  	"context"
  	"database/sql"
  	"flag"
  	"fmt"
  	"gorp-tips/controllers"
  	"gorp-tips/models"
  	"gorp-tips/repositories"
  
  	"github.com/go-gorp/gorp"
  	_ "github.com/go-sql-driver/mysql"
  	"github.com/labstack/gommon/log"
  )
  
  func initDb() *gorp.DbMap {
  	db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
  	dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "utf8mb4"}}
  	models.MapStructsToTables(dbmap)
  	return dbmap
  }
  
  func main() {
  	dbmap := initDb()
  	defer dbmap.Db.Close()
  
  	var (
  		age       = flag.Int("age", 0, "jet age")
  		pilotName = flag.String("pilot_name", "", "pilot name")
  		jetName   = flag.String("jet_name", "", "jet name")
  		language  = flag.String("language", "", "language")
  	)
  	flag.Parse()
  	req := models.Request{
  		Age:       *age,
  		PilotName: *pilotName,
  		JetName:   *jetName,
  		Language:  *language,
  	}
  	repo := repositories.NewJetRepository1(dbmap)
  	results, err := controllers.GetJets(context.Background(), repo, req)
  	if err != nil {
  		log.Fatal(err)
  		return
  	}
  	for _, record := range results {
  		fmt.Printf("%+v\n", record)
  	}
  }
  
  

• jet_repository1.go

  package repositories
  
  import (
  	"context"
  
  	"github.com/go-gorp/gorp"
  	"github.com/labstack/gommon/log"
  
  	"gorp-tips/db"
  	"gorp-tips/models"
  )
  
  type jetRepository1 struct {
  	exec gorp.SqlExecutor
  }
  
  func NewJetRepository1(exec gorp.SqlExecutor) JetRepository {
  	return &jetRepository1{
  		exec: exec,
  	}
  }
  
  func (r *jetRepository1) GetJets(ctx context.Context, req models.Request) ([]models.Result, error) {
  	query := db.GetSQL("query.sql", req)
  	log.Debug(query)
  
  	var results []models.Result
  	if _, err := r.exec.Select(&results, query, map[string]interface{}{
  		"age":        req.Age,
  		"pilot_name": "%" + req.PilotName + "%",
  		"jet_name":   "%" + req.JetName + "%",
  		"language":   req.Language,
  	}); err != nil {
  		log.Error(err)
  		return nil, err
  	}
  	return results, nil
  }
  
  

プログラムの最初の方は共通なので同じで、重要なのは後半です。 ファイル名(パス)と検索パラメータの構造体だけを渡してあげればSQLが取得できるのでとてもシンプルに見えませんか？

その後、取得した SQL中の 変数 を 展開するためのコンテキストをmapで渡してあげています。 渡した変数の使用可否を決めるのはテンプレートなのでこちらは何も考えずに検索条件を組み立ててあげればよいのです。

🐍 テンプレートファイルをバイナリに潜影蛇手するわね

アプリを動かす環境がコンテナの場合 実行するSQLファイルもイメージに乗せてあげないといけません。

docker add などで追加するのもいいんですが、今回は statik というプログラムを使います。

これは静的ファイルをパッケージに埋め込むことで go のプログラムから ファイルへのアクセスを擬似的に表現します。

statikの具体的な用途はDockerイメージ内でファイルを参照するとか、シングルバイナリにアセットを含めて配布するような場合です。

実際にファイルをバイナリ(プログラム)に埋め込むには go get github.com/rakyll/statik のように statik コマンドをインストールする必要があります。 (ビルドだけなら不要)

プログラム中に書かれている //go:generate statik -f -src=../files -m というコメントは ../files 内のファイルを statik パッケージ(正確には statik.go)に閉じ込めるアノテーションのようなもので、 go generate することで実行されます。(ディレクトリに再帰的に適用する場合は go generate ./... のようにすること)

自動的に同期されるわけではないのでファイルが更新されたら statik の実行も忘れないようにしましょう。

初回実行時には statik が空で go のパッケージとして認識されないので、私の環境では dummy.go というファイルを置いています。

使うSQLファイルは先ほどと同じなので説明を省略して、SQLを読み出すための関数を新設します。 雑に GetSQL2 と命名しました。

package db

import (
	"bytes"
	"io/ioutil"
	"os"
	"text/template"

	_ "gorp-tips/db/statik"

	"github.com/rakyll/statik/fs"
)

//go:generate statik -f -src=../sql -m

var files, _ = fs.New()

func GetSQL2(filename string, req interface{}) string {
	var buf bytes.Buffer
	f, _ := files.Open(string(os.PathSeparator) + filename)
	b, _ := ioutil.ReadAll(f)
	t := template.Must(template.New(filename).Parse(string(b)))
	t.Execute(&buf, req)
	return buf.String()
}

この関数を用いて先程と同様のプログラムを書いてみますよ。

• main_with_template_in_bin.go

  package main
  
  import (
  	"context"
  	"database/sql"
  	"flag"
  	"fmt"
  
  	"github.com/go-gorp/gorp"
  	_ "github.com/go-sql-driver/mysql"
  	"github.com/labstack/gommon/log"
  
  	"gorp-tips/controllers"
  	"gorp-tips/models"
  	"gorp-tips/repositories"
  )
  
  func initDb() *gorp.DbMap {
  	db, _ := sql.Open("mysql", "usr:pw@tcp(mysql:3306)/db")
  	dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{Engine: "InnoDB", Encoding: "utf8mb4"}}
  	models.MapStructsToTables(dbmap)
  	return dbmap
  }
  
  func main() {
  	dbmap := initDb()
  	defer dbmap.Db.Close()
  
  	var (
  		age       = flag.Int("age", 0, "jet age")
  		pilotName = flag.String("pilot_name", "", "pilot name")
  		jetName   = flag.String("jet_name", "", "jet name")
  		language  = flag.String("language", "", "language")
  	)
  	flag.Parse()
  	req := models.Request{
  		Age:       *age,
  		PilotName: *pilotName,
  		JetName:   *jetName,
  		Language:  *language,
  	}
  	repo := repositories.NewJetRepository2(dbmap)
  	results, err := controllers.GetJets(context.Background(), repo, req)
  	if err != nil {
  		log.Fatal(err)
  		return
  	}
  	for _, record := range results {
  		fmt.Printf("%+v\n", record)
  	}
  
  }
  
  

• jet_repository2.go

  package repositories
  
  import (
  	"context"
  
  	"github.com/go-gorp/gorp"
  	"github.com/labstack/gommon/log"
  
  	"gorp-tips/db"
  	"gorp-tips/models"
  )
  
  type jetRepository2 struct {
  	exec gorp.SqlExecutor
  }
  
  func NewJetRepository2(exec gorp.SqlExecutor) JetRepository {
  	return &jetRepository2{
  		exec: exec,
  	}
  }
  
  func (r *jetRepository2) GetJets(ctx context.Context, req models.Request) ([]models.Result, error) {
  	query := db.GetSQL2("query.sql", req)
  	log.Debug(query)
  
  	var results []models.Result
  	if _, err := r.exec.Select(&results, query, map[string]interface{}{
  		"age":        req.Age,
  		"pilot_name": "%" + req.PilotName + "%",
  		"jet_name":   "%" + req.JetName + "%",
  		"language":   req.Language,
  	}); err != nil {
  		log.Error(err)
  		return nil, err
  	}
  	return results, nil
  }
  
  

動いていそうなのでこれ以上言うことはないです。

👺 注意点

テンプレートの変数とSQL自体の変数で２段階の展開をしているので「なぜこんな面倒な使い分けをしてるの？」と思うかもしれませんが、 可能な限り SQLの変数(以降プレースホルダ)を使うようにしたいのです。

GoのテンプレートはSQLを理解しないので、テンプレートによって変数を出力すると 不正なSQL断片を受け入れてSQLインジェクション攻撃に対して脆弱になります。

ユーザからの入力値を描画する場合はプレースホルダを使いましょう。ぉじさんとの約束だょ。

• OK
• NG

• -- 前略 AND jets.age = :age -- 後略
• -- 前略 AND jets.age = {{.Age}} -- 後略

テンプレートにSQLを書くためのサードパーティライブラリもあるようですが、標準テンプレートだけで大した手間もなく実装できました。

もしSQLの組み立てに苦しんでいる方はぜひ試してみてください。

こんなん常識やろとか、もっといい方法があるぞという方はコメントお願いします！