Convert a SQL User Authentication Schema to Prisma

Authentication Schema Conversion

A real-world user authentication system involves multiple related tables: users, sessions, password reset tokens, and email verification records. Converting this entire schema to Prisma demonstrates how relations, enums, indexes, and defaults work together.

Example 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);

Generated Prisma Schema

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")
}

Schema Design Highlights

UUID user IDs — The User model uses UUID primary keys for security (non-guessable) and distributed generation.

Enum for roles — PostgreSQL's CREATE TYPE maps directly to a Prisma enum, providing type safety in your application code.

Session management — Each session links to a user with ON DELETE CASCADE, so deleting a user cleans up all sessions automatically.

Token security — Password reset tokens have @unique to prevent reuse and expires_at for time-limited validity.

Index strategy — Indexes on user_id foreign keys enable fast session lookups per user. The expires_at index supports efficient cleanup of expired sessions.

Security Considerations

• Store password_hash using bcrypt or argon2, never plaintext
• Session tokens should be cryptographically random (e.g., crypto.randomBytes(32))
• Consider adding a failed_login_attempts counter and lockout mechanism
• Use @db.VarChar(45) for ip_address to support both IPv4 and IPv6