Prisma TypeScript: Modern Database ORM Development

Prisma is a next-generation ORM that provides type-safe database access with TypeScript. This comprehensive guide covers schema design, migrations, advanced querying, performance optimization, and production deployment patterns.

Why Choose Prisma with TypeScript?

• Type Safety: Auto-generated TypeScript types from database schema
• Developer Experience: Intuitive API and excellent tooling
• Database Agnostic: Works with PostgreSQL, MySQL, SQLite, MongoDB, and more
• Modern Features: Connection pooling, middleware, and real-time subscriptions
• Production Ready: Battle-tested with enterprise-grade applications

Step 1: Project Setup and Configuration

Set up a comprehensive Prisma TypeScript development environment:

# Create project directory
mkdir prisma-typescript-app
cd prisma-typescript-app

# Initialize package.json
npm init -y

# Install Prisma dependencies
npm install prisma @prisma/client
npm install @prisma/client-extension

# Install additional dependencies
npm install express cors helmet morgan
npm install bcryptjs jsonwebtoken
npm install zod express-validator
npm install dotenv

# Install TypeScript and development tools
npm install -D typescript @types/node
npm install -D @types/express @types/cors @types/bcryptjs
npm install -D @types/jsonwebtoken
npm install -D nodemon ts-node concurrently
npm install -D jest @types/jest ts-jest supertest @types/supertest
npm install -D eslint @typescript-eslint/eslint-plugin @typescript-eslint/parser
npm install -D prettier husky lint-staged

# Initialize Prisma
npx prisma init

Configure TypeScript for optimal Prisma development:

{
  "compilerOptions": {
    "target": "ES2022",
    "lib": ["ES2022"],
    "module": "CommonJS",
    "moduleResolution": "node",
    "allowSyntheticDefaultImports": true,
    "esModuleInterop": true,
    "allowJs": true,
    "outDir": "./dist",
    "rootDir": "./src",
    "removeComments": true,
    "strict": true,
    "noImplicitAny": true,
    "strictNullChecks": true,
    "strictFunctionTypes": true,
    "noImplicitThis": true,
    "noImplicitReturns": true,
    "noFallthroughCasesInSwitch": true,
    "noUncheckedIndexedAccess": true,
    "exactOptionalPropertyTypes": true,
    "declaration": true,
    "declarationMap": true,
    "sourceMap": true,
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true,
    "resolveJsonModule": true,
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true,
    "baseUrl": ".",
    "paths": {
      "@/*": ["src/*"],
      "@/types/*": ["src/types/*"],
      "@/utils/*": ["src/utils/*"],
      "@/services/*": ["src/services/*"],
      "@/controllers/*": ["src/controllers/*"],
      "@/middleware/*": ["src/middleware/*"],
      "@/prisma/*": ["prisma/*"]
    }
  },
  "include": ["src/**/*", "prisma/**/*"],
  "exclude": ["node_modules", "dist"]
}tsconfig.json

Step 2: Advanced Prisma Schema Design

Create a comprehensive database schema with advanced relationships:

// Prisma schema file with comprehensive model definitions

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch", "fullTextIndex", "tracing", "metrics"]
  binaryTargets   = ["native", "linux-musl"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// User model with comprehensive fields
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  username  String   @unique
  firstName String
  lastName  String
  avatar    String?
  phone     String?

  // Authentication
  passwordHash String
  emailVerified Boolean @default(false)
  emailVerificationToken String?
  passwordResetToken String?
  passwordResetExpiresAt DateTime?

  // Profile information
  dateOfBirth DateTime?
  gender      Gender?
  bio         String?
  website     String?
  location    String?

  // Account settings
  role        UserRole @default(USER)
  status      UserStatus @default(ACTIVE)
  isOnline    Boolean @default(false)
  lastSeenAt  DateTime?

  // Preferences
  theme       Theme @default(SYSTEM)
  language    String @default("en")
  timezone    String @default("UTC")

  // Notification settings
  emailNotifications Boolean @default(true)
  pushNotifications  Boolean @default(true)
  smsNotifications   Boolean @default(false)

  // Security
  twoFactorEnabled Boolean @default(false)
  twoFactorSecret  String?
  loginAttempts    Int @default(0)
  lockedUntil      DateTime?

  // Metadata
  metadata Json?

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?

  // Relationships
  addresses       Address[]
  posts           Post[]
  comments        Comment[]
  likes           Like[]
  follows         Follow[] @relation("UserFollows")
  followers       Follow[] @relation("UserFollowers")
  sentMessages    Message[] @relation("MessageSender")
  receivedMessages Message[] @relation("MessageReceiver")
  orders          Order[]
  reviews         Review[]
  notifications   Notification[]
  sessions        Session[]
  auditLogs       AuditLog[]

  @@map("users")
  @@index([email])
  @@index([username])
  @@index([status])
  @@index([createdAt])
  @@fulltext([firstName, lastName, username])
}

// Address model for user addresses
model Address {
  id      String @id @default(cuid())
  userId  String

  type        AddressType @default(HOME)
  firstName   String
  lastName    String
  company     String?
  street1     String
  street2     String?
  city        String
  state       String
  zipCode     String
  country     String @default("US")
  isDefault   Boolean @default(false)

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relationships
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
  orders Order[]

  @@map("addresses")
  @@index([userId])
  @@index([zipCode])
}

// Product model with comprehensive features
model Product {
  id          String @id @default(cuid())
  name        String
  description String
  slug        String @unique

  // Pricing
  price         Decimal
  originalPrice Decimal?
  currency      String @default("USD")

  // Inventory
  sku           String? @unique
  stock         Int @default(0)
  lowStockAlert Int @default(10)
  trackInventory Boolean @default(true)

  // Product details
  weight      Decimal?
  dimensions  Json? // {length, width, height}
  color       String?
  size        String?
  material    String?

  // Media
  images      String[]
  videos      String[]
  thumbnailUrl String?

  // SEO
  seoTitle       String?
  seoDescription String?
  seoKeywords    String[]

  // Status
  status      ProductStatus @default(DRAFT)
  isDigital   Boolean @default(false)
  isFeatured  Boolean @default(false)

  // Shipping
  requiresShipping Boolean @default(true)
  shippingWeight   Decimal?
  shippingClass    String?

  // Metadata
  attributes Json? // Flexible product attributes
  metadata   Json?

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  publishedAt DateTime?
  deletedAt DateTime?

  // Relationships
  categoryId String
  category   Category @relation(fields: [categoryId], references: [id])
  brandId    String?
  brand      Brand? @relation(fields: [brandId], references: [id])

  tags         Tag[]
  orderItems   OrderItem[]
  cartItems    CartItem[]
  reviews      Review[]
  variants     ProductVariant[]

  @@map("products")
  @@index([slug])
  @@index([categoryId])
  @@index([brandId])
  @@index([status])
  @@index([isFeatured])
  @@index([createdAt])
  @@fulltext([name, description])
}

// Product variant model
model ProductVariant {
  id        String @id @default(cuid())
  productId String

  name      String
  sku       String? @unique
  price     Decimal?
  stock     Int @default(0)

  // Variant attributes
  attributes Json // {color: "red", size: "M"}
  image      String?

  // Status
  isActive Boolean @default(true)

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relationships
  product Product @relation(fields: [productId], references: [id], onDelete: Cascade)
  orderItems OrderItem[]
  cartItems CartItem[]

  @@map("product_variants")
  @@index([productId])
  @@index([sku])
}

// Category model with hierarchical structure
model Category {
  id          String @id @default(cuid())
  name        String
  slug        String @unique
  description String?

  // Hierarchy
  parentId String?
  parent   Category? @relation("CategoryHierarchy", fields: [parentId], references: [id])
  children Category[] @relation("CategoryHierarchy")

  // Media
  image String?
  icon  String?

  // Display settings
  sortOrder   Int @default(0)
  isActive    Boolean @default(true)
  isFeatured  Boolean @default(false)

  // SEO
  seoTitle       String?
  seoDescription String?
  seoKeywords    String[]

  // Metadata
  metadata Json?

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relationships
  products Product[]

  @@map("categories")
  @@index([slug])
  @@index([parentId])
  @@index([isActive])
  @@index([sortOrder])
}

// Brand model
model Brand {
  id          String @id @default(cuid())
  name        String @unique
  slug        String @unique
  description String?
  logo        String?
  website     String?

  // Status
  isActive Boolean @default(true)

  // SEO
  seoTitle       String?
  seoDescription String?

  // Metadata
  metadata Json?

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relationships
  products Product[]

  @@map("brands")
  @@index([slug])
  @@index([isActive])
}

// Tag model for flexible categorization
model Tag {
  id    String @id @default(cuid())
  name  String @unique
  slug  String @unique
  color String? // Hex color code

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relationships
  products Product[]
  posts    Post[]

  @@map("tags")
  @@index([slug])
}

// Order model with comprehensive order management
model Order {
  id          String @id @default(cuid())
  orderNumber String @unique @default(cuid())

  // Customer information
  userId    String
  user      User @relation(fields: [userId], references: [id])

  // Order status
  status    OrderStatus @default(PENDING)

  // Pricing
  subtotal    Decimal
  taxAmount   Decimal @default(0)
  shippingCost Decimal @default(0)
  discountAmount Decimal @default(0)
  totalAmount Decimal
  currency    String @default("USD")

  // Addresses
  shippingAddressId String?
  billingAddressId  String?
  shippingAddress   Address? @relation(fields: [shippingAddressId], references: [id])
  billingAddress    Address? @relation(fields: [billingAddressId], references: [id])

  // Shipping
  shippingMethod String?
  trackingNumber String?
  estimatedDelivery DateTime?
  shippedAt      DateTime?
  deliveredAt    DateTime?

  // Payment
  paymentStatus PaymentStatus @default(PENDING)
  paymentMethod String?
  paymentId     String? // External payment processor ID

  // Notes
  customerNotes String?
  adminNotes    String?

  // Metadata
  metadata Json?

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relationships
  items        OrderItem[]
  transactions PaymentTransaction[]

  @@map("orders")
  @@index([userId])
  @@index([status])
  @@index([orderNumber])
  @@index([createdAt])
}

// Order item model
model OrderItem {
  id      String @id @default(cuid())
  orderId String
  order   Order @relation(fields: [orderId], references: [id], onDelete: Cascade)

  // Product information
  productId        String
  product          Product @relation(fields: [productId], references: [id])
  productVariantId String?
  productVariant   ProductVariant? @relation(fields: [productVariantId], references: [id])

  // Item details (snapshot at time of order)
  name        String
  description String?
  price       Decimal
  quantity    Int
  totalPrice  Decimal

  // Product details at time of order
  productSnapshot Json // Store product details for historical record

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("order_items")
  @@index([orderId])
  @@index([productId])
}

// Shopping cart model
model Cart {
  id     String @id @default(cuid())
  userId String @unique
  user   User @relation(fields: [userId], references: [id], onDelete: Cascade)

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relationships
  items CartItem[]

  @@map("carts")
}

// Cart item model
model CartItem {
  id     String @id @default(cuid())
  cartId String
  cart   Cart @relation(fields: [cartId], references: [id], onDelete: Cascade)

  // Product information
  productId        String
  product          Product @relation(fields: [productId], references: [id], onDelete: Cascade)
  productVariantId String?
  productVariant   ProductVariant? @relation(fields: [productVariantId], references: [id], onDelete: SetNull)

  quantity Int @default(1)

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("cart_items")
  @@index([cartId])
  @@index([productId])
  @@unique([cartId, productId, productVariantId])
}

// Payment transaction model
model PaymentTransaction {
  id      String @id @default(cuid())
  orderId String
  order   Order @relation(fields: [orderId], references: [id], onDelete: Cascade)

  // Transaction details
  transactionId String @unique // External payment processor transaction ID
  amount        Decimal
  currency      String @default("USD")
  status        TransactionStatus
  type          TransactionType

  // Payment processor details
  processor     String // stripe, paypal, etc.
  processorData Json?  // Store processor-specific data

  // Failure information
  failureReason String?
  failureCode   String?

  // Timestamps
  processedAt DateTime?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@map("payment_transactions")
  @@index([orderId])
  @@index([transactionId])
  @@index([status])
}

// Post model for blog/content
model Post {
  id      String @id @default(cuid())
  title   String
  slug    String @unique
  content String
  excerpt String?

  // SEO
  seoTitle       String?
  seoDescription String?
  seoKeywords    String[]

  // Media
  featuredImage String?
  images        String[]

  // Publishing
  status      PostStatus @default(DRAFT)
  publishedAt DateTime?

  // Author
  authorId String
  author   User @relation(fields: [authorId], references: [id])

  // Engagement
  viewCount    Int @default(0)
  likeCount    Int @default(0)
  commentCount Int @default(0)

  // Metadata
  metadata Json?

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?

  // Relationships
  tags     Tag[]
  comments Comment[]
  likes    Like[]

  @@map("posts")
  @@index([slug])
  @@index([authorId])
  @@index([status])
  @@index([publishedAt])
  @@fulltext([title, content])
}

// Comment model
model Comment {
  id      String @id @default(cuid())
  content String

  // Hierarchy for nested comments
  parentId String?
  parent   Comment? @relation("CommentReplies", fields: [parentId], references: [id])
  replies  Comment[] @relation("CommentReplies")

  // Author
  authorId String
  author   User @relation(fields: [authorId], references: [id])

  // Post
  postId String
  post   Post @relation(fields: [postId], references: [id], onDelete: Cascade)

  // Status
  status    CommentStatus @default(PENDING)
  likeCount Int @default(0)

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?

  // Relationships
  likes Like[]

  @@map("comments")
  @@index([postId])
  @@index([authorId])
  @@index([parentId])
  @@index([status])
}

// Like model for posts and comments
model Like {
  id String @id @default(cuid())

  // User who liked
  userId String
  user   User @relation(fields: [userId], references: [id], onDelete: Cascade)

  // What was liked (polymorphic)
  postId    String?
  post      Post? @relation(fields: [postId], references: [id], onDelete: Cascade)
  commentId String?
  comment   Comment? @relation(fields: [commentId], references: [id], onDelete: Cascade)

  // Timestamp
  createdAt DateTime @default(now())

  @@map("likes")
  @@index([userId])
  @@index([postId])
  @@index([commentId])
  @@unique([userId, postId])
  @@unique([userId, commentId])
}

// Follow model for user relationships
model Follow {
  id String @id @default(cuid())

  followerId String
  follower   User @relation("UserFollows", fields: [followerId], references: [id], onDelete: Cascade)

  followingId String
  following   User @relation("UserFollowers", fields: [followingId], references: [id], onDelete: Cascade)

  // Timestamp
  createdAt DateTime @default(now())

  @@map("follows")
  @@unique([followerId, followingId])
  @@index([followerId])
  @@index([followingId])
}

// Review model for products
model Review {
  id      String @id @default(cuid())
  rating  Int // 1-5 stars
  title   String?
  content String

  // Author
  userId String
  user   User @relation(fields: [userId], references: [id])

  // Product
  productId String
  product   Product @relation(fields: [productId], references: [id], onDelete: Cascade)

  // Review metadata
  isVerified Boolean @default(false) // Verified purchase
  helpfulCount Int @default(0)

  // Media
  images String[]

  // Status
  status ReviewStatus @default(PENDING)

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("reviews")
  @@index([productId])
  @@index([userId])
  @@index([rating])
  @@index([status])
  @@unique([userId, productId]) // One review per user per product
}

// Message model for user-to-user messaging
model Message {
  id      String @id @default(cuid())
  content String

  // Sender and receiver
  senderId   String
  sender     User @relation("MessageSender", fields: [senderId], references: [id])
  receiverId String
  receiver   User @relation("MessageReceiver", fields: [receiverId], references: [id])

  // Message status
  isRead   Boolean @default(false)
  readAt   DateTime?

  // Message type
  type MessageType @default(TEXT)

  // Attachments
  attachments String[]

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?

  @@map("messages")
  @@index([senderId])
  @@index([receiverId])
  @@index([createdAt])
}

// Notification model
model Notification {
  id      String @id @default(cuid())
  title   String
  message String

  // Recipient
  userId String
  user   User @relation(fields: [userId], references: [id], onDelete: Cascade)

  // Notification details
  type        NotificationType
  isRead      Boolean @default(false)
  readAt      DateTime?

  // Action data
  actionUrl   String?
  actionData  Json?

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  expiresAt DateTime?

  @@map("notifications")
  @@index([userId])
  @@index([type])
  @@index([isRead])
  @@index([createdAt])
}

// Session model for user sessions
model Session {
  id        String   @id @default(cuid())
  userId    String
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  // Session details
  token     String   @unique
  userAgent String?
  ipAddress String?
  location  String?
  device    String?

  // Status
  isActive  Boolean  @default(true)
  expiresAt DateTime

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("sessions")
  @@index([userId])
  @@index([token])
  @@index([expiresAt])
}

// Audit log model for tracking changes
model AuditLog {
  id     String @id @default(cuid())
  userId String?
  user   User?  @relation(fields: [userId], references: [id])

  // Action details
  action      String // CREATE, UPDATE, DELETE, etc.
  entityType  String // User, Product, Order, etc.
  entityId    String

  // Change details
  oldValues   Json?
  newValues   Json?

  // Request metadata
  ipAddress   String?
  userAgent   String?

  // Timestamps
  createdAt DateTime @default(now())

  @@map("audit_logs")
  @@index([userId])
  @@index([entityType])
  @@index([entityId])
  @@index([action])
  @@index([createdAt])
}

// Enums
enum UserRole {
  ADMIN
  MODERATOR
  USER
  GUEST
}

enum UserStatus {
  ACTIVE
  INACTIVE
  SUSPENDED
  BANNED
}

enum Gender {
  MALE
  FEMALE
  OTHER
  PREFER_NOT_TO_SAY
}

enum Theme {
  LIGHT
  DARK
  SYSTEM
}

enum AddressType {
  HOME
  WORK
  BILLING
  SHIPPING
  OTHER
}

enum ProductStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
  OUT_OF_STOCK
}

enum OrderStatus {
  PENDING
  CONFIRMED
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
  REFUNDED
}

enum PaymentStatus {
  PENDING
  COMPLETED
  FAILED
  CANCELLED
  REFUNDED
}

enum TransactionStatus {
  PENDING
  PROCESSING
  COMPLETED
  FAILED
  CANCELLED
  REFUNDED
}

enum TransactionType {
  PAYMENT
  REFUND
  PARTIAL_REFUND
  CHARGEBACK
}

enum PostStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

enum CommentStatus {
  PENDING
  APPROVED
  REJECTED
  SPAM
}

enum ReviewStatus {
  PENDING
  APPROVED
  REJECTED
}

enum MessageType {
  TEXT
  IMAGE
  FILE
  SYSTEM
}

enum NotificationType {
  ORDER_UPDATE
  MESSAGE
  FOLLOW
  LIKE
  COMMENT
  SYSTEM
  MARKETING
}prisma/schema.prisma

Step 3: Database Service Layer

Create a comprehensive database service with advanced Prisma patterns:

import { PrismaClient, Prisma } from "@prisma/client";
import { DefaultArgs } from "@prisma/client/runtime/library";

// Extended Prisma client with custom extensions
class DatabaseService {
  private prisma: PrismaClient;

  constructor() {
    this.prisma = new PrismaClient({
      log: [
        {
          emit: "event",
          level: "query",
        },
        {
          emit: "event",
          level: "error",
        },
        {
          emit: "event",
          level: "info",
        },
        {
          emit: "event",
          level: "warn",
        },
      ],
      errorFormat: "colored",
    });

    // Set up event listeners
    this.setupEventListeners();

    // Add custom extensions
    this.setupExtensions();
  }

  private setupEventListeners() {
    this.prisma.$on("query", e => {
      if (process.env.NODE_ENV === "development") {
        console.log("Query: " + e.query);
        console.log("Params: " + e.params);
        console.log("Duration: " + e.duration + "ms");
      }
    });

    this.prisma.$on("error", e => {
      console.error("Prisma Error:", e);
    });

    this.prisma.$on("warn", e => {
      console.warn("Prisma Warning:", e);
    });

    this.prisma.$on("info", e => {
      console.info("Prisma Info:", e);
    });
  }

  private setupExtensions() {
    // Add soft delete extension
    this.prisma = this.prisma.$extends({
      query: {
        user: {
          async findMany({ args, query }) {
            // Exclude soft deleted users by default
            if (!args.where) {
              args.where = {};
            }
            if (!args.where.deletedAt) {
              args.where.deletedAt = null;
            }
            return query(args);
          },
          async findFirst({ args, query }) {
            if (!args.where) {
              args.where = {};
            }
            if (!args.where.deletedAt) {
              args.where.deletedAt = null;
            }
            return query(args);
          },
          async findUnique({ args, query }) {
            if (!args.where.deletedAt) {
              args.where = {
                ...args.where,
                deletedAt: null,
              };
            }
            return query(args);
          },
        },
        post: {
          async findMany({ args, query }) {
            if (!args.where) {
              args.where = {};
            }
            if (!args.where.deletedAt) {
              args.where.deletedAt = null;
            }
            return query(args);
          },
        },
      },
    });

    // Add audit logging extension
    this.prisma = this.prisma.$extends({
      query: {
        $allModels: {
          async create({ args, query, model }) {
            const result = await query(args);

            // Log creation
            await this.logAudit({
              action: "CREATE",
              entityType: model,
              entityId: result.id,
              newValues: result,
            });

            return result;
          },
          async update({ args, query, model }) {
            // Get old values before update
            const oldRecord = await this.prisma[
              model as keyof PrismaClient
            ].findUnique({
              where: args.where,
            });

            const result = await query(args);

            // Log update
            await this.logAudit({
              action: "UPDATE",
              entityType: model,
              entityId: result.id,
              oldValues: oldRecord,
              newValues: result,
            });

            return result;
          },
          async delete({ args, query, model }) {
            // Get record before deletion
            const oldRecord = await this.prisma[
              model as keyof PrismaClient
            ].findUnique({
              where: args.where,
            });

            const result = await query(args);

            // Log deletion
            await this.logAudit({
              action: "DELETE",
              entityType: model,
              entityId: oldRecord?.id,
              oldValues: oldRecord,
            });

            return result;
          },
        },
      },
    });

    // Add pagination extension
    this.prisma = this.prisma.$extends({
      model: {
        $allModels: {
          async paginate<T, A>(
            this: T,
            args: Prisma.Exact<A, Prisma.Args<T, "findMany">> & {
              page?: number;
              pageSize?: number;
            }
          ) {
            const page = args.page || 1;
            const pageSize = args.pageSize || 10;
            const skip = (page - 1) * pageSize;

            const [data, total] = await Promise.all([
              // @ts-ignore
              this.findMany({
                ...args,
                skip,
                take: pageSize,
              }),
              // @ts-ignore
              this.count({
                where: args.where,
              }),
            ]);

            return {
              data,
              pagination: {
                page,
                pageSize,
                total,
                pages: Math.ceil(total / pageSize),
                hasNext: page < Math.ceil(total / pageSize),
                hasPrev: page > 1,
              },
            };
          },
        },
      },
    });
  }

  private async logAudit({
    action,
    entityType,
    entityId,
    oldValues = null,
    newValues = null,
    userId = null,
  }: {
    action: string;
    entityType: string;
    entityId?: string;
    oldValues?: any;
    newValues?: any;
    userId?: string | null;
  }) {
    try {
      await this.prisma.auditLog.create({
        data: {
          action,
          entityType,
          entityId: entityId || "unknown",
          oldValues: oldValues ? JSON.parse(JSON.stringify(oldValues)) : null,
          newValues: newValues ? JSON.parse(JSON.stringify(newValues)) : null,
          userId,
        },
      });
    } catch (error) {
      console.error("Failed to log audit:", error);
    }
  }

  // Connection management
  async connect() {
    try {
      await this.prisma.$connect();
      console.log("Database connected successfully");
    } catch (error) {
      console.error("Failed to connect to database:", error);
      throw error;
    }
  }

  async disconnect() {
    try {
      await this.prisma.$disconnect();
      console.log("Database disconnected successfully");
    } catch (error) {
      console.error("Failed to disconnect from database:", error);
      throw error;
    }
  }

  // Health check
  async healthCheck(): Promise<boolean> {
    try {
      await this.prisma.$queryRaw`SELECT 1`;
      return true;
    } catch (error) {
      console.error("Database health check failed:", error);
      return false;
    }
  }

  // Transaction wrapper
  async transaction<T>(
    fn: (
      prisma: Omit<
        PrismaClient,
        | "$connect"
        | "$disconnect"
        | "$on"
        | "$transaction"
        | "$use"
        | "$extends"
      >
    ) => Promise<T>
  ): Promise<T> {
    return this.prisma.$transaction(fn);
  }

  // Bulk operations
  async bulkCreate<T extends keyof PrismaClient>(
    model: T,
    data: any[],
    batchSize = 100
  ) {
    const results = [];

    for (let i = 0; i < data.length; i += batchSize) {
      const batch = data.slice(i, i + batchSize);
      const batchResult = await (this.prisma[model] as any).createMany({
        data: batch,
        skipDuplicates: true,
      });
      results.push(batchResult);
    }

    return results;
  }

  async bulkUpdate<T extends keyof PrismaClient>(
    model: T,
    updates: Array<{ where: any; data: any }>
  ) {
    return this.prisma.$transaction(
      updates.map(update =>
        (this.prisma[model] as any).update({
          where: update.where,
          data: update.data,
        })
      )
    );
  }

  async bulkDelete<T extends keyof PrismaClient>(model: T, where: any) {
    return (this.prisma[model] as any).deleteMany({
      where,
    });
  }

  // Search utilities
  async fullTextSearch(
    model: keyof PrismaClient,
    searchTerm: string,
    fields: string[],
    additionalWhere: any = {}
  ) {
    const searchConditions = fields.map(field => ({
      [field]: {
        contains: searchTerm,
        mode: "insensitive" as const,
      },
    }));

    return (this.prisma[model] as any).findMany({
      where: {
        ...additionalWhere,
        OR: searchConditions,
      },
    });
  }

  // Statistics and analytics
  async getModelStats(model: keyof PrismaClient) {
    const [total, recentCount] = await Promise.all([
      (this.prisma[model] as any).count(),
      (this.prisma[model] as any).count({
        where: {
          createdAt: {
            gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000), // Last 30 days
          },
        },
      }),
    ]);

    return {
      total,
      recent: recentCount,
      model: model.toString(),
    };
  }

  // Raw query execution
  async executeRaw(query: string, params: any[] = []) {
    return this.prisma.$queryRawUnsafe(query, ...params);
  }

  // Get the raw Prisma client for direct access
  get client() {
    return this.prisma;
  }

  // Cleanup method for graceful shutdown
  async cleanup() {
    await this.disconnect();
  }
}

// Export singleton instance
export const db = new DatabaseService();
export type Database = typeof db.client;
export default db;src/services/databaseService.ts

Step 4: Repository Pattern Implementation

Create comprehensive repository classes:

import { Prisma, User, UserRole, UserStatus } from "@prisma/client";
import { db } from "@/services/databaseService";
import bcrypt from "bcryptjs";

export interface CreateUserData {
  email: string;
  username: string;
  firstName: string;
  lastName: string;
  password: string;
  phone?: string;
  role?: UserRole;
}

export interface UpdateUserData {
  username?: string;
  firstName?: string;
  lastName?: string;
  avatar?: string;
  phone?: string;
  bio?: string;
  website?: string;
  location?: string;
  dateOfBirth?: Date;
}

export interface UserFilters {
  role?: UserRole;
  status?: UserStatus;
  isOnline?: boolean;
  createdAfter?: Date;
  createdBefore?: Date;
  search?: string;
}

export interface PaginationOptions {
  page?: number;
  pageSize?: number;
  orderBy?: Prisma.UserOrderByWithRelationInput[];
}

export class UserRepository {
  // Create a new user
  async create(userData: CreateUserData): Promise<User> {
    // Hash password
    const passwordHash = await bcrypt.hash(userData.password, 12);

    try {
      const user = await db.client.user.create({
        data: {
          email: userData.email.toLowerCase(),
          username: userData.username.toLowerCase(),
          firstName: userData.firstName,
          lastName: userData.lastName,
          passwordHash,
          phone: userData.phone,
          role: userData.role || "USER",
          status: "ACTIVE",
        },
        include: {
          addresses: true,
        },
      });

      return user;
    } catch (error) {
      if (error instanceof Prisma.PrismaClientKnownRequestError) {
        if (error.code === "P2002") {
          const target = error.meta?.target as string[];
          if (target?.includes("email")) {
            throw new Error("Email already exists");
          }
          if (target?.includes("username")) {
            throw new Error("Username already taken");
          }
        }
      }
      throw new Error("Failed to create user");
    }
  }

  // Find user by ID
  async findById(id: string): Promise<User | null> {
    return db.client.user.findUnique({
      where: { id },
      include: {
        addresses: true,
        _count: {
          select: {
            posts: true,
            followers: true,
            follows: true,
          },
        },
      },
    });
  }

  // Find user by email
  async findByEmail(email: string): Promise<User | null> {
    return db.client.user.findUnique({
      where: { email: email.toLowerCase() },
      include: {
        addresses: true,
      },
    });
  }

  // Find user by username
  async findByUsername(username: string): Promise<User | null> {
    return db.client.user.findUnique({
      where: { username: username.toLowerCase() },
      include: {
        addresses: true,
      },
    });
  }

  // Update user
  async update(id: string, updateData: UpdateUserData): Promise<User> {
    try {
      return await db.client.user.update({
        where: { id },
        data: {
          ...updateData,
          username: updateData.username?.toLowerCase(),
          updatedAt: new Date(),
        },
        include: {
          addresses: true,
        },
      });
    } catch (error) {
      if (error instanceof Prisma.PrismaClientKnownRequestError) {
        if (error.code === "P2002") {
          throw new Error("Username already taken");
        }
        if (error.code === "P2025") {
          throw new Error("User not found");
        }
      }
      throw new Error("Failed to update user");
    }
  }

  // Soft delete user
  async softDelete(id: string): Promise<User> {
    return db.client.user.update({
      where: { id },
      data: {
        deletedAt: new Date(),
        status: "INACTIVE",
      },
    });
  }

  // Hard delete user (use with caution)
  async hardDelete(id: string): Promise<User> {
    return db.client.user.delete({
      where: { id },
    });
  }

  // Find users with filters and pagination
  async findMany(
    filters: UserFilters = {},
    pagination: PaginationOptions = {}
  ) {
    const where: Prisma.UserWhereInput = {};

    // Apply filters
    if (filters.role) {
      where.role = filters.role;
    }

    if (filters.status) {
      where.status = filters.status;
    }

    if (filters.isOnline !== undefined) {
      where.isOnline = filters.isOnline;
    }

    if (filters.createdAfter || filters.createdBefore) {
      where.createdAt = {};
      if (filters.createdAfter) {
        where.createdAt.gte = filters.createdAfter;
      }
      if (filters.createdBefore) {
        where.createdAt.lte = filters.createdBefore;
      }
    }

    if (filters.search) {
      where.OR = [
        { firstName: { contains: filters.search, mode: "insensitive" } },
        { lastName: { contains: filters.search, mode: "insensitive" } },
        { username: { contains: filters.search, mode: "insensitive" } },
        { email: { contains: filters.search, mode: "insensitive" } },
      ];
    }

    return db.client.user.paginate({
      where,
      page: pagination.page || 1,
      pageSize: pagination.pageSize || 20,
      orderBy: pagination.orderBy || [{ createdAt: "desc" }],
      include: {
        _count: {
          select: {
            posts: true,
            followers: true,
            follows: true,
          },
        },
      },
    });
  }

  // Verify password
  async verifyPassword(user: User, password: string): Promise<boolean> {
    return bcrypt.compare(password, user.passwordHash);
  }

  // Update password
  async updatePassword(id: string, newPassword: string): Promise<User> {
    const passwordHash = await bcrypt.hash(newPassword, 12);

    return db.client.user.update({
      where: { id },
      data: {
        passwordHash,
        passwordResetToken: null,
        passwordResetExpiresAt: null,
        updatedAt: new Date(),
      },
    });
  }

  // Update last seen
  async updateLastSeen(id: string): Promise<void> {
    await db.client.user.update({
      where: { id },
      data: {
        lastSeenAt: new Date(),
        isOnline: true,
      },
    });
  }

  // Set user offline
  async setOffline(id: string): Promise<void> {
    await db.client.user.update({
      where: { id },
      data: {
        isOnline: false,
      },
    });
  }

  // Get user statistics
  async getStatistics() {
    const [
      total,
      active,
      inactive,
      online,
      recentSignups,
      totalPosts,
      totalComments,
    ] = await Promise.all([
      db.client.user.count(),
      db.client.user.count({ where: { status: "ACTIVE" } }),
      db.client.user.count({ where: { status: "INACTIVE" } }),
      db.client.user.count({ where: { isOnline: true } }),
      db.client.user.count({
        where: {
          createdAt: {
            gte: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000), // Last 7 days
          },
        },
      }),
      db.client.post.count(),
      db.client.comment.count(),
    ]);

    return {
      users: {
        total,
        active,
        inactive,
        online,
        recentSignups,
      },
      content: {
        totalPosts,
        totalComments,
      },
    };
  }

  // Follow/unfollow operations
  async follow(followerId: string, followingId: string): Promise<void> {
    try {
      await db.client.follow.create({
        data: {
          followerId,
          followingId,
        },
      });
    } catch (error) {
      if (error instanceof Prisma.PrismaClientKnownRequestError) {
        if (error.code === "P2002") {
          throw new Error("Already following this user");
        }
      }
      throw new Error("Failed to follow user");
    }
  }

  async unfollow(followerId: string, followingId: string): Promise<void> {
    await db.client.follow.delete({
      where: {
        followerId_followingId: {
          followerId,
          followingId,
        },
      },
    });
  }

  // Get followers and following
  async getFollowers(userId: string, pagination: PaginationOptions = {}) {
    return db.client.follow.paginate({
      where: { followingId: userId },
      page: pagination.page || 1,
      pageSize: pagination.pageSize || 20,
      include: {
        follower: {
          select: {
            id: true,
            username: true,
            firstName: true,
            lastName: true,
            avatar: true,
          },
        },
      },
      orderBy: [{ createdAt: "desc" }],
    });
  }

  async getFollowing(userId: string, pagination: PaginationOptions = {}) {
    return db.client.follow.paginate({
      where: { followerId: userId },
      page: pagination.page || 1,
      pageSize: pagination.pageSize || 20,
      include: {
        following: {
          select: {
            id: true,
            username: true,
            firstName: true,
            lastName: true,
            avatar: true,
          },
        },
      },
      orderBy: [{ createdAt: "desc" }],
    });
  }

  // Search users with advanced options
  async search(
    searchTerm: string,
    options: {
      includeInactive?: boolean;
      roleFilter?: UserRole[];
      limit?: number;
    } = {}
  ) {
    const where: Prisma.UserWhereInput = {
      OR: [
        { firstName: { contains: searchTerm, mode: "insensitive" } },
        { lastName: { contains: searchTerm, mode: "insensitive" } },
        { username: { contains: searchTerm, mode: "insensitive" } },
      ],
    };

    if (!options.includeInactive) {
      where.status = "ACTIVE";
    }

    if (options.roleFilter && options.roleFilter.length > 0) {
      where.role = { in: options.roleFilter };
    }

    return db.client.user.findMany({
      where,
      take: options.limit || 10,
      select: {
        id: true,
        username: true,
        firstName: true,
        lastName: true,
        avatar: true,
        role: true,
        status: true,
        isOnline: true,
        lastSeenAt: true,
      },
      orderBy: [
        { isOnline: "desc" },
        { lastSeenAt: "desc" },
        { createdAt: "desc" },
      ],
    });
  }
}

// Export singleton instance
export const userRepository = new UserRepository();src/repositories/userRepository.ts