Interactive Lab
Modern Databases, Type-Safe ORMs & Connection Pooling
Design schema-first databases with Drizzle ORM, construct type-safe SQL queries, and manage connection exhaustion in serverless environments.
schema-and-db.ts
import { pgTable, text, timestamp, uuid, integer, index } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
// 1. Schema-First DDL Declaration
export const users = pgTable('users', {
id: uuid('id').defaultRandom().primaryKey(),
email: text('email').notNull().unique(),
role: text('role', { enum: ['admin', 'member'] }).default('member').notNull(),
reputation: integer('reputation').default(0).notNull(),
createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull()
}, (table) => [
index('email_idx').on(table.email)
]);
// 2. Serverless Connection Pooling Client
// In serverless/edge environments, direct TCP connections exhaust PostgreSQL limits.
// Use connection pooling URLs with max: 1 connection per isolate instance.
const connectionString = process.env.DATABASE_URL!;
const client = postgres(connectionString, { max: 1, idle_timeout: 20 });
export const db = drizzle(client);
// 3. Relational Type-Safe Query Execution
export async function getTopUsers(minRep: number) {
return await db.select({
id: users.id,
email: users.email,
reputation: users.reputation
})
.from(users)
.where((t) => gt(t.reputation, minRep))
.limit(10);
}Database Architecture Fundamentals
- Drizzle vs. Traditional ORMs: Drizzle operates as a thin TypeScript SQL dialect compiler with zero runtime binary engines, reducing bundle overhead and cold start times.
- The Serverless Connection Exhaustion Problem: Each serverless or edge function instance opens its own TCP connection. Without proxy poolers (PgBouncer, Neon WebSockets, AWS RDS Proxy), database connection limits are rapidly overwhelmed.
- Zero-Downtime Migrations: Apply additive schema changes (e.g., adding nullable columns) before updating application code, followed by cleanup phases to avoid runtime lockouts.
Type-Safe Query Builder Simulator Drizzle AST → SQL
Compiled SQL Output:
SELECT "id", "email", "role", "reputation" FROM "users" WHERE "reputation" >= 50 ORDER BY "reputation" DESC LIMIT 5;
Serverless Connection Pool Guard
Pool Utilization: 12 / 20 Connections (60%)