databases

Database Management Skill

Bonded to: database-management-agent

---

Quick Start

# Invoke databases skill
"Design a database schema for my e-commerce application"
"Optimize slow queries in PostgreSQL"
"Set up Redis caching for session storage"

---

Instructions

1. Analyze Requirements: Understand data patterns, volume, access needs
2. Select Database: Choose SQL vs NoSQL based on requirements
3. Design Schema: Create data models, relationships, constraints
4. Optimize Queries: Implement indexes, analyze execution plans
5. Set Up Operations: Configure backup, replication, monitoring

---

Database Selection Guide

Type	Best For	ACID	Scale	Examples
Relational	Complex queries, transactions	Full	Vertical	PostgreSQL, MySQL
Document	Flexible schema, JSON	Partial	Horizontal	MongoDB
Key-Value	Caching, sessions	No	Horizontal	Redis
Wide-Column	Time series, analytics	Partial	Horizontal	Cassandra
Graph	Relationships	Varies	Varies	Neo4j
Search	Full-text search	No	Horizontal	Elasticsearch

---

Decision Tree

Need ACID transactions?
    │
    ├─→ Yes → Complex queries?
    │           ├─→ Yes → PostgreSQL
    │           └─→ No  → MySQL
    │
    └─→ No  → Data type?
              ├─→ Documents/JSON → MongoDB
              ├─→ Key-Value pairs → Redis
              ├─→ Time series → Cassandra/TimescaleDB
              └─→ Full-text search → Elasticsearch

---

Examples

Example 1: Schema Design

-- E-commerce schema
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for common queries
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

Example 2: Query Optimization

-- Before: Full table scan
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 'abc123';

-- After: Add index
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Optimized query with selected columns
SELECT id, total, status, created_at
FROM orders
WHERE user_id = 'abc123'
ORDER BY created_at DESC
LIMIT 10;

Example 3: Redis Caching

import redis
import json

r = redis.Redis(host='localhost', port=6379, decode_responses=True)

def get_user(user_id: str) -> dict:
    # Try cache first
    cached = r.get(f"user:{user_id}")
    if cached:
        return json.loads(cached)

    # Cache miss - fetch from DB
    user = db.query(User).get(user_id)
    if user:
        r.setex(f"user:{user_id}", 3600, json.dumps(user.dict()))
    return user.dict()

---

Troubleshooting

Common Issues

Issue	Cause	Solution
Query timeout	Missing index	Run EXPLAIN ANALYZE, add index
Connection refused	Wrong config	Check host, port, credentials
Deadlock	Concurrent updates	Use proper isolation, retry logic
OOM on query	Large result set	Add LIMIT, use cursors

Debug Commands

-- PostgreSQL: Check slow queries
SELECT query, calls, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;

-- Check active connections
SELECT * FROM pg_stat_activity WHERE state = 'active';

-- Check table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

---

Test Template

# tests/test_database.py
import pytest
from sqlalchemy import create_engine

class TestDatabaseSchema:
    @pytest.fixture
    def engine(self):
        return create_engine("postgresql://test:test@localhost/testdb")

    def test_users_table_exists(self, engine):
        result = engine.execute("SELECT 1 FROM users LIMIT 1")
        assert result is not None

    def test_foreign_key_constraint(self, engine):
        with pytest.raises(IntegrityError):
            engine.execute(
                "INSERT INTO orders (user_id, total) VALUES ('invalid-uuid', 100)"
            )

---

References

See references/ directory for:

• DATABASE_GUIDE.md - Detailed database patterns
• Schema templates and examples

---

Resources

• PostgreSQL Documentation
• MongoDB Manual
• Redis Documentation
• Use The Index, Luke