Database Patterns
Modern database design and implementation patterns.
Database Selection
| Database | Best For | Use When |
|---|
| PostgreSQL | Relational data, complex queries | Structured data, ACID needed, analytics |
| MongoDB | Document-oriented, flexible schema | Rapid iteration, nested data, horizontal scale |
| SQLite | Embedded, local-first | Mobile apps, desktop apps, edge |
| Redis | Caching, sessions | High-speed reads, ephemeral data |
ORM Selection
| ORM | Best For | Trade-offs |
|---|
| Drizzle | Performance, serverless | SQL knowledge required |
| Prisma | Developer experience | Larger bundle, slower edge |
| TypeORM | NestJS, decorators | Legacy patterns |
| Kysely | Type-safe SQL builder | Lower-level |
Reference Files
| Topic | Load | Use When |
|---|
| Schema design | references/schema-design.md | Designing tables, relationships |
| PostgreSQL | references/postgresql-patterns.md | PostgreSQL-specific patterns |
| MongoDB | references/mongodb-patterns.md | MongoDB-specific patterns |
| ORM patterns | references/orm-patterns.md | Prisma, Drizzle usage |
| Migrations | references/migrations.md | Schema versioning |
| Security | references/security.md | SQL injection, encryption |
Quick Start Patterns
PostgreSQL Table
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
email_verified_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ
);
CREATE INDEX users_email_idx ON users(email) WHERE deleted_at IS NULL;
Drizzle Schema
import { pgTable, uuid, varchar, timestamp } from 'drizzle-orm/pg-core';
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
email: varchar('email', { length: 255 }).notNull().unique(),
name: varchar('name', { length: 100 }).notNull(),
passwordHash: varchar('password_hash', { length: 255 }).notNull(),
emailVerifiedAt: timestamp('email_verified_at', { withTimezone: true }),
createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
deletedAt: timestamp('deleted_at', { withTimezone: true }),
});
Prisma Schema
model User {
id String @id @default(uuid())
email String @unique
name String
passwordHash String @map("password_hash")
emailVerifiedAt DateTime? @map("email_verified_at")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
deletedAt DateTime? @map("deleted_at")
posts Post[]
@@map("users")
}
MongoDB Document
interface User {
_id: ObjectId;
email: string;
name: string;
passwordHash: string;
emailVerifiedAt?: Date;
createdAt: Date;
updatedAt: Date;
deletedAt?: Date;
// Embedded data
profile?: {
bio: string;
avatarUrl: string;
};
}
Schema Design Principles
- Normalize first - Start with 3NF, denormalize for performance
- UUID for IDs - Better for distributed systems
- Timestamps always - created_at, updated_at on every table
- Soft deletes - deleted_at over hard deletes
- Constraints - Use database constraints, not just app validation
Index Guidelines
| Index Type | Use For |
|---|
| B-tree (default) | Equality, range queries |
| Hash | Equality only (rare) |
| GIN | Arrays, JSONB, full-text |
| GiST | Geometric, range types |
| BRIN | Large sorted tables |
Common Patterns
| Pattern | Description |
|---|
| Soft delete | deleted_at column instead of DELETE |
| Audit log | Separate table tracking all changes |
| Polymorphic | type column + type-specific columns |
| EAV | Entity-Attribute-Value (avoid if possible) |
| Materialized view | Pre-computed query results |
Security Checklist
