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

| Script | Purpose |

|--------|---------|

| scripts/query_optimizer.py | Static analysis of SQL queries for performance issues |

| scripts/migration_generator.py | Generate migration file templates from change descriptions |

| scripts/schema_explorer.py | Generate schema documentation from introspection queries |

---

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) AS rn
  FROM employees
) ranked WHERE rn <= 3;

Running totals

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM transactions;

Gap detection

SELECT curr.id, curr.seq_num, prev.seq_num AS prev_seq
FROM records curr
LEFT JOIN records prev ON prev.seq_num = curr.seq_num - 1
WHERE prev.id IS NULL AND curr.seq_num > 1;

UPSERT (PostgreSQL)

INSERT INTO settings (key, value, updated_at)
VALUES ('theme', 'dark', NOW())
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value, updated_at = EXCLUDED.updated_at;

UPSERT (MySQL)

INSERT INTO settings (key_name, value, updated_at)
VALUES ('theme', 'dark', NOW())
ON DUPLICATE KEY UPDATE value = VALUES(value), updated_at = VALUES(updated_at);

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

---

Schema Exploration

Introspection Queries

PostgreSQL — list tables and columns

SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

PostgreSQL — foreign keys

SELECT tc.table_name, kcu.column_name,
  ccu.table_name AS foreign_table, ccu.column_name AS foreign_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

MySQL — table sizes

SELECT table_name, table_rows,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length DESC;

SQLite — schema dump

SELECT name, sql FROM sqlite_master WHERE type = 'table' ORDER BY name;

SQL Server — columns with types

SELECT t.name AS table_name, c.name AS column_name,
  ty.name AS data_type, c.max_length, c.is_nullable
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
ORDER BY t.name, c.column_id;

Generating Documentation from Schema

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

python scripts/schema_explorer.py --dialect postgres --tables all --format md
python scripts/schema_explorer.py --dialect mysql --tables users,orders --format json --json

---

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

| Anti-Pattern | Rewrite |

|-------------|---------|

| SELECT * FROM orders | SELECT id, status, total FROM orders (explicit columns) |

| WHERE YEAR(created_at) = 2025 | WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01' (sargable) |

| Correlated subquery in SELECT | LEFT JOIN with aggregation |

| NOT IN (SELECT ...) with NULLs | NOT EXISTS (SELECT 1 ...) |

| UNION (dedup) when not needed | UNION ALL |

| LIKE '%search%' | Full-text search index (GIN/FULLTEXT) |

| ORDER BY RAND() | Application-side random sampling or TABLESAMPLE |

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

python scripts/query_optimizer.py --query "SELECT * FROM orders WHERE status = 'pending'" --dialect postgres
python scripts/query_optimizer.py --query queries.sql --dialect mysql --json

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

---

Migration Generation

Zero-Downtime Migration Patterns

Adding a column (safe)

-- Up
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Down
ALTER TABLE users DROP COLUMN phone;

Renaming a column (expand-contract)

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
-- Step 2: Backfill
UPDATE users SET full_name = name;
-- Step 3: Deploy app reading both columns
-- Step 4: Deploy app writing only new column
-- Step 5: Drop old column
ALTER TABLE users DROP COLUMN name;

Adding a NOT NULL column (safe sequence)

-- Step 1: Add nullable
ALTER TABLE orders ADD COLUMN region VARCHAR(50);
-- Step 2: Backfill with default
UPDATE orders SET region = 'unknown' WHERE region IS NULL;
-- Step 3: Add constraint
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;
ALTER TABLE orders ALTER COLUMN region SET DEFAULT 'unknown';

Index creation (non-blocking, PostgreSQL)

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

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

python scripts/migration_generator.py --change "add email_verified boolean to users" --dialect postgres --format sql
python scripts/migration_generator.py --change "rename column name to full_name in customers" --dialect mysql --format alembic --json

---

Multi-Database Support

Dialect Differences

| Feature | PostgreSQL | MySQL | SQLite | SQL Server |

|---------|-----------|-------|--------|------------|

| UPSERT | ON CONFLICT DO UPDATE | ON DUPLICATE KEY UPDATE | ON CONFLICT DO UPDATE | MERGE |

| Boolean | Native BOOLEAN | TINYINT(1) | INTEGER | BIT |

| Auto-increment | SERIAL / GENERATED | AUTO_INCREMENT | INTEGER PRIMARY KEY | IDENTITY |

| JSON | JSONB (indexed) | JSON | Text (ext) | NVARCHAR(MAX) |

| Array | Native ARRAY | Not supported | Not supported | Not supported |

| CTE (recursive) | Full support | 8.0+ | 3.8.3+ | Full support |

| Window functions | Full support | 8.0+ | 3.25.0+ | Full support |

| Full-text search | tsvector + GIN | FULLTEXT index | FTS5 extension | Full-text catalog |

| LIMIT/OFFSET | LIMIT n OFFSET m | LIMIT n OFFSET m | LIMIT n OFFSET m | OFFSET m ROWS FETCH NEXT n ROWS ONLY |

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User   @relation(fields: [authorId], references: [id])
  authorId Int
}

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').defaultNow(),
});

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

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  email: string;

  @OneToMany(() => Post, post => post.author)
  posts: Post[];
}

Repository pattern: userRepo.find({ where: { email }, relations: ['posts'] })

Migrations: npx typeorm migration:generate -n AddUserEmail

SQLAlchemy

Declarative models

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    email = Column(String(255), unique=True, nullable=False)
    name = Column(String(255))
    posts = relationship('Post', back_populates='author')

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

| Level | Dirty Read | Non-Repeatable Read | Phantom Read | Use Case |

|-------|-----------|-------------------|-------------|----------|

| READ UNCOMMITTED | Yes | Yes | Yes | Never recommended |

| READ COMMITTED | No | Yes | Yes | Default for PostgreSQL, general OLTP |

| REPEATABLE READ | No | No | Yes (InnoDB: No) | Financial calculations |

| SERIALIZABLE | No | No | No | Critical consistency (billing, inventory) |

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

# Full backup
pg_dump -Fc --no-owner dbname > backup.dump
# Restore
pg_restore -d dbname --clean --no-owner backup.dump
# Point-in-time recovery: configure WAL archiving + restore_command

MySQL

# Full backup
mysqldump --single-transaction --routines --triggers dbname > backup.sql
# Restore
mysql dbname < backup.sql
# Binary log for PITR: mysqlbinlog --start-datetime="2025-01-01 00:00:00" binlog.000001

SQLite

# Backup (safe with concurrent reads)
sqlite3 dbname ".backup backup.db"

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

| Anti-Pattern | Problem | Fix |

|-------------|---------|-----|

| SELECT * | Transfers unnecessary data, breaks on schema changes | Explicit column list |

| Missing indexes on FK columns | Slow JOINs and cascading deletes | Add indexes on all foreign keys |

| N+1 queries | 1 + N round trips to database | Eager loading or batch queries |

| Implicit type coercion | WHERE id = '123' prevents index use | Match types in predicates |

| No connection pooling | Exhausts connections under load | PgBouncer, ProxySQL, or ORM pool |

| Unbounded queries | No LIMIT risks returning millions of rows | Always paginate |

| Storing money as FLOAT | Rounding errors | Use DECIMAL(19,4) or integer cents |

| God tables | One table with 50+ columns | Normalize or use vertical partitioning |

| Soft deletes everywhere | Complicates every query with WHERE deleted_at IS NULL | Archive tables or event sourcing |

| Raw string concatenation | SQL injection | Parameterized queries always |

---

Cross-References

| Skill | Relationship |

|-------|-------------|

| database-designer | Schema architecture, normalization analysis, ERD generation |

| database-schema-designer | Visual ERD modeling, relationship mapping |

| migration-architect | Complex multi-step migration orchestration |

| api-design-reviewer | Ensuring API endpoints align with query patterns |

| observability-platform | Query performance monitoring, slow query alerts |