Database Patterns

• Primary keys: id (UUID preferred over auto-increment for distributed systems)
• Foreign keys: <singular_table>_id (e.g., order_id)
• Timestamps: created_at, updated_at on every table
• Money: store as integer cents, not float (avoids floating point errors)
• Booleans: is_ or has_ prefix (is_active, has_verified_email)

Migration Strategies

Zero-Downtime Migrations

Never make breaking changes in a single deploy. Use expand-contract:

Phase 1 — Expand (backward-compatible):

-- Add new column as nullable
ALTER TABLE users ADD COLUMN display_name VARCHAR(255);

Phase 2 — Migrate (backfill data):

UPDATE users SET display_name = name WHERE display_name IS NULL;

Phase 3 — Contract (after code is deployed):

ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;
ALTER TABLE users DROP COLUMN name;

Dangerous operations to avoid in a single migration:

• Renaming columns (breaks existing queries)
• Adding NOT NULL columns without defaults
• Dropping columns still referenced by running code
• Large table locks (add indexes CONCURRENTLY)

-- Safe index creation (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Query Patterns

N+1 Prevention

The most common performance issue with ORMs.

// BAD: N+1 — 1 query for posts, then N queries for authors
const posts = await db.post.findMany()
for (const post of posts) {
  post.author = await db.user.findUnique({ where: { id: post.authorId } })
}

// GOOD: Eager loading — 2 queries total
const posts = await db.post.findMany({
  include: { author: true },
})

// GOOD: Manual join — 1 query
const posts = await db.$queryRaw`
  SELECT p.*, u.name as author_name
  FROM posts p JOIN users u ON p.author_id = u.id
`

Pagination

Cursor-based (recommended for large tables):

async function getPosts(cursor?: string, limit = 20) {
  const posts = await db.post.findMany({
    take: limit + 1,
    ...(cursor && {
      cursor: { id: cursor },
      skip: 1,
    }),
    orderBy: { createdAt: 'desc' },
  })

  const hasMore = posts.length > limit
  if (hasMore) posts.pop()

  return {
    data: posts,
    nextCursor: hasMore ? posts[posts.length - 1].id : null,
  }
}

Offset-based (simple, for admin UIs):

async function getPosts(page = 1, pageSize = 20) {
  const [data, total] = await Promise.all([
    db.post.findMany({ skip: (page - 1) * pageSize, take: pageSize }),
    db.post.count(),
  ])

  return { data, total, page, pageSize, totalPages: Math.ceil(total / pageSize) }
}

Bulk Operations

// Batch inserts
await db.post.createMany({
  data: posts.map(p => ({ title: p.title, content: p.content })),
  skipDuplicates: true,
})

// Batch updates with different values (Prisma transaction)
await db.$transaction(
  updates.map(u =>
    db.post.update({ where: { id: u.id }, data: { title: u.title } })
  ),
)

ORM Patterns

Repository Pattern

Abstract database access behind an interface. One repository per aggregate/entity.

interface PostRepository {
  findById(id: string): Promise<Post | null>
  findByAuthor(authorId: string): Promise<Post[]>
  save(post: Post): Promise<void>
  delete(id: string): Promise<void>
}

class PrismaPostRepository implements PostRepository {
  constructor(private readonly prisma: PrismaClient) {}

  async findById(id: string): Promise<Post | null> {
    const row = await this.prisma.post.findUnique({
      where: { id },
      include: { comments: true },
    })
    return row ? PostMapper.toDomain(row) : null
  }

  async save(post: Post): Promise<void> {
    const data = PostMapper.toPersistence(post)
    await this.prisma.post.upsert({
      where: { id: data.id },
      create: data,
      update: data,
    })
  }
}

Active Record vs Data Mapper

TypeORM supports both. Prisma is data mapper by design. Drizzle is query-builder-first.

ORM Comparison

Transaction Management

// Prisma interactive transaction
await db.$transaction(async (tx) => {
  const account = await tx.account.findUnique({ where: { id: fromId } })
  if (account.balance < amount) throw new Error('Insufficient funds')

  await tx.account.update({
    where: { id: fromId },
    data: { balance: { decrement: amount } },
  })
  await tx.account.update({
    where: { id: toId },
    data: { balance: { increment: amount } },
  })
})

Transaction rules:

• Keep transactions short — long transactions hold locks
• Don't do external API calls inside transactions
• Use serializable isolation for critical financial operations
• Always handle rollback scenarios

Connection Pooling

// Prisma — configure in connection string
// postgresql://user:pass@host:5432/db?connection_limit=20&pool_timeout=10

// For serverless (NextJS on Vercel), use Prisma Accelerate or PgBouncer
// Serverless functions create many short-lived connections

Serverless considerations:

• Each function instance creates its own connection pool
• Use an external pooler (PgBouncer, Prisma Accelerate, Supabase Supavisor)
• Set connection_limit=1 per serverless instance when using a pooler

Indexing Strategies

-- Single column: filter/sort on one column
CREATE INDEX idx_posts_author_id ON posts(author_id);

-- Composite: queries that filter on multiple columns (leftmost prefix rule)
CREATE INDEX idx_posts_author_status ON posts(author_id, status);

-- Partial: only index rows matching a condition
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- Covering: includes all columns needed by the query (index-only scan)
CREATE INDEX idx_posts_list ON posts(author_id, created_at) INCLUDE (title);

When to index:

• Foreign key columns (always)
• Columns in WHERE clauses of frequent queries
• Columns in ORDER BY of paginated queries
• Columns in JOIN conditions

When not to index:

• Small tables (< 1000 rows)
• Columns with very low cardinality (boolean flags)
• Write-heavy tables where index maintenance costs outweigh read benefits

Common Pitfalls

• No indexes on foreign keys: PostgreSQL doesn't auto-index FKs. Every FK column needs an index.
• SELECT * in production: Select only the columns you need. Wide rows waste bandwidth and memory.
• Missing connection pooling in serverless: Each function instance opens connections. Without pooling, you'll exhaust database connections under load.
• Large transactions: Holding locks for seconds blocks other writes. Keep transactions minimal.
• String-based status columns: Use enums or lookup tables instead of magic strings. status VARCHAR is a bug waiting to happen.
• No created_at/updated_at: Every table needs timestamps for debugging and auditing.

Related Skills

• nestjs-architecture — TypeORM/Prisma integration, module-scoped repositories, DI patterns
• testing — Database fixtures, test containers, integration test strategies