Agent skill

sql-patterns

SQL query patterns, optimization, and database best practices. Use when writing SQL queries, discussing database design, query optimization, or working with PostgreSQL, MySQL, SQLite. Triggers on mentions of SQL, SELECT, JOIN, INDEX, query optimization, database schema, PostgreSQL, MySQL.

View SKILL.md on GitHub Repository
Stars 0
Forks 0

Install this agent skill to your Project

npx add-skill https://github.com/eous/dotclaude/tree/main/skills/sql-patterns

SKILL.md

SQL Patterns and Best Practices

Query Fundamentals

SELECT Best Practices

sql
-- Bad: SELECT *
SELECT * FROM users;

-- Good: Explicit columns
SELECT id, name, email, created_at
FROM users
WHERE active = true;

Filtering

sql
-- Use indexes effectively
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND status = 'completed';

-- Avoid functions on indexed columns
-- Bad
WHERE YEAR(created_at) = 2024
-- Good
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

JOINs

JOIN Types

sql
-- INNER JOIN: Only matching rows
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: All left rows + matching right
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Use table aliases consistently

Avoid N+1 Queries

sql
-- Bad: Query per user for orders
-- Application loops and queries

-- Good: Single query with JOIN
SELECT u.id, u.name, o.id as order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true;

Aggregations

GROUP BY

sql
SELECT
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    SUM(quantity) as total_quantity
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

Window Functions

sql
-- Running totals
SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

-- Rank within groups
SELECT
    category,
    name,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) as price_rank
FROM products;

-- Previous/next values
SELECT
    date,
    amount,
    LAG(amount) OVER (ORDER BY date) as prev_amount,
    LEAD(amount) OVER (ORDER BY date) as next_amount
FROM transactions;

Indexing

When to Index

sql
-- Primary keys (automatic)
-- Foreign keys
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Frequently filtered columns
CREATE INDEX idx_users_email ON users(email);

-- Composite indexes (order matters)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Index Guidelines

  • Index columns in WHERE, JOIN, ORDER BY
  • Put equality conditions first in composite indexes
  • Consider partial indexes for filtered queries
  • Don't over-index (slows writes)
sql
-- Partial index
CREATE INDEX idx_active_users ON users(email)
WHERE active = true;

-- Covering index
CREATE INDEX idx_orders_covering
ON orders(user_id, status)
INCLUDE (total, created_at);

Common Table Expressions (CTEs)

Readable Queries

sql
WITH active_users AS (
    SELECT id, name
    FROM users
    WHERE active = true
),
user_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    u.name,
    COALESCE(o.order_count, 0) as recent_orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

Recursive CTEs

sql
-- Hierarchical data (org chart, categories)
WITH RECURSIVE category_tree AS (
    -- Base case
    SELECT id, name, parent_id, 0 as depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive case
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

Transactions

ACID Compliance

sql
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Verify
SELECT id, balance FROM accounts WHERE id IN (1, 2);

COMMIT;
-- or ROLLBACK on error;

Isolation Levels

sql
-- Read committed (default in PostgreSQL)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Serializable (strictest)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Performance Optimization

EXPLAIN ANALYZE

sql
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

Common Optimizations

sql
-- Pagination with OFFSET (slow for large offsets)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 1000;

-- Keyset pagination (faster)
SELECT * FROM products
WHERE id > 1000
ORDER BY id
LIMIT 20;

-- EXISTS vs IN
-- EXISTS is often faster for subqueries
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
    AND o.total > 100
);

Schema Design

Normalization

sql
-- Avoid data duplication
-- Use foreign keys for relationships
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Constraints

sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    sku VARCHAR(50) UNIQUE NOT NULL,
    category_id INTEGER REFERENCES categories(id)
);

Anti-Patterns to Avoid

  • SELECT * in production
  • Missing indexes on foreign keys
  • N+1 query patterns
  • Large OFFSET pagination
  • Storing arrays/JSON when relations fit better
  • Missing NOT NULL constraints
  • VARCHAR(255) by default without consideration
  • Functions on indexed columns in WHERE

Recommended Agent Skills

Expand your agent's capabilities with these related and highly-rated skills.

eous/dotclaude

typescript-best-practices

TypeScript development best practices, patterns, and conventions. Use when writing TypeScript code, reviewing .ts/.tsx files, discussing type safety, generics, utility types, or TypeScript project configuration. Triggers on mentions of TypeScript, tsconfig, type inference, generics, discriminated unions, React TypeScript.

0 0
Explore
eous/dotclaude

api-design

REST and GraphQL API design patterns, best practices, and conventions. Use when designing APIs, discussing HTTP methods, status codes, versioning, or API documentation. Triggers on mentions of REST, API, GraphQL, endpoint, HTTP methods, OpenAPI, swagger, API versioning.

0 0
Explore
eous/dotclaude

testing-patterns

Software testing patterns, strategies, and best practices. Use when writing tests, discussing test architecture, mocking, fixtures, or test organization. Triggers on mentions of testing, unit tests, integration tests, pytest, jest, mocking, fixtures, TDD, test coverage.

0 0
Explore
eous/dotclaude

security-checklist

Application security best practices and vulnerability prevention. Use when reviewing code for security issues, implementing authentication, or discussing OWASP vulnerabilities. Triggers on mentions of security, authentication, authorization, XSS, SQL injection, CSRF, OWASP, encryption, secrets.

0 0
Explore
eous/dotclaude

python-best-practices

Python development best practices, patterns, and conventions. Use when writing Python code, reviewing .py files, discussing pytest, asyncio, type hints, pydantic, dataclasses, or Python project structure. Triggers on mentions of Python, pytest, mypy, ruff, black, FastAPI, Django, Flask.

0 0
Explore
eous/dotclaude

memory-generator

Generate CLAUDE.md project memory files by exploring codebases. Use when user asks to "generate memory", "create CLAUDE.md", "document this project", "understand this codebase", or starts work on a new/unfamiliar repository. Triggers on new project onboarding and documentation requests.

0 0
Explore

Didn't find tool you were looking for?

Be as detailed as possible for better results