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

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