ORM Best Practices

ORM Best Practices

Leverage AI coding assistants to design efficient data models, generate type-safe queries, and implement robust database patterns using popular ORMs.

AI-Powered ORM Development

AI coding assistants excel at ORM development by understanding schema relationships, generating type-safe queries, handling migrations, and suggesting performance optimizations. Learn how to maximize these capabilities across different ORM frameworks.

Schema Design and Modeling

AI-Driven Schema Generation

Intelligent Schema Design

The Pattern: Use AI to design normalized, type-safe schemas with proper relationships and constraints.

// Prompt to AI Assistant
"Design a Prisma schema for an e-commerce platform with:
- Multi-tenant architecture (organizations)
- User roles and permissions
- Product catalog with variants
- Shopping cart and orders
- Inventory tracking
Include proper indexes, constraints, and soft deletes"

// AI generates comprehensive Prisma schema:
generator client {
  provider = "prisma-client-js"
}

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

// Multi-tenant base
model Organization {
  id        String   @id @default(cuid())
  name      String
  slug      String   @unique
  plan      Plan     @default(FREE)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?

  // Relations
  users     User[]
  products  Product[]
  orders    Order[]

  @@index([deletedAt])
  @@map("organizations")
}

// User management with roles
model User {
  id             String   @id @default(cuid())
  email          String   @unique
  passwordHash   String
  firstName      String?
  lastName       String?
  role           UserRole @default(MEMBER)
  emailVerified  Boolean  @default(false)
  lastLoginAt    DateTime?
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt
  deletedAt      DateTime?

  // Relations
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id])
  sessions       Session[]
  cart           Cart?
  orders         Order[]

  @@index([organizationId, deletedAt])
  @@index([email])
  @@map("users")
}

// Product catalog
model Product {
  id          String   @id @default(cuid())
  name        String
  slug        String
  description String?
  category    String
  basePrice   Decimal  @db.Money
  isActive    Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  deletedAt   DateTime?

  // Relations
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id])
  variants       ProductVariant[]

  @@unique([organizationId, slug])
  @@index([organizationId, category, deletedAt])
  @@index([isActive, deletedAt])
  @@map("products")
}

// Product variants for SKUs
model ProductVariant {
  id        String   @id @default(cuid())
  sku       String
  name      String
  price     Decimal  @db.Money
  cost      Decimal? @db.Money

  // Inventory
  stockQuantity     Int      @default(0)
  lowStockThreshold Int      @default(10)
  trackInventory    Boolean  @default(true)

  // Attributes
  attributes Json     @default("{}")
  images     String[]

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

  // Relations
  productId  String
  product    Product    @relation(fields: [productId], references: [id])
  cartItems  CartItem[]
  orderItems OrderItem[]

  @@unique([productId, sku])
  @@index([productId, deletedAt])
  @@index([stockQuantity])
  @@map("product_variants")
}

// Shopping cart
model Cart {
  id        String   @id @default(cuid())
  expiresAt DateTime @default(dbgenerated("NOW() + INTERVAL '7 days'"))
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  userId String @unique
  user   User   @relation(fields: [userId], references: [id])
  items  CartItem[]

  @@index([expiresAt])
  @@map("carts")
}

model CartItem {
  id        String   @id @default(cuid())
  quantity  Int
  price     Decimal  @db.Money
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  cartId    String
  cart      Cart           @relation(fields: [cartId], references: [id], onDelete: Cascade)
  variantId String
  variant   ProductVariant @relation(fields: [variantId], references: [id])

  @@unique([cartId, variantId])
  @@map("cart_items")
}

// Orders
model Order {
  id          String      @id @default(cuid())
  orderNumber String      @unique @default(dbgenerated("'ORD-' || TO_CHAR(NOW(), 'YYYYMMDD') || '-' || LPAD(nextval('order_seq')::TEXT, 6, '0')"))
  status      OrderStatus @default(PENDING)

  // Pricing
  subtotal    Decimal @db.Money
  tax         Decimal @db.Money
  shipping    Decimal @db.Money
  total       Decimal @db.Money

  // Customer info (denormalized for history)
  customerEmail String
  customerName  String

  // Timestamps
  placedAt   DateTime @default(now())
  paidAt     DateTime?
  shippedAt  DateTime?
  deliveredAt DateTime?
  cancelledAt DateTime?

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

  // Relations
  userId         String
  user           User         @relation(fields: [userId], references: [id])
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id])
  items          OrderItem[]

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

model OrderItem {
  id       String  @id @default(cuid())
  quantity Int
  price    Decimal @db.Money
  total    Decimal @db.Money

  // Product snapshot (denormalized)
  productName  String
  variantName  String
  variantSku   String

  // Relations
  orderId   String
  order     Order          @relation(fields: [orderId], references: [id])
  variantId String
  variant   ProductVariant @relation(fields: [variantId], references: [id])

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

// Enums
enum Plan {
  FREE
  STARTER
  PROFESSIONAL
  ENTERPRISE
}

enum UserRole {
  OWNER
  ADMIN
  MEMBER
  READONLY
}

enum OrderStatus {
  PENDING
  PROCESSING
  PAID
  SHIPPED
  DELIVERED
  CANCELLED
  REFUNDED
}

model Session {
  id        String   @id @default(cuid())
  token     String   @unique
  expiresAt DateTime
  createdAt DateTime @default(now())

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

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

AI-Generated Benefits:

• Proper normalization with strategic denormalization
• Comprehensive indexes for common queries
• Soft delete support throughout
• Type-safe enums for statuses
• Audit trails with timestamps

TypeORM Entity Design

Entity Definitions

// Request TypeORM entities from AI
"Convert this schema to TypeORM entities with:
- Proper decorators and relations
- Custom repositories
- Query builders
- Migration support"

// AI generates TypeORM entities:
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
  DeleteDateColumn,
  ManyToOne,
  OneToMany,
  Index,
  Unique,
  BeforeInsert,
  BeforeUpdate
} from 'typeorm';
import { IsEmail, IsEnum, Min } from 'class-validator';
import bcrypt from 'bcrypt';

@Entity('users')
@Index(['organizationId', 'deletedAt'])
@Unique(['email'])
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  @IsEmail()
  email: string;

  @Column()
  passwordHash: string;

  @Column({ nullable: true })
  firstName?: string;

  @Column({ nullable: true })
  lastName?: string;

  @Column({
    type: 'enum',
    enum: UserRole,
    default: UserRole.MEMBER
  })
  @IsEnum(UserRole)
  role: UserRole;

  @Column({ default: false })
  emailVerified: boolean;

  @Column({ type: 'timestamp', nullable: true })
  lastLoginAt?: Date;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;

  @DeleteDateColumn()
  deletedAt?: Date;

  // Relations
  @ManyToOne(() => Organization, org => org.users)
  organization: Organization;

  @Column()
  organizationId: string;

  @OneToMany(() => Order, order => order.user)
  orders: Order[];

  // Hooks
  @BeforeInsert()
  async hashPassword() {
    if (this.passwordHash) {
      this.passwordHash = await bcrypt.hash(this.passwordHash, 10);
    }
  }

  async validatePassword(password: string): Promise<boolean> {
    return bcrypt.compare(password, this.passwordHash);
  }

  // Virtual properties
  get fullName(): string {
    return [this.firstName, this.lastName].filter(Boolean).join(' ');
  }
}

// Custom repository
@EntityRepository(User)
export class UserRepository extends Repository<User> {
  async findActiveByEmail(email: string): Promise<User | null> {
    return this.createQueryBuilder('user')
      .leftJoinAndSelect('user.organization', 'org')
      .where('user.email = :email', { email })
      .andWhere('user.deletedAt IS NULL')
      .andWhere('org.deletedAt IS NULL')
      .getOne();
  }

  async findByOrganization(
    orgId: string,
    options: { includeDeleted?: boolean } = {}
  ): Promise<User[]> {
    const query = this.createQueryBuilder('user')
      .where('user.organizationId = :orgId', { orgId });

    if (!options.includeDeleted) {
      query.andWhere('user.deletedAt IS NULL');
    }

    return query.getMany();
  }

  async updateLastLogin(userId: string): Promise<void> {
    await this.update(userId, { lastLoginAt: new Date() });
  }
}

Complex Queries

// AI-generated query builder patterns
"Generate TypeORM query for:
- Users with recent orders
- Including order totals
- Grouped by organization
- With pagination"

export class AnalyticsRepository {
  constructor(
    private userRepo: UserRepository,
    private orderRepo: Repository<Order>
  ) {}

  async getActiveUsersWithOrderStats(
    organizationId: string,
    options: {
      startDate?: Date;
      endDate?: Date;
      page?: number;
      limit?: number;
    } = {}
  ) {
    const {
      startDate = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000),
      endDate = new Date(),
      page = 1,
      limit = 20
    } = options;

    const query = this.userRepo
      .createQueryBuilder('user')
      .leftJoin('user.orders', 'order')
      .select([
        'user.id',
        'user.email',
        'user.firstName',
        'user.lastName',
        'user.lastLoginAt'
      ])
      .addSelect('COUNT(DISTINCT order.id)', 'orderCount')
      .addSelect('COALESCE(SUM(order.total), 0)', 'totalSpent')
      .addSelect('MAX(order.placedAt)', 'lastOrderDate')
      .where('user.organizationId = :organizationId', { organizationId })
      .andWhere('user.deletedAt IS NULL')
      .andWhere('order.placedAt BETWEEN :startDate AND :endDate', {
        startDate,
        endDate
      })
      .andWhere('order.status != :cancelledStatus', {
        cancelledStatus: OrderStatus.CANCELLED
      })
      .groupBy('user.id')
      .having('COUNT(DISTINCT order.id) > 0')
      .orderBy('totalSpent', 'DESC')
      .offset((page - 1) * limit)
      .limit(limit);

    const [results, total] = await Promise.all([
      query.getRawMany(),
      query.getCount()
    ]);

    return {
      data: results.map(row => ({
        id: row.user_id,
        email: row.user_email,
        firstName: row.user_firstName,
        lastName: row.user_lastName,
        lastLoginAt: row.user_lastLoginAt,
        stats: {
          orderCount: parseInt(row.orderCount),
          totalSpent: parseFloat(row.totalSpent),
          lastOrderDate: row.lastOrderDate
        }
      })),
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit)
      }
    };
  }
}

Migration Patterns

AI-Assisted Migration Generation

Smart Migration Strategies

// Request migration strategy from AI
"Generate database migrations for:
- Adding multi-currency support to products
- Implementing audit logs
- Adding full-text search
- Zero-downtime deployment"

// AI provides comprehensive migration plan:

// Migration 1: Add currency support (safe)
export class AddMultiCurrencySupport1234567890 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Add currency columns with defaults (non-breaking)
    await queryRunner.query(`
      ALTER TABLE products
      ADD COLUMN currency VARCHAR(3) DEFAULT 'USD' NOT NULL,
      ADD COLUMN base_prices JSONB DEFAULT '{"USD": 0}'::jsonb;
    `);

    // Populate base_prices from existing data
    await queryRunner.query(`
      UPDATE products
      SET base_prices = jsonb_build_object('USD', base_price);
    `);

    // Create exchange rates table
    await queryRunner.query(`
      CREATE TABLE exchange_rates (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        from_currency VARCHAR(3) NOT NULL,
        to_currency VARCHAR(3) NOT NULL,
        rate DECIMAL(10, 6) NOT NULL,
        valid_from TIMESTAMP NOT NULL,
        valid_to TIMESTAMP,
        created_at TIMESTAMP DEFAULT NOW(),

        CONSTRAINT unique_rate_period
          UNIQUE (from_currency, to_currency, valid_from)
      );

      CREATE INDEX idx_exchange_rates_lookup
        ON exchange_rates(from_currency, to_currency, valid_from DESC);
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP TABLE exchange_rates`);
    await queryRunner.query(`
      ALTER TABLE products
      DROP COLUMN currency,
      DROP COLUMN base_prices;
    `);
  }
}

// Migration 2: Add audit logging (zero-downtime)
export class AddAuditLogs1234567891 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Create audit log table
    await queryRunner.query(`
      CREATE TABLE audit_logs (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        table_name VARCHAR(255) NOT NULL,
        record_id VARCHAR(255) NOT NULL,
        action VARCHAR(50) NOT NULL,
        user_id UUID,
        organization_id UUID,
        old_values JSONB,
        new_values JSONB,
        ip_address INET,
        user_agent TEXT,
        created_at TIMESTAMP DEFAULT NOW()
      );

      -- Partitioning for performance
      CREATE INDEX idx_audit_logs_table_record
        ON audit_logs(table_name, record_id, created_at DESC);
      CREATE INDEX idx_audit_logs_user
        ON audit_logs(user_id, created_at DESC);
      CREATE INDEX idx_audit_logs_org
        ON audit_logs(organization_id, created_at DESC);
    `);

    // Create trigger function
    await queryRunner.query(`
      CREATE OR REPLACE FUNCTION audit_trigger_function()
      RETURNS TRIGGER AS $$
      BEGIN
        INSERT INTO audit_logs (
          table_name,
          record_id,
          action,
          user_id,
          organization_id,
          old_values,
          new_values,
          created_at
        ) VALUES (
          TG_TABLE_NAME,
          CASE
            WHEN TG_OP = 'DELETE' THEN OLD.id::text
            ELSE NEW.id::text
          END,
          TG_OP,
          current_setting('app.current_user_id', true)::uuid,
          current_setting('app.current_org_id', true)::uuid,
          CASE
            WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD)
            ELSE NULL
          END,
          CASE
            WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW)
            ELSE NULL
          END,
          NOW()
        );
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `);

    // Add triggers to critical tables
    const auditedTables = ['users', 'products', 'orders', 'product_variants'];

    for (const table of auditedTables) {
      await queryRunner.query(`
        CREATE TRIGGER audit_trigger_${table}
        AFTER INSERT OR UPDATE OR DELETE ON ${table}
        FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
      `);
    }
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    const auditedTables = ['users', 'products', 'orders', 'product_variants'];

    for (const table of auditedTables) {
      await queryRunner.query(`DROP TRIGGER IF EXISTS audit_trigger_${table} ON ${table}`);
    }

    await queryRunner.query(`DROP FUNCTION IF EXISTS audit_trigger_function()`);
    await queryRunner.query(`DROP TABLE audit_logs`);
  }
}

// Migration 3: Add full-text search
export class AddFullTextSearch1234567892 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Add tsvector column
    await queryRunner.query(`
      ALTER TABLE products
      ADD COLUMN search_vector tsvector;
    `);

    // Create function to update search vector
    await queryRunner.query(`
      CREATE OR REPLACE FUNCTION update_search_vector()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.search_vector :=
          setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
          setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B') ||
          setweight(to_tsvector('english', COALESCE(NEW.category, '')), 'C');
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `);

    // Create trigger
    await queryRunner.query(`
      CREATE TRIGGER update_search_vector_trigger
      BEFORE INSERT OR UPDATE ON products
      FOR EACH ROW EXECUTE FUNCTION update_search_vector();
    `);

    // Update existing records
    await queryRunner.query(`
      UPDATE products SET
        search_vector =
          setweight(to_tsvector('english', COALESCE(name, '')), 'A') ||
          setweight(to_tsvector('english', COALESCE(description, '')), 'B') ||
          setweight(to_tsvector('english', COALESCE(category, '')), 'C');
    `);

    // Create GIN index
    await queryRunner.query(`
      CREATE INDEX idx_products_search
      ON products USING GIN(search_vector);
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP TRIGGER IF EXISTS update_search_vector_trigger ON products`);
    await queryRunner.query(`DROP FUNCTION IF EXISTS update_search_vector()`);
    await queryRunner.query(`DROP INDEX IF EXISTS idx_products_search`);
    await queryRunner.query(`ALTER TABLE products DROP COLUMN search_vector`);
  }
}

Query Optimization Patterns

AI-Powered Query Analysis

N+1 Query Prevention

"Detect and fix N+1 queries in this code:
const users = await User.findAll();
for (const user of users) {
  const orders = await user.getOrders();
  console.log(orders);
}"

// AI suggests eager loading:
const users = await User.findAll({
  include: [{
    model: Order,
    as: 'orders',
    where: { status: 'completed' },
    required: false
  }]
});

// Or with Prisma:
const users = await prisma.user.findMany({
  include: {
    orders: {
      where: { status: 'COMPLETED' }
    }
  }
});

Batch Operations

"Optimize bulk insert/update operations"

// AI generates efficient patterns:

// Prisma batch operations
async function bulkCreateProducts(products: ProductInput[]) {
  // Use createMany for inserts
  const created = await prisma.$transaction(async (tx) => {
    // Create products
    await tx.product.createMany({
      data: products.map(p => ({
        name: p.name,
        slug: generateSlug(p.name),
        organizationId: p.organizationId,
        basePrice: p.basePrice
      })),
      skipDuplicates: true
    });

    // Get created products for variants
    const createdProducts = await tx.product.findMany({
      where: {
        slug: {
          in: products.map(p => generateSlug(p.name))
        }
      }
    });

    // Batch create variants
    const variants = products.flatMap((p, idx) =>
      p.variants.map(v => ({
        ...v,
        productId: createdProducts[idx].id
      }))
    );

    await tx.productVariant.createMany({
      data: variants
    });

    return createdProducts;
  });

  return created;
}

// TypeORM batch operations
async function bulkUpdatePrices(
  updates: Array<{ id: string; price: number }>
) {
  // Chunk updates for performance
  const chunks = chunk(updates, 1000);

  for (const batch of chunks) {
    await dataSource
      .createQueryBuilder()
      .update(ProductVariant)
      .set({
        price: () => "CASE id " +
          batch.map(u => `WHEN '${u.id}' THEN ${u.price}`).join(' ') +
          " END",
        updatedAt: new Date()
      })
      .where("id IN (:...ids)", {
        ids: batch.map(u => u.id)
      })
      .execute();
  }
}

Connection Pooling

"Configure optimal connection pooling"

// Prisma configuration
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  // AI suggests pool settings
  connectionLimit = 25
  pool = {
    min: 5
    max: 25
    idleTimeoutMillis: 30000
    createTimeoutMillis: 30000
    acquireTimeoutMillis: 30000
  }
}

// TypeORM configuration
const dataSource = new DataSource({
  type: "postgres",
  // Connection pool settings
  extra: {
    max: 25,                    // Max connections
    min: 5,                     // Min connections
    idleTimeoutMillis: 30000,   // Close idle connections
    connectionTimeoutMillis: 30000,
    statement_timeout: 60000,    // Query timeout
    query_timeout: 60000,
    // Enable prepared statements
    prepare: true
  },
  // Enable query caching
  cache: {
    type: "redis",
    options: {
      socket: {
        host: "localhost",
        port: 6379
      }
    },
    duration: 30000 // 30 seconds
  }
});

Query Caching

"Implement intelligent query caching"

class CachedRepository<T> {
  constructor(
    private repository: Repository<T>,
    private cachePrefix: string
  ) {}

  async findWithCache(
    key: string,
    query: () => Promise<T[]>,
    ttl: number = 300 // 5 minutes
  ): Promise<T[]> {
    const cacheKey = `${this.cachePrefix}:${key}`;

    // Try cache first
    const cached = await redis.get(cacheKey);
    if (cached) {
      return JSON.parse(cached);
    }

    // Execute query
    const results = await query();

    // Cache results
    await redis.setex(
      cacheKey,
      ttl,
      JSON.stringify(results)
    );

    return results;
  }

  async invalidateCache(patterns: string[]) {
    for (const pattern of patterns) {
      const keys = await redis.keys(
        `${this.cachePrefix}:${pattern}`
      );
      if (keys.length) {
        await redis.del(...keys);
      }
    }
  }
}

Transaction Patterns

Complex Transaction Management

AI-Generated Transaction Patterns

// Request transaction pattern from AI
"Generate robust transaction handling for:
- Order placement with inventory check
- Payment processing
- Email notification
- Rollback on any failure"

// AI provides comprehensive solution:
import { DataSource } from 'typeorm';
import { PrismaClient } from '@prisma/client';

// TypeORM transaction pattern
export class OrderService {
  constructor(
    private dataSource: DataSource,
    private paymentService: PaymentService,
    private emailService: EmailService
  ) {}

  async placeOrder(
    userId: string,
    items: OrderItemInput[]
  ): Promise<Order> {
    const queryRunner = this.dataSource.createQueryRunner();

    await queryRunner.connect();
    await queryRunner.startTransaction('SERIALIZABLE');

    try {
      // 1. Lock and check inventory
      const variants = await queryRunner.manager
        .createQueryBuilder(ProductVariant, 'v')
        .setLock('pessimistic_write')
        .whereInIds(items.map(i => i.variantId))
        .getMany();

      // Validate inventory
      for (const item of items) {
        const variant = variants.find(v => v.id === item.variantId);
        if (!variant || variant.stockQuantity < item.quantity) {
          throw new InsufficientInventoryError(
            `Insufficient stock for ${variant?.name}`
          );
        }
      }

      // 2. Create order
      const order = await queryRunner.manager.save(Order, {
        userId,
        status: OrderStatus.PENDING,
        items: items.map(item => ({
          variantId: item.variantId,
          quantity: item.quantity,
          price: variants.find(v => v.id === item.variantId)!.price
        }))
      });

      // 3. Update inventory
      for (const item of items) {
        await queryRunner.manager.decrement(
          ProductVariant,
          { id: item.variantId },
          'stockQuantity',
          item.quantity
        );
      }

      // 4. Process payment (external service)
      const payment = await this.paymentService.charge({
        orderId: order.id,
        amount: order.total,
        userId
      });

      // 5. Update order status
      order.status = OrderStatus.PAID;
      order.paymentId = payment.id;
      await queryRunner.manager.save(order);

      // Commit transaction
      await queryRunner.commitTransaction();

      // 6. Send email (outside transaction)
      await this.emailService.sendOrderConfirmation(order);

      return order;

    } catch (error) {
      await queryRunner.rollbackTransaction();

      // Compensate external services if needed
      if (error.paymentId) {
        await this.paymentService.refund(error.paymentId);
      }

      throw error;
    } finally {
      await queryRunner.release();
    }
  }
}

// Prisma transaction pattern with retries
export class PrismaOrderService {
  constructor(
    private prisma: PrismaClient,
    private paymentService: PaymentService
  ) {}

  async placeOrder(
    userId: string,
    items: OrderItemInput[],
    maxRetries: number = 3
  ): Promise<Order> {
    let retries = 0;

    while (retries < maxRetries) {
      try {
        const order = await this.prisma.$transaction(
          async (tx) => {
            // 1. Check inventory with row locking
            const variants = await Promise.all(
              items.map(item =>
                tx.$queryRaw<ProductVariant[]>`
                  SELECT * FROM product_variants
                  WHERE id = ${item.variantId}
                  FOR UPDATE
                `
              )
            );

            // Validate
            for (let i = 0; i < items.length; i++) {
              const variant = variants[i][0];
              if (!variant || variant.stockQuantity < items[i].quantity) {
                throw new Error(`Insufficient inventory`);
              }
            }

            // 2. Create order
            const order = await tx.order.create({
              data: {
                userId,
                status: 'PENDING',
                subtotal: 0, // Calculate from items
                tax: 0,
                shipping: 0,
                total: 0,
                customerEmail: '', // Fetch from user
                customerName: '',
                items: {
                  create: items.map((item, idx) => ({
                    variantId: item.variantId,
                    quantity: item.quantity,
                    price: variants[idx][0].price,
                    total: variants[idx][0].price * item.quantity,
                    productName: '', // Fetch from joins
                    variantName: variants[idx][0].name,
                    variantSku: variants[idx][0].sku
                  }))
                }
              },
              include: {
                items: true
              }
            });

            // 3. Update inventory
            await Promise.all(
              items.map(item =>
                tx.productVariant.update({
                  where: { id: item.variantId },
                  data: {
                    stockQuantity: {
                      decrement: item.quantity
                    }
                  }
                })
              )
            );

            // 4. Integrate payment
            const payment = await this.processPaymentWithRetry(
              order,
              userId
            );

            // 5. Update order with payment
            const finalOrder = await tx.order.update({
              where: { id: order.id },
              data: {
                status: 'PAID',
                paidAt: new Date()
              },
              include: {
                items: {
                  include: {
                    variant: true
                  }
                }
              }
            });

            return finalOrder;
          },
          {
            isolationLevel: 'Serializable',
            timeout: 30000 // 30 seconds
          }
        );

        // Success - send notifications
        await this.sendOrderNotifications(order);
        return order;

      } catch (error) {
        retries++;

        if (retries >= maxRetries) {
          throw new Error(
            `Order placement failed after ${maxRetries} attempts: ${error.message}`
          );
        }

        // Exponential backoff
        await new Promise(resolve =>
          setTimeout(resolve, Math.pow(2, retries) * 100)
        );
      }
    }

    throw new Error('Unexpected error in order placement');
  }

  private async processPaymentWithRetry(
    order: Order,
    userId: string,
    maxAttempts: number = 2
  ): Promise<Payment> {
    let lastError: Error;

    for (let attempt = 1; attempt <= maxAttempts; attempt++) {
      try {
        return await this.paymentService.charge({
          orderId: order.id,
          amount: order.total,
          userId,
          idempotencyKey: `${order.id}-${attempt}`
        });
      } catch (error) {
        lastError = error;

        if (attempt < maxAttempts) {
          await new Promise(resolve =>
            setTimeout(resolve, 1000 * attempt)
          );
        }
      }
    }

    throw lastError!;
  }
}

Testing Patterns

AI-Generated Test Suites

Unit Tests

// Request test generation from AI
"Generate comprehensive unit tests for UserRepository with:
- Mocking strategies
- Edge cases
- Performance tests
- Snapshot testing"

import { Test } from '@nestjs/testing';
import { getRepositoryToken } from '@nestjs/typeorm';
import { User, Organization } from '../entities';
import { UserRepository } from './user.repository';

describe('UserRepository', () => {
  let repository: UserRepository;
  let mockQueryBuilder: any;

  beforeEach(async () => {
    // Mock query builder
    mockQueryBuilder = {
      leftJoinAndSelect: jest.fn().mockReturnThis(),
      where: jest.fn().mockReturnThis(),
      andWhere: jest.fn().mockReturnThis(),
      getOne: jest.fn(),
      getMany: jest.fn(),
      getManyAndCount: jest.fn()
    };

    const module = await Test.createTestingModule({
      providers: [
        UserRepository,
        {
          provide: getRepositoryToken(User),
          useValue: {
            createQueryBuilder: jest.fn(() => mockQueryBuilder),
            save: jest.fn(),
            findOne: jest.fn(),
            update: jest.fn()
          }
        }
      ]
    }).compile();

    repository = module.get<UserRepository>(UserRepository);
  });

  describe('findActiveByEmail', () => {
    it('should find active user with organization', async () => {
      const mockUser = {
        id: '123',
        email: 'test@example.com',
        organization: { id: '456', name: 'Test Org' }
      };

      mockQueryBuilder.getOne.mockResolvedValue(mockUser);

      const result = await repository.findActiveByEmail('test@example.com');

      expect(mockQueryBuilder.leftJoinAndSelect)
        .toHaveBeenCalledWith('user.organization', 'org');
      expect(mockQueryBuilder.where)
        .toHaveBeenCalledWith('user.email = :email', {
          email: 'test@example.com'
        });
      expect(mockQueryBuilder.andWhere)
        .toHaveBeenCalledWith('user.deletedAt IS NULL');
      expect(result).toEqual(mockUser);
    });

    it('should return null for non-existent user', async () => {
      mockQueryBuilder.getOne.mockResolvedValue(null);

      const result = await repository.findActiveByEmail('none@example.com');

      expect(result).toBeNull();
    });
  });

  describe('Performance', () => {
    it('should execute queries within acceptable time', async () => {
      mockQueryBuilder.getMany.mockResolvedValue([]);

      const start = Date.now();
      await repository.findByOrganization('123');
      const duration = Date.now() - start;

      expect(duration).toBeLessThan(100); // 100ms threshold
    });
  });
});

Integration Tests

// AI generates integration test patterns
"Create integration tests with real database"

import { DataSource } from 'typeorm';
import { PrismaClient } from '@prisma/client';
import { PostgreSqlContainer } from '@testcontainers/postgresql';

describe('Order Service Integration', () => {
  let container: PostgreSqlContainer;
  let dataSource: DataSource;
  let prisma: PrismaClient;

  beforeAll(async () => {
    // Start test container
    container = await new PostgreSqlContainer()
      .withDatabase('testdb')
      .withUsername('test')
      .withPassword('test')
      .start();

    // Initialize connections
    const dbUrl = container.getConnectionUri();

    dataSource = new DataSource({
      type: 'postgres',
      url: dbUrl,
      entities: [__dirname + '/../**/*.entity{.ts,.js}'],
      synchronize: true
    });

    await dataSource.initialize();

    prisma = new PrismaClient({
      datasources: {
        db: { url: dbUrl }
      }
    });

    // Run migrations
    await prisma.$executeRaw`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`;
    await runMigrations(dbUrl);
  });

  afterAll(async () => {
    await dataSource.destroy();
    await prisma.$disconnect();
    await container.stop();
  });

  beforeEach(async () => {
    // Clean database
    await dataSource.query('TRUNCATE TABLE users, orders CASCADE');
  });

  describe('Order Placement', () => {
    it('should handle concurrent orders correctly', async () => {
      // Seed test data
      const org = await createTestOrganization();
      const user = await createTestUser(org.id);
      const products = await createTestProducts(org.id, 5);

      // Test concurrent orders
      const orderPromises = Array(10).fill(null).map(() =>
        orderService.placeOrder(user.id, [
          {
            variantId: products[0].variants[0].id,
            quantity: 1
          }
        ])
      );

      const results = await Promise.allSettled(orderPromises);

      // Verify inventory consistency
      const variant = await prisma.productVariant.findUnique({
        where: { id: products[0].variants[0].id }
      });

      const successfulOrders = results.filter(
        r => r.status === 'fulfilled'
      ).length;

      expect(variant.stockQuantity).toBe(
        products[0].variants[0].stockQuantity - successfulOrders
      );
    });
  });

  // Snapshot testing for complex queries
  it('should match query snapshot', async () => {
    const query = repository
      .createQueryBuilder('user')
      .getSql();

    expect(query).toMatchSnapshot();
  });
});

Performance Monitoring

ORM Performance Tracking

// Request performance monitoring setup from AI
"Add comprehensive ORM performance monitoring with:
- Query execution time tracking
- Slow query detection
- Connection pool metrics
- Memory usage patterns"

// AI provides monitoring solution:
import { Logger } from '@nestjs/common';
import { EntityManager } from 'typeorm';

// Query performance interceptor
export class QueryLogger {
  private logger = new Logger('Database');
  private slowQueryThreshold = 1000; // 1 second

  constructor(private entityManager: EntityManager) {
    this.setupLogging();
  }

  private setupLogging() {
    const queryRunner = this.entityManager.connection.createQueryRunner();

    // Override query method
    const originalQuery = queryRunner.query.bind(queryRunner);

    queryRunner.query = async (query: string, params?: any[]) => {
      const start = Date.now();
      const id = Math.random().toString(36).substring(7);

      try {
        // Log query start
        this.logger.debug(`[${id}] Starting query: ${this.truncate(query)}`);

        const result = await originalQuery(query, params);
        const duration = Date.now() - start;

        // Log completion
        if (duration > this.slowQueryThreshold) {
          this.logger.warn(
            `[${id}] Slow query detected (${duration}ms): ${query}`,
            { params, duration }
          );

          // Send to monitoring service
          await this.sendMetrics({
            type: 'slow_query',
            query,
            duration,
            params
          });
        } else {
          this.logger.debug(`[${id}] Query completed in ${duration}ms`);
        }

        return result;

      } catch (error) {
        const duration = Date.now() - start;

        this.logger.error(
          `[${id}] Query failed after ${duration}ms: ${error.message}`,
          { query, params, error }
        );

        throw error;
      }
    };
  }

  private truncate(query: string, maxLength = 200): string {
    return query.length > maxLength
      ? query.substring(0, maxLength) + '...'
      : query;
  }
}

// Prisma performance middleware
export function setupPrismaMonitoring(prisma: PrismaClient) {
  // Query metrics
  prisma.$use(async (params, next) => {
    const start = Date.now();

    try {
      const result = await next(params);
      const duration = Date.now() - start;

      // Track metrics
      metrics.histogram('prisma.query.duration', duration, {
        model: params.model,
        action: params.action
      });

      if (duration > 1000) {
        logger.warn('Slow Prisma query', {
          model: params.model,
          action: params.action,
          duration,
          args: params.args
        });
      }

      return result;

    } catch (error) {
      metrics.increment('prisma.query.error', {
        model: params.model,
        action: params.action,
        error: error.constructor.name
      });

      throw error;
    }
  });

  // Connection pool monitoring
  setInterval(async () => {
    const pool = await prisma.$metrics.json();

    metrics.gauge('prisma.pool.active', pool.counters.find(
      c => c.key === 'prisma_pool_active_connections'
    )?.value || 0);

    metrics.gauge('prisma.pool.idle', pool.counters.find(
      c => c.key === 'prisma_pool_idle_connections'
    )?.value || 0);

    metrics.gauge('prisma.pool.wait', pool.counters.find(
      c => c.key === 'prisma_pool_wait_count'
    )?.value || 0);
  }, 30000); // Every 30 seconds
}

// Memory usage tracking
export class ORMMemoryMonitor {
  private baseline: NodeJS.MemoryUsage;

  constructor() {
    this.baseline = process.memoryUsage();
    this.startMonitoring();
  }

  private startMonitoring() {
    setInterval(() => {
      const current = process.memoryUsage();
      const heapDelta = current.heapUsed - this.baseline.heapUsed;

      if (heapDelta > 100 * 1024 * 1024) { // 100MB increase
        logger.warn('High memory usage detected', {
          heapUsed: Math.round(current.heapUsed / 1024 / 1024) + 'MB',
          heapDelta: Math.round(heapDelta / 1024 / 1024) + 'MB',
          external: Math.round(current.external / 1024 / 1024) + 'MB'
        });
      }

      metrics.gauge('orm.memory.heap', current.heapUsed);
      metrics.gauge('orm.memory.external', current.external);
    }, 60000); // Every minute
  }
}

Best Practices Summary

ORM Development with AI

Key Takeaways:

1. Schema Design: Use AI to generate normalized schemas with proper relationships, indexes, and constraints
2. Type Safety: Leverage AI for generating type-safe queries and catching errors at compile time
3. Performance: Let AI identify N+1 queries, suggest eager loading, and optimize batch operations
4. Testing: Generate comprehensive test suites including edge cases and performance benchmarks
5. Monitoring: Implement robust logging and metrics to track ORM performance in production

Common Patterns:

• Repository pattern for complex queries
• Unit of Work for transaction management
• Query builders for dynamic queries
• Migration strategies for zero-downtime deployments
• Caching layers for read-heavy operations

Anti-patterns to Avoid:

• Over-fetching data (select *)
• N+1 queries without eager loading
• Missing indexes on foreign keys
• Transactions that are too large
• Ignoring connection pool limits

Security First

Always validate AI-generated queries for SQL injection vulnerabilities. Use parameterized queries, validate inputs, and never concatenate user input directly into queries. Review AI suggestions for proper authentication and authorization checks before implementing.