database-patterns

name	database-patterns
description	Use when designing database schemas, implementing repository patterns, writing optimized queries, managing migrations, or working with indexes and transactions for SQL/NoSQL databases.
metadata	{"version":"1.0.0","updated":"2026-01-20T00:00:00.000Z","keywords":["database design","schema design","repository pattern","SQL queries","PostgreSQL","MySQL","MongoDB","indexes","migrations","transactions"],"plugin":"dev"}

Database Patterns

Overview

Database design and access patterns for relational and NoSQL databases.

Schema Design

Normalization Levels

Level	Description	Use Case
1NF	Atomic values, no repeating groups	Base requirement
2NF	No partial dependencies	Most applications
3NF	No transitive dependencies	OLTP systems
Denormalized	Redundant data for reads	Read-heavy, analytics

Common Table Patterns

-- Users table (PostgreSQL)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    version INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()  -- NOTE: only set on INSERT; update via trigger or app layer
);

-- Soft delete pattern (PostgreSQL)
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ NULL;
CREATE INDEX idx_users_deleted ON users(deleted_at) WHERE deleted_at IS NULL;

-- Audit columns
ALTER TABLE users ADD COLUMN created_by UUID REFERENCES users(id);
ALTER TABLE users ADD COLUMN updated_by UUID REFERENCES users(id);

-- Users table (MySQL)
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at BIGINT NOT NULL,  -- Unix timestamp in milliseconds
    updated_at BIGINT NOT NULL   -- Unix timestamp in milliseconds
);

-- Soft delete pattern (MySQL)
ALTER TABLE users ADD COLUMN deleted_at BIGINT NULL;  -- NULL means not deleted
CREATE INDEX idx_users_deleted ON users(deleted_at);

Timestamp Types by Database

Database	Recommended Type	Notes
PostgreSQL	TIMESTAMPTZ	Stores as UTC internally, timezone-aware, recommended default
MySQL	BIGINT	Store Unix timestamp in milliseconds; avoids timezone issues and 2038 limit

Recommendation:

• PostgreSQL: use TIMESTAMPTZ for all time columns
• MySQL: use BIGINT (Unix ms) + handle conversion at application layer

Relationships

-- One-to-Many
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_orders_user ON orders(user_id);

-- Many-to-Many
CREATE TABLE order_products (
    order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID REFERENCES products(id) ON DELETE CASCADE,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Self-referential (tree/hierarchy)
CREATE TABLE categories (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id UUID REFERENCES categories(id)
);
CREATE INDEX idx_categories_parent ON categories(parent_id);

Indexing Strategies

Index Types

Type	Use Case	Example
B-tree	Range, equality	Most columns
Hash	Equality only	Exact matches
GIN	Arrays, JSON, full-text	JSONB, text search
GiST	Geometric, range types	PostGIS, IP ranges

Index Guidelines

-- Primary key (automatic)
CREATE TABLE users (id UUID PRIMARY KEY);

-- Foreign keys
CREATE INDEX idx_orders_user ON orders(user_id);

-- Frequent filters
CREATE INDEX idx_users_status ON users(status);

-- Composite for multi-column queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index for common queries
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

When NOT to Index

• Small tables (< 1000 rows)
• Frequently updated columns
• Low cardinality columns
• Columns rarely used in WHERE

Query Patterns

Efficient Queries

-- Use specific columns, not *
SELECT id, name, email FROM users WHERE id = $1;

-- Limit results
SELECT * FROM users ORDER BY created_at DESC LIMIT 20;

-- Exists vs COUNT
SELECT EXISTS(SELECT 1 FROM users WHERE email = $1);

-- Batch inserts
INSERT INTO users (name, email) VALUES
    ('User 1', '[e-mail]'),
    ('User 2', '[e-mail]'),
    ('User 3', '[e-mail]');

Pagination

-- Offset pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 100;

-- Keyset pagination with tie-breaker (recommended: handles duplicate timestamps correctly)
SELECT * FROM users
WHERE (created_at, id) < ($cursor_time, $cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Common Query Patterns

-- Upsert (INSERT or UPDATE)
INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = $1;
SELECT * FROM users WHERE deleted_at IS NULL;

-- Lock for update (prevent race conditions)
SELECT * FROM accounts WHERE id = $1 FOR UPDATE;

-- Bulk update
UPDATE orders SET status = 'shipped'
WHERE id = ANY($1::uuid[]);

Repository Pattern

Interface

interface UserRepository {
  findById(id: string): Promise<User | null>;
  findByEmail(email: string): Promise<User | null>;
  findAll(filter: UserFilter, pagination: Pagination): Promise<PaginatedResult<User>>;
  create(data: CreateUserInput): Promise<User>;
  update(id: string, data: UpdateUserInput): Promise<User>;
  delete(id: string): Promise<void>;
}

Implementation

class PostgresUserRepository implements UserRepository {
  constructor(private db: Database) {}

  async findById(id: string): Promise<User | null> {
    const result = await this.db.query(
      'SELECT id, name, email, status, created_at FROM users WHERE id = $1 AND deleted_at IS NULL',
      [id]
    );
    return result.rows[0] || null;
  }

  async create(data: CreateUserInput): Promise<User> {
    const result = await this.db.query(
      `INSERT INTO users (name, email, password_hash)
       VALUES ($1, $2, $3)
       RETURNING id, name, email, status, created_at`,
      [data.name, data.email, await hashPassword(data.password)]
    );
    return result.rows[0];
  }
}

Transaction Patterns

Basic Transaction

async function transferFunds(fromId: string, toId: string, amount: number) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Lock accounts in consistent order to prevent deadlocks
    await client.query(
      'SELECT id FROM accounts WHERE id IN ($1, $2) FOR UPDATE ORDER BY id',
      [fromId, toId]
    );

    // Debit
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );

    // Credit
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );

    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

Optimistic Locking

Use a version column (defined in table schema) to detect concurrent modifications without holding locks.

async function updateOrder(id: string, data: UpdateOrderInput, version: number) {
  const result = await db.query(
    `UPDATE orders
     SET status = $1, version = version + 1
     WHERE id = $2 AND version = $3
     RETURNING id, version`,
    [data.status, id, version]
  );

  if (result.rowCount === 0) {
    throw new Error('Conflict: record was modified by another process');
  }

  return result.rows[0];
}

• Suitable for low-contention scenarios (read-heavy, occasional conflicts)
• Prefer over FOR UPDATE when locks would be held across network round-trips
• Caller must retry on conflict

Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Yes	Yes	Yes
Read Committed	No	Yes	Yes
Repeatable Read	No	No	Yes
Serializable	No	No	No

-- Set isolation level
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Migration Patterns

Migration Structure

migrations/
├── 001_create_users.sql
├── 002_add_user_status.sql
├── 003_create_orders.sql
└── 004_add_order_index.sql

Migration Best Practices

-- Always reversible
-- UP
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- DOWN
ALTER TABLE users DROP COLUMN phone;

-- Non-blocking index creation
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);

-- Safe column renames (PostgreSQL)
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Add NOT NULL safely
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
UPDATE users SET status = 'active' WHERE status IS NULL;
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

Connection Pooling

Pool Configuration

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,              // Max connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

Best Practices

• Use connection pool (don't create new connections)
• Release connections promptly
• Set appropriate pool size (CPU cores * 2-4)
• Handle connection errors gracefully

NoSQL Patterns (MongoDB/DynamoDB)

Document Design

// Embedded (for one-to-few)
{
  _id: ObjectId("..."),
  name: "John",
  addresses: [
    { type: "home", street: "123 Main St" },
    { type: "work", street: "456 Office Blvd" }
  ]
}

// Referenced (for one-to-many)
{
  _id: ObjectId("..."),
  name: "John",
  orderIds: [ObjectId("..."), ObjectId("...")]
}

DynamoDB Single-Table Design

PK              | SK                | Attributes
----------------|-------------------|------------------
USER#123        | METADATA          | name, email, ...
USER#123        | ORDER#001         | total, status, ...
USER#123        | ORDER#002         | total, status, ...
ORDER#001       | METADATA          | userId, total, ...
ORDER#001       | ITEM#1            | productId, qty, ...

---

Database design and access patterns