database ยท diff
v1.0.0 to v1.0.0
14 added, 1 removed. Audit A to A.
---
name: "database"
description: 'Design database operations including migrations, indexing strategies, transactions, connection pooling, and ORM best practices. Use when creating database schemas, writing migrations, optimizing slow queries, configuring connection pools, or choosing between ORM frameworks.'
metadata:
author: "AgentX"
version: "1.0.0"
created: "2025-01-15"
updated: "2025-01-15"
---
# Database
> **Purpose**: Efficient, reliable database operations with migrations, indexes, and transactions.
> **Focus**: ORM patterns, query optimization, data integrity.
> **Note**: For database-specific details, see [PostgreSQL](../../languages/postgresql/SKILL.md) or [SQL Server](../../languages/sql-server/SKILL.md).
---
## When to Use This Skill
- Creating database schemas or migrations
- Optimizing slow database queries
- Configuring connection pooling
- Choosing between ORM frameworks
- Implementing data integrity constraints
## Prerequisites
- SQL fundamentals
- Access to a relational database
## Decision Tree
```
Database operation?
+- Schema change?
| +- New table/column? -> Migration (up + down)
| +- Rename/drop? -> Migration + verify no dependents
| - Index needed? -> CREATE INDEX CONCURRENTLY (avoid locks)
+- Query performance?
| +- Slow query? -> EXPLAIN ANALYZE -> add index or rewrite
| +- N+1 problem? -> Use eager loading / JOIN
| - Large result set? -> Pagination (cursor-based preferred)
+- Data integrity?
| +- Multiple writes? -> Use transaction
| - Concurrent access? -> Optimistic concurrency (version column)
- Connection management?
- Always use connection pooling, never open/close per query
```
## Performance Best Practices
### Database Optimization Checklist
- [ ] Add indexes on foreign keys
- [ ] Add indexes on columns in WHERE/JOIN/ORDER BY
- [ ] Use composite indexes for multi-column queries
- [ ] Analyze query execution plans
- [ ] Fix N+1 queries with eager loading
- [ ] Use connection pooling
- [ ] Cache frequently accessed data
- [ ] Denormalize for read-heavy workloads
- [ ] Partition large tables
- [ ] Archive old data
- [ ] Monitor slow query logs
### Query Analysis
**Analyze Query Performance:**
```sql
-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';
-- MySQL
EXPLAIN
SELECT * FROM users WHERE email = 'test@example.com';
```
**Look for:**
- **Seq Scan** - Table scan (bad for large tables)
- **Index Scan** - Uses index (good)
- **Nested Loop** - Join method (can be slow)
- **Hash Join** - Better for large datasets
---
- ## Common Pitfalls
+ ## Core Rules
+
+ 1. **Always use migrations** - Never modify production schemas by hand; every change gets a versioned migration with rollback support.
+ 2. **Parameterize all queries** - Never concatenate user input into SQL strings; use parameterized queries or ORM bindings.
+ 3. **Index foreign keys and filter columns** - Every foreign key and every column used in WHERE, JOIN, or ORDER BY should have an index.
+ 4. **Use connection pooling** - Never open/close connections per request; configure a pool with sensible min/max sizes.
+ 5. **Keep transactions short** - Acquire locks late, release early; never hold a transaction open across network calls.
+ 6. **Prefer cursor-based pagination** - Offset pagination degrades on large tables; use keyset (cursor) pagination for stable performance.
+ 7. **Validate data at the boundary** - Enforce NOT NULL, CHECK, and UNIQUE constraints in the schema, not only in application code.
+ 8. **Monitor slow queries** - Enable slow-query logging and review EXPLAIN plans regularly in production.
+
+ ---
+
+ ## Anti-Patterns
| Issue | Problem | Solution |
|-------|---------|----------|
| **N+1 queries** | Loading relations one by one | Use eager loading, JOINs |
| **Missing indexes** | Slow queries | Add indexes on WHERE/JOIN columns |
| **SELECT *** | Loading unnecessary data | Select only needed columns |
| **No connection pooling** | Too many connections | Implement connection pooling |
| **Large transactions** | Locks held too long | Keep transactions short |
| **No query timeout** | Queries run forever | Set query timeout limits |
---
## ORM Frameworks
**Popular ORMs:**
- **.NET**: Entity Framework Core, Dapper, NHibernate
- **Python**: SQLAlchemy, Django ORM, Peewee
- **Node.js**: Sequelize, TypeORM, Prisma
- **Java**: Hibernate, JPA, MyBatis
- **PHP**: Doctrine, Eloquent (Laravel)
- **Ruby**: ActiveRecord (Rails)
---
## Resources
**Database Docs:**
- [PostgreSQL Documentation](https://www.postgresql.org/docs/)
- [MySQL Documentation](https://dev.mysql.com/doc/)
- [SQL Server Documentation](https://learn.microsoft.com/sql/)
**Tools:**
- **Query Optimization**: EXPLAIN ANALYZE, query plan visualizers
- **Monitoring**: pg_stat_statements, slow query logs
- **Migration Tools**: Flyway, Liquibase, Alembic
---
**See Also**: [Skills.md](../../../../Skills.md) - [AGENTS.md](../../../../AGENTS.md) - [PostgreSQL](../../languages/postgresql/SKILL.md) - [SQL Server](../../languages/sql-server/SKILL.md)
**Last Updated**: January 27, 2026
## Scripts
| Script | Purpose | Usage |
|--------|---------|-------|
| [`scaffold-migration.py`](scripts/scaffold-migration.py) | Generate migration scaffold (SQL/EF Core/Alembic) | `python scripts/scaffold-migration.py --type sql --name add_users_table` |
## Troubleshooting
| Issue | Solution |
|-------|----------|
| Slow query performance | Check EXPLAIN plan, add missing indexes, avoid SELECT * |
| Connection pool exhaustion | Increase pool size, ensure connections are properly disposed/returned |
| Migration conflicts | Use sequential numbering, resolve merge conflicts in migration order |
## References
- [Migrations Indexing Queries](references/migrations-indexing-queries.md)
- [Transactions Pooling Orm](references/transactions-pooling-orm.md)