ハコヅミを作る #5：コンテナと外部キー、D1でテーブルを作り直すマイグレーション

#1で書いたとおり、ハコヅミの一番の売りは「コンテナを積めば、中のギアもまとめてチェックされる」ことです。 その前提になる、コンテナの機能を作りました。

• コンテナの登録・編集・削除
• ギアがどのコンテナに入っているかの紐づけ

Issueは2つに分けました。今回は、2つ目の紐づけで出てきた、D1でのマイグレーションの話が中心です。

コンテナの登録・編集・削除

こちらは、ギアの画面と同じ形で作りました。1つだけ決めたのは、同じ名前のコンテナは登録できないことです。チェックリストで同じ名前のコンテナが2つ並ぶと、見分けがつかなくなるためです。

画面は、この記事の時点のコードを手元で動かして撮ったものです。データは#7でNotionから取り込んだ、実際のギアを入れています。

画面での確認（「同じ名前のコンテナがあります」と表示する）に加えて、DBにも一意インデックスを付けて守っています。

export const containers = sqliteTable(
	'containers',
	{
		id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
		userId: text('user_id').notNull(),
		name: text('name').notNull()
		// ...
	},
	(table) => [uniqueIndex('containers_user_id_name_unique').on(table.userId, table.name)]
);

ギアとコンテナを紐づける

ギアのテーブルには、最初から container_id の列を用意していました（#1の設計どおり）。今回、ここに containers.id への外部キーを付けます。

コンテナを削除したときの扱いは、Issueの段階で決めておきました。

• 中のギアは「コンテナなし」に戻す（ギアは消さない）
• 削除の確認ダイアログで、「中のギア N 件は『コンテナなし』になります」と知らせる

Drizzleでは、次のように書きます。

containerId: text('container_id').references(() => containers.id, { onDelete: 'set null' }),

SQLiteは、あとから外部キーを足せない

ここで1つ問題があります。SQLiteは、既存の列にあとから外部キーを足すことができません。 そのため、drizzle-kit generate は、次のような「テーブルを作り直す」マイグレーションを出します。

PRAGMA foreign_keys=OFF;
CREATE TABLE `__new_gears` ( ... FOREIGN KEY (`container_id`) REFERENCES `containers`(`id`) ON DELETE set null );
INSERT INTO `__new_gears`(...) SELECT ... FROM `gears`;
DROP TABLE `gears`;
ALTER TABLE `__new_gears` RENAME TO `gears`;
PRAGMA foreign_keys=ON;
CREATE INDEX `gears_user_id_idx` ON `gears` (`user_id`);

新しいテーブルを作り、データを移し、古いテーブルを消して、名前を付け替える流れです。 既存のギアのデータが、本当に移るかが一番の心配どころです。開発用のD1には受入確認で登録したギアが、本番にも実際のギアが入っています。

D1では PRAGMA foreign_keys=OFF が使えない

もう1つ気になったのが、先頭と末尾の PRAGMA foreign_keys=OFF / ON です。作り直しの途中で外部キーの確認を止めるためのものです。

ところが、Cloudflare D1は外部キーの確認を切ることができません。代わりに、確認をトランザクションの終わりまで遅らせる PRAGMA defer_foreign_keys を使うよう案内されています。

そこで、生成されたマイグレーションのこの2行を書き換えました。

-- D1 は外部キーの確認を切れないので、drizzle-kit が出す PRAGMA foreign_keys=OFF/ON の代わりに
-- 確認をトランザクションの終わりまで遅らせる（Cloudflare D1 の推奨）
PRAGMA defer_foreign_keys = on;
-- （作り直しの SQL はそのまま）
PRAGMA defer_foreign_keys = off;

生成されたSQLを手で直すのは、今回が初めてです。今後も同じことが起きるので、リポジトリの CLAUDE.md と /implement のスキルに、ルールとして書いておきました。

データが消えないことを、3か所で確かめた

1. ローカル

まずローカルのD1で試しました。E2Eテストのデータが37件入っていたので、ちょうどよい試し台です。

# 適用前
npx wrangler d1 execute DB --local --command "SELECT count(*) FROM gears"   # → 37
npm run db:migrate:local
# 適用後
npx wrangler d1 execute DB --local --command "SELECT count(*) FROM gears"   # → 37

37件すべて移り、外部キーも付いていました。

2. 開発環境

PRをマージする前に、開発用のD1のギアの件数（5件）を控えておきました。 develop にマージしてデプロイが終わったあと、次を確かめています。

• デプロイのログで、このマイグレーションだけが適用されたこと（8つのSQLを実行）
• ギアが5件のままであること
• 外部キーが付いていること（pragma_foreign_key_list('gears') で、containers への SET NULL の外部キーが見える）
• 作り直しの途中のテーブル（__new_gears）が残っていないこと

3. 本番環境

本番は手元から触らない方針なので、リリースの前に、ダッシュボードのコンソールで本番のギアの件数を控えました。 リリースのあとに同じSQLを実行して、件数が変わっていないこと、外部キーが付いていることを確かめています。書き換えた PRAGMA defer_foreign_keys も、本番のD1で問題なく通りました。

削除は、外部キーに任せきりにしない

コンテナを削除したときに中のギアを「コンテナなし」に戻すのは、外部キーの on delete set null がやってくれます。 ただ、アプリ側でも、同じバッチの中で明示的に戻してからコンテナを消すようにしました。

const [, deleted] = await db.batch([
	db.update(gears).set({ containerId: null })
		.where(and(eq(gears.userId, userId), eq(gears.containerId, id))),
	db.delete(containers)
		.where(and(eq(containers.userId, userId), eq(containers.id, id)))
		.returning({ id: containers.id })
]);

外部キーの確認が効かない環境（たとえばテストのやり方によっては）でも、同じ結果になるようにするためです。

本番へのリリースは、機能がたまってからまとめて

最初の機能は、受入確認のあとすぐ本番に出しました。 ただ、今は本番を使っているのも自分だけです。機能ごとにリリースのPRを作るのは手間なので、いくつか機能がたまってからまとめて出すことにしました。

今回は、ギアの編集・削除、コンテナ、ギアとコンテナの紐づけを、まとめて1回でリリースしています。 その分、本番のD1には2つのマイグレーションが続けて適用されました。デプロイのログで、2つが順に適用されたことを確かめています。

おわりに

外部キーを1つ足すだけのつもりが、テーブルの作り直しになり、D1向けの書き換えも必要でした。

• SQLiteは、あとから外部キーを足せない（テーブルの作り直しになる）
• D1では PRAGMA foreign_keys=OFF の代わりに PRAGMA defer_foreign_keys を使う
• 作り直しの前後で件数を比べる。ローカル → 開発 → 本番の順に確かめる

実は、この機能を作っている途中で、E2Eテストがたびたび落ちるようになりました。次回は、その原因と直し方です。