sql-database-assistant

---
name: sql-database-assistant
description: Use when the user asks to write SQL queries, optimize database performance, generate migrations, explore schemas, or work with ORMs like Prisma, Drizzle, TypeORM, or SQLAlchemy.
category: Engineering
version: 1.0.0
tools: []
---

# SQL Database Assistant

The operational companion to database design. Where schema architecture and ERD modeling own the "what should the data model look like" question, 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

## Natural Language to SQL

Translation sequence: identify entities (nouns to tables), identify relationships (verbs to JOINs or subqueries), identify filters (adjectives/conditions to WHERE clauses), identify aggregations ("total", "average", "count" to GROUP BY), identify ordering ("top", "latest", "highest" to ORDER BY + LIMIT).

**Top-N per group (window function)**
```sql
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
) ranked WHERE rn <= 3;
```

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

**Gap detection**
```sql
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);`

## Schema Exploration

Introspection queries by engine:

- **PostgreSQL — 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**: join `information_schema.table_constraints`, `key_column_usage`, and `constraint_column_usage` filtering `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**: join `sys.columns`, `sys.tables`, `sys.types` ordered by table/column id.

## Query Optimization

**EXPLAIN analysis workflow:** run `EXPLAIN ANALYZE` (PostgreSQL) or `EXPLAIN FORMAT=JSON` (MySQL); identify the costliest node (Seq Scan on large tables, Nested Loop with high row estimates); check for missing indexes on sequential scans over filtered columns; look for estimation errors (planned vs actual rows divergence signals stale statistics); evaluate JOIN order to 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 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: an application loop executing one query per parent row, ORM lazy-loading related entities inside a loop, query logs showing hundreds of identical SELECT patterns with different IDs. Fixes: eager loading (`include` in Prisma, `joinedload` in SQLAlchemy), batching with `WHERE id IN (...)`, or the DataLoader pattern for GraphQL resolvers.

## Migration Generation

**Adding a column (safe):** `ALTER TABLE users ADD COLUMN phone VARCHAR(20);` with a matching `DROP COLUMN` down script.

**Renaming a column (expand-contract):** add the new column, backfill it, deploy an app version reading both columns, deploy a version writing only the new column, then drop the old column.

**Adding a NOT NULL column (safe sequence):** add it nullable, backfill with a default, then set NOT NULL and DEFAULT in a final step.

**Non-blocking index creation (PostgreSQL):** `CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);`

**Data backfill strategies:** batch updates in chunks of 1,000-10,000 rows to avoid lock contention; run backfills as background jobs with progress tracking; dual-write to old and new columns during the transition period; run validation queries to verify row counts and data integrity after each batch.

**Rollback strategies:** every migration needs a reversible down script; for irreversible changes, back up affected tables before execution, use feature flags so the application can switch between old/new schema reads, or keep shadow tables during the migration window.

## Multi-Database Support

| 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 an adapter layer; test migrations on the target engine since `information_schema` varies between engines; use ISO date format (`'YYYY-MM-DD'`) everywhere; quote identifiers with double quotes (SQL standard) or backticks (MySQL).

## ORM Patterns

**Prisma** — schema-first with `model User { id Int @id @default(autoincrement()) email String @unique posts Post[] }`. 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 with `pgTable('users', { id: serial('id').primaryKey(), email: varchar('email',{length:255}).notNull().unique() })`. Query builder: `db.select().from(users).where(eq(users.email, email))`. Migrations: `drizzle-kit generate:pg` then `drizzle-kit push:pg`.

**TypeORM** — entity decorators (`@Entity()`, `@PrimaryGeneratedColumn()`, `@Column({unique:true})`, `@OneToMany(...)`). Repository pattern: `userRepo.find({ where: { email }, relations: ['posts'] })`. Migrations: `typeorm migration:generate -n AddUserEmail`.

**SQLAlchemy** — declarative models (`__tablename__`, `Column(Integer, primary_key=True)`, `relationship('Post', back_populates='author')`). Always use `with Session() as session:` context manager. Alembic migrations: `alembic revision --autogenerate -m "add user email"`.

## Data Integrity

**Constraint strategy:** every table needs a primary key (prefer surrogate keys — serial/UUID); foreign keys enforce referential integrity with an explicit `ON DELETE` behavior; UNIQUE constraints for business-level uniqueness (email, slug, API key); CHECK constraints validate ranges, enums, and business rules at the DB level; 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:** consistent lock ordering (always acquire locks in the same table/row order); short transactions (minimize time between first lock and commit); advisory locks (`pg_advisory_lock()`) for application-level coordination; retry logic that catches deadlock errors and retries with exponential backoff.

## Backup & Restore

- **PostgreSQL**: `pg_dump -Fc --no-owner dbname > backup.dump` / `pg_restore -d dbname --clean --no-owner backup.dump`. Point-in-time recovery needs WAL archiving plus a restore command.
- **MySQL**: `mysqldump --single-transaction --routines --triggers dbname > backup.sql` / `mysql dbname < backup.sql`. PITR via binary logs (`mysqlbinlog --start-datetime=...`).
- **SQLite**: `sqlite3 dbname ".backup backup.db"` (safe with concurrent reads).
- **Best practices**: automate backups on a schedule (never manual-only), verify restores periodically, and keep backups off the primary host.