Loading...
Loading...
Database operations: migrations, queries, transactions, and performance. Use when: - Writing database migrations - Optimizing queries or adding indexes - Managing transactions and connections - Setting up connection pooling - Designing audit logging Keywords: database, migration, SQL, query optimization, index, transaction, connection pool, N+1, ORM, audit log
npx skill4agent add phrazzld/claude-config database-patterns-- Add nullable column (backward compatible)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Later: make required after backfill
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;-- Composite for common query
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
-- Partial for filtered queries
CREATE INDEX idx_orders_pending ON orders (status) WHERE status = 'pending';idx_scan < 100# Good
users = User.query.options(joinedload(User.posts)).all()
# Bad (N+1)
users = User.query.all()
for user in users:
print(user.posts) # N queries!async with db.transaction():
order = await create_order(data)
await update_inventory(order.items)
# Commit on exit
# OUTSIDE transaction: send emails, call external APIs
await send_confirmation(order)# Size based on measured peak concurrency
create_engine(
url,
pool_size=15, # Based on load testing
max_overflow=5, # Burst capacity
pool_timeout=30, # Fail fast
pool_recycle=3600, # Prevent stale connections
pool_pre_ping=True # Validate before use
)# Validate input before INSERT
validated = CreateUserSchema.parse(input)
if await email_exists(validated.email):
raise ValidationError("Email taken")
# Validate output after retrieval (detect corruption)
return UserOutputSchema.parse(row)