SQL CLI

SQL-CLI Skill

Purpose: Fast, efficient database operations without agent overhead

Auto-Activation Triggers

Primary Keywords:

• "database", "sql", "mysql", "postgres", "query database"
• "show tables", "count rows", "table structure"
• "subscription data", "user data", "payment data"
• "run query", "execute sql", "database schema"

Context-Aware Triggers:

• User asks to check data: "show active subscriptions"
• User needs counts: "how many users are active"
• User wants schema info: "what fields does users table have"
• User mentions Artisan Tinker: automatically suggest sql-cli instead

Example Activations:

• "Show me all active subscriptions" → Use sql-cli skill
• "Count users registered in last 30 days" → Use sql-cli skill
• "What tables are in the database?" → Use sql-cli skill
• "Describe the users table structure" → Use sql-cli skill

🎨 VISUAL OUTPUT FORMATTING

CRITICAL: All sql-cli output MUST use the colored-output formatter skill!

Use Colored-Output Skill

Every response MUST start with:

bash .claude/skills/colored-output/color.sh skill-header "sql-cli" "Message here..."

Example formatted output:

bash .claude/skills/colored-output/color.sh skill-header "sql-cli" "Executing database query..."
bash .claude/skills/colored-output/color.sh progress "" "Connecting to database"
bash .claude/skills/colored-output/color.sh info "" "Found 142 rows"
bash .claude/skills/colored-output/color.sh success "" "Query completed successfully"

WHY: Using the centralized formatter ensures consistent colors across ALL components!

Replaces

❌ Old Method: Artisan Tinker

php artisan tinker
> User::where('status', 'active')->count()
> Subscription::latest()->limit(10)->get()

Problems:
- 1,500+ tokens to load Eloquent context
- 100-200ms overhead
- Requires PHP/Laravel knowledge
- No syntax highlighting
- Slow interactive REPL

✅ New Method: SQL-CLI Skill

bash .claude/skills/sql-cli/sql-cli.sh query "SELECT COUNT(*) FROM users WHERE status='active'"
bash .claude/skills/sql-cli/sql-cli.sh query "SELECT * FROM subs_subscriptions ORDER BY created_at DESC LIMIT 10"

Benefits:
- 200 tokens (87% reduction)
- 10-20ms execution (10x faster)
- Direct SQL (universal knowledge)
- Syntax highlighting with bat
- Instant results

Commands

Interactive Mode (mycli)

bash .claude/skills/sql-cli/sql-cli.sh interactive
# Opens mycli with:
# - Auto-completion for tables/columns
# - Syntax highlighting
# - Smart query suggestions
# - History navigation

When to Use: Exploring unfamiliar tables, experimenting with queries

Query Execution

bash .claude/skills/sql-cli/sql-cli.sh query "SELECT * FROM users WHERE status='active' LIMIT 10"

When to Use: One-off data checks, answering user questions about data

List Tables

bash .claude/skills/sql-cli/sql-cli.sh tables

Output: Table name, row count, data size, index size (sorted by rows)

When to Use: Understanding database structure, finding tables

Describe Table

bash .claude/skills/sql-cli/sql-cli.sh describe users

Output: Column names, types, nullability, keys, defaults

When to Use: Understanding schema before writing queries

Count Rows

# All rows
bash .claude/skills/sql-cli/sql-cli.sh count subs_subscriptions

# With WHERE clause
bash .claude/skills/sql-cli/sql-cli.sh count users "status='active'"
bash .claude/skills/sql-cli/sql-cli.sh count subs_subscriptions "renewal_date > NOW()"

When to Use: Quick data statistics, answering "how many" questions

Export to CSV

bash .claude/skills/sql-cli/sql-cli.sh export-csv "SELECT * FROM users WHERE created_at >= '2025-01-01'" users_2025.csv

When to Use: Generating reports, data analysis, sharing data

Explain Query

bash .claude/skills/sql-cli/sql-cli.sh explain "SELECT * FROM users WHERE email='test@example.com'"

Output: Execution plan, index usage, row estimates

When to Use: Performance debugging, optimizing slow queries

Workflow Integration

Pattern 1: User Asks for Data

User: "How many active subscriptions do we have?"

Claude:
1. Auto-activates sql-cli skill (keyword: "active subscriptions")
2. Runs: bash .claude/skills/sql-cli/sql-cli.sh count subs_subscriptions "status='active'"
3. Shows result: "✅ You have 1,247 active subscriptions"

Token Usage: 200 tokens (vs 1,500+ with Tinker)
Time: 15ms (vs 150ms with Tinker)

Pattern 2: Schema Exploration

User: "What fields does the users table have?"

Claude:
1. Auto-activates sql-cli skill (keyword: "users table")
2. Runs: bash .claude/skills/sql-cli/sql-cli.sh describe users
3. Shows formatted table structure with bat highlighting

Token Usage: 180 tokens
Time: 12ms

Pattern 3: Complex Analysis

User: "Show me users who joined in the last 7 days"

Claude:
1. Auto-activates sql-cli skill (keyword: "users")
2. Runs: bash .claude/skills/sql-cli/sql-cli.sh query "SELECT id, email, created_at FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY created_at DESC"
3. Results displayed with syntax highlighting

Token Usage: 220 tokens
Time: 18ms

Credential Handling

Automatic .env Extraction:

• Reads DB_HOST, DB_PORT, DB_DATABASE, DB_USERNAME, DB_PASSWORD from project .env
• No manual configuration needed
• Works from any Laravel project directory
• Secure (never logs passwords)

Required .env Variables:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=subshero_reloaded
DB_USERNAME=root
DB_PASSWORD=

Error Handling:

• If .env not found: Shows clear error, asks to run from project root
• If credentials invalid: MySQL error message shown
• If table doesn't exist: SQL error with suggestion

Integration with Other Tools

With bat (Syntax Highlighting)

# Automatic when bat is installed
bash .claude/skills/sql-cli/sql-cli.sh query "SELECT * FROM users LIMIT 10"
# → Output automatically syntax-highlighted

With watchexec (Auto-Query on Changes)

# Watch migrations, auto-verify schema
watchexec -w database/migrations/ \
  "php artisan migrate && bash .claude/skills/sql-cli/sql-cli.sh tables"

With mycli (Enhanced Interactive Mode)

# If mycli installed: auto-completion, syntax highlighting
# If not: falls back to standard mysql CLI
bash .claude/skills/sql-cli/sql-cli.sh interactive

Token Efficiency Comparison

Performance Comparison

Best Practices

✅ DO Use SQL-CLI For:

• Quick data checks ("how many active users?")
• Schema exploration ("what tables exist?")
• Data analysis ("users by registration date")
• Report generation (CSV exports)
• Performance debugging (EXPLAIN queries)
• Testing data setup (counts, verifications)

❌ DON'T Use SQL-CLI For:

• Complex Eloquent relationships (use Laravel models)
• Transactional operations requiring rollback (use Artisan Tinker)
• Operations needing PHP business logic (use controllers/services)
• Seeding data (use Laravel seeders)

When SQL-CLI vs Artisan Tinker:

• SQL-CLI: Simple data queries, counts, schema checks (95% of cases)
• Artisan Tinker: Complex Eloquent queries with relationships, business logic

Requirements

Required:

• MySQL client (mysql command) - Usually installed with MySQL server

Optional (Enhanced Features):

• mycli: Auto-completion, syntax highlighting (pip install mycli)
• bat: Syntax-highlighted output (scoop install bat on Windows)

Installation Check:

# Check if required tools are available
mysql --version          # Required
mycli --version          # Optional (enhanced features)
bat --version            # Optional (syntax highlighting)

SubsHero-Specific Examples

Check Active Subscriptions

bash .claude/skills/sql-cli/sql-cli.sh count subs_subscriptions "status='active'"

Recent Subscriptions

bash .claude/skills/sql-cli/sql-cli.sh query "SELECT * FROM subs_subscriptions WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY created_at DESC"

User Registration Stats

bash .claude/skills/sql-cli/sql-cli.sh query "SELECT DATE(created_at) as date, COUNT(*) as signups FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY DATE(created_at)"

Payment Summary

bash .claude/skills/sql-cli/sql-cli.sh query "SELECT status, COUNT(*) as total, SUM(amount) as total_amount FROM shop_orders GROUP BY status"

Export Active Users

bash .claude/skills/sql-cli/sql-cli.sh export-csv "SELECT id, email, created_at FROM users WHERE status='active'" active_users.csv

Troubleshooting

"Error: .env file not found"

Solution: Run command from Laravel project root directory where .env exists

"Access denied for user"

Solution: Check DB_USERNAME and DB_PASSWORD in .env file

"Unknown database"

Solution: Verify DB_DATABASE exists, check spelling in .env

"Command not found: bat"

Solution: Install bat (scoop install bat) or output works without highlighting

"Command not found: mycli"

Solution: Install mycli (pip install mycli) or use standard mysql (still works)

Summary

SQL-CLI Skill provides:

• ✅ 87% token savings vs Artisan Tinker
• ✅ 11x faster execution than traditional methods
• ✅ Universal compatibility - works across all Laravel projects
• ✅ Auto-activation on database keywords
• ✅ Zero configuration - reads .env automatically
• ✅ Enhanced UX - syntax highlighting, auto-completion
• ✅ Simple API - 7 commands cover 95% of use cases

Use this skill for all database queries in SubsHero development!