家計簿アプリを作る #89：外部キー制約とカスケード削除

家計簿アプリ作成シリーズの第89回です。お題85のリストア、お題87のアカウント削除と、「後から追加したテーブルが削除処理から漏れる」問題が続けて見つかりました。今回は、各テーブルのuserIdにuser.idへの外部キー制約とON DELETE CASCADEを付け、ユーザーを削除すれば関連データもDBの仕組みで一緒に消えるようにしました。

なぜ外部キー制約なのか

これまでのアカウント削除は、テーブルごとに削除文を書いていました。

await db.batch([
  db.delete(transactions).where(eq(transactions.userId, userId)),
  db.delete(categories).where(eq(categories.userId, userId)),
  // ...テーブルを追加するたびにここへ書き足す
]);
await auth.api.deleteUser({ body: { password }, headers: request.headers });

この形だと、テーブルを追加するたびに削除処理へ書き足す必要があり、忘れるとデータが残ります。実際にtransactionTemplatesとbudgetAlertsが漏れていました。

外部キー制約にON DELETE CASCADEを付けておけば、userの行を削除した時点で、それを参照している行もDBが自動的に削除します。削除処理を書き足す必要自体が無くなります。

実は、Better Authが管理するsessionとaccountテーブルには、最初からこの設定が付いていました。

// auth-schema.ts
userId: text("user_id")
  .notNull()
  .references(() => user.id, { onDelete: "cascade" }),

アプリ側のテーブルだけが、userIdをただのtextカラムとして持っていた状態です。

事前確認:孤児データが無いか

外部キー制約は「存在しないユーザーを指すデータ」を許しません。そうしたデータ(孤児データ)が残っていると、制約を付けたテーブルへデータをコピーする段階で失敗する可能性があります。適用前に、各テーブルについて確認しました。

npx wrangler d1 execute kakeibo-app-db --local --command "SELECT COUNT(*) FROM transaction_templates WHERE user_id NOT IN (SELECT id FROM user)"

ローカル・本番とも、全テーブルで0件でした。

スキーマの変更

7テーブルのuserIdに、auth-schema.tsのuserを参照する制約を付けました。

import { user } from './auth-schema';

export const transactions = sqliteTable('transactions', {
  // ...
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  // ...
});

生成されたSQL:テーブルの作り直し

npm run db:generateで生成されたマイグレーションは、テーブルごとに次の4ステップになっていました。

CREATE TABLE `__new_transactions` (
  -- ...
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade
);
INSERT INTO `__new_transactions`(...) SELECT ... FROM `transactions`;
DROP TABLE `transactions`;
ALTER TABLE `__new_transactions` RENAME TO `transactions`;

SQLiteは、既存のテーブルに後から外部キー制約を追加できません。そのため、制約付きの新しいテーブルを作り、データをコピーし、古いテーブルを消して名前を付け替える、という作り直しになります。

D1ではPRAGMA foreign_keysが効かない

生成されたSQLには、作り直しの前後に次の2行がありました。

PRAGMA foreign_keys=OFF;
-- ...テーブルの作り直し...
PRAGMA foreign_keys=ON;

作り直しの途中で外部キーの検証に引っかからないよう、一時的に検証を切るための行です。ところがD1のドキュメントを確認すると、D1はすべてのクエリを暗黙のトランザクション内で実行するため、クエリの中からこの設定を変更できません。drizzle-kitは一般的なSQLite向けにSQLを生成するので、D1ではこの2行が実質的に何もしない行になります。

さらによく見ると、このOFF〜ONのペアは最初の1テーブルの作り直しだけを囲んでいて、残り6テーブルはペアの外側にありました。

D1で外部キーの検証を一時的に緩めたいときは、代わりにPRAGMA defer_foreign_keys = onを使います。これはトランザクションが終わるまで検証を先送りするもので、トランザクションの終了時点で違反が残っていればFOREIGN KEY constraint failedで失敗します。offに戻す処理はトランザクションの終了時に暗黙的に行われます。

生成されたSQLを次のように書き換えました。

• 先頭のPRAGMA foreign_keys=OFF;をPRAGMA defer_foreign_keys = on;に置き換える
• 途中のPRAGMA foreign_keys=ON;は削除する

今回の移行は、孤児データが無く、削除する7テーブルはどれも他から参照されていないので、実際には検証を先送りする必要はありません。それでも、効かない行を残しておくと、後から読んだときに「外部キーを切っているから安全だ」と誤解してしまうので直しました。

この仕組みは、マイグレーションのSQL全体が1つのトランザクションとして実行されることが前提です。wrangler d1 migrations applyはファイル内の文をまとめて実行するので、この前提を満たします。

ローカルで成功しても本番の保証にはならない

ローカル(Miniflare)は通常のSQLiteなので、PRAGMA foreign_keys=OFFがそのまま効く可能性があります。ローカルで適用が通ったことは、本番で通る根拠になりません。D1固有の挙動が絡む変更では、特に意識しておく必要があります。

カスケード削除の確かめ方

ローカルに適用した後、カスケードが効いているかを確認しました。ここで気を付けたのは、この時点ではアカウント削除にまだ手動の削除処理が残っていたことです。画面からアカウントを削除しても、手動削除の方でデータが消えるので、カスケードが効いていなくても成功してしまいます。

そこで、アプリを通さずにuserの行を直接削除しました。

npx wrangler d1 execute kakeibo-app-db --local --command "DELETE FROM user WHERE id = 'テスト用ユーザーのID'"
npx wrangler d1 execute kakeibo-app-db --local --command "SELECT COUNT(*) FROM transactions WHERE user_id = 'テスト用ユーザーのID'"

件数が0になり、カスケードが機能していることを確認できました。E2Eテストも通りました。

手動削除を消す

カスケードで同じことが起きるので、deleteAccountの手動削除は削除しました。

// パスワードの認証
// ...

// ユーザの削除(関連データはカスケードで削除される)
await auth.api.deleteUser({ body: { password }, headers: request.headers });

// ログインページにリダイレクト
cookies.delete('better-auth.session_token', { path: '/' });
redirect(303, '/login');

お題87では、「データ削除のバッチ」と「ユーザー削除」が別々の処理だったので、片方だけ成功する可能性を考えて削除の順番を工夫していました。カスケードではuserの削除と関連データの削除が1つのDELETE文の中で行われるので、中途半端な状態が原理的に起きなくなります。

better-auth.session_tokenのCookie削除は残しています。DB側のセッションはsessionテーブルのカスケードで消えるので、Cookieが残っていても認証は通りませんが、無効な値をブラウザに残しておく理由はありません。

残っている課題:R2のレシート画像

カスケードで消えるのはD1のレコードだけです。お題72でR2に保存したレシート画像は、アカウントを削除しても残ります。これは手動削除だった頃から同じで、別の機会に対応する予定です。

まとめ