ck-databases

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