OverviewHistoryStatsSecurity
npx skills add ...
Documentation
SKILL.md
npx skills add alirezarezvani/claude-skills --skill database-designer
Use when the user asks to design database schemas, plan data migrations, optimize queries, choose between SQL and NoSQL, or model data relationships.
npx skills add alirezarezvani/claude-skills --skill database-designer
A comprehensive database design skill that provides expert-level analysis, optimization, and migration capabilities for modern database systems. This skill combines theoretical principles with practical tools to help architects and developers create scalable, performant, and maintainable database schemas.
All paths relative to this skill folder; sample inputs in assets/.
Accepts SQL DDL or JSON schema (assets/sample_schema.sql / sample_schema.json). Output includes normalization findings, missing constraints, naming issues, and a Mermaid ERD — show the ERD to the user and fix flagged issues before optimizing.
Write the user's hot queries into a query-patterns JSON first (copy assets/sample_query_patterns.json). Output is a priority-ordered list of CREATE INDEX recommendations plus redundant-index removals.
--zero-downtime emits an expand-contract plan; --validate-only checks feasibility without generating SQL.
Re-run step 1 on the target schema and assert the issues found in the first pass are gone; run migration_generator.py --validate-only before handing over the migration.
→ See references/database-design-reference.md for details
Every migration must have a reversible counterpart. Name files with a timestamp prefix for ordering:
Use the expand-contract pattern to avoid locking or breaking running code:
down.sql in staging before deploying up.sql to production| Index Type | Use Case | Example |
|---|---|---|
| B-tree (default) | Equality, range, ORDER BY | CREATE INDEX idx_users_email ON users(email); |
| GIN | Full-text search, JSONB, arrays | CREATE INDEX idx_docs_body ON docs USING gin(to_tsvector('english', body)); |
| GiST | Geometry, range types, nearest-neighbor | CREATE INDEX idx_locations ON places USING gist(coords); |
| Partial | Subset of rows (reduce size) | CREATE INDEX idx_active ON users(email) WHERE active = true; |
| Covering | Index-only scans | CREATE INDEX idx_cov ON orders(customer_id) INCLUDE (total, created_at); |
Key signals to watch:
Symptoms: application issues one query per row (e.g., fetching related records in a loop).
Fixes:
JOIN or subquery to fetch in one round-tripselect_related / includes / with)| Tool | Protocol | Best For |
|---|---|---|
| PgBouncer | PostgreSQL | Transaction/statement pooling, low overhead |
| ProxySQL | MySQL | Query routing, read/write splitting |
| Built-in pool (HikariCP, SQLAlchemy pool) | Any | Application-level pooling |
Rule of thumb: Set pool size to (2 * CPU cores) + disk spindles. For cloud SSDs, start with 2 * vCPUs and tune.
SELECT queries to replicas; writes to primarypg_last_wal_replay_lsn() to detect lag before reading critical data| Criteria | PostgreSQL | MySQL | SQLite | SQL Server |
|---|---|---|---|---|
| Best for | Complex queries, JSONB, extensions | Web apps, read-heavy workloads | Embedded, dev/test, edge | Enterprise .NET stacks |
| JSON support | Excellent (JSONB + GIN) | Good (JSON type) | Minimal | Good (OPENJSON) |
| Replication | Streaming, logical | Group replication, InnoDB cluster | N/A | Always On AG |
| Licensing | Open source (PostgreSQL License) | Open source (GPL) / commercial | Public domain | Commercial |
| Max practical size | Multi-TB | Multi-TB | ~1 TB (single-writer) | Multi-TB |
When to choose:
| Database | Model | Use When |
|---|---|---|
| MongoDB | Document | Schema flexibility, rapid prototyping, content management |
| Redis | Key-value / cache | Session store, rate limiting, leaderboards, pub/sub |
| DynamoDB | Wide-column | Serverless AWS apps, single-digit-ms latency at any scale |
Use SQL as default. Reach for NoSQL only when the access pattern clearly benefits from it.
| Strategy | How It Works | Pros | Cons |
|---|---|---|---|
| Hash | shard = hash(key) % N | Even distribution | Resharding is expensive |
| Range | Shard by date or ID range | Simple, good for time-series | Hot spots on latest shard |
| Geographic | Shard by user region | Data locality, compliance | Cross-region queries are hard |
| Pattern | Consistency | Latency | Use Case |
|---|---|---|---|
| Synchronous | Strong | Higher write latency | Financial transactions |
| Asynchronous | Eventual | Low write latency | Read-heavy web apps |
| Semi-synchronous | At-least-one replica confirmed | Moderate | Balance of safety and speed |
python3 migration_generator.py --current current_schema.json --target target_schema.json --zero-downtime --format sql -o migration.sql-- INNER JOIN: only matching rows
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;
-- LEFT JOIN: all left rows, NULLs for non-matches
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;
-- Self-join: hierarchical data (employees/managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;-- Recursive CTE for org chart
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.depth + 1
FROM employees e INNER JOIN org o ON o.id = e.manager_id
)
SELECT * FROM org ORDER BY depth, name;-- ROW_NUMBER for pagination / dedup
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;
-- RANK with gaps, DENSE_RANK without gaps
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;
-- LAG/LEAD for comparing adjacent rows
SELECT date, revenue,
revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;-- FILTER clause (PostgreSQL) for conditional aggregation
SELECT
COUNT(*) AS total,
COUNT(*) FILTER (WHERE status = 'active') AS active,
AVG(amount) FILTER (WHERE amount > 0) AS avg_positive
FROM accounts;
-- GROUPING SETS for multi-level rollups
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ());migrations/
├── 20260101_000001_create_users.up.sql
├── 20260101_000001_create_users.down.sql
├── 20260115_000002_add_users_email_index.up.sql
└── 20260115_000002_add_users_email_index.down.sql-- Batch update to avoid long-running locks
UPDATE users SET email_normalized = LOWER(email)
WHERE id IN (SELECT id FROM users WHERE email_normalized IS NULL LIMIT 5000);
-- Repeat in a loop until 0 rows affectedEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;