今回はPostgreSQLに対し、本番環境を想定してPrismaを使ったデータマイグレーションとDBマイグレーションを実施するための流れについて備忘録を残す。

前提条件

今回の話は以下のような場面を想定している。

ある程度大規模なサービスを想定
非同期でバッチ処理されていない
リリース時のサービス停止はOK
DBマイグレーションの対象となるテーブルは外部キー依存がないので DROP TABLE が可能

言葉の定義もしておく（ChatGPTを使いました）

DBマイグレーション

DBマイグレーションとは、データベースのスキーマ（構造）を変更するプロセスのこと。例えば、新しいテーブルを追加したり、既存のテーブルにカラムを追加・削除したりする作業を指す。これにより、アプリケーションの新しい機能や要件に対応できる。

データマイグレーション

本記事でのデータマイグレーションとは、データベース内の既存データを変更するプロセスのこと。主にDBのカラム定義の変更やカラム名の変更に伴う既存データの更新が含まれる。例えば、カラムのデータ型を変更したり、カラム名を変更することで、既存データを新しい定義に適合させる。これにより、データの整合性を保ちながらアプリケーションの新しい要件や改善に対応することができる。

余談

「普通にマイグレーションファイルとデータマイグレーションのタスク処理を書いて実行すればいいんじゃないですか？」 と思うエンジニアもいると思うので、なぜそれではダメなのかを軽く説明する。

例えばデータ数が1000万を超えるような大規模システムであり、かつ秒間のトランザクションが100万リクエストとかいう大規模なサービスの場合、DBのカラム変更やデータの持たせ方の変更は致命的なデータ差分を生み出してしまう可能性が非常に高い。
加えて、大規模なデータ処理はバックグラウンド処理（非同期処理）としてHTTP/HTTPSでの処理ではなくバッチ処理として実行されている場合が多い。そして、非同期で実行されている状態でデータマイグレーションが実行されると、特定のタイミングでDBアクセスが発生したとき、データが変わっている状態と変わっていない状態で処理結果も違ってくるということが発生する。無停止が原則のサービスの場合、より難易度が上がる。

と、このように単純なDBのカラム定義の変更であっても実際は結構注意が必要なのである。

今回の例

今回の例では以下のような作業を行う。

ユーザーの支払いステータスを user_statuses テーブルで管理
ステータス状況カラムをString型として定義していたが、Int型に変更

実際の作業の流れを示す。

Prismaスキーマの修正（新規カラムを追加）
prisma migration dev でマイグレーション用のSQLファイルを生成
生成されたSQLを修正
prisma migration deploy で本番DBに適用
データマイグレーションを実施
不要となった既存カラムを削除
本番デプロイ（改修したプログラムを本番サービスに反映）

なお、今回はDBマイグレーションとサービスデプロイを同時に行う必要がない。
内容はカラムの定義変更だが、直接カラムの定義を変更するのではなく新規カラムを追加し、それをあとから既存カラムと同じ名前にリネームして対応するという流れなので、既存の状態でもカラムの参照エラーが起きないためである。

まずテーブルの状況はこんな感じ。

スキーマ定義は以下。

prisma.schema

model users {
  id         String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  created_at DateTime @default(now()) @db.Timestamptz(6)
  updated_at DateTime @updatedAt @db.Timestamptz(6)
}

model user_statuses {
  id                String     @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  created_at        DateTime   @default(now()) @db.Timestamptz(6)
  updated_at        DateTime   @updatedAt @db.Timestamptz(6)
  status            String     
  user_id           String     @unique @db.Uuid
  user              users      @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "users_user_statuses")
}

user_statues のデータは以下のようなものが格納されていると想定。

ID	USER_ID	STATUS
1	101	支払い審査中
2	102	支払済
3	103	NULL
4	104	支払い審査中
5	105	支払済

Prismaスキーマの修正

schema.prisma を以下のように変更。

新しいprisma.schemaの定義

model user_statuses {
  id                String     @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  created_at        DateTime   @default(now()) @db.Timestamptz(6)
  updated_at        DateTime   @updatedAt @db.Timestamptz(6)
- status            String        
+ status            Int        @default(0)
  user_id           String     @unique @db.Uuid
  user              users      @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "users_user_statuses")
}

マイグレーションファイルの生成

スキーマ定義を変更したあと db migrate dev --create-only すると以下のようなSQLが生成される。なお、生成したマイグレーションファイルを適用したくないので --create-only オプションを付けている。

migrate.sql

/*
  Warnings:

  - You are about to drop the column `status_int` on the `user_statuses` table. All the data in the column will be lost.
  - The `status` column on the `user_statuses` table would be dropped and recreated. This will lead to data loss if there is data in the column.
  - A unique constraint covering the columns `[user_id]` on the table `startup_funding_statuses` will be added. If there are existing duplicate values, this will fail.

*/
-- AlterTable
ALTER TABLE "user_statuses" DROP COLUMN "user_status_int",
DROP COLUMN "status",
ADD COLUMN  "status" INTEGER NOT NULL DEFAULT 0;

-- CreateIndex
CREATE UNIQUE INDEX "user_statuses_user_id_key" ON "user_statuses"("user_id");

普通のマイグレーションだとこれでも問題ないのだが、2つほど問題が出てくる。

既存のカラムをDROPすると現在実行中の処理がエラーになる可能性がある
データマイグレーションしたいのに元のカラムが消えてしまうためマイグレーションができなくなる
PostgreSQLだと ADD COLUMN はカラムの最後に追加されるので、テーブルのカラムの並び順がちょっと気持ち悪くなる（気にならない人は無視してOK)

小規模かつサービス停止可能なリリース作業においても（２）は大きな問題になるため、このDBマイグレーションをウキウキしながら本番適用すると絶望の淵に立たされることとなる。
そこで、データマイグレーションができるようにSQLを直接編集する。

修正後のマイグレーションファイル

修正後のマイグレーションファイル

BEGIN;

-- AlterTable
CREATE TABLE "user_statuses_temp" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL,
    "status" TEXT,
    "status_int" INTEGER NOT NULL DEFAULT 0,
    "user_id" UUID NOT NULL,

    CONSTRAINT "temp_pkey" PRIMARY KEY ("id")
);

-- CopyData
INSERT INTO "user_statuses_temp" (id, created_at, updated_at, status, user_id)
SELECT id, created_at, updated_at, status, user_id
FROM "user_statuses";

-- 元のテーブルの削除
DROP TABLE "user_statuses";

-- 一時テーブルをリネーム
ALTER TABLE "user_statuses_temp" RENAME TO "user_statuses";

-- 主キー制約の名前を元の名前に変更
ALTER TABLE "user_statuses" RENAME CONSTRAINT "temp_pkey" TO "user_statuses_pkey";

-- AddForeignKey
ALTER TABLE "user_statuses" ADD CONSTRAINT "user_user_statuses" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

COMMIT;

ファイル内にコメントを記述しているとおり、PostgreSQLではMySQLのように ALTER TABLE テーブル名 ADD 新規カラム名型 AFTER 既存カラム名; のように、特定のカラムの後や前(FIRST)に追加したい新規カラムを書くことができない。そのため、いちいち一時テーブルを作るor既存テーブルをリネームするなどが必要がある。

また、PostgreSQLでは明示的にテーブルロックをしてくれないようなので、 BEGIN / COMMIT で囲む必要がある。これがないと例えば DROP TABLE 中にデータアクセスしてきた処理がエラーやタイムアウトになる可能性がある。
加えて、データマイグレーションを実施するために既存のカラム status は残しつつ status_int として新しい定義用のカラムを追加する。

ロックの件については以下を参考にしていただきたい:

最後に変更したスキーママイグレーションファイルを、本番DBに対して prisma migrate deploy する。
これでDBマイグレーションは一旦完了。

データマイグレーションを実行する

テーブルの定義変更が終わったら、データを移行するためのスクリプトを書く。

// このスクリプトは、startup_longlistsテーブルのtransaction_statusカラムの型を変更する
// 一度だけ実行するので、スクリプトは削除しても問題ない
import {Statuses} from '../../src/data/status';
import {prisma} from '../client';

// statusをstring→intに変更するためのマッチング用関数
const matchFundingStatus = (status: string) => {
  switch (status) {
    case '':
      return status.unknown;
    case '支払い審査中':
      return status.judging;
    case '支払済':
      return status.paid;
    default:
      return status.unknown; // とりあえず
  }
};

async function migrate() {
  // データが存在しないスタートアップに対しては、データを作成する
  const users = await prisma.users.findMany({
    select: {
      id: true,
    },
  });
  users.forEach(async (user) => {
    // 今回は1件のデータしか存在しないことを想定しているのでfindFirstを使用
    // また、prismaをanyとして定義し、型チェックを無効化（funding_statusの型をstring→intに変更するため）
    const s = await (prisma as any).user_statuses.findFirst({
      where: {
        user_id: user.id,
      },
    });
    if(s){
      await prisma.user_statuses.update({
        where: {
          id: s.id,
        },
        data: {
          status_int: matchFundingStatus(s.status || ''), // NULLの場合は空文字
        },
      });
    }else{
      // データが存在しない場合の処理が必要なときはここに書く
    }
  });
}

migrate()
  .then(() => {
    console.log('Migration completed');
  })
  .catch((error) => {
    console.error('Migration failed', error);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

今回は特別大規模なサービスを想定していないので、デッドロックの発生は考慮していない。実際は稀に発生するので、データマイグレーション側はデッドロックを検知できるような仕組みを実装しておくか、インフラ側で検知できるようにしておくことを推奨する。

いらないカラム定義を削除する

データマイグレーションが終わったので、不要になった旧カラムを削除するためのDBマイグレーション用のファイルを手動で作成する。（ディレクトリ、ファイル名は前回作成したカラム追加用のマイグレーションファイルより後の日時となるように作成する）

-- ステップ1: 元の status カラムを削除
ALTER TABLE "user_statuses"
DROP COLUMN "status";

-- ステップ2: status_int カラムを status にリネーム
ALTER TABLE "user_statuses"
RENAME COLUMN "status_int" TO "status";

-- CreateIndex
CREATE UNIQUE INDEX "user_statuses_user_id_key" ON "user_statuses"("user_id");

作成後に prisma migrate deploy して終了。
これで prisma.schema との差分がなくなるはずなので prisma migrate status または prisma migrate diff で問題がないか確認すると良い。

本番デプロイする

デプロイ処理は省略する。
今回は非同期処理がないので、デプロイ時に考慮すべき事は特にないが、実際のサービスではリリースの前後でバッチ処理が過去のDBのテーブル定義を参照することもあるので、その際はエラーとなるだろう。
この場合、エラーとなった処理に対してリトライするような実装をしておくとか、失敗したジョブをあとから再実行できるよう失敗したデータ処理のIDを記録しておくとか工夫されていることが多い。