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
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',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Soft delete pattern
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP 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);
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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
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', 'user1@example.com'),
('User 2', 'user2@example.com'),
('User 3', 'user3@example.com');
Pagination
-- Offset pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 100;
-- Cursor pagination (better performance)
SELECT * FROM users
WHERE created_at < $cursor
ORDER BY created_at DESC
LIMIT 20;
-- Keyset pagination with tie-breaker
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 * 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 *`,
[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
await client.query(
'SELECT * FROM accounts WHERE id IN ($1, $2) FOR UPDATE',
[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();
}
}
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
