sql-database-assistant

SQL Database Assistant - POWERFUL Tier Skill

Overview

The operational companion to database design. While database-designer focuses on schema architecture and database-schema-designer handles ERD modeling, this skill covers the day-to-day: writing queries, optimizing performance, generating migrations, and bridging the gap between application code and database engines.

Core Capabilities

• Natural Language to SQL — translate requirements into correct, performant queries
• Schema Exploration — introspect live databases across PostgreSQL, MySQL, SQLite, SQL Server
• Query Optimization — EXPLAIN analysis, index recommendations, N+1 detection, rewrite patterns
• Migration Generation — up/down scripts, zero-downtime strategies, rollback plans
• ORM Integration — Prisma, Drizzle, TypeORM, SQLAlchemy patterns and escape hatches
• Multi-Database Support — dialect-aware SQL with compatibility guidance

Tools

Natural Language to SQL

Translation Patterns

When converting requirements to SQL, follow this sequence:

1. Identify entities — map nouns to tables
2. Identify relationships — map verbs to JOINs or subqueries
3. Identify filters — map adjectives/conditions to WHERE clauses
4. Identify aggregations — map "total", "average", "count" to GROUP BY
5. Identify ordering — map "top", "latest", "highest" to ORDER BY + LIMIT

Common Query Templates

Top-N per group (window function)

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC

Running totals

Gap detection

UPSERT (PostgreSQL)

UPSERT (MySQL)

See references/query_patterns.md for JOINs, CTEs, window functions, JSON operations, and more.

Schema Exploration

Introspection Queries

PostgreSQL — list tables and columns

PostgreSQL — foreign keys

MySQL — table sizes

SQLite — schema dump

SQL Server — columns with types

Generating Documentation from Schema

Use scripts/schema_explorer.py to produce markdown or JSON documentation:

Query Optimization

EXPLAIN Analysis Workflow

1. Run EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL)
2. Identify the costliest node — Seq Scan on large tables, Nested Loop with high row estimates
3. Check for missing indexes — sequential scans on filtered columns
4. Look for estimation errors — planned vs actual rows divergence signals stale statistics
5. Evaluate JOIN order — ensure the smallest result set drives the join

Index Recommendation Checklist

• Columns in WHERE clauses with high selectivity
• Columns in JOIN conditions (foreign keys)
• Columns in ORDER BY when combined with LIMIT
• Composite indexes matching multi-column WHERE predicates (most selective column first)
• Partial indexes for queries with constant filters (e.g., WHERE status = 'active')
• Covering indexes to avoid table lookups for read-heavy queries

Query Rewriting Patterns

N+1 Detection

Symptoms:

• Application loop that executes one query per parent row
• ORM lazy-loading related entities inside a loop
• Query log shows hundreds of identical SELECT patterns with different IDs

Fixes:

• Use eager loading (include in Prisma, joinedload in SQLAlchemy)
• Batch queries with WHERE id IN (...)
• Use DataLoader pattern for GraphQL resolvers

Static Analysis Tool

See references/optimization_guide.md for EXPLAIN plan reading, index types, and connection pooling.

Migration Generation

Zero-Downtime Migration Patterns

Adding a column (safe)

Renaming a column (expand-contract)

Adding a NOT NULL column (safe sequence)

Index creation (non-blocking, PostgreSQL)

Data Backfill Strategies

• Batch updates — process in chunks of 1000-10000 rows to avoid lock contention
• Background jobs — run backfills asynchronously with progress tracking
• Dual-write — write to old and new columns during transition period
• Validation queries — verify row counts and data integrity after each batch

Rollback Strategies

Every migration must have a reversible down script. For irreversible changes:

1. Backup before execution — pg_dump the affected tables
2. Feature flags — application can switch between old/new schema reads
3. Shadow tables — keep a copy of the original table during migration window

Migration Generator Tool

Multi-Database Support

Dialect Differences

Compatibility Tips

• Always use parameterized queries — prevents SQL injection across all dialects
• Avoid dialect-specific functions in shared code — wrap in adapter layer
• Test migrations on target engine — information_schema varies between engines
• Use ISO date format — 'YYYY-MM-DD' works everywhere
• Quote identifiers — use double quotes (SQL standard) or backticks (MySQL)

ORM Patterns

Prisma

Schema definition

Migrations: npx prisma migrate dev --name add_user_email Query API: prisma.user.findMany({ where: { email: { contains: '@' } }, include: { posts: true } }) Raw SQL escape hatch: prisma.$queryRaw\SELECT * FROM users WHERE id = ${userId}``

Drizzle

Schema-first definition

Query builder: db.select().from(users).where(eq(users.email, email)) Migrations: npx drizzle-kit generate:pg then npx drizzle-kit push:pg

TypeORM

Entity decorators

Repository pattern: userRepo.find({ where: { email }, relations: ['posts'] }) Migrations: npx typeorm migration:generate -n AddUserEmail

SQLAlchemy

Declarative models

Session management: Always use with Session() as session: context manager Alembic migrations: alembic revision --autogenerate -m "add user email"

See references/orm_patterns.md for side-by-side comparisons and migration workflows per ORM.

Data Integrity

Constraint Strategy

• Primary keys — every table must have one; prefer surrogate keys (serial/UUID)
• Foreign keys — enforce referential integrity; define ON DELETE behavior explicitly
• UNIQUE constraints — for business-level uniqueness (email, slug, API key)
• CHECK constraints — validate ranges, enums, and business rules at the DB level
• NOT NULL — default to NOT NULL; make nullable only when genuinely optional

Transaction Isolation Levels

Deadlock Prevention

1. Consistent lock ordering — always acquire locks in the same table/row order
2. Short transactions — minimize time between first lock and commit
3. Advisory locks — use pg_advisory_lock() for application-level coordination
4. Retry logic — catch deadlock errors and retry with exponential backoff

Backup & Restore

PostgreSQL

MySQL

SQLite

Backup Best Practices

• Automate — cron or systemd timer, never manual-only
• Test restores — untested backups are not backups
• Offsite copies — S3, GCS, or separate region
• Retention policy — daily for 7 days, weekly for 4 weeks, monthly for 12 months
• Monitor backup size and duration — sudden changes signal issues

Anti-Patterns

Cross-References