SQL

Master relational databases with SQL. Schema design, queries, performance, migrations for PostgreSQL, MySQL, SQLite, SQL Server.

Security Scan

This is a documentation-only SQL skill (queries, schema patterns, ops) that is internally consistent with no unexpected installs or credential requests.

Purpose & Capability

The name/description match the content: guidance covers SQLite, PostgreSQL, MySQL, and SQL Server and the declared required binaries (sqlite3, psql, mysql, sqlcmd) are exactly the clients you would expect for those databases.

Instruction Scope

SKILL.md and supporting files are procedural/documentation showing CLI commands, SQL examples, and operational procedures. They reference database files, standard DB client commands, and common admin paths (e.g., /var/lib/postgresql/data) which is expected for operations guidance and not out-of-scope data collection.

Install Mechanism

No install specification and no code files — the skill is instruction-only, so nothing is downloaded or written to disk by the skill itself.

Credentials

The skill does not request environment variables, credentials, or config paths. Examples show typical connection flags (host/user) but the skill itself does not declare or require secrets, which is proportional to its purpose.

Persistence & Privilege

always=false and the skill does not request persistent system privileges or modify other skills' configuration. It is user-invocable and may be invoked autonomously by the agent (platform default), which is expected for an instruction skill.

Assessment

This skill is documentation and appears coherent and safe. Before running any example commands: (1) don't paste live credentials into sample commands shown here; replace placeholders and validate hosts/ports; (2) avoid running migration/restore/drop commands against production without backups and review; (3) some operations reference system paths or require elevated privileges (e.g., pg_basebackup to /var/lib/postgresql/data) — ensure you have proper permissions and that commands are appropriate for your environment; (4) the skill does not install software, so ensure the appropriate DB clients are present if you plan to run CLI examples.

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

Topic	File
Query patterns	patterns.md
Schema design	schemas.md
Operations	operations.md

Core Rules

1. Choose the Right Database

Use Case	Database	Why
Local/embedded	SQLite	Zero setup, single file
General production	PostgreSQL	Best standards, JSONB, extensions
Legacy/hosting	MySQL	Wide hosting support
Enterprise/.NET	SQL Server	Windows integration

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

Feature	PostgreSQL	MySQL	SQLite	SQL Server
LIMIT	LIMIT n	LIMIT n	LIMIT n	TOP n
UPSERT	ON CONFLICT	ON DUPLICATE KEY	ON CONFLICT	MERGE
Boolean	true/false	1/0	1/0	1/0
Concat	||	CONCAT()	||	+

---

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