database-patterns
数据库设计模式,包括模式设计、索引策略、查询优化和迁移最佳实践。设计数据模型或优化数据库性能时使用。
📖 中文导读:数据库设计模式,包括模式设计、索引策略、查询优化和迁移最佳实践。设计数据模型或优化数据库性能时使用。 以下正文为该技能的原始 SKILL.md 内容(英文),上方安装命令可直接使用。
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 seeAlso:
- skill: api-design when: designing data-backed APIs
- 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