{"skill":{"slug":"sql-toolkit","displayName":"SQL Toolkit","summary":"Query, design, migrate, and optimize SQL databases. Use when working with SQLite, PostgreSQL, or MySQL — schema design, writing queries, creating migrations, indexing, backup/restore, and debugging slow queries. No ORMs required.","description":"---\nname: sql-toolkit\ndescription: Query, design, migrate, and optimize SQL databases. Use when working with SQLite, PostgreSQL, or MySQL — schema design, writing queries, creating migrations, indexing, backup/restore, and debugging slow queries. No ORMs required.\nmetadata: {\"clawdbot\":{\"emoji\":\"🗄️\",\"requires\":{\"anyBins\":[\"sqlite3\",\"psql\",\"mysql\"]},\"os\":[\"linux\",\"darwin\",\"win32\"]}}\n---\n\n# SQL Toolkit\n\nWork with relational databases directly from the command line. Covers SQLite, PostgreSQL, and MySQL with patterns for schema design, querying, migrations, indexing, and operations.\n\n## When to Use\n\n- Creating or modifying database schemas\n- Writing complex queries (joins, aggregations, window functions, CTEs)\n- Building migration scripts\n- Optimizing slow queries with indexes and EXPLAIN\n- Backing up and restoring databases\n- Quick data exploration with SQLite (zero setup)\n\n## SQLite (Zero Setup)\n\nSQLite is included with Python and available on every system. Use it for local data, prototyping, and single-file databases.\n\n### Quick Start\n\n```bash\n# Create/open a database\nsqlite3 mydb.sqlite\n\n# Import CSV directly\nsqlite3 mydb.sqlite \".mode csv\" \".import data.csv mytable\" \"SELECT COUNT(*) FROM mytable;\"\n\n# One-liner queries\nsqlite3 mydb.sqlite \"SELECT * FROM users WHERE created_at > '2026-01-01' LIMIT 10;\"\n\n# Export to CSV\nsqlite3 -header -csv mydb.sqlite \"SELECT * FROM orders;\" > orders.csv\n\n# Interactive mode with headers and columns\nsqlite3 -header -column mydb.sqlite\n```\n\n### Schema Operations\n\n```sql\n-- Create table\nCREATE TABLE users (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    email TEXT NOT NULL UNIQUE,\n    name TEXT NOT NULL,\n    created_at TEXT DEFAULT (datetime('now')),\n    updated_at TEXT DEFAULT (datetime('now'))\n);\n\n-- Create with foreign key\nCREATE TABLE orders (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    total REAL NOT NULL CHECK(total >= 0),\n    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','paid','shipped','cancelled')),\n    created_at TEXT DEFAULT (datetime('now'))\n);\n\n-- Add column\nALTER TABLE users ADD COLUMN phone TEXT;\n\n-- Create index\nCREATE INDEX idx_orders_user_id ON orders(user_id);\nCREATE UNIQUE INDEX idx_users_email ON users(email);\n\n-- View schema\n.schema users\n.tables\n```\n\n## PostgreSQL\n\n### Connection\n\n```bash\n# Connect\npsql -h localhost -U myuser -d mydb\n\n# Connection string\npsql \"postgresql://user:pass@localhost:5432/mydb?sslmode=require\"\n\n# Run single query\npsql -h localhost -U myuser -d mydb -c \"SELECT NOW();\"\n\n# Run SQL file\npsql -h localhost -U myuser -d mydb -f migration.sql\n\n# List databases\npsql -l\n```\n\n### Schema Design Patterns\n\n```sql\n-- Use UUIDs for distributed-friendly primary keys\nCREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";\n\nCREATE TABLE users (\n    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\n    email TEXT NOT NULL,\n    name TEXT NOT NULL,\n    password_hash TEXT NOT NULL,\n    role TEXT NOT NULL DEFAULT 'user' CHECK(role IN ('user','admin','moderator')),\n    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),\n    CONSTRAINT users_email_unique UNIQUE(email)\n);\n\n-- Auto-update updated_at\nCREATE OR REPLACE FUNCTION update_modified_column()\nRETURNS TRIGGER AS $$\nBEGIN\n    NEW.updated_at = NOW();\n    RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\nCREATE TRIGGER update_users_modtime\n    BEFORE UPDATE ON users\n    FOR EACH ROW EXECUTE FUNCTION update_modified_column();\n\n-- Enum type (PostgreSQL-specific)\nCREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'delivered', 'cancelled');\n\nCREATE TABLE orders (\n    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\n    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    status order_status NOT NULL DEFAULT 'pending',\n    total NUMERIC(10,2) NOT NULL CHECK(total >= 0),\n    metadata JSONB DEFAULT '{}',\n    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\n);\n\n-- Partial index (only index active orders — smaller, faster)\nCREATE INDEX idx_orders_active ON orders(user_id, created_at)\n    WHERE status NOT IN ('delivered', 'cancelled');\n\n-- GIN index for JSONB queries\nCREATE INDEX idx_orders_metadata ON orders USING GIN(metadata);\n```\n\n### JSONB Queries (PostgreSQL)\n\n```sql\n-- Store JSON\nINSERT INTO orders (user_id, total, metadata)\nVALUES ('...', 99.99, '{\"source\": \"web\", \"coupon\": \"SAVE10\", \"items\": [{\"sku\": \"A1\", \"qty\": 2}]}');\n\n-- Query JSON fields\nSELECT * FROM orders WHERE metadata->>'source' = 'web';\nSELECT * FROM orders WHERE metadata->'items' @> '[{\"sku\": \"A1\"}]';\nSELECT metadata->>'coupon' AS coupon, COUNT(*) FROM orders GROUP BY 1;\n\n-- Update JSON field\nUPDATE orders SET metadata = jsonb_set(metadata, '{source}', '\"mobile\"') WHERE id = '...';\n```\n\n## MySQL\n\n### Connection\n\n```bash\nmysql -h localhost -u root -p mydb\nmysql -h localhost -u root -p -e \"SELECT NOW();\" mydb\n```\n\n### Key Differences from PostgreSQL\n\n```sql\n-- Auto-increment (not SERIAL)\nCREATE TABLE users (\n    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\n    email VARCHAR(255) NOT NULL UNIQUE,\n    name VARCHAR(255) NOT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\n\n-- JSON type (MySQL 5.7+)\nCREATE TABLE orders (\n    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\n    user_id BIGINT UNSIGNED NOT NULL,\n    metadata JSON,\n    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE\n);\n\n-- Query JSON\nSELECT * FROM orders WHERE JSON_EXTRACT(metadata, '$.source') = 'web';\n-- Or shorthand:\nSELECT * FROM orders WHERE metadata->>'$.source' = 'web';\n```\n\n## Query Patterns\n\n### Joins\n\n```sql\n-- Inner join (only matching rows)\nSELECT u.name, o.total, o.status\nFROM users u\nINNER JOIN orders o ON o.user_id = u.id\nWHERE o.created_at > '2026-01-01';\n\n-- Left join (all users, even without orders)\nSELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS total_spent\nFROM users u\nLEFT JOIN orders o ON o.user_id = u.id\nGROUP BY u.id, u.name;\n\n-- Self-join (find users with same email domain)\nSELECT a.name, b.name, SPLIT_PART(a.email, '@', 2) AS domain\nFROM users a\nJOIN users b ON SPLIT_PART(a.email, '@', 2) = SPLIT_PART(b.email, '@', 2)\nWHERE a.id < b.id;\n```\n\n### Aggregations\n\n```sql\n-- Group by with having\nSELECT status, COUNT(*) AS cnt, SUM(total) AS revenue\nFROM orders\nGROUP BY status\nHAVING COUNT(*) > 10\nORDER BY revenue DESC;\n\n-- Running total (window function)\nSELECT date, revenue,\n    SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue\nFROM daily_sales;\n\n-- Rank within groups\nSELECT user_id, total,\n    RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rank\nFROM orders;\n\n-- Moving average (last 7 entries)\nSELECT date, revenue,\n    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7\nFROM daily_sales;\n```\n\n### Common Table Expressions (CTEs)\n\n```sql\n-- Readable multi-step queries\nWITH monthly_revenue AS (\n    SELECT DATE_TRUNC('month', created_at) AS month,\n           SUM(total) AS revenue\n    FROM orders\n    WHERE status = 'paid'\n    GROUP BY 1\n),\ngrowth AS (\n    SELECT month, revenue,\n           LAG(revenue) OVER (ORDER BY month) AS prev_revenue,\n           ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) /\n                 NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1) AS growth_pct\n    FROM monthly_revenue\n)\nSELECT * FROM growth ORDER BY month;\n\n-- Recursive CTE (org chart / tree traversal)\nWITH RECURSIVE org_tree AS (\n    SELECT id, name, manager_id, 0 AS depth\n    FROM employees\n    WHERE manager_id IS NULL\n    UNION ALL\n    SELECT e.id, e.name, e.manager_id, t.depth + 1\n    FROM employees e\n    JOIN org_tree t ON e.manager_id = t.id\n)\nSELECT REPEAT('  ', depth) || name AS org_chart FROM org_tree ORDER BY depth, name;\n```\n\n## Migrations\n\n### Manual Migration Script Pattern\n\n```bash\n#!/bin/bash\n# migrate.sh - Run numbered SQL migration files\nDB_URL=\"${1:?Usage: migrate.sh <db-url>}\"\nMIGRATIONS_DIR=\"./migrations\"\n\n# Create tracking table\npsql \"$DB_URL\" -c \"CREATE TABLE IF NOT EXISTS schema_migrations (\n    version TEXT PRIMARY KEY,\n    applied_at TIMESTAMPTZ DEFAULT NOW()\n);\"\n\n# Run pending migrations in order\nfor file in $(ls \"$MIGRATIONS_DIR\"/*.sql | sort); do\n    version=$(basename \"$file\" .sql)\n    already=$(psql \"$DB_URL\" -tAc \"SELECT 1 FROM schema_migrations WHERE version='$version';\")\n    if [ \"$already\" = \"1\" ]; then\n        echo \"SKIP: $version (already applied)\"\n        continue\n    fi\n    echo \"APPLY: $version\"\n    psql \"$DB_URL\" -f \"$file\" && \\\n    psql \"$DB_URL\" -c \"INSERT INTO schema_migrations (version) VALUES ('$version');\" || {\n        echo \"FAILED: $version\"\n        exit 1\n    }\ndone\necho \"All migrations applied.\"\n```\n\n### Migration File Convention\n\n```\nmigrations/\n  001_create_users.sql\n  002_create_orders.sql\n  003_add_users_phone.sql\n  004_add_orders_metadata_index.sql\n```\n\nEach file:\n```sql\n-- 003_add_users_phone.sql\n-- Up\nALTER TABLE users ADD COLUMN phone TEXT;\n\n-- To reverse: ALTER TABLE users DROP COLUMN phone;\n```\n\n## Query Optimization\n\n### EXPLAIN (PostgreSQL)\n\n```sql\n-- Show query plan\nEXPLAIN SELECT * FROM orders WHERE user_id = '...' AND status = 'paid';\n\n-- Show actual execution times\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT * FROM orders WHERE user_id = '...' AND status = 'paid';\n```\n\n**What to look for:**\n- `Seq Scan` on large tables → needs an index\n- `Nested Loop` with large row counts → consider `Hash Join` (may need more `work_mem`)\n- `Rows Removed by Filter` being high → index doesn't cover the filter\n- Actual rows far from estimated → run `ANALYZE tablename;` to update statistics\n\n### Index Strategy\n\n```sql\n-- Single column (most common)\nCREATE INDEX idx_orders_user_id ON orders(user_id);\n\n-- Composite (for queries filtering on both columns)\nCREATE INDEX idx_orders_user_status ON orders(user_id, status);\n-- Column ORDER matters: put equality filters first, range filters last\n\n-- Covering index (includes data columns to avoid table lookup)\nCREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);\n\n-- Partial index (smaller, faster — only index what you query)\nCREATE INDEX idx_orders_pending ON orders(user_id) WHERE status = 'pending';\n\n-- Check unused indexes\nSELECT schemaname, tablename, indexname, idx_scan\nFROM pg_stat_user_indexes\nWHERE idx_scan = 0 AND indexname NOT LIKE '%pkey%'\nORDER BY pg_relation_size(indexrelid) DESC;\n```\n\n### SQLite EXPLAIN\n\n```sql\nEXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 5;\n-- Look for: SCAN (bad) vs SEARCH USING INDEX (good)\n```\n\n## Backup & Restore\n\n### PostgreSQL\n\n```bash\n# Full dump (custom format, compressed)\npg_dump -Fc -h localhost -U myuser mydb > backup.dump\n\n# Restore\npg_restore -h localhost -U myuser -d mydb --clean --if-exists backup.dump\n\n# SQL dump (portable, readable)\npg_dump -h localhost -U myuser mydb > backup.sql\n\n# Dump specific tables\npg_dump -h localhost -U myuser -t users -t orders mydb > partial.sql\n\n# Copy table to CSV\npsql -c \"\\copy (SELECT * FROM users) TO 'users.csv' CSV HEADER\"\n```\n\n### SQLite\n\n```bash\n# Backup (just copy the file, but use .backup for consistency)\nsqlite3 mydb.sqlite \".backup backup.sqlite\"\n\n# Dump to SQL\nsqlite3 mydb.sqlite .dump > backup.sql\n\n# Restore from SQL\nsqlite3 newdb.sqlite < backup.sql\n```\n\n### MySQL\n\n```bash\n# Dump\nmysqldump -h localhost -u root -p mydb > backup.sql\n\n# Restore\nmysql -h localhost -u root -p mydb < backup.sql\n```\n\n## Tips\n\n- Always use parameterized queries in application code — never concatenate user input into SQL\n- Use `TIMESTAMPTZ` (not `TIMESTAMP`) in PostgreSQL for timezone-aware dates\n- Set `PRAGMA journal_mode=WAL;` in SQLite for concurrent read performance\n- Use `EXPLAIN` before deploying any query that runs on large tables\n- PostgreSQL: `\\d+ tablename` shows columns, indexes, and size. `\\di+` lists all indexes with sizes\n- For quick data exploration, import any CSV into SQLite: `sqlite3 :memory: \".mode csv\" \".import file.csv t\" \"SELECT ...\"`\n","tags":{"latest":"1.0.0"},"stats":{"comments":0,"downloads":18981,"installsAllTime":196,"installsCurrent":196,"stars":41,"versions":1},"createdAt":1770155077543,"updatedAt":1778486013286},"latestVersion":{"version":"1.0.0","createdAt":1770155077543,"changelog":"Initial release: SQLite, PostgreSQL, MySQL coverage - schema design, queries, migrations, optimization, backup/restore","license":null},"metadata":{"setup":[],"os":["linux","darwin","win32"],"systems":null},"owner":{"handle":"gitgoodordietrying","userId":"s17bsk9s8a501ckx95hd6m2b75885xxv","displayName":"gitgoodordietrying","image":"https://avatars.githubusercontent.com/u/116975874?v=4"},"moderation":null}