t1k:web:backend:databases
| Field | Value |
|---|---|
| Module | backend |
| Version | 1.13.12 |
| Effort | medium |
| Tools | — |
Keywords: aggregation, database, migration, MongoDB, PostgreSQL, query, schema, SQL
How to invoke
Section titled “How to invoke”/t1k:web:backend:databases[query or schema task]Databases Skill
Section titled “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
Section titled “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
- Storing growth-driven config (allow-lists, tenants, flags) as rows, not env or JSON: see
t1k:web:devops:deploy§ “Config Is Data, Not Deploy Config” - Managing database users and permissions
- Analyzing slow queries and performance issues
- Administering production database deployments
Reference Navigation
Section titled “Reference Navigation”Database Design
Section titled “Database Design”- 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
Section titled “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
Section titled “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
Section titled “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 migrationpython scripts/db_migrate.py --db mongodb generate add_user_index
# Run backuppython scripts/db_backup.py --db postgres --backup-dir /backups/ backup --uri "$DATABASE_URL"
# Check performance (threshold in ms)python scripts/db_performance_check.py --db mongodb --uri "$MONGODB_URI" --threshold 100Best Practices
Section titled “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
Section titled “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/
Hard rules
Section titled “Hard rules”prisma migrate diffRESETS whatever--shadow-database-urlpoints at — the--from-migrations/--to-migrationsforms require a shadow DB and drop/recreate every table in it to replay migrations. Passing a liveDATABASE_URLthere destroys production data. For a read-only drift check use--from-url "$DATABASE_URL" --to-schema-datamodel prisma/schema.prisma, which needs no shadow DB; if one is genuinely required,CREATE DATABASE shadow_checkand point at that.