database-patterns

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

1. Normalize first - Start with 3NF, denormalize for performance
2. UUID for IDs - Better for distributed systems
3. Timestamps always - created_at, updated_at on every table
4. Soft deletes - deleted_at over hard deletes
5. 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

• [ ] Parameterized queries only
• [ ] Passwords hashed with bcrypt/argon2
• [ ] PII encrypted at rest
• [ ] Database user has minimal privileges
• [ ] SSL/TLS for connections
• [ ] No sensitive data in logs