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

-- ✅ 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

-- ✅ 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

-- 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

deleted_at TIMESTAMP WITH TIME ZONE,

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

UUIDs vs Auto-increment

-- 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

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

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

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 *

-- ❌ 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

-- ❌ 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

-- ❌ 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

-- ❌ 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

-- 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

-- ✅ 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

-- 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

-- ✅ 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

// ❌ 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

-- 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)

-- 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
• Use The Index, Luke
• sanjay3290/postgres