Database Patterns

Database Patterns

Never Use SELECT *

Selecting all columns retrieves data you don't need, prevents index-only scans, and breaks code when the schema changes.

-- ❌ WRONG — fetches all columns including blobs, secrets, unused fields
SELECT * FROM users WHERE id = $1;

-- ✅ CORRECT — explicit columns; lighter payloads, index-covered scans possible
SELECT id, email, name, created_at FROM users WHERE id = $1;

// ❌ WRONG — Prisma: no field selection
const user = await prisma.user.findUnique({ where: { id } });

// ✅ CORRECT — Prisma: explicit select
const user = await prisma.user.findUnique({
  where: { id },
  select: { id: true, email: true, name: true, createdAt: true },
});

N+1 Query Prevention

N+1 is the most common database performance bug: one query to fetch a list, then one more query per item to fetch related data.

// ❌ WRONG — 1 query for posts + N queries for each post's author
const posts = await prisma.post.findMany();
for (const post of posts) {
  const author = await prisma.user.findUnique({ where: { id: post.authorId } });
  // renders post with author
}

// ✅ CORRECT — Prisma include: 2 queries total (or 1 with JOIN)
const posts = await prisma.post.findMany({
  include: { author: { select: { id: true, name: true } } },
});

// ✅ CORRECT — Drizzle: explicit join
const posts = await db
  .select({
    id: postsTable.id,
    title: postsTable.title,
    authorName: usersTable.name,
  })
  .from(postsTable)
  .innerJoin(usersTable, eq(postsTable.authorId, usersTable.id));

Detection: In development, log all queries. Any route that generates more than 3 queries for a single page load likely has an N+1.

Indexing Strategy

An unindexed query on a large table causes a full sequential scan. Add indexes on every column used in WHERE, JOIN ON, or ORDER BY.

-- ❌ WRONG — filtering by email with no index: full table scan
SELECT id, name FROM users WHERE email = $1;

-- ✅ CORRECT — add a unique index (also enforces uniqueness at DB level)
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- ✅ CORRECT — composite index for frequent compound queries
-- Index column order: most selective first, then sort/range columns
CREATE INDEX idx_orders_user_status ON orders (user_id, status, created_at DESC);

Rules:

• Every foreign key column should have an index unless you never query from child → parent
• Partial indexes reduce index size for filtered queries: CREATE INDEX ... WHERE status = 'active'
• Check EXPLAIN ANALYZE before and after adding an index; an index that is never used wastes write overhead
• Don't index low-cardinality boolean columns alone — combine them into composite indexes

Connection Pooling

Opening a new database connection per request is expensive (~50-100ms) and will exhaust the DB's connection limit under load.

// ❌ WRONG — new connection on every request
import { Client } from 'pg';

export async function getUser(id: string) {
  const client = new Client({ connectionString: process.env.DATABASE_URL });
  await client.connect();
  const result = await client.query('SELECT id, email FROM users WHERE id = $1', [id]);
  await client.end();
  return result.rows[0];
}

// ✅ CORRECT — singleton pool, reused across all requests
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,          // max connections in pool (tune for your DB plan)
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

export async function getUser(id: string) {
  const { rows } = await pool.query(
    'SELECT id, email FROM users WHERE id = $1',
    [id],
  );
  return rows[0] ?? null;
}

Serverless note: In serverless environments (Vercel, Cloudflare Workers), use a connection pooler like PgBouncer or Neon's serverless driver — standard connection pools don't survive across cold starts.

Transactions: All or Nothing

Mutations that span multiple tables must be wrapped in a transaction. A failure midway otherwise leaves data in an inconsistent state.

// ❌ WRONG — if the second insert fails, the order exists with no items
await prisma.order.create({ data: { userId, total } });
await prisma.orderItem.createMany({ data: items.map(i => ({ orderId, ...i })) });

// ✅ CORRECT — Prisma interactive transaction
const result = await prisma.$transaction(async (tx) => {
  const order = await tx.order.create({
    data: { userId, total },
  });
  await tx.orderItem.createMany({
    data: items.map((item) => ({ orderId: order.id, ...item })),
  });
  await tx.inventory.updateMany({
    where: { productId: { in: items.map((i) => i.productId) } },
    data: { reserved: { increment: 1 } },
  });
  return order;
});

-- ✅ CORRECT — raw SQL transaction
BEGIN;
  INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING id;
  INSERT INTO order_items (order_id, product_id, qty) VALUES ($3, $4, $5);
  UPDATE inventory SET reserved = reserved + 1 WHERE product_id = $4;
COMMIT;
-- Any error above automatically triggers ROLLBACK

Rules:

• Keep transactions short — long-running transactions hold locks and block other writers
• Never perform network calls (HTTP, email) inside a transaction
• Use SERIALIZABLE isolation only when you need true snapshot consistency; READ COMMITTED is sufficient for most OLTP workloads

Migration Safety

Unsafe migrations can lock tables and cause downtime on live databases.

-- ❌ WRONG — ALTER TABLE ... ADD COLUMN with NOT NULL and no DEFAULT
-- On large tables this rewrites every row and locks the table for minutes
ALTER TABLE users ADD COLUMN preferences JSONB NOT NULL;

-- ✅ CORRECT — add nullable first, backfill, then add constraint
ALTER TABLE users ADD COLUMN preferences JSONB;
UPDATE users SET preferences = '{}' WHERE preferences IS NULL;
ALTER TABLE users ALTER COLUMN preferences SET NOT NULL;
ALTER TABLE users ALTER COLUMN preferences SET DEFAULT '{}';

-- ❌ WRONG — dropping a column immediately (app still reads it until deploy)
ALTER TABLE users DROP COLUMN legacy_field;

-- ✅ CORRECT — ignore → deprecate → remove over multiple deploys
-- Deploy 1: stop reading legacy_field in application code
-- Deploy 2: run migration to drop the column
ALTER TABLE users DROP COLUMN legacy_field;

Rules:

• Every migration must be reversible — write a down migration
• Never rename a column directly; add the new column, backfill, switch reads/writes, then drop the old one
• Test migrations against a production-size data snapshot before deploying
• Run migrations before deploying new app code (not after)

Parameterized Queries (SQL Injection Prevention)

String interpolation into SQL is a direct injection vulnerability. Always use parameterized queries.

// ❌ WRONG — SQL injection: attacker sends userId = "1 OR 1=1"
const result = await db.query(
  `SELECT * FROM users WHERE id = ${userId}`,
);

// ✅ CORRECT — driver handles escaping
const result = await db.query(
  'SELECT id, email FROM users WHERE id = $1',
  [userId],
);

Checklist

• No SELECT * — columns are always explicit
• Related data fetched with JOIN or include — no N+1 loops
• Every filtered column has an appropriate index
• EXPLAIN ANALYZE reviewed for slow queries before merging
• Connection pool configured with a max and timeout
• Multi-table mutations wrapped in a transaction
• Migrations are backward-compatible and have a down script
• All queries use parameterized placeholders — no string interpolation
• Serverless functions use a pooler (PgBouncer / Neon serverless)
• No network I/O inside a transaction