Introduction 🎯

Database design is where most backend engineers make mistakes that haunt them for years. You ship a feature, it works great with 100 users. Then at 10,000 users, queries start timing out, your database locks up, and you’re frantically rewriting migrations at 2 AM.

This guide covers the decisions you need to make before you build—and how to fix the ones you got wrong. Whether you’re using TypeORM, Prisma, or raw SQL, the principles are identical.

The difference between a database that groans under load and one that scales gracefully isn’t luck. It’s deliberate design.

Section 1: 🏗️ Schema Design Fundamentals

Why it matters: Your schema is your system’s skeleton. Get it wrong, and you’ll be breaking bones to fix it later.

The First Question: What Are You Actually Storing?

Before you write any schema, answer these questions:

1. What entities exist? (Users, Orders, Products, Comments)
2. How do they relate? (One-to-many, many-to-many, hierarchical)
3. What queries will be common? (This determines your indexes)
4. What constraints are non-negotiable? (Foreign keys, uniqueness)

❌ Bad approach: Start coding, add tables as you need them.

✅ Good approach: Sketch your schema on paper first. Ask “what queries will be slow?”

Core Entities & Relationships

Let’s build a real-world e-commerce system: Users → Orders → Items → Products.

With Prisma:

// schema.prisma
model User {
  id        Int     @id @default(autoincrement())
  email     String  @unique
  password  String
  name      String
  role      String  @default("user") // admin | user | seller
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  orders        Order[]
  reviews       Review[]
  sellerProducts Product[]
  addresses     Address[]
  
  @@index([email])
  @@index([role])
}

model Product {
  id          Int     @id @default(autoincrement())
  sku         String  @unique
  name        String
  description String  @db.Text
  price       Decimal @db.Decimal(10, 2)
  inventory   Int     @default(0)
  sellerId    Int     // Foreign key
  categoryId  Int

  // Seller must exist
  seller      User     @relation("SellerProducts", fields: [sellerId], references: [id], onDelete: Cascade)
  category    Category @relation(fields: [categoryId], references: [id])
  
  // Relations
  orderItems  OrderItem[]
  reviews     Review[]
  
  @@index([sellerId])
  @@index([categoryId])
  @@index([sku])
}

model Category {
  id   Int    @id @default(autoincrement())
  name String @unique
  
  products Product[]
}

model Order {
  id        Int     @id @default(autoincrement())
  userId    Int
  status    String  @default("pending") // pending | processing | shipped | delivered | cancelled
  total     Decimal @db.Decimal(10, 2)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  user      User        @relation(fields: [userId], references: [id], onDelete: Cascade)
  items     OrderItem[]
  shipment  Shipment?
  
  @@index([userId])
  @@index([status])
  @@index([createdAt])
}

model OrderItem {
  id        Int     @id @default(autoincrement())
  orderId   Int
  productId Int
  quantity  Int     @default(1)
  price     Decimal @db.Decimal(10, 2) // Price at time of order (snapshot)

  order     Order   @relation(fields: [orderId], references: [id], onDelete: Cascade)
  product   Product @relation(fields: [productId], references: [id])
  
  @@unique([orderId, productId])
  @@index([orderId])
  @@index([productId])
}

model Shipment {
  id        Int     @id @default(autoincrement())
  orderId   Int     @unique // One shipment per order
  trackingId String  @unique
  carrier   String
  status    String  @default("pending")
  createdAt DateTime @default(now())

  order     Order   @relation(fields: [orderId], references: [id], onDelete: Cascade)
  
  @@index([trackingId])
}

model Review {
  id        Int     @id @default(autoincrement())
  userId    Int
  productId Int
  rating    Int     @db.SmallInt // 1-5
  text      String  @db.Text
  createdAt DateTime @default(now())

  user      User    @relation(fields: [userId], references: [id], onDelete: Cascade)
  product   Product @relation(fields: [productId], references: [id], onDelete: Cascade)
  
  @@unique([userId, productId])
  @@index([productId])
  @@index([rating])
}

model Address {
  id       Int    @id @default(autoincrement())
  userId   Int
  street   String
  city     String
  state    String
  zip      String
  country  String
  isDefault Boolean @default(false)

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

With TypeORM:

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  ManyToOne,
  OneToMany,
  OneToOne,
  ManyToMany,
  JoinColumn,
  Index,
  Unique,
} from "typeorm";

@Entity("users")
@Index(["email"])
@Index(["role"])
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  email: string;

  @Column()
  password: string;

  @Column()
  name: string;

  @Column({ default: "user" })
  role: "admin" | "user" | "seller";

  @Column({ type: "timestamp", default: () => "CURRENT_TIMESTAMP" })
  createdAt: Date;

  @Column({
    type: "timestamp",
    default: () => "CURRENT_TIMESTAMP",
    onUpdate: "CURRENT_TIMESTAMP",
  })
  updatedAt: Date;

  // Relations
  @OneToMany(() => Order, (order) => order.user, { cascade: true })
  orders: Order[];

  @OneToMany(() => Review, (review) => review.user, { cascade: true })
  reviews: Review[];

  @OneToMany(() => Product, (product) => product.seller, { cascade: true })
  sellerProducts: Product[];

  @OneToMany(() => Address, (address) => address.user, { cascade: true })
  addresses: Address[];
}

@Entity("products")
@Index(["sellerId"])
@Index(["categoryId"])
@Index(["sku"])
@Unique(["sku"])
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  sku: string;

  @Column()
  name: string;

  @Column({ type: "text" })
  description: string;

  @Column({ type: "decimal", precision: 10, scale: 2 })
  price: number;

  @Column({ default: 0 })
  inventory: number;

  @Column()
  sellerId: number;

  @Column()
  categoryId: number;

  @ManyToOne(() => User, (user) => user.sellerProducts, {
    onDelete: "CASCADE",
  })
  @JoinColumn({ name: "sellerId" })
  seller: User;

  @ManyToOne(() => Category, (category) => category.products)
  @JoinColumn({ name: "categoryId" })
  category: Category;

  @OneToMany(() => OrderItem, (item) => item.product)
  orderItems: OrderItem[];

  @OneToMany(() => Review, (review) => review.product)
  reviews: Review[];
}

@Entity("categories")
@Unique(["name"])
export class Category {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @OneToMany(() => Product, (product) => product.category)
  products: Product[];
}

@Entity("orders")
@Index(["userId"])
@Index(["status"])
@Index(["createdAt"])
export class Order {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @Column({ default: "pending" })
  status: "pending" | "processing" | "shipped" | "delivered" | "cancelled";

  @Column({ type: "decimal", precision: 10, scale: 2 })
  total: number;

  @Column({ type: "timestamp", default: () => "CURRENT_TIMESTAMP" })
  createdAt: Date;

  @Column({
    type: "timestamp",
    default: () => "CURRENT_TIMESTAMP",
    onUpdate: "CURRENT_TIMESTAMP",
  })
  updatedAt: Date;

  @ManyToOne(() => User, (user) => user.orders, { onDelete: "CASCADE" })
  @JoinColumn({ name: "userId" })
  user: User;

  @OneToMany(() => OrderItem, (item) => item.order, { cascade: true })
  items: OrderItem[];

  @OneToOne(() => Shipment, (shipment) => shipment.order)
  shipment: Shipment;
}

@Entity("order_items")
@Index(["orderId"])
@Index(["productId"])
@Unique(["orderId", "productId"])
export class OrderItem {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  orderId: number;

  @Column()
  productId: number;

  @Column({ default: 1 })
  quantity: number;

  @Column({ type: "decimal", precision: 10, scale: 2 })
  price: number; // Snapshot of price at order time

  @ManyToOne(() => Order, (order) => order.items, { onDelete: "CASCADE" })
  @JoinColumn({ name: "orderId" })
  order: Order;

  @ManyToOne(() => Product)
  @JoinColumn({ name: "productId" })
  product: Product;
}

@Entity("shipments")
@Index(["trackingId"])
@Unique(["orderId"])
export class Shipment {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  orderId: number;

  @Column()
  trackingId: string;

  @Column()
  carrier: string;

  @Column({ default: "pending" })
  status: string;

  @Column({ type: "timestamp", default: () => "CURRENT_TIMESTAMP" })
  createdAt: Date;

  @OneToOne(() => Order, (order) => order.shipment, {
    onDelete: "CASCADE",
  })
  @JoinColumn({ name: "orderId" })
  order: Order;
}

@Entity("reviews")
@Index(["productId"])
@Index(["rating"])
@Unique(["userId", "productId"])
export class Review {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @Column()
  productId: number;

  @Column({ type: "smallint" })
  rating: number; // 1-5

  @Column({ type: "text" })
  text: string;

  @Column({ type: "timestamp", default: () => "CURRENT_TIMESTAMP" })
  createdAt: Date;

  @ManyToOne(() => User, (user) => user.reviews, { onDelete: "CASCADE" })
  @JoinColumn({ name: "userId" })
  user: User;

  @ManyToOne(() => Product, (product) => product.reviews, {
    onDelete: "CASCADE",
  })
  @JoinColumn({ name: "productId" })
  product: Product;
}

@Entity("addresses")
@Index(["userId"])
export class Address {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @Column()
  street: string;

  @Column()
  city: string;

  @Column()
  state: string;

  @Column()
  zip: string;

  @Column()
  country: string;

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

  @ManyToOne(() => User, (user) => user.addresses, { onDelete: "CASCADE" })
  @JoinColumn({ name: "userId" })
  user: User;
}

Key Design Decisions Made Here

1. Foreign Keys with CASCADE: Delete a user → automatically delete their orders
2. Unique constraints: One review per user per product (@@unique([userId, productId]))
3. Price snapshot: OrderItem.price is separate from Product.price (order history matters)
4. Indexes on foreign keys: Every FK gets an index automatically
5. Optional relationships: Shipment is optional (not all orders are shipped)

Section 2: 📐 Normalization: The Right Level

Why it matters: Under-normalized schemas explode in size. Over-normalized schemas explode in query time.

Understanding Normal Forms

First Normal Form (1NF): No repeating groups. Atomic values only.

❌ Bad (violates 1NF):

CREATE TABLE orders (
  id INT PRIMARY KEY,
  product_ids JSON,  -- Not atomic!
  quantities JSON    -- Not atomic!
);

✅ Good (follows 1NF):

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  quantity INT
);

Second Normal Form (2NF): All non-key attributes depend on the entire primary key.

❌ Bad (violates 2NF):

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  product_name STRING,  -- Depends on product_id, not order_id!
  PRIMARY KEY (order_id, product_id)
);

✅ Good (follows 2NF):

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE products (
  id INT PRIMARY KEY,
  name STRING
);

Third Normal Form (3NF): Non-key attributes depend only on the primary key, not on each other.

❌ Bad (violates 3NF):

CREATE TABLE users (
  id INT PRIMARY KEY,
  email STRING,
  city STRING,
  state STRING,
  zip STRING,  -- Depends on city/state, not user_id!
  country STRING
);

✅ Good (follows 3NF):

CREATE TABLE users (
  id INT PRIMARY KEY,
  email STRING,
  city_id INT REFERENCES cities(id)
);

CREATE TABLE cities (
  id INT PRIMARY KEY,
  name STRING,
  state STRING,
  zip STRING
);

The Normalization Trade-off: Query Complexity vs. Storage

100% normalized schema = many joins = slower queries but smaller storage. Denormalized schema = fewer joins = faster queries but data redundancy.

Rule of thumb for 2026:

• Start at 3NF (normalized)
• Selectively denormalize when profiling shows a problem
• Use materialized views or caches instead of storing duplicate data

Strategic Denormalization (When & How)

Denormalize only when you have profiling data showing the problem.

Example: Denormalizing Product Average Rating

❌ Normalized (slow for reads):

// Prisma query
const product = await prisma.product.findUnique({
  where: { id: 1 },
  include: {
    reviews: true, // Must fetch ALL reviews
  },
});

// Then calculate average in application
const avgRating = product.reviews.reduce((sum, r) => sum + r.rating, 0) / product.reviews.length;

With 100,000 reviews, this is brutal.

✅ Denormalized (fast for reads):

model Product {
  id Int @id
  name String
  
  // Denormalized fields
  avgRating Float @default(0)
  reviewCount Int @default(0)
  
  reviews Review[]
}

// Update these on every review insert/update
await prisma.$transaction([
  prisma.review.create({
    data: { userId, productId, rating, text },
  }),
  // Recalculate and update product
  prisma.product.update({
    where: { id: productId },
    data: {
      reviewCount: { increment: 1 },
      avgRating: /* raw SQL to average */ ,
    },
  }),
]);

With TypeORM:

@Entity("products")
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column({ type: "float", default: 0 })
  avgRating: number; // Denormalized

  @Column({ type: "int", default: 0 })
  reviewCount: number; // Denormalized

  @OneToMany(() => Review, (review) => review.product)
  reviews: Review[];
}

// Update hooks (using hooks or triggers)
@AfterInsert()
async recalculateRating() {
  const reviews = await this.reviews;
  const avg = reviews.reduce((sum, r) => sum + r.rating, 0) / reviews.length;
  this.avgRating = avg;
  this.reviewCount = reviews.length;
  await getRepository(Product).save(this);
}

Or better: Use a database trigger:

CREATE TRIGGER update_product_rating
AFTER INSERT ON reviews
FOR EACH ROW
BEGIN
  UPDATE products
  SET
    review_count = (SELECT COUNT(*) FROM reviews WHERE product_id = NEW.product_id),
    avg_rating = (SELECT AVG(rating) FROM reviews WHERE product_id = NEW.product_id)
  WHERE id = NEW.product_id;
END;

⚡ For solo developers: Stay at 3NF until your profiler screams. Premature denormalization causes more bugs than slow queries.

Section 3: 🔀 Relationships: 1:1, 1:N, N:N & Pivot Tables

Why it matters: How you structure relationships determines query speed, data integrity, and how easy your code becomes. Wrong choice = refactoring nightmare.

One-to-One (1:1) Relationships

One entity is associated with exactly one other entity.

Real-world examples:

• User ↔ UserProfile (one user, one profile)
• User ↔ Subscription (one active subscription per user)
• Person ↔ Passport (one passport per person)
• Order ↔ Shipment (one shipment per order)

When to use 1:1:

• Optional relationship (profile might not exist yet)
• Sensitive data separation (password in separate table)
• Infrequently accessed data (keep in separate table for query speed)

Schema design (Foreign key on dependent side - RECOMMENDED):

// Prisma
model User {
  id Int @id
  email String
  
  shipment Shipment? // Optional, zero or one
}

model Shipment {
  id Int @id
  orderId Int @unique  // UNIQUE = one-to-one
  trackingId String
  
  order Order @relation(fields: [orderId], references: [id], onDelete: Cascade)
}

// TypeORM
@Entity("shipments")
export class Shipment {
  @OneToOne(() => Order)
  @JoinColumn({ name: "orderId" })
  order: Order;
}

Query 1:1:

// Prisma - no N+1 problem
const order = await prisma.order.findUnique({
  where: { id: 1 },
  include: { shipment: true }, // Optional one-to-one
});

if (order.shipment) {
  console.log(order.shipment.trackingId);
}

One-to-Many (1:N) & Many-to-One (N:1)

One entity has multiple related entities (but each related entity belongs to only one parent).

Real-world examples:

• User → Orders (one user, many orders)
• Product → Reviews (one product, many reviews)
• Category → Products (one category, many products)
• Company → Employees (one company, many employees)

Schema:

// Prisma
model User {
  id Int @id
  email String
  
  orders Order[] // One user, many orders
}

model Order {
  id Int @id
  userId Int
  total Decimal
  
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  @@index([userId]) // CRITICAL: Index foreign keys
}

// TypeORM
@Entity("users")
export class User {
  @OneToMany(() => Order, (order) => order.user)
  orders: Order[];
}

@Entity("orders")
@Index(["userId"])
export class Order {
  @ManyToOne(() => User, (user) => user.orders)
  @JoinColumn({ name: "userId" })
  user: User;
}

Query 1:N (prevent N+1):

// ❌ Bad: N+1
const users = await prisma.user.findMany();
for (const user of users) {
  const orders = await prisma.order.findMany({
    where: { userId: user.id }, // Separate query per user
  });
}

// ✅ Good: Batch in one query
const users = await prisma.user.findMany({
  include: { orders: true }, // All user orders in one query
});

for (const user of users) {
  console.log(user.orders); // Already loaded
}

Many-to-Many (N:N) with Implicit Pivot

Entities can have multiple relationships, and both sides can have many.

Real-world examples:

• Students ↔ Courses (student enrolled in many courses, course has many students)
• Products ↔ Tags (product has many tags, tag on many products)
• Users ↔ Groups (user in many groups, group has many users)

Prisma implicit pivot (simplest):

model Student {
  id Int @id
  name String
  
  courses Course[] // Implicit junction table
}

model Course {
  id Int @id
  title String
  
  students Student[] // Implicit junction table
}

// Prisma automatically creates StudentCourse junction table

Query implicit pivot:

// Get student with all their courses
const student = await prisma.student.findUnique({
  where: { id: 1 },
  include: { courses: true },
});

console.log(student.courses); // [Course1, Course2, Course3]

// Add a course to a student
await prisma.student.update({
  where: { id: 1 },
  data: {
    courses: {
      connect: { id: 5 }, // Connect existing course
    },
  },
});

Many-to-Many (N:N) with Explicit Pivot Table

Use when you need to store extra data on the relationship itself.

Real-world examples:

• User ↔ Group with role (user has “admin” role in group A, “member” in group B)
• Product ↔ Supplier with pricing (supplier A sells product at $10, supplier B at $12)
• Employee ↔ Project with hours_allocated

Schema:

// Prisma with explicit pivot
model User {
  id Int @id
  email String
  
  groupMemberships GroupMember[]
}

model Group {
  id Int @id
  name String
  
  members GroupMember[]
}

model GroupMember {
  id Int @id
  userId Int
  groupId Int
  role String // "admin" | "member" | "moderator"
  joinedAt DateTime @default(now())
  
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
  group Group @relation(fields: [groupId], references: [id], onDelete: Cascade)
  
  @@unique([userId, groupId]) // One membership per user per group
  @@index([userId])
  @@index([groupId])
}

// TypeORM
@Entity("users")
export class User {
  @OneToMany(() => GroupMember, (gm) => gm.user)
  groupMemberships: GroupMember[];
}

@Entity("groups")
export class Group {
  @OneToMany(() => GroupMember, (gm) => gm.group)
  members: GroupMember[];
}

@Entity("group_members")
@Unique(["userId", "groupId"])
@Index(["userId"])
@Index(["groupId"])
export class GroupMember {
  @ManyToOne(() => User, (user) => user.groupMemberships)
  @JoinColumn({ name: "userId" })
  user: User;

  @ManyToOne(() => Group, (group) => group.members)
  @JoinColumn({ name: "groupId" })
  group: Group;

  @Column()
  role: string; // Extra data on the relationship

  @Column({ default: () => "CURRENT_TIMESTAMP" })
  joinedAt: Date;
}

Query explicit pivot:

// Get user with their groups and roles
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    groupMemberships: {
      include: { group: true },
    },
  },
});

console.log(user.groupMemberships);
// [
//   { groupId: 1, role: "admin", group: { id: 1, name: "Engineering" } },
//   { groupId: 2, role: "member", group: { id: 2, name: "Marketing" } }
// ]

// Add user to group with role
await prisma.groupMember.create({
  data: {
    userId: 1,
    groupId: 3,
    role: "member",
  },
});

// Update role
await prisma.groupMember.update({
  where: {
    userId_groupId: { userId: 1, groupId: 3 }, // Composite unique
  },
  data: { role: "admin" },
});

Relationship Trade-off Matrix

When to use implicit vs explicit pivot:

✅ Implicit pivot (simpler): Just connecting items, no extra data ✅ Explicit pivot (more control): Storing extra info like role, price, permissions

⚡ For solo developers: Use implicit pivots until you need to store relationship data. Then migrate to explicit. Both work well with TypeORM/Prisma.

Section 4: 📋 JSON/JSONB Columns (When Structure is Flexible)

Why it matters: Sometimes you need semi-structured data—metadata, configuration, user preferences. JSON columns let you store complex data without rigid schemas.

When to use JSON:

• User preferences (dark mode, language, notifications)
• Flexible metadata (custom fields per entity type)
• Configuration documents
• Audit trails (store what changed)
• Third-party API responses (cache before processing)
• Feature flags and experiments

When NOT to use JSON:

• If you query inside the JSON frequently (use table instead)
• If you need transactions (JSON is harder to transaction)
• If data size > 10MB per row

JSON vs JSONB (PostgreSQL)

JSON: Text storage, slower queries, smaller disk JSONB: Binary storage, faster queries, built-in indexes, compression

✅ Always use JSONB in PostgreSQL.

Schema Design with JSON

// Prisma
model User {
  id Int @id
  email String
  
  // Flexible preferences without separate table
  preferences Json @default({})
  // { "theme": "dark", "notifications": true, "language": "en" }
  
  // Store metadata
  metadata Json @default({})
  // { "lastLogin": "2026-02-01", "loginCount": 42, "source": "google" }
}

model Product {
  id Int @id
  name String
  
  // Store attributes that vary by product type
  attributes Json
  // Electronics: { "weight": 500, "warranty": 24 }
  // Clothing: { "sizes": ["S", "M", "L"], "colors": ["red", "blue"] }
}

// TypeORM
@Entity("users")
export class User {
  @Column({ type: "jsonb", default: {} })
  preferences: Record<string, any>;

  @Column({ type: "jsonb", default: {} })
  metadata: Record<string, any>;
}

Querying JSON

PostgreSQL JSONB operators:

-- Check if key exists
SELECT * FROM users WHERE preferences ? 'theme';

-- Get value
SELECT preferences ->> 'theme' as theme FROM users WHERE id = 1;
-- Output: "dark"

-- Filter by JSON value
SELECT * FROM users WHERE preferences ->> 'theme' = 'dark';

-- Check array contains
SELECT * FROM products WHERE attributes ->> 'colors' @> '"red"';

With Prisma:

// For simple access:
const user = await prisma.user.findUnique({ where: { id: 1 } });
const theme = user.preferences.theme; // Works, but no DB filtering

// For complex queries, use raw SQL:
const darkModeUsers = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE preferences ->> 'theme' = 'dark'
`;

With TypeORM:

const darkModeUsers = await getRepository(User)
  .createQueryBuilder("user")
  .where("user.preferences->>'theme' = :theme", { theme: "dark" })
  .getMany();

Real-world Examples

User Preferences (No Table Bloat):

model User {
  id Int @id
  email String
  
  preferences Json @default({
    "theme": "light",
    "notifications": {
      "email": true,
      "sms": false,
      "push": true
    },
    "language": "en",
    "timezone": "UTC"
  })
}

// Update preference
await prisma.user.update({
  where: { id: 1 },
  data: {
    preferences: {
      theme: "dark",
      notifications: { email: false, sms: true, push: true },
    },
  },
});

Audit Trail (Track Changes):

model AuditLog {
  id Int @id
  entityType String // "User", "Product", "Order"
  entityId Int
  action String // "create", "update", "delete"
  
  // Store the change
  changes Json // { "email": { "old": "john@old.com", "new": "john@new.com" } }
  
  userId Int
  timestamp DateTime @default(now())
}

Dynamic Attributes (E-commerce):

// Better: Use explicit attributes table (queryable)
model Product {
  id Int @id
  name String
  
  attributes ProductAttribute[]
}

model ProductAttribute {
  id Int @id
  productId Int
  key String // "cpu", "size", "color"
  value String // "Intel i7", "M", "blue"
  
  @@unique([productId, key])
  @@index([productId])
}

// Queryable: filter products by attribute
SELECT DISTINCT p.* FROM products p
JOIN product_attributes pa ON p.id = pa.product_id
WHERE pa.key = 'cpu' AND pa.value = 'Intel i7';

JSON Performance Tips

1. Create indexes on JSON keys:

CREATE INDEX idx_user_preferences_theme
  ON users USING gin (preferences);

-- Now filtering by preferences is fast
SELECT * FROM users WHERE preferences ->> 'theme' = 'dark';

2. Validate JSON structure at application level:

import { z } from "zod";

const PreferencesSchema = z.object({
  theme: z.enum(["light", "dark"]),
  notifications: z.object({
    email: z.boolean(),
    sms: z.boolean(),
  }),
  language: z.string(),
});

// Update with validation
const validated = PreferencesSchema.parse(newPreferences);
await prisma.user.update({
  where: { id: 1 },
  data: { preferences: validated },
});

3. Keep JSON flat (don’t nest deeply):

// ❌ Hard to query
{ user: { profile: { settings: { theme: "dark" } } } }

// ✅ Flat is better
{ userTheme: "dark", userLanguage: "en" }

⚡ For solo developers: JSON is great for “nice to have” features (preferences, metadata). For core queryable features, use tables.

Section 5: 🔗 The N+1 Query Problem (Your Biggest Enemy)

Why it matters: N+1 queries are the #1 performance killer in backend systems. One innocent query can become 10,000.

Understanding N+1

❌ N+1 Problem:

// Prisma
const orders = await prisma.order.findMany();

for (const order of orders) {
  // ⚠️ This runs ONCE for each order (N queries)
  const user = await prisma.user.findUnique({
    where: { id: order.userId },
  });
  console.log(order.id, user.name);
}

// Database queries:
// SELECT * FROM orders;           -- 1 query
// SELECT * FROM users WHERE id = 1;    -- 2nd query
// SELECT * FROM users WHERE id = 2;    -- 3rd query
// ... (repeated for each order)
// Total: 1 + N queries ❌

✅ Fixed with includes/relations:

// Prisma - use include/select
const orders = await prisma.order.findMany({
  include: {
    user: true, // Fetch user data with the order
  },
});

// Database queries:
// SELECT * FROM orders;
// SELECT * FROM users WHERE id IN (1, 2, 3, ...);
// Total: 2 queries ✅

for (const order of orders) {
  console.log(order.id, order.user.name); // Data is already there
}

With TypeORM:

// ❌ N+1 Problem
const orders = await getRepository(Order).find();

for (const order of orders) {
  // Separate query for each user
  const user = await getRepository(User).findOne(order.userId);
  console.log(order.id, user.name);
}

// ✅ Fixed with leftJoinAndSelect or eager relations
const orders = await getRepository(Order)
  .createQueryBuilder("order")
  .leftJoinAndSelect("order.user", "user")
  .getMany();

for (const order of orders) {
  console.log(order.id, order.user.name); // User already loaded
}

Nested N+1 (Even More Insidious)

❌ Triple Query Problem:

const orders = await prisma.order.findMany({
  include: {
    items: true, // Get items for each order
  },
});

// ⚠️ Now you query for product for each item
for (const order of orders) {
  for (const item of order.items) {
    const product = await prisma.product.findUnique({
      where: { id: item.productId },
    });
    // Process item + product
  }
}

// Result: 1 + N + (N * M) queries ❌ DISASTER

✅ Fixed with nested includes:

const orders = await prisma.order.findMany({
  include: {
    items: {
      include: {
        product: true, // Include product in items
      },
    },
  },
});

// Database queries:
// SELECT * FROM orders;
// SELECT * FROM order_items WHERE order_id IN (...);
// SELECT * FROM products WHERE id IN (...);
// Total: 3 queries (constant, regardless of depth)

for (const order of orders) {
  for (const item of order.items) {
    console.log(item.product.name); // Already loaded
  }
}

With TypeORM:

const orders = await getRepository(Order)
  .createQueryBuilder("order")
  .leftJoinAndSelect("order.items", "items")
  .leftJoinAndSelect("items.product", "product")
  .getMany();

// One query with JOIN, zero N+1

Detecting N+1 in Development

With Prisma:

// Enable query logging
const prisma = new PrismaClient({
  log: ["query"],
});

// Output shows every query:
// prisma:query SELECT ... FROM orders;
// prisma:query SELECT ... FROM users WHERE id = 1;
// prisma:query SELECT ... FROM users WHERE id = 2;

With TypeORM:

const dataSource = new DataSource({
  logging: ["query"],
});

// Output shows every query with timing

Best: Use New Relic or DataDog

import NewRelic from "newrelic";

// Track database calls
NewRelic.recordMetric("db.query.count", 1);
NewRelic.recordMetric("db.query.time", duration);

Batch Loading Pattern (For Complex Cases)

When you can’t use include, use batch loading:

import DataLoader from "dataloader";

// Create a loader that batches queries
const userLoader = new DataLoader(async (userIds) => {
  // Single query for multiple IDs
  const users = await prisma.user.findMany({
    where: { id: { in: userIds } },
  });
  
  // Return in same order as requested
  return userIds.map(id => users.find(u => u.id === id));
});

// Usage (in a loop):
for (const order of orders) {
  // Batches all user fetches together
  const user = await userLoader.load(order.userId);
  console.log(order.id, user.name);
}

⚡ For solo developers: Every time you write a query, ask: “What else do I need?” If you need related data, include it in one go.

Section 6: 🎯 Indexing Strategy (The Performance Multiplier)

Why it matters: Right indexes = 10x faster queries. Wrong indexes = wasted storage and slow writes.

How Indexes Work

An index is a sorted copy of specific columns. Databases use them to avoid scanning every row.

-- Without index: scan all 1M rows
SELECT * FROM orders WHERE user_id = 42;  -- O(N) = 1M comparisons

-- With index: binary search on 1M rows
CREATE INDEX idx_orders_user_id ON orders(user_id);
SELECT * FROM orders WHERE user_id = 42;  -- O(log N) = ~20 comparisons

Index Types

1. Single-Column Index (Most Common)

// Prisma
model Order {
  id Int @id
  userId Int
  status String
  
  @@index([userId])  // Index for user lookups
  @@index([status])  // Index for filtering by status
}

// TypeORM
@Entity("orders")
@Index(["userId"])
@Index(["status"])
export class Order { ... }

2. Composite Index (Multi-Column)

When you filter on multiple columns together:

// Prisma
model Order {
  id Int @id
  userId Int
  status String
  createdAt DateTime
  
  // Index for queries like:
  // WHERE user_id = ? AND status = ? AND created_at > ?
  @@index([userId, status, createdAt])
}

// Query that uses this index:
const orders = await prisma.order.findMany({
  where: {
    userId: 42,
    status: "pending",
    createdAt: { gt: new Date("2026-01-01") },
  },
});

3. Unique Index

// Prisma (automatic)
model User {
  id Int @id
  email String @unique  // Creates unique index
  phone String @unique
}

// Explicit with TypeORM
@Entity("users")
@Index(["email"], { unique: true })
export class User {
  @Column({ unique: true })
  email: string;
}

4. Covering Index (Include Non-Key Columns)

For queries where the index has all needed data:

-- Query: SELECT email, name FROM users WHERE user_id = 42;
-- Without covering index: use index to find row, then fetch full row
-- With covering index: index has everything, no second fetch

CREATE INDEX idx_users_covering 
  ON users(user_id) 
  INCLUDE (email, name);  -- PostgreSQL 11+

5. Partial Index (Conditional)

Only index rows matching a condition:

// Prisma (use raw migration)
// Only index active orders (status != 'deleted')
await prisma.$executeRaw`
  CREATE INDEX idx_active_orders
    ON orders(user_id)
    WHERE status != 'deleted';
`;

// Benefits:
// - Smaller index (less storage)
// - Faster inserts (less index to update)
// - Better for soft deletes

What NOT to Index

❌ Don’t index:

• Low cardinality columns (mostly same values)
• Columns you never filter on
• Columns that change frequently
• Very large columns (TEXT, JSON)

❌ Don’t create too many indexes:

• Every index slows down writes
• More indexes = more RAM needed
• Slow migrations

Index Design Strategy

Start with these questions:

1. What are your slow queries? (Use EXPLAIN ANALYZE)
2. What columns are in the WHERE clause?
3. What order are they queried in? (matters for composite indexes)

Composite Index Column Order:

-- Query: WHERE user_id = ? AND status = ? AND created_at > ?

-- ✅ Best index (matches query order):
CREATE INDEX idx_orders ON orders(user_id, status, created_at);

-- ✅ Also works:
CREATE INDEX idx_orders ON orders(status, user_id, created_at);

-- ❌ Bad (created_at can't be used efficiently):
CREATE INDEX idx_orders ON orders(created_at, user_id, status);

Analyzing Query Performance

With PostgreSQL EXPLAIN:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Output:
-- Seq Scan on orders  (cost=0.00..35000.00 rows=1000)
--   Filter: (user_id = 42 AND status = 'pending')
-- ❌ Sequential scan = bad, need index

-- After creating index:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Output:
-- Index Scan using idx_orders_user_id on orders
-- ✅ Index scan = good

In TypeORM:

const [result] = await getRepository(Order).query(`
  EXPLAIN ANALYZE
  SELECT * FROM orders WHERE user_id = $1 AND status = $2
`, [42, 'pending']);

console.log(result); // Shows execution plan

Index Monitoring

// Find unused indexes (wastes space and slows writes)
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

// Result with idx_scan = 0 = never used, consider dropping

// Find missing indexes (slow queries that could be indexed)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
WHERE mean_time > 100  -- Queries taking >100ms
ORDER BY mean_time DESC;

⚡ For solo developers: Profile first, then index. Most apps need 5-10 indexes total. More than that is premature optimization.

Section 7: 🔐 Constraints & Data Integrity

Why it matters: Constraints catch bugs in your application code. They’re your safety net.

Primary Keys (Always Auto-Increment)

// Prisma
model User {
  id Int @id @default(autoincrement())
  // ...
}

// TypeORM
@Entity("users")
export class User {
  @PrimaryGeneratedColumn()
  id: number;
}

// SQL (explicit)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  -- ...
);

Foreign Keys (Prevent Orphaned Data)

// Prisma
model Order {
  id Int @id
  userId Int
  
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}

// TypeORM
@Entity("orders")
export class Order {
  @ManyToOne(() => User, { onDelete: "CASCADE" })
  @JoinColumn({ name: "userId" })
  user: User;
}

// SQL
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  -- ...
);

ON DELETE behaviors:

• CASCADE: Delete order when user is deleted ✅ (most common)
• SET NULL: Set user_id to NULL (only if nullable) ⚠️
• RESTRICT: Prevent deletion if orders exist ❌ (don’t use, causes issues)
• NO ACTION: Same as RESTRICT but checked at transaction end

Unique Constraints (Prevent Duplicates)

Unique constraints ensure only one row matches a condition. Critical for enforcing business rules without application code.

When to use:

• Email addresses (no duplicate accounts)
• Usernames (platform identity)
• SKU/barcode (product uniqueness)
• External IDs (3rd-party integrations)
• Slug URLs (SEO-friendly URLs)

Single-column unique:

// Prisma
model User {
  id Int @id
  email String @unique      // One user per email
  username String @unique   // One user per username
  externalId String @unique // One user per external system ID
  slug String @unique       // One user per slug
}

// TypeORM
@Entity("users")
@Index(["email"], { unique: true })
@Index(["username"], { unique: true })
@Index(["externalId"], { unique: true })
export class User {
  @Column({ unique: true })
  email: string;

  @Column({ unique: true })
  username: string;

  @Column({ unique: true })
  externalId: string;
}

// SQL
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR UNIQUE NOT NULL,
  username VARCHAR UNIQUE NOT NULL,
  external_id VARCHAR UNIQUE,
  slug VARCHAR UNIQUE
);

Composite unique (multiple columns together):

Ensure a combination is unique (but individual columns can repeat).

// Prisma - One review per user per product
model Review {
  id Int @id
  userId Int
  productId Int
  rating Int
  
  // Can't have two reviews from same user on same product
  @@unique([userId, productId])
}

// TypeORM
@Entity("reviews")
@Unique(["userId", "productId"])
export class Review {
  @Column()
  userId: number;

  @Column()
  productId: number;
}

// SQL
CREATE TABLE reviews (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  CONSTRAINT unique_user_product UNIQUE (user_id, product_id)
);

Conditional unique (only for active rows):

Use with soft deletes: allow multiple deleted records with same unique value.

-- PostgreSQL: unique partial index
CREATE UNIQUE INDEX idx_active_email
  ON users(email)
  WHERE deleted_at IS NULL;

-- Now you can have multiple deleted users with same email
-- But at most one active user per email

Handling unique constraint violations:

import { PrismaClientKnownRequestError } from "@prisma/client";

try {
  const user = await prisma.user.create({
    data: { email: "john@example.com", username: "john" },
  });
} catch (error) {
  if (error instanceof PrismaClientKnownRequestError) {
    if (error.code === "P2002") {
      // Unique constraint violation
      const field = error.meta?.target?.[0]; // Which field?
      console.error(`${field} already exists`);
    }
  }
}

⚠️ Race condition gotcha:

// ❌ UNSAFE: Check then insert (TOCTOU bug)
const existing = await prisma.user.findUnique({ where: { email } });
if (!existing) {
  await prisma.user.create({ data: { email } });
  // Another request might create it between check and insert!
}

// ✅ SAFE: Let database enforce uniqueness
try {
  await prisma.user.create({ data: { email } });
} catch (error) {
  if (error.code === "P2002") {
    // Someone else created it first, that's fine
  }
}

Check Constraints (Validate Values)

// Prisma (limited support, use raw SQL)
model Product {
  id Int @id
  price Decimal @db.Decimal(10, 2)
  inventory Int
  
  // Add check constraint via migration
}

// TypeORM
@Entity("products")
export class Product {
  @Column({
    type: "decimal",
    precision: 10,
    scale: 2,
  })
  price: number;

  @Column({
    type: "int",
    comment: "Must be >= 0",
  })
  inventory: number;
}

// SQL (explicit)
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
  inventory INT NOT NULL CHECK (inventory >= 0),
  -- ...
);

NOT NULL Constraints

// Prisma
model Order {
  id Int @id
  userId Int        // NOT NULL (default)
  status String     // NOT NULL (default)
  optional String?  // Nullable
}

// TypeORM
@Column({ nullable: false })
userId: number;

@Column({ nullable: true })
optional: string;

// SQL
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  status VARCHAR NOT NULL,
  optional VARCHAR NULL,
);

Default Values (Prevent NULL)

// Prisma
model Order {
  id Int @id @default(autoincrement())
  status String @default("pending")
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  isArchived Boolean @default(false)
}

// TypeORM
@Column({ default: "pending" })
status: string;

@Column({ type: "timestamp", default: () => "CURRENT_TIMESTAMP" })
createdAt: Date;

// SQL
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  status VARCHAR DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_archived BOOLEAN DEFAULT FALSE,
);

⚡ For solo developers: Use constraints liberally. They’re your CI/CD for the database. A constraint violation in production beats discovering a bug from user data months later.

Section 8: 🏢 Big Enterprises: Patterns at Scale

Why it matters: At scale, you hit problems most developers never see. These patterns solve them.

Soft Deletes (Don’t Actually Delete)

Hard delete loses data and breaks foreign keys. Soft delete is safer:

// Prisma
model User {
  id Int @id
  email String
  deletedAt DateTime? // NULL = active, non-null = deleted
  
  @@index([deletedAt])
}

// Query only active users
const activeUsers = await prisma.user.findMany({
  where: {
    deletedAt: null,
  },
});

// Soft delete
await prisma.user.update({
  where: { id: 1 },
  data: { deletedAt: new Date() },
});

// Restore
await prisma.user.update({
  where: { id: 1 },
  data: { deletedAt: null },
});

Problem: You now have to filter deletedAt in EVERY query.

Solution: Use Views or Triggers

-- Create a view with soft delete filter built-in
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;

-- Now query the view instead
SELECT * FROM active_users WHERE id = 42;

-- Or use triggers to hide deleted rows
CREATE TRIGGER hide_deleted_users
BEFORE SELECT ON users
FOR EACH ROW
BEGIN
  IF NEW.deleted_at IS NOT NULL THEN
    SKIP ROW;  -- Exclude from result
  END IF;
END;

Temporal Tables (Audit History)

Track every change to important tables:

// Prisma (requires manual setup)
model User {
  id Int @id
  email String
  name String
  
  // Audit fields
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  createdBy Int?
  updatedBy Int?
}

model UserAudit {
  id Int @id @default(autoincrement())
  userId Int
  email String
  name String
  action String // "created" | "updated" | "deleted"
  changedFields Json // What changed
  changedBy Int?
  changedAt DateTime @default(now())
}

// Trigger to auto-create audit entries
@AfterUpdate()
@AfterInsert()
async createAuditEntry() {
  const audit = new UserAudit();
  audit.userId = this.id;
  audit.action = "updated";
  audit.changedFields = { /* diff */ };
  await getRepository(UserAudit).save(audit);
}

Or better: PostgreSQL built-in temporal tables:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR,
  name VARCHAR,
  valid_from TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  valid_to TIMESTAMP,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH SYSTEM VERSIONING;

-- Query history
SELECT * FROM users
  FOR SYSTEM_TIME BETWEEN '2026-01-01' AND '2026-02-01'
  WHERE id = 1;

Partitioning (For Massive Tables)

When a table gets too large (>100GB), partition it:

-- Partition orders by year (each year gets its own table)
CREATE TABLE orders (
  id SERIAL,
  user_id INT,
  created_at TIMESTAMP,
  total DECIMAL
) PARTITION BY RANGE (YEAR(created_at));

CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM (2024) TO (2025);

CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM (2025) TO (2026);

-- Queries automatically use the right partition
SELECT * FROM orders WHERE created_at >= '2025-01-01';
-- Only scans orders_2025, ignoring orders_2024

Benefits:

• Faster queries (smaller partitions)
• Faster deletes (drop entire partition)
• Parallel index maintenance

Sharding (When You Outgrow One Database)

For millions of users, shard by user_id:

┌─────────────────┐
│  Load Balancer  │
└────────┬────────┘
         │
    ┌────┴────┐
    ▼         ▼
┌────────┐  ┌────────┐
│ Shard 1│  │ Shard 2│
│users   │  │users   │
│1-50M   │  │50-100M │
└────────┘  └────────┘

// Determine which shard a user belongs to
function getShardId(userId: number, totalShards: number = 2): number {
  return userId % totalShards;
}

const shards = [
  new Pool({ host: "shard-1.db.com" }),
  new Pool({ host: "shard-2.db.com" }),
];

// Route query to correct shard
const shardId = getShardId(userId);
const shard = shards[shardId];

const user = await shard.query(
  "SELECT * FROM users WHERE id = $1",
  [userId]
);

Trade-offs:

• ✅ Scales to billions of rows
• ❌ Cross-shard queries are hard
• ❌ Rebalancing is painful

Only shard when you absolutely have to (100GB+ database, millions of QPS).

Columnar Storage (For Analytics)

OLTP (Online Transactional Processing) vs OLAP (Online Analytical Processing):

OLTP (Your API):          OLAP (Analytics):
├─ Optimized for writes   ├─ Optimized for reads
├─ Row-based storage      ├─ Column-based storage
├─ PostgreSQL             └─ ClickHouse, Redshift,
└─ Fast inserts              Vertica, DuckDB

For analytics, use a columnar database:

// PostgreSQL (primary) → ClickHouse (analytics)
import { createClient } from "@clickhouse/client";

const clickhouse = createClient({
  host: "analytics.clickhouse.com",
});

// Copy data from PostgreSQL to ClickHouse (periodically)
async function syncAnalytics() {
  const events = await prisma.event.findMany({
    where: { synced: false },
  });
  
  await clickhouse.insert({
    table: "events",
    values: events,
    format: "JSONEachRow",
  });
}

// Queries
const stats = await clickhouse.query({
  query: `
    SELECT COUNT(*) as total, AVG(price) as avg_price
    FROM events
    WHERE created_at >= '2026-01-01'
  `,
});

Caching Patterns

1. Cache-Aside (Most Common)

const redis = new Redis();

async function getUser(userId: number) {
  // Try cache first
  const cached = await redis.get(`user:${userId}`);
  if (cached) return JSON.parse(cached);
  
  // Cache miss, hit database
  const user = await prisma.user.findUnique({
    where: { id: userId },
  });
  
  // Store in cache (1 hour TTL)
  await redis.setex(`user:${userId}`, 3600, JSON.stringify(user));
  
  return user;
}

// Invalidate on update
await prisma.user.update({
  where: { id: userId },
  data: { name: "New Name" },
});
await redis.del(`user:${userId}`); // Bust cache

2. Write-Through Cache

// Update both simultaneously
async function updateUser(userId: number, data: any) {
  const updated = await prisma.user.update({
    where: { id: userId },
    data,
  });
  
  // Write to cache
  await redis.setex(`user:${userId}`, 3600, JSON.stringify(updated));
  
  return updated;
}

3. Write-Behind Cache (Dangerous But Fast)

// Write to cache immediately, sync database later
async function updateUserFast(userId: number, data: any) {
  // Immediate response to user
  await redis.setex(`user:${userId}`, 3600, JSON.stringify(data));
  
  // Async database update
  asyncQueue.add(async () => {
    await prisma.user.update({
      where: { id: userId },
      data,
    });
  });
  
  return { success: true };
}

// Problem: If worker crashes, data is lost. Only use for non-critical data.

⚡ For solo developers: Cache-aside is safe and effective. Implement it when profiling shows hot rows. Don’t cache everything.

Section 9: ⚠️ Common Mistakes & How to Avoid Them

Mistake 1: Missing Indexes on Foreign Keys

❌ Bad:

model Order {
  id Int @id
  userId Int
  // No index on userId!
  
  user User @relation(fields: [userId], references: [id])
}

Every query filtering by userId scans all orders. Disaster at scale.

✅ Good:

model Order {
  id Int @id
  userId Int
  
  @@index([userId])  // Explicit index
  user User @relation(fields: [userId], references: [id])
}

Mistake 2: SELECT * (Loading Everything)

❌ Bad:

const users = await prisma.user.findMany();
// Fetches id, email, password, phone, ssn, preferences, etc.
// Wastes network bandwidth, memory

✅ Good:

const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    name: true,
    // Only what you need
  },
});

Mistake 3: No Pagination

❌ Bad:

const allOrders = await prisma.order.findMany(); // Fetches 10 million rows!

✅ Good:

const orders = await prisma.order.findMany({
  skip: (pageNum - 1) * 50,
  take: 50,
});

Mistake 4: Storing Passwords, API Keys, or PII in Logs

❌ Bad:

logger.info({ user: userData, order: orderData }); // Logs everything!

✅ Good:

logger.info({
  userId: userData.id,
  orderId: orderData.id,
  // Never log sensitive fields
});

Mistake 5: No Rollback Strategy for Migrations

❌ Bad:

// Migration that can't be reversed
async function up() {
  await db.schema.dropTable("legacy_users");
}

// No down() function!

✅ Good:

async function up() {
  await db.schema.createTable("users_v2", (t) => {
    t.increments("id");
    t.string("email");
  });
  // Copy data from old table
}

async function down() {
  // Fully reversible
  await db.schema.dropTable("users_v2");
}

Mistake 6: Storing Computed Values Instead of Computing Them

❌ Bad:

model Product {
  id Int @id
  price Decimal
  tax Decimal  // Computed: price * 0.1
  total Decimal  // Computed: price + tax
  
  // Now if you update price, you must update tax AND total
  // Easy to get out of sync
}

✅ Good:

model Product {
  id Int @id
  price Decimal
  
  // Compute on the fly
  get tax() { return this.price * 0.1; }
  get total() { return this.price + this.tax; }
}

Or use database views:

CREATE VIEW product_totals AS
SELECT
  id,
  price,
  price * 0.1 as tax,
  price + (price * 0.1) as total
FROM products;

Mistake 7: Not Indexing Search Fields

❌ Bad:

// User searches by name
const users = await prisma.user.findMany({
  where: {
    name: { contains: "john" }, // No index!
  },
});
// Full table scan, O(N)

✅ Good:

model User {
  id Int @id
  name String
  
  @@fulltext([name])  // Full-text index for searching
}

// With index
const users = await prisma.user.findMany({
  where: {
    name: { search: "john" },
  },
});

Or use PostgreSQL full-text search:

CREATE INDEX idx_user_name_search ON users USING gin(to_tsvector('english', name));

SELECT * FROM users WHERE to_tsvector('english', name) @@ to_tsquery('english', 'john');

Mistake 8: Race Conditions in Updates

❌ Bad:

const user = await prisma.user.findUnique({ where: { id: 1 } });
user.balance -= 100;
await prisma.user.update({
  where: { id: 1 },
  data: user,
});
// If two processes run simultaneously, one update is lost

✅ Good:

await prisma.user.update({
  where: { id: 1 },
  data: {
    balance: { decrement: 100 }, // Atomic
  },
});

Or use optimistic locking:

model User {
  id Int @id
  balance Decimal
  version Int @default(1)  // Increment on update
}

await prisma.user.updateMany({
  where: {
    id: 1,
    version: currentVersion, // Only update if version matches
  },
  data: {
    balance: { decrement: 100 },
    version: { increment: 1 },
  },
});

⚡ For solo developers: Profile your queries first. 90% of performance issues have obvious fixes.

Section 10: 🛠️ Tools & Monitoring

Visual Schema Designers

Prisma Studio:

npx prisma studio
# Opens UI at http://localhost:5555
# Browse data, edit records, explore schema

DBeaver (Free):

Download and connect to your database. Visual ER diagrams, SQL editor, export data.

Query Performance Profiling

PostgreSQL EXPLAIN:

EXPLAIN ANALYZE VERBOSE
SELECT * FROM orders WHERE user_id = 42;

-- Timing: "Planning Time: 0.043 ms, Execution Time: 2.145 ms"
-- Cost: "Total cost=0.00..35.00"
-- Number of rows: "Rows=5"

TypeORM Query Logging:

const dataSource = new DataSource({
  logging: ["query"],
  logger: new FileLogger(),
});

Database Monitoring

pg_stat_statements (PostgreSQL):

SELECT query, calls, mean_time FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;
-- Find your slowest queries

New Relic / Datadog:

Automatic database monitoring with alerting.

🗺️ Schema Design Progression

Stage 1: MVP (0-1,000 users)

• ✅ 3NF normalized schema
• ✅ Basic foreign keys
• ✅ Few indexes (just FKs)
• ✅ Single database
• ❌ Skip replication, sharding, partitioning

Stage 2: Growing (1K-100K users)

• ✅ Add covering indexes on slow queries
• ✅ Implement caching (Redis)
• ✅ Soft deletes if needed
• ✅ Database read replicas
• ❌ Still no sharding

Stage 3: Scale (100K+ users)

• ✅ Partial indexes
• ✅ Columnar database for analytics
• ✅ Write-through caching
• ✅ Denormalization where profiling shows gains
• ✅ Consider sharding (if QPS > 10K)

Stage 4: Enterprise (1M+ users)

• ✅ Temporal tables for compliance
• ✅ Advanced partitioning strategies
• ✅ Distributed transactions
• ✅ Sharding by region
• ✅ Custom performance optimizations

✅ Conclusion

Database design is the foundation of everything. The decisions you make today—normalization, constraints, indexes—compound over time.

Start at 3NF, normalize aggressively, index conservatively. Use profiling to guide denormalization decisions. Prevent N+1 queries like the plague.

The best part? PostgreSQL + TypeORM or Prisma gives you 80% of enterprise database capabilities out of the box. You’re not missing anything—you’re just learning to use what’s already there.

Your database isn’t a blob to throw data at. It’s a partner in your architecture. Treat it with respect, and it will scale with you. 🚀