# Sql Expert

> Expert SQL & relational databases: queries, schema design, indexing, query-plan optimization, and transactions (Postgres/MySQL/SQLite). Trigger keywords: SQL, database, Postgres, MySQL, SQLite, JOIN, index, EXPLAIN, query plan, slow query, N+1, normalization, transaction, isolation, deadlock, migration. Use for writing/optimizing queries, designing schemas, or fixing slow/locking queries.

- **Type:** Skill
- **Install:** `agentstack add skill-miaoge-ge-coding-agent-skills-sql-expert`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [Miaoge-Ge](https://agentstack.voostack.com/s/miaoge-ge)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [Miaoge-Ge](https://github.com/Miaoge-Ge)
- **Source:** https://github.com/Miaoge-Ge/coding-agent-skills/tree/main/plugins/sql-expert/skills/sql-expert

## Install

```sh
agentstack add skill-miaoge-ge-coding-agent-skills-sql-expert
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# SQL & Database Expert

> Think in sets, not loops. The database is smarter than your application loop. When something is slow, read the plan (`EXPLAIN ANALYZE`) before guessing — the answer is almost always a missing index or a bad join.

## When to Use
- Writing, debugging, or optimizing SQL.
- Schema design: tables, relationships, constraints, types, migrations.
- Slow queries, missing/unused indexes, N+1, lock contention.
- Transactions, isolation levels, and concurrency correctness.

## When NOT to Use
- ORM/app wiring → `nodejs-backend-expert` / language skill.
- Vector/semantic retrieval → `rag-expert`.
- Data-platform / warehouse-scale architecture → `software-architect`.

## Core Principles

### 1. Schema design
- Normalize to 3NF by default; denormalize only for a **measured** read win, and then keep it consistent (triggers/jobs).
- Right types: `timestamptz` (not naive timestamps), `numeric`/`decimal` for money (never float), `uuid`/`bigint` keys, native `enum`/`boolean`. Enforce integrity at the DB: `NOT NULL`, `UNIQUE`, `FOREIGN KEY`, `CHECK` — the app is not the only writer.

### 2. Query like a pro
- Select only needed columns (no `SELECT *` in app queries — it breaks covering indexes and over-fetches).
- Know your JOINs and beware **fan-out**: a one-to-many join multiplies rows and inflates `SUM`/`COUNT`. Pre-aggregate in a subquery/CTE.
- Kill **N+1** patterns: one query with a JOIN or `WHERE id IN (...)` instead of a query per row.
- `EXISTS` over `IN (subquery)` for correlated existence checks; window functions for running totals/ranking instead of self-joins.

### 3. Indexing
- Index columns in `WHERE`, `JOIN`, and `ORDER BY`. **Composite index order**: equality columns first, then the range/sort column. The index serves a left-to-right prefix.
- A function/expression on the column (`WHERE lower(email)=…`, `WHERE created_at::date=…`) defeats a plain index — index the expression or rewrite as a sargable range.
- Indexes speed reads, slow writes, and use space — add deliberately. Drop unused ones. Use partial/covering indexes for hot queries.

### 4. Read the plan & transactions
- `EXPLAIN (ANALYZE, BUFFERS)`. Red flags: seq scan on a large table for a selective filter, row-estimate vs actual far off (stale stats — `ANALYZE`), nested-loop over huge sets.
- Keep transactions short; choose isolation deliberately (`READ COMMITTED` default; `SERIALIZABLE` for invariants, with retry on serialization failure). Acquire locks in a consistent order to avoid deadlocks.

## Common Mistakes
- **`SELECT *`** in application code → over-fetch, fragile, no covering index.
- **Aggregates over a fanned-out JOIN** → doubled sums; aggregate before joining.
- **Functions on indexed columns in `WHERE`** → full scan.
- **Wrong composite-index column order** → index unused for the query.
- **Offset pagination on huge tables** (`OFFSET 100000`) → slow; use keyset/cursor pagination.
- **Floating-point money** → rounding errors; use `numeric`.
- **Implicit type mismatch** in joins/filters → silent full scans.

## Examples

**Avoid JOIN fan-out with a CTE; keyset pagination**
```sql
WITH totals AS (
  SELECT order_id, SUM(qty * unit_price) AS total
  FROM order_items GROUP BY order_id
)
SELECT o.id, o.created_at, t.total
FROM orders o
JOIN totals t ON t.order_id = o.id
WHERE (o.created_at, o.id) < ($1, $2)   -- keyset cursor, not OFFSET
ORDER BY o.created_at DESC, o.id DESC
LIMIT 20;
```

**Composite index matching a query**
```sql
-- WHERE tenant_id = $1 AND status = $2 ORDER BY created_at DESC
CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);
```

## See Also
- `nodejs-backend-expert` — calling the DB safely (pooling, parameterization).
- `performance-expert` — end-to-end latency and profiling.
- `api-design-expert` — cursor pagination contracts backed by SQL.
- `security-expert` — parameterization and least-privilege access.

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [Miaoge-Ge](https://github.com/Miaoge-Ge)
- **Source:** [Miaoge-Ge/coding-agent-skills](https://github.com/Miaoge-Ge/coding-agent-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-miaoge-ge-coding-agent-skills-sql-expert
- Seller: https://agentstack.voostack.com/s/miaoge-ge
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.