Agent skill

databases

Master relational and NoSQL databases. Learn PostgreSQL, MySQL, MongoDB, Redis, and other technologies for data persistence, optimization, and scaling.

View SKILL.md on GitHub Repository
Stars 1
Forks 0

Install this agent skill to your Project

npx add-skill https://github.com/pluginagentmarketplace/custom-plugin-backend/tree/main/skills/databases

SKILL.md

Database Management Skill

Bonded to: database-management-agent

Quick Start

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

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

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

python
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

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

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

Recommended Agent Skills

Expand your agent's capabilities with these related and highly-rated skills.

pluginagentmarketplace/custom-plugin-backend

languages

Master programming languages for backend development. Learn language selection, fundamentals, and ecosystem for JavaScript, Python, Go, Java, C#, PHP, Ruby, and Rust.

1 0
Explore
pluginagentmarketplace/custom-plugin-backend

api-design

Design and build professional APIs with REST, GraphQL, and gRPC. Master authentication, documentation, testing, and operational concerns.

1 0
Explore
pluginagentmarketplace/custom-plugin-backend

architecture

Master architectural design with SOLID principles, design patterns, microservices, and event-driven systems. Learn to design scalable backend systems.

1 0
Explore
pluginagentmarketplace/custom-plugin-backend

testing

Backend testing strategies and test automation. Unit, integration, E2E, and load testing with best practices.

1 0
Explore
pluginagentmarketplace/custom-plugin-backend

devops

Deploy applications with Docker and Kubernetes, automate with CI/CD, manage infrastructure with code, and configure cloud platforms and networking.

1 0
Explore
pluginagentmarketplace/custom-plugin-backend

messaging

Message queues and event-driven backend architecture. RabbitMQ, Kafka, pub/sub patterns, and async communication.

1 0
Explore

Didn't find tool you were looking for?

Be as detailed as possible for better results