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 or SQL Server.

---

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:

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

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
• MySQL Documentation
• SQL Server Documentation

Tools:

• Query Optimization: EXPLAIN ANALYZE, query plan visualizers
• Monitoring: pg_stat_statements, slow query logs
• Migration Tools: Flyway, Liquibase, Alembic

---

See Also: Skills.md - AGENTS.md - PostgreSQL - SQL Server

Last Updated: January 27, 2026

Scripts

Script	Purpose	Usage
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
• Transactions Pooling Orm