database

设计数据库操作，包括迁移、索引策略、事务、连接池以及 ORM 最佳实践。在创建数据库表结构、编写迁移脚本、优化慢查询、配置连接池，或在多种 ORM 框架之间做出选择时，可使用此技能。

SKILL.md

--- frontmatter

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

code

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

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

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:

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