# Database

> Database design and optimization. Use for schema design, queries, migrations, indexing, and performance tuning. Use when this capability is needed.

- Skill: `tomevault-io/database-5` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add tomevault-io/database-5`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/database-5/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: tomevault-io (https://skillmd.com/u/tomevault-io)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/tomevault-io/database-5

---


# Database Skill

Database design, query optimization, and best practices for PostgreSQL/MySQL.

## When to Use This Skill

- Designing database schemas
- Writing efficient queries
- Creating migrations
- Performance optimization
- Index design

---

# 📐 Schema Design

## Table Naming

```sql
-- ✅ Good: snake_case, plural, descriptive
CREATE TABLE users (...);
CREATE TABLE api_keys (...);
CREATE TABLE model_providers (...);
CREATE TABLE request_logs (...);

-- ❌ Bad: CamelCase, singular, abbreviations
CREATE TABLE User (...);
CREATE TABLE apiKey (...);
CREATE TABLE mdl_prvdr (...);
```

## Column Naming

```sql
-- ✅ Good: snake_case, clear names
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE
);
```

## Common Patterns

### Timestamps
```sql
-- Always include these columns
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()

-- Auto-update updated_at with trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();
```

### Soft Deletes
```sql
deleted_at TIMESTAMP WITH TIME ZONE,

-- Query active records
SELECT * FROM users WHERE deleted_at IS NULL;
```

### UUIDs vs Auto-increment
```sql
-- UUID (recommended for distributed systems)
id UUID PRIMARY KEY DEFAULT gen_random_uuid()

-- Auto-increment (simpler, better for single DB)
id SERIAL PRIMARY KEY
-- or for larger tables
id BIGSERIAL PRIMARY KEY
```

---

# 🔗 Relationships

## One-to-Many

```sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL
);

CREATE TABLE api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    key_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100)
);

CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);
```

## Many-to-Many

```sql
CREATE TABLE users (
    id UUID PRIMARY KEY
);

CREATE TABLE roles (
    id UUID PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE user_roles (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    PRIMARY KEY (user_id, role_id)
);
```

---

# 🔍 Query Optimization

## Use EXPLAIN ANALYZE

```sql
EXPLAIN ANALYZE
SELECT u.*, COUNT(ak.id) as key_count
FROM users u
LEFT JOIN api_keys ak ON ak.user_id = u.id
WHERE u.is_active = true
GROUP BY u.id;
```

## Avoid SELECT *

```sql
-- ❌ Bad: Fetches all columns
SELECT * FROM users WHERE id = $1;

-- ✅ Good: Only needed columns
SELECT id, name, email FROM users WHERE id = $1;
```

## Use Proper JOINs

```sql
-- ❌ Bad: Implicit join
SELECT * FROM users, orders WHERE users.id = orders.user_id;

-- ✅ Good: Explicit JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
```

## Batch Operations

```sql
-- ❌ Bad: Multiple INSERT statements
INSERT INTO logs (message) VALUES ('log1');
INSERT INTO logs (message) VALUES ('log2');
INSERT INTO logs (message) VALUES ('log3');

-- ✅ Good: Single batch INSERT
INSERT INTO logs (message) VALUES 
    ('log1'),
    ('log2'),
    ('log3');
```

## Use EXISTS instead of IN for large sets

```sql
-- ❌ Slower for large subqueries
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM active_sessions);

-- ✅ Better performance
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM active_sessions s WHERE s.user_id = u.id
);
```

---

# 📇 Indexing

## When to Create Indexes

- Columns in WHERE clauses
- Columns in JOIN conditions
- Columns in ORDER BY
- Columns with high selectivity (many unique values)

## Index Types

```sql
-- B-tree (default, most common)
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_logs_user_date ON request_logs(user_id, created_at DESC);

-- Partial index (filtered)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- GIN index (for arrays, JSONB)
CREATE INDEX idx_users_tags ON users USING GIN(tags);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
```

## Index Guidelines

```sql
-- ✅ Good: Frequently queried columns
CREATE INDEX idx_api_keys_key_hash ON api_keys(key_hash);

-- ✅ Good: Foreign keys
CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);

-- ❌ Bad: Low cardinality columns
CREATE INDEX idx_users_is_active ON users(is_active);  -- Only true/false

-- ❌ Bad: Too many indexes (slow writes)
```

---

# 🔄 Migrations

## Migration Best Practices

```sql
-- migrations/001_create_users.sql

-- Up
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

-- Down
DROP TABLE IF EXISTS users;
```

## Safe Schema Changes

```sql
-- ✅ Safe: Add column with default (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- ✅ Safe: Create index concurrently (no lock)
CREATE INDEX CONCURRENTLY idx_users_status ON users(status);

-- ⚠️ Careful: Add NOT NULL column
-- First add nullable, backfill, then add constraint
ALTER TABLE users ADD COLUMN role VARCHAR(50);
UPDATE users SET role = 'user' WHERE role IS NULL;
ALTER TABLE users ALTER COLUMN role SET NOT NULL;
```

---

# 🔒 Security

## Parameterized Queries

```go
// ❌ NEVER: String concatenation (SQL injection!)
query := "SELECT * FROM users WHERE email = '" + email + "'"

// ✅ ALWAYS: Parameterized query
query := "SELECT * FROM users WHERE email = $1"
row := db.QueryRow(query, email)
```

## Least Privilege

```sql
-- Create read-only user for reporting
CREATE USER report_user WITH PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;

-- App user with limited permissions
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON users, api_keys TO app_user;
```

---

# 📊 JSONB Patterns (PostgreSQL)

```sql
-- Store flexible data
CREATE TABLE model_configs (
    id UUID PRIMARY KEY,
    model_name VARCHAR(100),
    parameters JSONB DEFAULT '{}'::jsonb
);

-- Query JSONB
SELECT * FROM model_configs 
WHERE parameters->>'temperature' = '0.7';

-- Index JSONB
CREATE INDEX idx_configs_params ON model_configs USING GIN(parameters);

-- Update JSONB
UPDATE model_configs 
SET parameters = parameters || '{"max_tokens": 1000}'::jsonb
WHERE id = $1;
```

---

# 📚 References

- [PostgreSQL Documentation](https://www.postgresql.org/docs/)
- [Use The Index, Luke](https://use-the-index-luke.com/)
- [sanjay3290/postgres](https://github.com/sanjay3290/ai-skills/tree/main/skills/postgres)

---
> Converted and distributed by [TomeVault](https://tomevault.io/claim/aiyuekuang) — claim your Tome and manage your conversions.
<!-- tomevault:4.0:skill_md:2026-04-11 -->