1️⃣
Nest.js Prisma SQLiteでのID発行

2025/02/23に公開
Prisma
 概要Nest.js アプリケーションで連番のIDをDBで発行する Prisma * SQLite での実装例
1つの連番管理用テーブルで複数IDの管理する仕組み
複数種類のIDを管理したい・IDの種類が増えるたびにテーブルが増えるのを避けたいため、SQLiteの機能(INTEGER PRIMARY KEY)は使用しない
アプリケーション側で、各シーケンスのインクリメント処理をトランザクション内で実施


 検討事項DB機能の利用
PostgreSQL, MySQLにあるDBの自動採番機能の利用は可能か
SQLiteではPKに対して自動採番が可能
今回はPKではなく、一つのテーブルで複数シーケンスを管理したいため

トランザクション管理と排他制御
並列してID発行処理が呼ばれた際に、IDの重複を防ぐ

ロールバックの考慮
トランザクションの途中で失敗した場合にインクリメントされた値をロールバックするか、連番にギャップが生じるのを許すか
今回はギャップが問題にならない使用なので考慮していない

負荷とパフォーマンス
同時アクセスが非常に高い環境では、トランザクションの競合やDBロックがボトルネックとなる可能性がある
今回は参考までにどのような手法があるかの記載にとどめる


 実装例
 連番管理用テーブルschema.prisma
model Sequence {
  idType  String  @id @map("id_type")
  sequenceValue Int @map("sequence_value")
  numberingDate DateTime  @default(now()) @map("numbering_date")

  @@map("sequences")
}
idType
IDの種別

userIdなどID名をそのまま使用してもよいし、ID名の変更も想定する場合は、ID名と対応する値で管理してもよい（01: userId の対応を別途マスタ管理するなど）
sequenceValue
各シーケンスに対して発行された最新のID番号
新しいIDの採番時、アプリケーション側でこの値をインクリメントし、その値を返す
numberingDate
シーケンスの更新時刻

 採番処理Prisma の upsert とトランザクションを利用して、SQLite 上で複数ID: ユーザーID（"user"）と画像ID（"post"）の採番処理を実装する一例
呼び出し側実装例(sequence.service.ts 内実装例)
  const USER_ID_TYPE = 'user'; // 別途定義

  /**
   * ユーザーIDの採番処理
   */
  async getNextUserId(): Promise<string> {
    const newUserSequence =
      await this.sequenceRepository.getNextSequence(USER_ID_TYPE);

    // ID採番の業務ルールの実装
    // sample: 'user-0000000001'
    const newArticleId = `user-${newArticleSequence.toString().padStart(10, '0')}`;

    return newUserId;
  }
sequence.repository.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';

export type SequenceIdType = 'user' | 'post';

@Injectable()
export class SequenceRepository {
  constructor(private readonly prisma: PrismaService) {}

  async getNextSequence(idType: SequenceIdType): Promise<number> {
    const updatedSequence = await this.prisma.$transaction(async (tx) => {
      return await tx.sequence.upsert({
        where: { idType },
        update: { sequenceValue: { increment: 1 } },
        create: { idType, sequenceValue: 1 }
      });
    });
    return updatedSequence.sequenceValue;
  }
}

upsert の利用
idTypeがまだ作成されていないの場合は create され、存在する場合は update で sequenceValue をインクリメントする。
これにより、初回の採番とその後の更新処理を一貫して扱う。
トランザクションによる排他制御
複数のリクエストが同時に呼ばれても、$transaction 内で処理が順次実行されるため、採番値が重複するリスクを低減できる。
SQLite は書き込み時にテーブルロックを行うため、同時更新に対して十分な安全性が確保されます。
注意
「トランザクション開始時にはロックを取得せず, データの読み込み/書き込みをする時点までロック取得を延期する. そのため, BEGINステートメントによるトランザクション開始のみでは何のロックも取得されない. ロックの取得がBEGIN～データの読み込み/書き込みまで延期されるため, 別トランザクションによるデータ書き込みの割り込みが発生する可能性がある.」参考: 同時実行制御とSQLite3
結論
更新処理の１操作内で最新の値に基づいた評価を使用すれば問題ない。  UPDATE Sequence
  SET sequenceValue = sequenceValue + 1
  WHERE idType = 'user'
  RETURNING sequenceValue;
参考: NestJS で Prisma の発行するクエリを出力する
SELECT と UPDATE を分離しアプリケーション側で「読み取った値を元に UPDATE」する設計になっていると古い他のプロセスで更新される前の古い値を使って更新してしまう可能性がある



 その他参考
 負荷とパフォーマンス
 キャッシュ（プリフェッチ）方式概要
プリフェッチ方式では、IDを一度に一定のブロック（例: 100個、1000個）まとめてデータベースから取得し、アプリケーションのメモリ上にキャッシュする。
アプリケーション側は、個々のリクエストに対してこのキャッシュ内から順次IDを割り当てる。
キャッシュが使い果たされたら、再度新たなブロックをデータベースから取得する。

効果・メリット
DBアクセスの削減: 毎回データベースに問い合わせる必要がなくなるため、同時アクセスが高い環境でもDBの負荷が大幅に軽減される。
高速なレスポンス: キャッシュから直接IDを供給できるため、ID発行のレイテンシが低くなる。

考慮点
ギャップの発生: システム障害やクラッシュにより、キャッシュ内の未使用IDが失われる可能性があり、結果として連続性にギャップが生じる可能性がある。
ただし、ほとんどの場合、ユニークであれば良いという要件であれば問題にならないことが多い。

一貫性の管理: キャッシュ更新とデータベースの状態の同期が必要となるため、キャッシュの有効期限や再取得のタイミングを適切に管理する必要がある。


 キューイング方式概要
キューイング方式では、ID発行のリクエストをキューに登録し、専用のワーカー（またはシングルスレッドのプロセス）がキューから順番にリクエストを処理する。
このワーカーは1件ずつ順次、データベースへアクセスして次のIDを発行し、処理結果を返す。

効果・メリット
シリアライズされた処理: 複数のリクエストが同時に来た場合でも、ワーカーが順番に処理するため、データベースの同一レコードへの同時アクセスが防止され、IDの重複リスクが大幅に低減される。
一貫性の担保: リクエストが順番に処理されるため、IDの発行順序が保証されやすくなる。

考慮点
ボトルネックの可能性: キューにリクエストが集中すると、処理待ちのリクエストが増加し、レスポンスが遅延するリスクがある。負荷の高い環境では、キューのスケーリングや複数ワーカーの導入など、ボトルネック対策が必要になる場合がある。
システム設計の複雑性: キューシステムを導入することで、システム全体のアーキテクチャが複雑になるため、その管理や監視が必要になる。
概要

検討事項

実装例

連番管理用テーブル

採番処理

その他参考

負荷とパフォーマンス

キャッシュ（プリフェッチ）方式

キューイング方式

Discussion
