# Language Sql

> SQL idioms — query tuning, EXPLAIN plans, table definitions, constraints, indexes, transactions, window functions, CTEs, and pagination. Auto-load when working with .sql files or migrations, or when the user mentions SQL syntax, SELECT, JOIN, EXPLAIN, CTE, window functions, transaction isolation, deadlocks, or SQL indexes.

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

## Install

```sh
agentstack add skill-lugassawan-swe-workbench-language-sql
```

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

## About

# SQL

## Query optimization basics
- Start with the access pattern: filters, joins, grouping, sorting, and result size.
- Index columns used for selective `WHERE`, join keys, and stable `ORDER BY` clauses.
- Prefer narrow projections over `SELECT *`; return only the columns callers need.
- Avoid accidental row multiplication in joins; check cardinality before adding `DISTINCT`.
- Watch for N+1 query loops at application boundaries.

## EXPLAIN and EXPLAIN ANALYZE
- Use `EXPLAIN` to inspect the planned access path before changing indexes or query shape.
- Use `EXPLAIN ANALYZE` when you need actual timing and row counts; run it against safe data and statements.
- Compare estimated vs actual rows. Large gaps often mean stale statistics, skewed data, or missing predicates.
- Optimize the highest-cost operation first, but confirm the full query got faster.

## Schema design
- Model durable facts, not current screens. Let queries influence indexes, not table names.
- Choose primary keys deliberately; use foreign keys for integrity unless there is a measured reason not to.
- Normalize to remove update anomalies, then denormalize only for proven read pressure.
- Encode invariants with constraints: `NOT NULL`, `UNIQUE`, `CHECK`, and referential actions.
- Plan migrations as expand-and-contract changes when existing clients need compatibility.

## Transactions and isolation
- Keep transactions short; do not wait on users or remote services while holding locks.
- Pick the weakest isolation level that preserves correctness for the workflow.
- Know the anomalies you are allowing: dirty reads, non-repeatable reads, phantoms, and write skew.
- Use optimistic concurrency with version columns when conflicts are rare and retriable.

## Deadlock avoidance
- Touch shared tables and rows in a consistent order across code paths.
- Lock only what you need, as late as possible, and commit as soon as the invariant is protected.
- Add retry logic for deadlock and serialization failures; they are expected under contention.
- Index foreign keys and hot predicates so updates do not scan and lock more rows than intended.

## Window functions
- Use window functions for rankings, running totals, deduplication, and "top N per group" queries.
- Keep `PARTITION BY` and `ORDER BY` explicit; frame clauses matter for aggregates.

```sql
SELECT customer_id, order_id, total,
       row_number() OVER (
         PARTITION BY customer_id
         ORDER BY created_at DESC
       ) AS recency_rank
FROM orders;
```

## CTEs vs subqueries
- Use CTEs to name meaningful intermediate results or reuse the same derived relation.
- Use subqueries when the scope is local and the surrounding query stays readable.
- Check your database's optimizer behavior; some engines inline CTEs, others may materialize them.
- Do not use CTEs as a performance hint unless the engine documents that behavior.

## Pagination patterns
- Prefer keyset pagination for large or frequently changing result sets.
- Use `LIMIT`/`OFFSET` only for small, stable lists; deep offsets get slower and can skip or duplicate rows.
- Make ordering deterministic with a unique tie-breaker.

```sql
SELECT id, customer_id, created_at, total
FROM orders
WHERE (created_at, id)  .` (replace `` with e.g. `ansi`, `postgres`, `bigquery` — check `.sqlfluff` config or project README)
- **Lint:** `sqlfluff lint --dialect  .`
- **Test:** engine-specific (pgTAP for PostgreSQL, `dbt test` if using dbt)

## Avoid
- Schema changes without rollback or compatibility planning.
- Unbounded queries in production paths.
- Relying on implicit ordering without `ORDER BY`.
- Never build SQL by concatenating untrusted input — use parameterized queries or prepared statements. ORM raw-query escape hatches (e.g. Django's `extra()`, SQLAlchemy's `text()`) are equally dangerous. See `swe-workbench:principle-security`.

## Source & license

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

- **Author:** [lugassawan](https://github.com/lugassawan)
- **Source:** [lugassawan/swe-workbench](https://github.com/lugassawan/swe-workbench)
- **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-lugassawan-swe-workbench-language-sql
- Seller: https://agentstack.voostack.com/s/lugassawan
- 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%.