フューチャー技術ブログ

業務システム開発でsqlcを導入して良かった点とハマった点

はじめに

Technogoly Innovation Group 辻です。Go には GormSQLBoiler をはじめとして様々な ORM があります。2021 年には当社のブログで OR マッパーの連載を行ったこともありました。絶対的な ORM があるわけではなく、業務システムの特性やチーム構成などに合わせて ORM を選択することになるでしょう。

今回、私たちのチームでは、バッチ処理が中心的な業務システム開発において Go の ORM に sqlc を採用しました。素の SQL を書いていくチームの開発方針1とマッチし、開発体験は非常に良かったです。一方、枯れきってはいない ORM ではあります。いくつか想定外の挙動が発生し GitHub の Issue を見ながら問題を切り分けることもありました。

これから sqlc を導入してみようかな、と考えている方々の参考になればと思い、sqlc を業務システム開発に導入してみて良かった点とハマった点を紹介します。

なお本記事では sqlc の概要は扱いません。そもそも sqlc って何?って方は sqlc の作者が書いた記事や OR マッパー連載で紹介したこちらの記事を見てみると良いでしょう。

ソフトウェアのバージョンは以下です。データベースは PostgreSQL を採用していることに注意してください。

  • PostgreSQL 13.7
  • Go 1.19
  • sqlc 1.15.0

良かった点

1. SQL の開発に集中できる

sqlc を使う一番のメリットは SQL の開発に集中できることです。SQL の実装以外の面倒なことは sqlc がやってくれます。以下の流れで開発できます。

  • SQL を書く
  • sqlc generate でコード生成する
  • 生成された API を Go のコードから呼び出す

sqlc を使うと、SQL から、SQL の結果をマッピングできる構造体やデータベースにアクセスできる API を生成できます。生成される構造体のフィールドの型は SQL の結果に応じて、適切に選択されます。また、バインドパラメータとして SQL に値を渡す場合も、パラメータを渡すための構造体とその構造体を API に渡せるコードが生成されます。アプリケーションではパラメータの構造体に値をセットするだけでよく、構造体のフィールドの型はコンパイル時にチェックできます。

クエリの結果をマッピングする Go の構造体を作成したり、バインドパラメータの順番やデータ型を注意深く確認したり、クエリを発行するボイラープレートコードを実装したりすることは必要はなくなります。

2. ファイル生成が高速である

sqlc はファイル生成が高速です。数十の SQL ファイルからコードを生成するのに 1 秒もかかりません2。高速な理由の 1 つは sqlc ではコード生成時にデータベースアクセスを必要としないためです3。SQL ファイルからクエリの結果をマッピングできるコード生成ライブラリは sqlc の他に xo/xo などがあります4。ただコードを生成するときにデータベースアクセスが必要になり、生成にオーバーヘッドがかかります。ファイル生成は何度も実行するため、その操作が高速であることは地味ながら非常に嬉しいポイントでした。

3. 自然なコードが生成できる

自然なコードが生成でき、sqlc が生成したコードを違和感なく利用できました。特に、パラメータに関して $1, $2 などとしたときに、生成される Go のコードはテーブルのカラム名に応じたフィールド名となります。たとえば以下の users テーブルにレコードを INSERT する SQL から生成されるコードは NewUser()NewUserParams のようになります。

-- name: NewUser :exec
INSERT INTO users (
id,
first_name,
last_name,
age,
) VALUES ($1, $2, $3, $4);

IDFirstName といったフィールド名を持つ構造体が生成されます。

type NewUserParams struct {
ID int32
FirstName string
LastName sql.NullString
Age int32
}

func (q *Queries) NewUser(ctx context.Context, arg NewUserParams) error {
_, err := q.db.ExecContext(ctx, newUser,
arg.ID,
arg.FirstName,
arg.LastName,
arg.Age,
)
return err
}

また、必要に応じて SQL 上のパラメータを sqlc 固有の特殊な構文を使い、 @my_first_name などと記述して、生成される Go のコードのフィールド名を書き換えられます5$1 などのパラメータで生成できるフィールド名に違和感がある場合は @xxxx を使って任意のフィールド名に書き換えられる柔軟さも良かったです。

  • 補足事項

なお、チーム開発で導入する場合はあらかじめ命名規約を決めておいたほうが良いです。

API 名は SQL のコメントで記載した命名が使われます。上の例では -- name: NewUser としている NewUser が API 名や構造体名に使われます。NewUser, CreateUser あるいは InsertUser などと選択する余地があり、開発者間で揺れるポイントです。

4. 生成できるコードの種類が豊富である

database/sql パッケージの ExecContext()QueryContext()QueryRowContext() を使って SQL を実行できるコードが生成できます。また PostgreSQL 専用のドライバーである pgx の機能を使ったバッチ処理可能なコードも生成できます。さらに Copy プロトコルを使った高速なデータコピーができるコードも生成できます。業務システム開発では 10 万~100 万といったオーダーのデータを扱うこともあったため、バッチ処理や Copy プロトコルをサポートしていることは非常に嬉しいです。

生成するコードは sqlc のクエリアノテーションで指定できます。詳しくは Query annotations などを参照してください。

5. 生成されるデータ型を柔軟に変更できる

生成されるコードのデータ型を柔軟に設定できる点も便利です。

たとえばドライバーに pgx を使っているとき PostgreSQL の numeric 型のカラムはデフォルトでは pgtype.Numeric 型にマッピングされます。しかし誤差を許容して float64 型で扱いたいケースがありました6

設定ファイルの overrides 機能を使うことで生成される型を変更できます。上記のケースであれば、以下のような記述を YAML の設定ファイルに記載します。

データベースの numeric 型を Go の sql.NullFloat64 型でマッピングするように更新する設定ファイル例
overrides:
- db_type: "pg_catalog.numeric"
go_type: "database/sql.NullFloat64"
nullable: true
- db_type: "pg_catalog.numeric"
go_type: "float64"

6. 複数の開発言語をサポートしている

2022/11 現時点ではまだベータ版ですが、実は sqlc は Go 以外にも Python や Kotlin といった言語をサポートしています7。「SQL を書いて、コード生成し、そのコードを呼び出す」という開発体験は言語を問わず、同じです。私たちのチームでは主に Go での開発がメインでした。ただ、いくつか小さなバッチ機能を Python で実装する必要があり、 sqlc を導入することでスムーズに開発できました。

sqlc の作者は protoc のような多言語のコードを生成することを目的の 1 つとしている、と言っています。TypeScript や Rust などの言語も将来的にサポートされるかもしれません(#296)。

7. 1SQL ファイルに複数のクエリを書いても良い

1SQL ファイルに 1 クエリ、というイメージがあるかもしれませんが特に制約はありません。1SQL ファイルに複数のクエリを書いても良いです。たとえば、以下のように CRUD を 1SQL ファイルに集約しても、各 SQL に紐づく API が生成できます。

-- name: GetAll :many
SELECT * FROM users;

-- name: NewUser :exec
INSERT INTO users (
id,
first_name,
last_name,
age,
shoe_size,
shirt_size
) VALUES
($1, $2, $3, $4, $5, $6);

-- name: UpdateSizes :exec
UPDATE users
SET shoe_size = $2, shirt_size = $3
WHERE id = $1;

-- name: DeleteBySize :exec
DELETE FROM users
WHERE shoe_size = $1 AND shirt_size = $2;

ハマった点

sqlc は素晴らしい ORM ですが、開発中にいくつかハマりどころがありました。インパクトが大きかった順に記載します。

1. 有効な SQL にもかかわらず sqlc でコード生成できないことがある

基本的には PostgreSQL で実行できる SQL はすべて sqlc で扱えます。しかし、いくつかの SQL は PostgreSQL 上は有効にも関わらず、sqlc でコード生成に失敗します。コード生成できない場合はその SQL を sqlc では使わないように書き換えする、あるいは sqlc を経由せず database/sql パッケージの API から直接 SQL を呼び出すなどの回避する必要があります。

参考までに sqlc でコード生成できない SQL の例をあげます。

  • 例1:UPDATE 句で JOIN を使う場合(#1100

このパターンが一番影響がありました。JOINUPDATE が同時にできないため、JOIN して更新データを SELECT するクエリと UPDATE するクエリの 2 つに分割して対処しました。

UPDATE
group_calc_totals gct
SET
npn = nem.npn
FROM
producer_group_attribute ga
JOIN
npn_external_map nem
ON ga.npn_external_map_id = nem.id
WHERE
gct.group_id = ga.group_id;
  • 例2:サブクエリと AS, GROUP 句を使う場合(#1886

以下のような SQL もコード生成に失敗します。

SELECT
s.id AS "s_id"
FROM
(
SELECT
spam.id
FROM
spam
) AS "s"
-- 以下を s.id に変更すれば機能する
GROUP BY s_id;

2. マッピングする構造体の型が NULL になりうる場合でも NULL を許容しない型で生成されることがある

テーブルのカラム自体は NOT NULL 制約があるが、SQL の条件によって NULL になりうるケースです(#1208)。このケースはコンパイル時はエラーにならず、実行時にカラムの値が NULL になるとエラーが発生するため、注意が必要です。

たとえば以下の one テーブルの name カラムは NOT NULL 制約が付与されています。しかしサブクエリの WHERE 句の条件に該当しない場合は SELECT できる結果が NULL になりえます。このような条件下でも sqlc は NOT NULL なカラムとして構造体を生成します。期待としては sql.NullInt32 型として NULL を許容される型のフィールドを想定しますが、実際には int32 と NULL な値をマッピングできない型のフィールドになります。

CREATE TABLE one (
id INTEGER PRIMARY KEY,
otherfield TEXT,
name VARCHAR(128) NOT NULL
);
SELECT
otherfield,
(SELECT o.id FROM one o WHERE one.name = 'foo' LIMIT 1) AS "id"
FROM one;
type TestRow struct {
Otherfield sql.NullString
ID int32 // 本当は sql.NullInt32 になってほしい!
}

3. IN 句に複数の値を渡せない

IN 句で複数の値を渡して、複数のレコードを抽出するクエリは一般的ですが sqlc を使ってコード生成する場合は技巧が必要になります(#77, #167)。たとえば authors テーブルからいくつかの id にマッチするレコードを抽出するクエリを考えてみます。

-- name: ListAuthorsByIDs :many
SELECT * FROM authors
WHERE id IN ($1)
;

このようなクエリから生成される Go のコードでは string の単一の値は渡せますが複数の ID を含んだ []string の値は渡せません。生成されるメソッドのシグネチャは以下になります。

ListAuthorsByIDs(ctx context.Context, id string) ([]Author, error)

対処方法として、クエリを以下のように ANY8を使うように書き換える必要があります。

SELECT * FROM authors
WHERE id = ANY(@ids::VARCHAR[]);
;

上記のような SQL からは以下のシグネチャのコードが生成でき、また複数の値にマッチするレコードを取得できます。

ListAuthorsByIDs(ctx context.Context, ids []string) ([]Author, error)

4. 集計関数の結果や CASE 句の値が interface{} 型になる

MIN()MAX(), SUM() といった集計関数の結果は、引数のデータ型ではなく interface{} 型として生成されます。

たとえば以下の MIN を使うケースでは int32 型で生成されることを期待するかもしれませんが、interface{} 型になります(#1574)。

CREATE TABLE sales (
id SERIAL PRIMARY KEY,
vat INTEGER NOT NULL
);

SELECT
MIN(vat) AS vatpence
FROM
sales;

こうしたケースでは明示的に SQL で型キャストを行うことで回避できます。上記のケースであれば ::INTEGER あるいは CAST(MIN(vat) AS INTEGER) と明示することで Go では int32 型にマッピングできます。

SELECT
MIN(vat)::INTEGER AS vatpence
FROM
sales;

CASE 句を扱う場合も同様に interface{} 型となるため、必要に応じて型キャストをすると良いでしょう。

5. DDL が扱えない

sqlc は 1.15.0 では DDL をサポートしていません(#1661)。複雑なビジネスロジックを SQL で実装しているときに、処理の中間結果を一時表に保持したいユースケースがありました。セッション開始時に CREATE TEMPORARY TABLE ... として一時表を作成しようとしましたが sqlc ではコード生成できませんでした。これは pgxExec() から直接クエリを発行して対処しました。

まとめ

業務システム開発に sqlc を導入して良かった点とハマった点を紹介しました。素の SQL を書きたいケースでは非常に良い ORM と感じています。Go の ORM の選択に迷っている方の参考になれば嬉しいです。


  1. 1.業務システム開発ではロジックを SQL に寄せることも多く、複数のテーブルを JOIN したり、ウィンドウ関数を使って処理をしたりなど、SQL が複雑になりがちです。複雑な SQL は ORM のクエリビルダーAPI を使ってクエリを組み立てるのではなく、手書きで実装したいケースが多いです。たとえば ORM に Gorm を使っていたとしても Raw() を使わざるを得ない、あるいは使いたいケースが多くありました。
  2. 2.生成するコード量は 1 ファイルに含まれる SQL の数にもよるため、あくまで目安です。
  3. 3.データベースアクセスしないにも関わらず型安全なコードを生成できるのは理由は、sqlc が C 言語で書かれた本物の PostgreSQL のクエリパーサを Go から呼び出し、パースされたツリーからコード生成するアプローチを採用しているためです。
  4. 4.xo はファイル生成に時間がかかるだけでなく、生成できるコードが私たちの求める機能/非機能要件と合わなかったため、採用を見送りました。
  5. 5.https://docs.sqlc.dev/en/stable/howto/named_parameters.html?highlight=Named parameter#naming-parameters
  6. 6.pgtype.Numeric 型は big.Int 型の値をフィールドに含んでおり、浮動小数点演算の誤差が許されない場合では、このデータ型を使うことは妥当です。一方、数値計算が float64 のような +, -, /, * でできません。API を使って演算しますが、コードが読みづらく、メンテナンスが難しくなる点がデメリットとしてあります。
  7. 7.https://docs.sqlc.dev/en/stable/reference/language-support.html?highlight=beta#database-and-language-support
  8. 8.ANY 句は SOME 句でもよいです。https://www.postgresql.jp/document/13/html/functions-comparisons.html