5. Database Integration

---

1. SQL vs NoSQL

	SQL (Postgres, MySQL)	NoSQL (MongoDB)
Schema	Fixed, enforced	Flexible
Relations	Joins	Embed or manual lookup
Transactions	ACID, mature	Supported, more limited
Scaling	Vertical, then read replicas / sharding	Horizontal by design
Query language	SQL	Query documents
Best for	Relational data, financial correctness	Documents, varied shapes, high write volume

The Answer That Works

"Postgres by default. It handles relational data, JSON columns cover the flexible-shape cases, and ACID transactions matter more than people expect. I'd pick MongoDB when the data is genuinely document-shaped and the access pattern is 'fetch this whole document by id'."

"NoSQL scales better" is not a good answer on its own — Postgres scales fine for the vast majority of applications, and you lose joins and transactions to get there.

---

2. Connection Pooling

A new database connection costs a TCP handshake plus auth — 20–100ms. A pool keeps connections open and reuses them.

import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                        // max connections
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
  ssl: process.env.NODE_ENV === "production" ? { rejectUnauthorized: true } : false,
});

const { rows } = await pool.query("SELECT * FROM users WHERE id = $1", [id]);

Sizing the Pool

The trap: max: 20 per process × 4 processes × 3 replicas = 240 connections. Postgres defaults to 100 and rejects the rest.

Formula worth quoting: pool_size ≈ (core_count × 2) + effective_spindle_count. In practice, start small (5–10 per process) and measure. More connections is not faster — Postgres uses a process per connection.

For serverless, use a connection pooler like PgBouncer, Prisma Accelerate, Neon's pooler or Supabase's — each invocation opening its own connection will exhaust the database instantly.

---

3. SQL Injection

The single most important database security topic.

// ❌ Catastrophic
const q = `SELECT * FROM users WHERE email = '${req.body.email}'`;
// email = "' OR '1'='1" returns every user
// email = "'; DROP TABLE users; --" is worse

// ✅ Parameterised — the driver sends query and data separately
await pool.query("SELECT * FROM users WHERE email = $1", [req.body.email]);

Parameterised queries work because the SQL text and the values travel to the database separately. The value is never parsed as SQL, so there is nothing to inject.

ORMs Are Safe Until You Escape Them

// Safe
await prisma.user.findMany({ where: { email } });

// Unsafe — raw string interpolation defeats the ORM
await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${email}'`);

// Safe raw query — tagged template, parameterised
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`;

The tagged-template form is parameterised. $queryRawUnsafe with interpolation is not — the name is a warning.

NoSQL Injection

// Client posts { "email": "a@b.com", "password": { "$gt": "" } }
await User.findOne({ email: req.body.email, password: req.body.password });
// $gt: "" matches any password

Validate that inputs are the expected type, not just present. Zod handles this.

---

4. Prisma

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  posts     Post[]
  createdAt DateTime @default(now())

  @@index([email])
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  authorId Int
  author   User   @relation(fields: [authorId], references: [id], onDelete: Cascade)

  @@index([authorId])
}

const user = await prisma.user.findUnique({
  where: { id },
  select: { id: true, name: true, email: true },   // allowlist — never leak passwordHash
  include: { posts: { take: 10, orderBy: { createdAt: "desc" } } },
});

Transactions

// Sequential array — all or nothing
await prisma.$transaction([
  prisma.account.update({ where: { id: from }, data: { balance: { decrement: 100 } } }),
  prisma.account.update({ where: { id: to },   data: { balance: { increment: 100 } } }),
]);

// Interactive — when you need logic between steps
await prisma.$transaction(async (tx) => {
  const account = await tx.account.findUnique({ where: { id: from } });
  if (account.balance < 100) throw new Error("Insufficient funds");

  await tx.account.update({ where: { id: from }, data: { balance: { decrement: 100 } } });
  await tx.account.update({ where: { id: to },   data: { balance: { increment: 100 } } });
}, { timeout: 5000 });

Note { balance: { decrement: 100 } } rather than reading the value and writing balance - 100 — an atomic update avoids a lost-update race between concurrent requests.

Migrations

npx prisma migrate dev --name add_user_role   # dev: create + apply
npx prisma migrate deploy                      # production: apply only
npx prisma generate                            # regenerate the typed client

Never use prisma db push in production — it can drop data without a migration record.

---

5. Mongoose

const userSchema = new mongoose.Schema({
  email: { type: String, required: true, unique: true, lowercase: true, trim: true },
  passwordHash: { type: String, required: true, select: false },
  role: { type: String, enum: ["user", "admin"], default: "user" },
}, { timestamps: true });

userSchema.index({ email: 1 });

userSchema.pre("save", async function () {
  if (!this.isModified("passwordHash")) return;
  this.passwordHash = await bcrypt.hash(this.passwordHash, 12);
});

select: false means passwordHash is excluded from queries unless explicitly requested — a good default.

Populate and the N+1 Problem

// ❌ N+1 — one query for posts, then one per post
const posts = await Post.find();
for (const p of posts) p.author = await User.findById(p.authorId);

// ✅ One extra query total
const posts = await Post.find().populate("author", "name email");

populate is a second query with $in, not a join. For heavy relational work, that's an argument for SQL.

---

6. The N+1 Query Problem

The most-asked database performance question.

// ❌ 1 + N queries
const users = await prisma.user.findMany();          // 1
for (const u of users) {
  u.posts = await prisma.post.findMany({ where: { authorId: u.id } });   // N
}

// ✅ 2 queries — Prisma batches the relation
const users = await prisma.user.findMany({ include: { posts: true } });

In Raw SQL

SELECT u.id, u.name, p.id AS post_id, p.title
FROM users u
LEFT JOIN posts p ON p.author_id = u.id;

How To Spot It

Turn on query logging in development. If one HTTP request produces 200 queries, you have an N+1. For GraphQL, DataLoader is the standard fix — it batches and dedupes lookups within a request tick.

---

7. Indexes

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_author_created ON posts(author_id, created_at DESC);
CREATE UNIQUE INDEX idx_users_email_unique ON users(LOWER(email));

Rules

• Index columns used in WHERE, JOIN and ORDER BY
• Composite index column order matters — an index on (a, b) helps queries filtering on a, or a and b, but not b alone
• Every index slows writes and costs disk — don't index everything
• A low-cardinality column (a boolean) rarely benefits

Proving It

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';

Look for Seq Scan on a large table — that means no usable index. Index Scan means it's working.

Saying "I'd check the query plan with EXPLAIN ANALYZE" is a much better answer than "I'd add an index".

---

8. Transactions and ACID

Property	Meaning
Atomicity	All steps commit, or none do
Consistency	Constraints hold before and after
Isolation	Concurrent transactions don't corrupt each other
Durability	Committed data survives a crash

Isolation Levels

Level	Prevents
Read Uncommitted	Nothing (dirty reads possible)
Read Committed	Dirty reads — Postgres default
Repeatable Read	Dirty + non-repeatable reads
Serializable	All anomalies, including phantoms

Higher isolation means more locking and more contention. Read Committed is right for almost everything; use Serializable for money movement and inventory decrements.

Optimistic Locking

const updated = await prisma.product.updateMany({
  where: { id, version: currentVersion },
  data: { stock: { decrement: 1 }, version: { increment: 1 } },
});

if (updated.count === 0) throw new ConflictError("Modified by another request");

No locks held between read and write; the version check catches concurrent modification. Return 409 Conflict to the client.

---

9. Caching

async function getUser(id) {
  const cached = await redis.get(`user:${id}`);
  if (cached) return JSON.parse(cached);

  const user = await db.user.findUnique({ where: { id } });
  if (user) await redis.setex(`user:${id}`, 300, JSON.stringify(user));

  return user;
}

async function updateUser(id, data) {
  const user = await db.user.update({ where: { id }, data });
  await redis.del(`user:${id}`);   // invalidate, don't try to update the cache
  return user;
}

Strategies

Pattern	How
Cache-aside	Check cache, miss → DB → populate (the example above)
Write-through	Write to cache and DB together
Write-behind	Write to cache, flush to DB async — risks data loss

Invalidation

The hard part. Options: short TTLs (simplest, accepts staleness), explicit deletion on write (shown above), or versioned keys (user:123:v7 — bump the version instead of deleting).

Delete rather than update the cache on a write. Updating opens a race where a stale read repopulates the old value after your update.

Cache Stampede

When a hot key expires, hundreds of requests miss simultaneously and all hit the database. Mitigate with a short lock around the repopulation, or by refreshing slightly before expiry.

---

10. Common Mistakes

Mistake	Consequence
No connection pooling	Latency and connection exhaustion
Pool too large × many processes	Database refuses connections
String-concatenated SQL	SQL injection
SELECT *	Leaks passwordHash; wastes bandwidth
No index on a filtered column	Sequential scan, slow at scale
N+1 queries	200 queries per request
No transaction on multi-step writes	Partial writes on failure
Unbounded findMany()	Loads a million rows into memory
Migrations run by hand	Environments drift
Credentials in code	Leaked on the first public push
No timeout on queries	One slow query holds a connection forever

Always Paginate

// ❌ Fine with 100 rows, fatal with 10 million
const all = await db.user.findMany();

// ✅
const page = await db.user.findMany({ take: 50, skip: offset });

Every list endpoint needs a default limit and a maximum limit. This is the failure that only appears in production, which is exactly why interviewers ask about it.