playbooks

home / skills / jjuidev / jss / databases

databases skill

/.claude/skills/databases

This skill helps you design databases, write queries, and optimize MongoDB and PostgreSQL, accelerating data modeling and performance improvements.

npx playbooks add skill jjuidev/jss --skill databases

Review the files below or copy the command above to add this skill to your agents.

Files (28)
SKILL.md
3.5 KB
---
name: databases
description: Design schemas, write queries for MongoDB and PostgreSQL. Use for database design, SQL/NoSQL queries, aggregation pipelines, indexes, migrations, replication, performance optimization, psql CLI.
license: MIT
---

# Databases Skill

Unified guide for working with MongoDB (document-oriented) and PostgreSQL (relational) databases. Choose the right database for your use case and master both systems.

## When to Use This Skill

Use when:
- Designing database schemas and data models
- Writing queries (SQL or MongoDB query language)
- Building aggregation pipelines or complex joins
- Optimizing indexes and query performance
- Implementing database migrations
- Setting up replication, sharding, or clustering
- Configuring backups and disaster recovery
- Managing database users and permissions
- Analyzing slow queries and performance issues
- Administering production database deployments

## Reference Navigation

### Database Design
- **[db-design.md](references/db-design.md)** - Activate when user requests: Database/table design for transactional (OLTP), analytics (OLAP), create or extend schema, design fact/dimension tables, analyze/review CSV/JSON/SQL files to create tables, or need advice on data storage structure.

### MongoDB References
- **[mongodb-crud.md](references/mongodb-crud.md)** - CRUD operations, query operators, atomic updates
- **[mongodb-aggregation.md](references/mongodb-aggregation.md)** - Aggregation pipeline, stages, operators, patterns
- **[mongodb-indexing.md](references/mongodb-indexing.md)** - Index types, compound indexes, performance optimization
- **[mongodb-atlas.md](references/mongodb-atlas.md)** - Atlas cloud setup, clusters, monitoring, search

### PostgreSQL References
- **[postgresql-queries.md](references/postgresql-queries.md)** - SELECT, JOINs, subqueries, CTEs, window functions
- **[postgresql-psql-cli.md](references/postgresql-psql-cli.md)** - psql commands, meta-commands, scripting
- **[postgresql-performance.md](references/postgresql-performance.md)** - EXPLAIN, query optimization, vacuum, indexes
- **[postgresql-administration.md](references/postgresql-administration.md)** - User management, backups, replication, maintenance

## Python Utilities

Database utility scripts in `scripts/`:
- **db_migrate.py** - Generate and apply migrations for both databases (MongoDB and PostgreSQL)
- **db_backup.py** - Backup and restore MongoDB and PostgreSQL
- **db_performance_check.py** - Analyze slow queries and recommend indexes

```bash
# Generate migration
python scripts/db_migrate.py --db mongodb --generate "add_user_index"

# Run backup
python scripts/db_backup.py --db postgres --output /backups/

# Check performance
python scripts/db_performance_check.py --db mongodb --threshold 100ms
```

## Best Practices

**MongoDB:**
- Use embedded documents for 1-to-few relationships
- Reference documents for 1-to-many or many-to-many
- Index frequently queried fields
- Use aggregation pipeline for complex transformations
- Enable authentication and TLS in production
- Use Atlas for managed hosting

**PostgreSQL:**
- Normalize schema to 3NF, denormalize for performance
- Use foreign keys for referential integrity
- Index foreign keys and frequently filtered columns
- Use EXPLAIN ANALYZE to optimize queries
- Regular VACUUM and ANALYZE maintenance
- Connection pooling (pgBouncer) for web apps

## Resources

- MongoDB: https://www.mongodb.com/docs/
- PostgreSQL: https://www.postgresql.org/docs/
- MongoDB University: https://learn.mongodb.com/
- PostgreSQL Tutorial: https://www.postgresqltutorial.com/

Overview

This skill helps design schemas and write queries for MongoDB and PostgreSQL, covering both document-oriented and relational patterns. I provide practical guidance on schema design, queries, aggregation pipelines, indexing, migrations, replication, backups, and performance tuning. Use it to move from design to production-ready database configurations and operational tasks.

How this skill works

I inspect your data shapes, access patterns, and performance goals to recommend a schema (normalized or document model), appropriate indexes, and concrete queries or aggregation pipelines. I generate SQL or MongoDB query examples, explain EXPLAIN/EXPLAIN ANALYZE output, and outline migration and backup commands. I also provide actionable steps for replication, sharding, and production hardening.

When to use it

  • Design or review database schemas for OLTP or OLAP workloads
  • Write or optimize SQL queries, joins, CTEs, or window functions
  • Build MongoDB CRUD operations and aggregation pipelines
  • Diagnose slow queries and recommend indexes or query rewrites
  • Plan migrations, backups, replication, or disaster recovery
  • Configure psql CLI workflows, connection pooling, and production settings

Best practices

  • Model data for the access patterns: normalize in Postgres, embed vs reference in MongoDB
  • Index selectively: index frequently filtered or joined columns/fields, avoid over-indexing
  • Use EXPLAIN/EXPLAIN ANALYZE (Postgres) and explain() (MongoDB) before changing queries
  • Keep migrations idempotent and test them on staging; back up before applying to production
  • Enable authentication, TLS, and least-privilege roles for production databases
  • Use connection pooling (pgBouncer) and monitoring to avoid connection storms

Example use cases

  • Design a Postgres schema for an e-commerce product catalog with inventory tracking and analytics-ready fact/dimension tables
  • Create a MongoDB aggregation pipeline to transform and aggregate event streams for dashboards
  • Optimize a slow JOIN-heavy report by adding indexes and rewriting with CTEs and window functions
  • Generate migration scripts to add indexes safely and backfill columns with minimal downtime
  • Set up replication, backups, and failover steps for a production Postgres or MongoDB cluster

FAQ

Can you convert a relational schema to a MongoDB document model?

Yes. I assess relationships and access patterns, then provide canonical embedding or referencing strategies and sample document shapes and queries.

How do you approach a slow query in Postgres?

I examine the query and its EXPLAIN ANALYZE plan, identify expensive operations (sequential scans, large sorts), propose index or query changes, and recommend maintenance like VACUUM/ANALYZE or partitioning if needed.