Zenn
🔧

SpannerでもsqlcでSQLから型安全なコードを生成してみる

2022/12/25に公開
Go
Google Cloud
Cloud Spanner
tech

この記事はMakuake Advent Calendar 2022の25日目の記事です。

皆さんはCloud Spannerは使っていますか？SpannerはACIDに対応した分散DBで、分類上はNewSQLと呼ばれています（他にはTiDBとか）。また、メンテナンスに際したダウンタイムが発生しない点でも運用コストが低く、社内では一部のサービスでSpannerを採用しています。

先日、ついにdatabase/sqlドライバーの一般提供がスタートしました！

https://cloud.google.com/blog/ja/topics/developers-practitioners/golangs-databasesql-driver-support-cloud-spanner-now-generally-available

これまでは独自のクライアントライブラリを使用しなければなりませんでしたが、これでMySQLやpostgreSQLなど他DBと同様に同一のinterfaceを介してDB操作が可能になります。
※ただし、一部制限事項もあります：https://github.com/googleapis/go-sql-spanner/blob/main/docs/limitations.rst

database/sqlに対応したことで、sqlcも使えるんじゃなかろうかというところの検証をやってみたいと思います。

注意点

現状sqlcはZetaSQLは非対応ですが、それをMySQLに変換して（若干強引に）使ってみようという実験的な試みについての記事になります。
よって、productionレベルでの使用は想定していません。

sqlc？

https://github.com/kyleconroy/sqlc

SQLから型安全なコード生成をしてくれるツール＆ライブラリです。

  1. コード上で実行したいSQLクエリを考える
  2. DBMSとやりとりするためのデータ構造を作成する（modelとか）
  3. ドライバを介してDBMSにクエリ実行を行う処理を作成する（repositoryとか）

このうち、２と３を自動生成してくれます（３についてはトランザクションをどのレイヤーで管理するかにもよるところはありますが）

詳細はこのあたりの記事が詳しそうです
https://debimate.jp/2022/06/25/【golang】sqlcコマンドで「sqlクエリから型安全なgoコード/

やってみる

では実際に使ってみます。

なお、最近Spannerの無料トライアルインスタンスが一般提供開始したので、エミュレーターではなく実インスタンスでやってみます。

インスタンスやDB作成は割愛します。

スキーマ定義

簡単な1:nのテーブルと、トランザクション確認用のCounterを作成します。

CREATE TABLE Users (
  ID STRING(36) NOT NULL,
  Name STRING(255) NOT NULL
) PRIMARY KEY(ID);

CREATE TABLE Addresses (
  ID STRING(36) NOT NULL,
  UserID STRING(36) NOT NULL,
  Address STRING(255) NOT NULL,
  CONSTRAINT FK_UserAddress FOREIGN KEY (UserID) REFERENCES Users (ID)
) PRIMARY KEY(ID);

Create Index AddressesByUserID On Addresses(UserID);

CREATE TABLE Counters (
  ID STRING(36) NOT NULL,
  Count INT64 NOT NULL,
) PRIMARY KEY(ID);

サンプルデータ準備

-- Users
INSERT INTO
  Users (ID, Name)
VALUES
  ('199f8059-558a-4c6f-aad3-526859cfa88e', 'User A'),
  ('7d586bac-1c9e-4c3d-aefe-53c8649352f0', 'User B'),
  ('a94c740a-3791-492d-88be-b05cf0b85252', 'User C');

-- Addresses
INSERT INTO
  Addresses (ID, UserID, Address)
VALUES
  ('3b9a8a97-5975-46f9-81ff-2b4b7b74d996', '199f8059-558a-4c6f-aad3-526859cfa88e', 'Address A'), -- User A
  ('bf640863-0636-4ba4-aa77-b4b20bfff7c0', '7d586bac-1c9e-4c3d-aefe-53c8649352f0', 'Address B'), -- User B
  ('2c002eae-e0fd-40bf-b09f-c8f582302500', '199f8059-558a-4c6f-aad3-526859cfa88e', 'Address C'), -- User A
  ('d3be1e3f-0ad4-4d7e-bf43-fe61e890cd32', 'a94c740a-3791-492d-88be-b05cf0b85252', 'Address D'), -- User C
  ('ab02a7fa-7edc-4b1d-8ef6-ae82b69f9d00', '7d586bac-1c9e-4c3d-aefe-53c8649352f0', 'Address E'), -- User B
  ('0ea07838-34dd-4fa4-af2f-06babaab75a0', '199f8059-558a-4c6f-aad3-526859cfa88e', 'Address F'); -- User A

-- Counters
INSERT INTO
	Counters (ID, Count)
VALUES
	('05a7f30c-823c-4502-a866-6ac783050e4f', 0);

コード生成

ソースコードはこちら

https://github.com/ymtdzzz/sqlc-spanner-sample

sqlcのインストール

ひとまずgo installで入れてみます（他のインストール方法についてはこちら）。

go install github.com/kyleconroy/sqlc/cmd/sqlc@latest

設定ファイル作成

sqlcの設定ファイルを作成します（yaml or json）。

version: 1
packages:
  - path: "db"
    name: "db"
    engine: "mysql"
    schema: "schema.sql"
    queries: "query.sql"

DBエンジン、生成先と生成元のSQLを指定します。ここでは

  • 生成先：db
  • パッケージ名：db
  • スキーマ定義：schema.sql
  • クエリ定義：query.sql

SQL作成

まずはschema.sqlでテーブル定義を記述します。

CREATE TABLE Users (
  ID VARCHAR(36) NOT NULL PRIMARY KEY,
  Name VARCHAR(255) NOT NULL
);

CREATE TABLE Addresses (
  ID VARCHAR(36) NOT NULL PRIMARY KEY,
  UserID VARCHAR(36) NOT NULL,
  Address VARCHAR(255) NOT NULL
);

CREATE TABLE Counters (
  ID VARCHAR(36) NOT NULL PRIMARY KEY,
  Count BIGINT NOT NULL
);

※Spannerの型が使えないのでMySQLの型に合うように変換します

Spannerの構文だけだと生成時にschema.sql:2:12: syntax error near "STRING(36) NOT NULL,"エラーが発生します。それはそうですよね。

続いて、流したいクエリをquery.sqlで記述します。

-- name: GetUser :one
SELECT * FROM Users
WHERE ID = ? LIMIT 1;

-- name: GetUserWithAddresses :many
SELECT * FROM Users u
JOIN Addresses a ON u.ID = a.UserID
WHERE u.ID = ?;

-- name: GetCounter :one
SELECT * FROM Counters
WHERE ID = ? LIMIT 1;

-- name: UpdateCounter :exec
UPDATE Counters SET Count = ?
WHERE ID = ?;

基本的な構文なのでクエリは変換不要ですが、@{FORCE_INDEX=HogeIndex}のようなSpanner固有の構文は使用できません。

nameで指定した名称が、生成されたコードの関数名になります。

コード生成

sqlc generate

生成に成功すると、db配下にいくつかファイルが生成されます（全文はこちら）。

model

type Address struct {
	ID      string
	Userid  string
	Address string
}

type Counter struct {
	ID    string
	Count int64
}

type User struct {
	ID   string
	Name string
}

query

func (q *Queries) GetUser(ctx context.Context, id string) (User, error) {
	row := q.db.QueryRowContext(ctx, getUser, id)
	var i User
	err := row.Scan(&i.ID, &i.Name)
	return i, err
}

func (q *Queries) UpdateCounter(ctx context.Context, arg UpdateCounterParams) error {
	_, err := q.db.ExecContext(ctx, updateCounter, arg.Count, arg.ID)
	return err
}

実装

生成されたコードを使用していくつか処理を行ってみます。

  • Usersテーブルの行を取得（JOIN無し）
  • Usersテーブルの行を取得（JOIN有り）
  • トランザクションを使用してCountersテーブルの行をカウントアップ（10本並列実行）

connection生成等

	sqldb, err := sql.Open("spanner", fmt.Sprintf("projects/%s/instances/%s/databases/%s", project, instance, database))
	if err != nil {
		panic(err)
	}

	queries := db.New(sqldb)

Users情報の取得

	fmt.Println("------- query: Users -------")
	for _, id := range userIDs {
		user, err := queries.GetUser(ctx, id)
		if err != nil {
			panic(err)
		}

		fmt.Printf("%#v\n", user)
	}

	fmt.Println("------- query: UsersWithAddresses -------")
	for _, id := range userIDs {
		useraddrRows, err := queries.GetUserWithAddresses(ctx, id)
		if err != nil {
			panic(err)
		}

		for _, useraddr := range useraddrRows {
			fmt.Printf("%#v\n", useraddr)
		}
	}

Countersの並列カウントアップ

	waitNum := 10
	var wg sync.WaitGroup
	for i := 0; i < waitNum; i++ {
		fmt.Printf("------- query: UpdateCounter (%d) -------\n", i)
		wg.Add(1)
		go func() {
			defer wg.Done()
			for {
				err := incrementCounter(ctx, queries, sqldb)
				if err == spannerdriver.ErrAbortedDueToConcurrentModification {
					continue
				}
				if err != nil {
					panic(err)
				}
				break
			}
		}()
	}

	wg.Wait()

	c, err := queries.GetCounter(ctx, counterID)
	if err != nil {
		panic(err)
	}
	fmt.Printf("counter: %d\n", c.Count)

// ...

func incrementCounter(ctx context.Context, queries *db.Queries, sqldb *sql.DB) error {
	// Read-Write Transaction
	tx, err := sqldb.BeginTx(ctx, &sql.TxOptions{})
	if err != nil {
		return err
	}
	defer tx.Rollback()

	qtx := queries.WithTx(tx)
	c, err := qtx.GetCounter(ctx, counterID)
	if err != nil {
		return err
	}

	if err := qtx.UpdateCounter(ctx, db.UpdateCounterParams{
		ID:    c.ID,
		Count: c.Count + 1,
	}); err != nil {
		return err
	}

	return tx.Commit()
}

connectionさえ準備してあげればあとはqueriesの関数（GetCounter(), GetUserWithAddresses() etc.）を呼び出すだけでDB操作が可能になります。

一点、トランザクション操作は少し特殊で

err := incrementCounter(ctx, queries, sqldb)
if err == spannerdriver.ErrAbortedDueToConcurrentModification {
	continue
}

Spannerのabort対策は必要です。

If the driver detects that the data that was used by the transaction was changed by another transaction between the initial attempt and the retry attempt, the Aborted error will be propagated to the client application as an spannerdriver.ErrAbortedDueToConcurrentModification error.
cf. https://github.com/googleapis/go-sql-spanner#troubleshooting

また、書き込みを伴うDB操作でRead-only Transactionを使用するとspanner: code = "FailedPrecondition", desc = "read-only transactions cannot write"が発生します。

tx, err := sqldb.BeginTx(ctx, &sql.TxOptions{
	ReadOnly: true,
})

動作確認

実行してみます。同時に10本同一行を更新するので何度かAbort-Retryが入って少し時間がかかります（数秒程度）。

$ go run main.go
------- query: Users -------
db.User{ID:"199f8059-558a-4c6f-aad3-526859cfa88e", Name:"User A"}
db.User{ID:"7d586bac-1c9e-4c3d-aefe-53c8649352f0", Name:"User B"}
db.User{ID:"a94c740a-3791-492d-88be-b05cf0b85252", Name:"User C"}
------- query: UsersWithAddresses -------
db.GetUserWithAddressesRow{ID:"199f8059-558a-4c6f-aad3-526859cfa88e", Name:"User A", ID_2:"0ea07838-34dd-4fa4-af2f-06babaab75a0", Userid:"199f8059-558a-4c6f-aad3-526859cfa88e", Address:"Address F"}
db.GetUserWithAddressesRow{ID:"199f8059-558a-4c6f-aad3-526859cfa88e", Name:"User A", ID_2:"2c002eae-e0fd-40bf-b09f-c8f582302500", Userid:"199f8059-558a-4c6f-aad3-526859cfa88e", Address:"Address C"}
db.GetUserWithAddressesRow{ID:"199f8059-558a-4c6f-aad3-526859cfa88e", Name:"User A", ID_2:"3b9a8a97-5975-46f9-81ff-2b4b7b74d996", Userid:"199f8059-558a-4c6f-aad3-526859cfa88e", Address:"Address A"}
db.GetUserWithAddressesRow{ID:"7d586bac-1c9e-4c3d-aefe-53c8649352f0", Name:"User B", ID_2:"ab02a7fa-7edc-4b1d-8ef6-ae82b69f9d00", Userid:"7d586bac-1c9e-4c3d-aefe-53c8649352f0", Address:"Address E"}
db.GetUserWithAddressesRow{ID:"7d586bac-1c9e-4c3d-aefe-53c8649352f0", Name:"User B", ID_2:"bf640863-0636-4ba4-aa77-b4b20bfff7c0", Userid:"7d586bac-1c9e-4c3d-aefe-53c8649352f0", Address:"Address B"}
db.GetUserWithAddressesRow{ID:"a94c740a-3791-492d-88be-b05cf0b85252", Name:"User C", ID_2:"d3be1e3f-0ad4-4d7e-bf43-fe61e890cd32", Userid:"a94c740a-3791-492d-88be-b05cf0b85252", Address:"Address D"}
------- query: UpdateCounter (0) -------
------- query: UpdateCounter (1) -------
------- query: UpdateCounter (2) -------
------- query: UpdateCounter (3) -------
------- query: UpdateCounter (4) -------
------- query: UpdateCounter (5) -------
------- query: UpdateCounter (6) -------
------- query: UpdateCounter (7) -------
------- query: UpdateCounter (8) -------
------- query: UpdateCounter (9) -------
counter: 10

JOINしたデータもGetUserWithAddressesRowとして、SELECTで取得する行に合わせてmodelが自動的に定義されていることがわかります。便利。

おわりに

いかがでしたでしょうか。Spannerでも一応sqlcで良い感じにDB操作ができることがわかりました。ただし、冒頭でも記載した通り若干強引にZetaSQL→MySQLに変換した形なので商用で利用するのは微妙な感じです。

でも、一応下記の通りsqlcでZetaSQLに対応するissueが上がっているので、Spannerでもきちんとした形で利用できるようになる未来は近いかもしれません！

https://github.com/kyleconroy/sqlc/issues/1505

Discussion