SQLのユーザー認証スキーマをPrismaに変換する

ユーザー、セッション、パスワードリセット、メール認証を含む完全なユーザー認証SQLスキーマを、完全に型付けされたPrismaスキーマに変換する方法を学びます。

Real-World Schemas

詳細な説明

認証スキーマの変換

実際のユーザー認証システムには、ユーザー、セッション、パスワードリセットトークン、メール認証レコードなど、複数の関連テーブルが含まれます。このスキーマ全体をPrismaに変換することで、リレーション、enum、インデックス、デフォルトがどのように連携するかを示します。

SQLの例

CREATE TYPE user_role AS ENUM ('user', 'admin', 'moderator');

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(320) NOT NULL UNIQUE,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(255),
  role user_role NOT NULL DEFAULT 'user',
  is_verified BOOLEAN NOT NULL DEFAULT false,
  last_login_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  token VARCHAR(500) NOT NULL UNIQUE,
  ip_address VARCHAR(45),
  user_agent TEXT,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE password_resets (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL,
  token VARCHAR(255) NOT NULL UNIQUE,
  expires_at TIMESTAMPTZ NOT NULL,
  used_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_sessions_user_id ON sessions (user_id);
CREATE INDEX idx_sessions_expires_at ON sessions (expires_at);
CREATE INDEX idx_password_resets_user_id ON password_resets (user_id);

生成されるPrismaスキーマ

enum UserRole {
  user
  admin
  moderator
}

model User {
  id             String          @id @default(uuid()) @db.Uuid
  email          String          @unique @db.VarChar(320)
  username       String          @unique @db.VarChar(50)
  passwordHash   String          @map("password_hash")
  displayName    String?         @map("display_name")
  role           UserRole        @default(user)
  isVerified     Boolean         @default(false) @map("is_verified")
  lastLoginAt    DateTime?       @map("last_login_at") @db.Timestamptz()
  createdAt      DateTime        @default(now()) @map("created_at") @db.Timestamptz()
  updatedAt      DateTime        @updatedAt @map("updated_at") @db.Timestamptz()
  sessions       Session[]
  passwordResets PasswordReset[]

  @@map("users")
}

model Session {
  id        String   @id @default(uuid()) @db.Uuid
  userId    String   @map("user_id") @db.Uuid
  token     String   @unique @db.VarChar(500)
  ipAddress String?  @map("ip_address") @db.VarChar(45)
  userAgent String?  @map("user_agent") @db.Text
  expiresAt DateTime @map("expires_at") @db.Timestamptz()
  createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz()
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId], map: "idx_sessions_user_id")
  @@index([expiresAt], map: "idx_sessions_expires_at")
  @@map("sessions")
}

model PasswordReset {
  id        Int       @id @default(autoincrement())
  userId    String    @map("user_id") @db.Uuid
  token     String    @unique
  expiresAt DateTime  @map("expires_at") @db.Timestamptz()
  usedAt    DateTime? @map("used_at") @db.Timestamptz()
  createdAt DateTime  @default(now()) @map("created_at") @db.Timestamptz()
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId], map: "idx_password_resets_user_id")
  @@map("password_resets")
}

スキーマ設計のポイント

UUIDユーザーIDUser modelはセキュリティ(推測不可能)と分散生成のためにUUID主キーを使用しています。

ロール用のenum — PostgreSQLのCREATE TYPEはPrismaのenumに直接マッピングされ、アプリケーションコードでの型安全性を提供します。

セッション管理 — 各セッションはON DELETE CASCADEでユーザーにリンクされているため、ユーザーを削除すると自動的にすべてのセッションがクリーンアップされます。

トークンセキュリティ — パスワードリセットトークンには再使用防止のための@uniqueと、時間制限の有効性のためのexpires_atがあります。

インデックス戦略user_id外部キーのインデックスによりユーザーごとの高速なセッション検索が可能になります。expires_atインデックスは期限切れセッションの効率的なクリーンアップをサポートします。

セキュリティに関する考慮事項

  • password_hashはbcryptまたはargon2を使用して保存し、平文は絶対に使用しない
  • セッショントークンは暗号学的にランダムにする(例:crypto.randomBytes(32)
  • failed_login_attemptsカウンターとロックアウトメカニズムの追加を検討する
  • ip_addressには@db.VarChar(45)を使用してIPv4とIPv6の両方をサポート

ユースケース

ユーザー、セッション、パスワードリセットの既存SQLスキーマを持つ完全な認証システムを備えたWebアプリケーションを構築している場合に、コンバーターがすべてのリレーション、enum、インデックス、カスケードルールを持つ完全に型付けされたPrismaスキーマを生成します。

試してみる — SQL to Prisma Schema

フルツールを開く