SQL

SQL

Master relational databases from the command line. Covers SQLite, PostgreSQL, MySQL, and SQL Server with battle-tested patterns for schema design, querying, migrations, and operations.

When to Use

Working with relational databases—designing schemas, writing queries, building migrations, optimizing performance, or managing backups. Applies to SQLite, PostgreSQL, MySQL, and SQL Server.

Quick Reference

Core Rules

1. Choose the Right Database

2. Always Parameterize Queries

# ❌ NEVER
cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")

# ✅ ALWAYS
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

3. Index Your Filters

Any column in WHERE, JOIN ON, or ORDER BY on large tables needs an index.

4. Use Transactions

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

5. Prefer EXISTS Over IN

-- ✅ Faster (stops at first match)
SELECT * FROM orders o WHERE EXISTS (
  SELECT 1 FROM users u WHERE u.id = o.user_id AND u.active
);

Quick Start

SQLite

sqlite3 mydb.sqlite                              # Create/open
sqlite3 mydb.sqlite "SELECT * FROM users;"       # Query
sqlite3 -header -csv mydb.sqlite "SELECT *..." > out.csv
sqlite3 mydb.sqlite "PRAGMA journal_mode=WAL;"   # Better concurrency

PostgreSQL

psql -h localhost -U myuser -d mydb              # Connect
psql -c "SELECT NOW();" mydb                     # Query
psql -f migration.sql mydb                       # Run file
\dt  \d+ users  \di+                             # List tables/indexes

MySQL

mysql -h localhost -u root -p mydb               # Connect
mysql -e "SELECT NOW();" mydb                    # Query

SQL Server

sqlcmd -S localhost -U myuser -d mydb            # Connect
sqlcmd -Q "SELECT GETDATE()"                     # Query
sqlcmd -S localhost -d mydb -E                   # Windows auth

Common Traps

NULL Traps

• NOT IN (subquery) returns empty if subquery has NULL → use NOT EXISTS
• NULL = NULL is NULL, not true → use IS NULL
• COUNT(column) excludes NULLs, COUNT(*) counts all

Index Killers

• Functions on columns → WHERE YEAR(date) = 2024 scans full table
• Type conversion → WHERE varchar_col = 123 skips index
• LIKE '%term' can't use index → only LIKE 'term%' works
• Composite (a, b) won't help filtering only on b

Join Traps

• LEFT JOIN with WHERE on right table becomes INNER JOIN
• Missing JOIN condition = Cartesian product
• Multiple LEFT JOINs can multiply rows

EXPLAIN

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 5;

-- SQLite
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 5;

Red flags:

• Seq Scan on large tables → needs index
• Rows Removed by Filter high → index doesn't cover filter
• Actual vs estimated rows differ → run ANALYZE tablename;

Index Strategy

-- Composite index (equality first, range last)
CREATE INDEX idx_orders ON orders(user_id, status);

-- Covering index (avoids table lookup)
CREATE INDEX idx_orders ON orders(user_id) INCLUDE (total);

-- Partial index (smaller, faster)
CREATE INDEX idx_pending ON orders(user_id) WHERE status = 'pending';

Portability

Related Skills

Install with clawhub install <slug> if user confirms:

• prisma — Node.js ORM
• sqlite — SQLite-specific patterns
• analytics — data analysis queries

Feedback

• If useful: clawhub star sql
• Stay updated: clawhub sync