database-patterns
This skill should be used for database schema design, indexes, query optimization, migrations, SQL, PostgreSQL, MySQL, ORM patterns, data modeling, database relationships
When & Why to Use This Skill
This Claude skill provides comprehensive expertise in database schema design, SQL query optimization, and data modeling. It assists developers in implementing high-performance indexing strategies, managing migrations, and applying best practices for PostgreSQL, MySQL, and popular ORMs like Prisma and SQLAlchemy to ensure scalable and efficient data layers.
Use Cases
- Designing scalable and normalized database schemas for new software projects to ensure data integrity and performance.
- Optimizing slow-performing SQL queries by identifying missing indexes and resolving common bottlenecks like the N+1 query problem.
- Creating and managing safe database migration scripts for seamless deployment and rollback capabilities across different environments.
- Implementing efficient data access patterns using ORMs to handle complex many-to-many or one-to-many relationships effectively.
- Configuring connection pooling and database settings to improve application responsiveness and resource management.
| name | database-patterns |
|---|---|
| description | This skill should be used for database schema design, indexes, query optimization, migrations, SQL, PostgreSQL, MySQL, ORM patterns, data modeling, database relationships |
| whenToUse | Database schema, indexes, SQL performance, data modeling, PostgreSQL, MySQL, SQLite, ORM design, Prisma, TypeORM, SQLAlchemy, database relationships |
| whenNotToUse | Simple CRUD, established schemas, NoSQL-only projects |
| - skill | architecture-patterns |
| when | data layer architecture |
Database Patterns
Database design and optimization patterns.
Schema Design
- Use appropriate data types
- Add NOT NULL where applicable
- Use foreign keys for relationships
- Index frequently queried columns
Indexing
-- Single column
CREATE INDEX idx_users_email ON users(email);
-- Composite (order matters)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
Query Optimization
N+1 Problem
-- Bad: N+1 queries
SELECT * FROM users;
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
...
-- Good: JOIN or eager load
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
Migrations
- One migration per change
- Up and down migrations
- Never modify deployed migrations
- Test rollback
Connection Pooling
- Reuse connections
- Set appropriate pool size
- Handle connection timeouts