fogserv.cloud fogserv.cloud

Database Integration — Connecting Backends to Databases

Status: Active | Last Updated: 2026-08-29 Category: Backend — Data Prerequisites: SQL fundamentals; backend basics Tags: database, prisma, orm, connection-pool, query Estimated Time: 4-5 hours (Self-paced, includes lab time)

Summary

Most backends store and retrieve data from a database. This article covers connection management (connection pooling), the two ways to query (ORM vs raw SQL), and when to use each. Prisma is the ORM used in this project, but the patterns apply to any backend.

What You'll Learn (Core Competencies)

Table of Contents

  1. Architectural Overview & Core Schema
  2. Deep Dive & Implementation
  3. Anti-Patterns & Common Pitfalls
  4. Independent Challenge
  5. Consolidation & Key Invariants
  6. Next Steps

1. Architectural Overview & Core Schema

Opening a database connection is expensive: TCP handshake, authentication, protocol negotiation. For every request, you do not want to open a new connection, run a query, then close it. Instead, you use a connection pool — a set of pre-opened connections that are reused.

The pool size depends on your workload. PostgreSQL's default max_connections is 100 per database. Prisma's default pool size is num_physical_cpus * 2 + 1. For most workloads, this is appropriate, but high-traffic APIs need tuning: a pool too small causes request queuing; a pool too large exhausts database connections.

Why an ORM? Prisma generates TypeScript types from your schema, so queries are type-safe. The result of prisma.user.findMany() is User[] with all relations typed. This eliminates an entire class of bugs (typos in field names, missing relations).

When raw SQL? Complex reports, recursive CTEs, database-specific features (e.g., RETURNING clauses, UPSERT on conflict), or queries the ORM cannot express efficiently. Prisma provides $queryRaw and $executeRaw as escape hatches.

Transactions are essential for atomic multi-table updates. prisma.$transaction() rolls back on exception, ensuring all-or-nothing semantics.

Caching with Redis reduces database load for read-heavy endpoints. Cache invalidation on writes keeps the cache consistent.

2. Deep Dive & Implementation

Prisma ORM

// prisma/schema.prisma
model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

model Post {
  id     Int   @id @default(autoincrement())
  title  String
  userId Int
  user   User  @relation(fields: [userId], references: [id])
}

// Query with type-safe relations
const users = await prisma.user.findMany({
  where: { name: { contains: "fogserv" } },
  include: { posts: true }
});
// users: (User & { posts: Post[] })[]

The result is fully typed — TypeScript knows each user has posts: Post[]. Prisma's type generation runs automatically when you run prisma generate after a schema change.

Connection Pool Deep Dive

Configure pool size based on workload:

const prisma = new PrismaClient({
  datasources: { db: { url: process.env.DATABASE_URL } },
  log: ["query", "info", "warn", "error"]
});

For PostgreSQL, set the pool to roughly 80% of the database's max_connections to leave headroom for admin connections. Connection leaks occur when queries are not awaited or transactions are not committed. Always await and handle errors.

Monitor with prisma.$metrics() in development. In production, use the Prisma metrics endpoint or scrape query duration logs.

Raw SQL

When the ORM gets in the way, use $queryRaw or $executeRaw:

const posts = await prisma.$queryRaw`
  SELECT u.name, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  GROUP BY u.id
  ORDER BY post_count DESC
  LIMIT 10
`;
// Returns: { name: string, post_count: number }[]

await prisma.$executeRaw`DELETE FROM sessions WHERE expires_at < NOW()`;

$queryRaw returns rows; $executeRaw returns the number of affected rows. Use parameterized queries to prevent SQL injection.

Transactions

await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { email } });
  await tx.profile.create({ data: { userId: user.id, bio } });
  // If any statement throws, all changes roll back
});

For simple atomic updates, use the array form:

const [user, profile] = await prisma.$transaction([
  prisma.user.create({ data: { email } }),
  prisma.profile.create({ data: { userId: 1, bio } })
]);

Nested transactions (savepoints) are supported via $transaction with a { maxWait, timeout } option for long-running operations.

Caching with Redis

Cache read-heavy query results with TTL. Invalidate on writes to keep data consistent:

async function getUsers(filter: string) {
  const key = `users:${filter}`;
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  const data = await prisma.user.findMany({ where: { name: { contains: filter } } });
  await redis.setex(key, 300, JSON.stringify(data)); // 5 min TTL
  return data;
}

// Invalidate on write
async function createUser(data: { email: string; name: string }) {
  const user = await prisma.user.create({ data });
  await redis.del("users:"); // Invalidate all user caches (use a more specific key in production)
  return user;
}

The cache key strategy matters. For a simple read endpoint, users:list:{filterHash} works. For complex invalidation patterns, use tagged caches or pub/sub.

Guided Checkpoint

Verify the database layer with a sequence of operations:

# Start: bun run server.ts (assumes DATABASE_URL is set)

# Create a user — returns 201 with id
curl -s -X POST http://localhost:3000/users \
  -H "Content-Type: application/json" \
  -d '{"email":"alice@example.com","name":"Alice"}' | jq .

# Create a post linked to that user — returns 201
curl -s -X POST http://localhost:3000/users/1/posts \
  -H "Content-Type: application/json" \
  -d '{"title":"First Post","content":"Hello world"}' | jq .

# List users with their posts — returns nested relations
curl -s http://localhost:3000/users | jq .

# Read user by id — returns single user with cache hit log
curl -s http://localhost:3000/users/1 | jq .

# Second read — should log "cache hit" (Redis lookup)
curl -s http://localhost:3000/users/1 | jq .

# Create another user — should invalidate user cache
curl -s -X POST http://localhost:3000/users \
  -H "Content-Type: application/json" \
  -d '{"email":"bob@example.com","name":"Bob"}' | jq .

# Read user by id again — should log "cache miss" (invalidated)
curl -s http://localhost:3000/users/2 | jq .

# Test transaction rollback — invalid user creation should not create profile
curl -s -X POST http://localhost:3000/users-with-profile \
  -H "Content-Type: application/json" \
  -d '{"email":"invalid","name":""}' | jq .

The second read should be served from cache (faster, with a cache hit log line). The third read after invalidation should be a cache miss. The transaction test should show that an invalid user creation does not leave an orphaned profile.

3. Anti-Patterns & Common Pitfalls

Forgetting to await queries. A query that is not awaited silently fails. Errors are swallowed and data is never persisted. Always await database calls and handle errors with try/catch.

Not using transactions for multi-table writes. Inserting a user and a profile in two separate queries leaves the system in an inconsistent state if the second query fails. Wrap multi-table writes in prisma.$transaction().

Using $queryRaw with string concatenation. Concatenated SQL is vulnerable to injection. Always use Prisma's tagged template literal syntax, which parameterizes values automatically.

Connection leaks from unclosed transactions. A prisma.$transaction(async (tx) => ...) that throws without a catch leaks the connection. Always wrap in try/catch or use the array form which auto-cleans.

Caching without invalidation. A cache that is never invalidated serves stale data indefinitely. On every write, invalidate the corresponding cache keys. Test invalidation explicitly.

Using JSON.parse on redis.get without try/catch. Corrupted cache entries (manual edits, version mismatches) cause JSON.parse to throw. Wrap in try/catch and treat parse errors as cache misses.

Over-using $queryRaw instead of letting the ORM handle the query. Raw SQL bypasses type safety, relation loading, and Prisma's query optimizations. Use it only when the ORM cannot express the query or when performance requires it.

Pooling too many connections for the database's limit. Setting connection_limit=200 against a PostgreSQL with max_connections=100 causes connection errors under load. Pool size must be smaller than the database's connection cap.

4. Independent Challenge

Build a user profile system with the following requirements:

  1. User and Profile models with a 1:1 relation in Prisma schema
  2. POST /users — creates a user (returns 201 with the new user)
  3. POST /users/:id/profile — creates a profile for an existing user, wrapped in a transaction
  4. GET /users/:id — returns user with profile (use cache for 60s TTL)
  5. PUT /users/:id — updates user, invalidates the user cache
  6. GET /users/top — returns top 10 users by post count using $queryRaw and a join
  7. Validation: reject duplicate emails (return 409), reject users with no email (return 400)
  8. Connection pool size: 5 connections (configurable via DATABASE_POOL_SIZE env var)

Implement all 6 routes. Use prisma.$transaction for the user+profile creation. Use Redis for caching.

5. Consolidation & Key Invariants

6. Next Steps

Change Log

Choose Theme

Your selection is saved locally.

Neural Cacophony
Aperture v2
Flux v1
Mosaic Chaos
Nexus v1
Nexus Zest
Prism v2
Synapse