2021-05-26

SQLBoiler の使い方を簡単にまとめた

最近、業務で使っている SQLBoiler の使い方についてわかったことをまとめておきます。

info

当記事で扱うSQLBoilerのバージョンは v3.6.1 です。
バージョンによっては挙動が異なる箇所があるかもしれません。

SQLBoilerとは？

SQLBoilerはデータベーススキーマから Go の ORMコードを自動生成してくれるライブラリです。

参照するスキーマは定義ファイルではなく実際に動いているDBなので、実際に試すためには実行環境が必要です。

手元の検証用コードをリポジトリに移動したので必要があれば使ってみてください。

GitHub - righ/sqlboiler-exampleContribute to righ/sqlboiler-example development by creating an account on GitHub.https://github.com/righ/sqlboiler-example

$ docker-compose up # -d
$ docker exec -it sqlboiler bash

のようにコンテナを立ち上げて中に入り、自動生成するためのコマンドは以下のようにインストールしましょう。

$ go get -u -t github.com/volatiletech/sqlboiler

これで準備完了です。この記事で示すコマンド等はこのコンテナの中で実行できるはずです。

PostgreSQLの説明も多少書きますが、基本的に MySQL を前提に進めていきます。

もしかしたらハマりどこかもしれないのでスキーマに関する注意点も書いておきます。

warning

SQLBoilerが対象とするテーブルは一つ以上の主キーをもつ必要があります。ただしサロゲートキーである必要はありません。(ないと Error: unable to initialize tables: primary key missing in tables (pilots) のようになります)
ユニークキー, 外部キー, NOT NULL のような制約も自動生成されるコードに影響するので用途に合わせてできるだけ設定しておくとよいでしょう。
- NOT NULL制約がないとモデル(後述)のフィールドはプリミティブな型ではなくnull.String のような独自の型で出力されます。
  - 詳細はこの辺を: GitHub - volatiletech/null: reasonable handling of nullable valuesreasonable handling of nullable values. Contribute to volatiletech/null development by creating an account on GitHub.https://github.com/volatiletech/null
  - 同僚曰く扱いづらいということなのでできる限り NOT NULL 制約をつけるようにしたほうがよいでしょう。

接続情報

以下のように接続情報を設定ファイルに書きます。形式は TOML, YAML, JSON のいずれかです。今回は TOML にしてみました。

RDBMSの違いによる実装方法はさほど違いありませんが、設定や出力方法、接続については多少の違いがあります。

MySQL
PostgreSQL

# スキーマ作成
$ mysql -h mysql -uusr -p db < schema.sql
Enter password: # pw

$ go get github.com/volatiletech/sqlboiler/drivers/sqlboiler-mysql
$ sqlboiler mysql --output models_mysql --pkgname models_mysql --wipe

# スキーマ作成
$ psql -h postgres -U usr db -f schema.sql
Password for user usr: # pw

$ go get github.com/volatiletech/sqlboiler/drivers/sqlboiler-psql
$ sqlboiler psql --output models_psql --pkgname models_psql --wipe

今回は複数のDBがあるため便宜的に出力先およびパッケージ名を指定しましたが指定しなければデフォルトで models になります。

--output

出力先のディレクトリを変更する(default: models)

--pkgname

パッケージ名を変更する(default: models)

GitHub - volatiletech/sqlboiler: Generate a Go ORM tailored to your database schema.Generate a Go ORM tailored to your database schema. - GitHub - volatiletech/sqlboiler: Generate a Go ORM tailored to your database schema.https://github.com/volatiletech/sqlboiler

スキーマ作成時に指定するセッションと利用時に接続するセッションは異なるので混同しないようにしましょう。

info

上記の例では一度しかDBに接続しなかったため、とくに不便さを感じませんでしたが DBに接続するたびに dbセッションを指定するのはめんどくさいと感じるかもしれません。
boil.SetDB(db) とすることで接続情報をグローバル領域に保存してそれを利用することができます。
この接続情報は .AllG() のように G を末尾につけた関数を呼び出すことで利用できます。
P を指定するとエラーのときにパニックが発生するようになります。(err の返却値がなくなるため注意)
G と併用する場合 G を先に書き、 AllGP() のようにします。

Model

SQLBoiler では一つのテーブルをモデル構造体として出力します。

各モデル構造体にはレコードを扱うためのメソッドが以下のように定義されています。

Insert

レコードをテーブルに追加するためのメソッドです。

root@8ebb694c0f41:~# go run insert.go
root@8ebb694c0f41:~# mysql -h mysql -uusr -ppw db -e "SELECT * FROM pilots;"
+----+------+
| id | name |
+----+------+
|  1 | Righ |
+----+------+

バルクインサートはサポートされていません。
カスタムテンプレートを書けば実現できるようです。(カスタムテンプレートについては後述)

Update

変更後のレコードをテーブルに反映させるメソッドです。

root@8ebb694c0f41:~# mysql -h mysql -uusr -ppw db -e "SELECT * FROM pilots;"
+----+---------+
| id | name    |
+----+---------+
|  1 | Crohaco |
+----+---------+

Reload

レコードを最新の状態に更新するメソッドです。

root@8ebb694c0f41:~# go run reload.go
1 Crohaco

Upsert

レコードが既に登録されていれば更新、されていなければ追加するメソッドです。登録の有無はプライマリキーによって行われます。(未検証)
3番目引数には UPDATEフィールド, 4番目の引数には INSERTフィールド を指定します。

root@8ebb694c0f41:~# go run upsert.go
root@8ebb694c0f41:~# mysql -h mysql -uusr -ppw db -e "SELECT * FROM jets;"
+----+----------+-----+------+-------+
| id | pilot_id | age | name | color |
+----+----------+-----+------+-------+
|  1 |        1 |  10 | MRJ  | White |
|  2 |        1 |  20 | F-14 | Gray  |
+----+----------+-----+------+-------+

フィールドは boil.Whitelist("field", "field2") のように可変長で複数指定します。 id=1 のレコードは age が更新されていないのがわかりますね。
UPDATEのフィールド以外に追加のフィールドも指定できますが、追加するフィールドを限定する場合はスキーマにデフォルト値が設定されている必要があります。
- (MySQLの場合)例えば boil.Whitelist("id", "pilot_id") を INSERTフィールドに指定すると以下のようなエラーになります。
  - models_mysql: unable to upsert for jets: Error 1364: Field 'age' doesn't have a default value
- フィールドを指定しない場合は boil.Infer() を指定すると推測されたフィールドが利用されます。大抵はこれで十分です。
複合キーを持つテーブルでのUpsertはバグにより正常に動作しない箇所があります。
Upsert Problem on Multiple Columns Unique Key (MySQL) · Issue #328 · volatiletech/sqlboilerUpsert() returns a error cannot upsert with a table that cannot conflict on a unique column. What version of SQLBoiler are you using (sqlboiler --version)? SQLBoiler v3.0.0-rc9 If this happened at ...https://github.com/volatiletech/sqlboiler/issues/328
正確には覚えていないんですが、追加用のフィールドと更新用のフィールドを両方とも明示するとどちらかが勝ってしまって期待した動作をしなかったような記憶があります。こんな感じにUpsertはいくつかバグがあったので、正常に動作しない場合は発行されているSQLを確認してみることをおすすめします。

Delete

該当レコードをテーブルから削除するためのメソッドです。

root@8ebb694c0f41:~# go run delete.go
root@8ebb694c0f41:~# mysql -h mysql -uusr -ppw db -e "SELECT * FROM pilots;"
root@8ebb694c0f41:~#

info

テーブルが外部から参照されている場合、外部レコードを作るためのメソッド(Add{モデル}s?, Set{モデル}s?)が作られますが制約によって作られるメソッドは異なります。
- 例えば Pilot モデルは AddJets メソッドを持ちますが、 jets.pilot_id にUNIQUE制約があったり、 pilot_id 自体を主キーに設定した場合、 AddJets ではなく SetJet メソッドが作られるという具合です。
(記憶が曖昧ですが) 主キーを間違えた場合に AddJet (単数形)が作られたこともありました。期待通りのモデルが作られない場合はスキーマを見直してみてください。
２カラムしかない純粋な中間テーブルはモデル自体が生成されないと言った挙動をするみたいです。
生成ルールに関するドキュメントはなさそうな気がする

Query

Queryはテーブルからレコードを抽出するための関数群です。

Django をやったことがあるなら QuerySet と同じようなものと考えるとわかりやすいです。

Modelと同様、Queryという名前の関数があるわけではなくテーブルごとに対応するQueryがあります。 models.Jet モデルに対応するQueryなら models.Jets のような関数があり、実行するには models.Jets(mods...) のようにします。

(modsについては次のセクションで説明します)

Mod

SQLBoilerはクエリに対する操作は mod という概念で表現され、以下のような種類があります。

Select

抽出対象の列を指定

InnerJoin

内部結合のテーブルと条件を指定

Where

検索条件を指定

Eq

完全一致検索の条件を指定

GroupBy

グルーピングする列を１つ以上指定する

OrderBy

ソート対象の列を１つ以上指定する

Having

HAVING(グルーピング後の絞り込み条件)を指定する

Limit

LIMITを指定する

Offset

OFFSETを指定する

Load

Earger load する列を指定する

これだけだとどのように使うかわかりにくいので使用例を書いてみます。たとえば

SELECT pilots.name, MIN(jets.age) AS age
FROM jets
INNER JOIN pilots ON pilots.id = jets.pilot_id
WHERE jets.age >= 20
GROUP BY pilots.name
HAVING COUNT(*) >= 2
ORDER BY age desc
LIMIT 2
OFFSET 1
;

のようなSQLを作ろうとしたら以下のように書きます。

root@8ebb694c0f41:~# go run mods.go
Albert 20
Warner 20

Eager loading

先程軽く触れましたが、これについてはもう少し詳細に書いておきます。

qm.Load("Field") とすることで外部キーのIDをまとめて参照先テーブルに別クエリを発行し、取得した結果を自動的に紐付けてくれます。 SQL自体を変更するものではないと言う意味でほかの Mod とは少々異なる機能と言えます。

Django でいうなら prefetch_related といったところでしょうか。イメージがわかない方はこちらを見たほうがわかりやすいかもしれません。

外部キーのリレーションを一つずつ解決しても同じ結果を得ることはできますが、レコード件数の回数クエリが発生する所謂 N+1問題 となるためおすすめできません。

Load の第一引数はリレーションを表すドット区切りの文字列です。この文字列は XXXRels (XXXはモデル名)に定義されているのでそれを使うのが確実です。

(ドット区切りにする場合は自分で組み立てる必要がある)

ちなみにLoadしたリレーションは R 経由で参照できます。リレーションが単数形か複数系かはリレーションの向きとユニーク制約によります。

languages と pilots は中間テーブルにより N:M の関係になっているため複数形での参照となります。

info

別のリレーションを参照するときに真っ先に思い浮かぶのは JOIN ですね。
では JOIN と比べた Eager load の優位性はなんでしょうか？
JOINは結合条件に該当するレコードが複数あれば行数が増え、該当がなければ行数は０になります。この性質はうまく使えばありがたいものですが、単に一覧したい場合は扱いにくいものでしょう。
Eager load で取得したリレーションはその件数によらず、もとのレコード数に影響を与えません。
先程のコードを見てもわかるかもしれませんが、 qm.InnerJoin によって結合したフィールドは Select() で指定することで参照できますが、それを受けるためのフィールドが定義された構造体が必要です。
How to access inner join data? · Issue #536 · volatiletech/sqlboilerHello, I am sorry for a potentially silly question, but I've been stuck on this for a while now. What is the correct way of accessing data that we 'joined' after we got the results back? Here is an...https://github.com/volatiletech/sqlboiler/issues/536
おそらく、 SQLBoiler は外部リレーション取得は基本的に JOIN ではなく Eager load で行うというコンセプトなんでしょう。(LEFT JOINも使えないしね)

warning

qm.Select でフィールドを限定する場合、外部キーのフィールドも含めないとロードしたリレーションが nil になります。
Loadしないでリレーション(R)を参照しても何も取れません。

Finisher

さて、先程実行例を書いたのでもうお気づきかも知れませんが実はクエリはそのままでは利用できません。

クエリから結果を抽出したり、テーブルに変更を加えるための操作が Finisher です。

One

該当レコードの先頭をモデル(構造体)のリファレンスで取得します。

root@8ebb694c0f41:~# go run one.go
&{1 Manfred <nil> {}}

All

すべての該当レコードをモデルのスライスで取得します。

root@8ebb694c0f41:~# go run all.go
Manfred
William
Albert
Warner
Andrew

Count

該当レコードの数を返却します。

root@8ebb694c0f41:~# go run count.go
num of pilots: 5

UpdateAll

該当レコードをすべて更新します。更新内容は map で指定します。

root@8ebb694c0f41:~# mysql -h mysql -uusr -ppw db -e "SELECT * FROM pilots;"
+----+------+
| id | name |
+----+------+
|  1 | Saki |
|  2 | Saki |
|  3 | Saki |
|  4 | Saki |
|  5 | Saki |
+----+------+

DeleteAll

該当レコードをすべて削除します。

root@8ebb694c0f41:~# go run delete_all.go
root@8ebb694c0f41:~# mysql -h mysql -uusr -ppw db -e "SELECT * FROM jets;"
root@8ebb694c0f41:~#

Exists

該当するレコードが１件以上存在することを確認します。

root@8ebb694c0f41:~# go run exists2.go
true

Shortcuts

クエリを経由して Finisher で結果を得られますが、単純な処理を長々と書きたくないものです。

以下のメソッドについては Queryを介さずに呼び出せます。

Find

Query(qm.Where("id=?", id)).One(ctx, db) と同じです。

root@8ebb694c0f41:~# go run find.go
&{1 Crohaco <nil> {}} <nil>

Exists

Query(qm.Where("id=?", id)).Exists(ctx, db) と同じです。

root@8ebb694c0f41:~# go run exists.go
true false

Raw query

残念なことにこれまでの機能ではサブクエリを含む複雑なクエリや LEFT JOIN などを実現できません。

一応 Raw() によって生SQLを書けばカバーできるのでその方法を抑えておきましょう。

Bind

Raw によって生成されたクエリを Bind() によって構造体に紐付けることでレコードを抽出できます。
このときフィールド名を構造体のフィールド合わせる必要があります。複数の構造体を埋め込んでフィールド名が重複している場合 {モデル名(小文字).フィールド名} とします。 (READMEだと複数形で書いてあったけど動かなかった..)

$ go run raw.go
{{1 Saki <nil> {}} {0 NO LANG <nil> {}}}
{{2 Saki <nil> {}} {0 English <nil> {}}}
{{3 Saki <nil> {}} {0 English <nil> {}}}
{{4 Saki <nil> {}} {0 German <nil> {}}}
{{4 Saki <nil> {}} {0 English <nil> {}}}
{{5 Saki <nil> {}} {0 NO LANG <nil> {}}}

LEFT JOIN で NULL になると変換に失敗するので COALESCE 関数などで補完する必要がありそうです。
error
- failed to bind pointers to obj: sql: Scan error on column index 2, name "language": converting NULL to string is unsupported

Exec

Exec はクエリを実行するためのメソッドです。 SELECT 以外の変更を加える文を実行するために使います。

root@8ebb694c0f41:~# go run exec.go
&{6 Diddy <nil> {}}

おまけ

以下のメソッドはクエリの実行結果を sql.Row または sql.Rows で返します。

SQLBoilerをメインで使っていればあまり使うことはないかもしれませんが、これまでの資産としてこれらを引数にしたユーティリティーなどを持っていれば使ってみましょう。

QueryRow

root@8ebb694c0f41:~# go run queryrow.go
1 Saki

Query

root@8ebb694c0f41:~# go run query.go
1 Saki
2 Saki
3 Saki
4 Saki
5 Saki

カスタムテンプレート

詳しくはやりませんが、欲しい機能がないときは自分でテンプレートを書くことで自動生成されるコードに機能追加できます。

sqlboiler.toml (設定ファイル)に以下のように書くか

templates = [
  "/path/to/sqlboiler/templates", # sqlboilerのテンプレートディレクトリ
  "/path/to/sqlboiler/templates_test", # sqlboilerのテスト用テンプレートディレクトリ
  "/path/to/your_project/more_templates" # 自分が作ったカスタムテンプレートディレクトリ
]

--templates オプションで指定します。

sqlboiler mysql --output models_mysql --pkgname models_mysql --wipe \
--templates /path/to/sqlboiler/templates --templates /path/to/sqlboiler/templates_test \
--templates /path/to/your_project/more_templates

注意が必要なのはこれらのテンプレートパスには SQLBoiler自身のテンプレートも含める必要があるということです。

オプションで指定する場合は以下のように $GOPATH のパスを指定してあげるか

sqlboiler mysql --output models_mysql --pkgname models_mysql --wipe \
--templates $GOPATH/pkg/mod/github.com/volatiletech/sqlboiler/v4@v4.1.2/templates --templates $GOPATH/pkg/mod/github.com/volatiletech/sqlboiler/v4@v4.1.2/templates_test \
--templates /path/to/your_project/more_templates

SQLBoilerのテンプレートディレクトリをプロジェクトリポジトリにコピーしてそのパスを指定する必要があります。

info

この方法は冗長だと感じたので追加分のテンプレートだけ指定できる引数を作って PR を投げたんですが、明示的に指定する今の方法が気に入っているということで却下されてしまったのでおそらく今後もテンプレートの指定方法が変わることはないと思います。
機能不足をカスタムテンプレートで補えるようになっているのにカスタムテンプレートの指定が冗長でやりづらいというのは個人的に残念なポイントでした。
仮にMakefileでやるにしても私は実行者の環境に依存するような書き方をしたくないし、バージョンが変わるたびにパスを書き換えるのも忘れそうです。

終わりに

実務で使っていたものも総合すると(ネガティブになりますが)、構造体の自動生成自体は便利でしたが、テンプレート指定の件を抜きにしても現時点(v3.6.1)では新規プロジェクトがあっても採用はしないかなーという感想です。

どうしても複雑なクエリは生SQLでの実行になりますし、期待通りのメソッドが生成されなくてハマるみたいなことが結構ありました。これについては前述したように制約で変わるものですが、詳細な生成ルールがドキュメントされていないため慣れないうちはしんどいです。開発者が自分だけなら慣れればいいだけですが他のプロジェクトメンバーに強いるのは辛そう。実際、他の開発者も違うORMを使いたがっていたように思えます。

あとプロジェクトや実行環境に拠ると思いますがコードの自動生成に結構時間(数分)がかかるので最初はそれが割と苦痛でした(ちなみに当時使っていたのは普通スペックのMacbook Pro)。慣れてくると先にコード生成しておいて別のタスクをやるようになるので大丈夫ｗ

似たようなライブラリとして以下があるので、今後Goでの開発で技術選定する機会があったら調べてみようと思います。

GitHub - xo/xo: Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL ServerCommand line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server - GitHub - xo/xo: Command line tool to generate idiomatic Go...https://github.com/xo/xo GitHub - smallnest/gen: Converts a database into gorm structs and RESTful apiConverts a database into gorm structs and RESTful api - GitHub - smallnest/gen: Converts a database into gorm structs and RESTful apihttps://github.com/smallnest/gen

DBスキーマからコードを生成するというコンセプトはおもしろいものでした。今後に期待ですね！