{"skill":{"slug":"db-explorer-tool","displayName":"Db Explorer","summary":"Connect to and explore databases (PostgreSQL, MySQL, SQLite, MongoDB, Redis). Run queries, inspect schemas, export data. Use when user wants to query a datab...","description":"---\nname: db-explorer\ndescription: \"Connect to and explore databases (PostgreSQL, MySQL, SQLite, MongoDB, Redis). Run queries, inspect schemas, export data. Use when user wants to query a database, explore schema, check data, export results, or debug database issues.\"\nversion: 2.0.0\nauthor: lrg913427-dot\nlicense: MIT\nmetadata:\n  hermes:\n    tags: [database, sql, postgresql, mysql, sqlite, mongodb, redis, query, schema, data]\n    related_skills: [jupyter-live-kernel, airtable]\n---\n\n# DB Explorer\n\nConnect to databases, run queries, explore schemas, and export data — all from the terminal.\n\n## When to Use\n\nActivate this skill when the user:\n- Says \"check the database\", \"query the DB\", \"show me the data\"\n- Wants to see table structure, row counts, or sample data\n- Needs to export data to CSV/JSON\n- Wants to find slow queries or check DB health\n- Mentions a database connection string or DB name\n\n## Supported Databases\n\n| Database   | CLI Tool     | Install (macOS)           | Install (Linux)                    |\n|-----------|-------------|---------------------------|-----------------------------------|\n| PostgreSQL | psql        | brew install postgresql    | apt install postgresql-client      |\n| MySQL      | mysql       | brew install mysql         | apt install mysql-client           |\n| SQLite     | sqlite3     | (built-in on macOS)       | apt install sqlite3                |\n| MongoDB    | mongosh     | brew install mongosh       | See mongodb.com/docs/shell         |\n| Redis      | redis-cli   | brew install redis         | apt install redis-tools            |\n\n## Quick Start\n\n### 1. Identify the Database\n\nAsk the user for:\n- Database type (postgres/mysql/sqlite/mongo/redis)\n- Connection string OR host/port/database/user/password\n- For SQLite: just the file path\n\n### 2. Connect and Explore\n\n```bash\n# PostgreSQL\npsql \"postgresql://user:password@host:5432/dbname\" -c \"\\dt\"           # list tables\npsql \"postgresql://user:password@host:5432/dbname\" -c \"\\d table_name\" # describe table\npsql \"postgresql://user:password@host:5432/dbname\" -c \"SELECT count(*) FROM table_name;\"\n\n# MySQL\nmysql -h host -u user -p dbname -e \"SHOW TABLES;\"\nmysql -h host -u user -p dbname -e \"DESCRIBE table_name;\"\nmysql -h host -u user -p dbname -e \"SELECT count(*) FROM table_name;\"\n\n# SQLite\nsqlite3 /path/to/db.db \".tables\"                    # list tables\nsqlite3 /path/to/db.db \".schema table_name\"         # describe table\nsqlite3 /path/to/db.db \"SELECT count(*) FROM table_name;\"\n\n# MongoDB\nmongosh \"mongodb://user:password@host:27017/dbname\" --eval \"db.getCollectionNames()\"\nmongosh \"mongodb://user:password@host:27017/dbname\" --eval \"db.collection_name.countDocuments()\"\n\n# Redis\nredis-cli -h host -p 6379 -a password INFO keyspace\nredis-cli -h host -p 6379 -a password DBSIZE\nredis-cli -h host -p 6379 -a password KEYS \"*\"\n```\n\n### 3. Safety Rules\n\n**ALWAYS follow these rules:**\n\n1. **Read-only by default** — Never run INSERT/UPDATE/DELETE/DROP without explicit user confirmation\n2. **Limit results** — Always add `LIMIT 100` (or equivalent) to SELECT queries unless user asks for all\n3. **Show before execute** — For any write operation, show the exact SQL/command and ask for confirmation\n4. **No passwords in history** — Use environment variables or connection strings, don't echo passwords\n5. **Transaction safety** — For writes, wrap in BEGIN/ROLLBACK first, show results, then ask to COMMIT\n\n### 4. Schema Exploration Workflow\n\nWhen user says \"explore the database\" or \"show me the schema\":\n\n```bash\n# Step 1: List all tables\n# Step 2: For each table, show columns, types, and constraints\n# Step 3: Show row counts\n# Step 4: Show foreign key relationships\n# Step 5: Summarize as a readable schema map\n```\n\nPostgreSQL full schema dump:\n```bash\npsql \"$CONN\" -c \"\nSELECT table_name, column_name, data_type, is_nullable, column_default\nFROM information_schema.columns\nWHERE table_schema = 'public'\nORDER BY table_name, ordinal_position;\n\"\n```\n\nMySQL full schema dump:\n```bash\nmysql \"$CONN\" -e \"\nSELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT\nFROM INFORMATION_SCHEMA.COLUMNS\nWHERE TABLE_SCHEMA = DATABASE()\nORDER BY TABLE_NAME, ORDINAL_POSITION;\n\"\n```\n\n### 5. Export Formats\n\nExport query results to common formats:\n\n```bash\n# CSV (PostgreSQL)\npsql \"$CONN\" -c \"\\copy (SELECT * FROM table_name) TO '/tmp/export.csv' WITH CSV HEADER\"\n\n# CSV (MySQL)\nmysql \"$CONN\" -e \"SELECT * FROM table_name\" | sed 's/\\t/,/g' > /tmp/export.csv\n\n# JSON (PostgreSQL)\npsql \"$CONN\" -t -c \"SELECT json_agg(t) FROM (SELECT * FROM table_name LIMIT 100) t;\" > /tmp/export.json\n\n# SQLite to CSV\nsqlite3 /path/to/db.db \".mode csv\" \".headers on\" \".output /tmp/export.csv\" \"SELECT * FROM table_name;\" \".quit\"\n```\n\n### 6. Common Diagnostic Queries\n\n```sql\n-- PostgreSQL: Table sizes\nSELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))\nFROM pg_tables WHERE schemaname = 'public' ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;\n\n-- PostgreSQL: Active connections\nSELECT pid, usename, application_name, client_addr, state, query_start, query\nFROM pg_stat_activity WHERE state != 'idle';\n\n-- PostgreSQL: Slow queries (> 1s)\nSELECT pid, now() - pg_stat_activity.query_start AS duration, query\nFROM pg_stat_activity WHERE state = 'active' AND now() - pg_stat_activity.query_start > interval '1 second';\n\n-- MySQL: Table sizes\nSELECT table_name, ROUND(data_length/1024/1024, 2) AS data_mb, table_rows\nFROM information_schema.tables WHERE table_schema = DATABASE() ORDER BY data_length DESC;\n\n-- MySQL: Process list\nSHOW FULL PROCESSLIST;\n```\n\n## Performance Analysis\n\n### PostgreSQL Performance\n\n```bash\n# Slow queries (active for > 1s)\npsql \"$CONN\" -c \"\nSELECT pid, now() - query_start AS duration, query\nFROM pg_stat_activity\nWHERE state = 'active' AND now() - query_start > interval '1 second'\nORDER BY duration DESC;\n\"\n\n# Index usage\npsql \"$CONN\" -c \"\nSELECT schemaname, tablename, indexname, idx_scan, idx_tup_read\nFROM pg_stat_user_indexes\nORDER BY idx_scan ASC LIMIT 20;\n\"\n\n# Table bloat\npsql \"$CONN\" -c \"\nSELECT schemaname, tablename,\n  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,\n  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,\n  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size\nFROM pg_tables\nWHERE schemaname = 'public'\nORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 10;\n\"\n\n# Cache hit ratio (should be > 99%)\npsql \"$CONN\" -c \"\nSELECT\n  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio\nFROM pg_statio_user_tables;\n\"\n```\n\n### MySQL Performance\n\n```bash\n# Slow queries\nmysql \"$CONN\" -e \"SELECT * FROM information_schema.processlist WHERE TIME > 1 ORDER BY TIME DESC;\"\n\n# Index usage\nmysql \"$CONN\" -e \"\nSELECT table_name, index_name, cardinality\nFROM information_schema.statistics\nWHERE table_schema = DATABASE()\nORDER BY cardinality DESC LIMIT 20;\n\"\n\n# Table sizes\nmysql \"$CONN\" -e \"\nSELECT table_name,\n  ROUND(data_length/1024/1024, 2) AS data_mb,\n  ROUND(index_length/1024/1024, 2) AS index_mb,\n  table_rows\nFROM information_schema.tables\nWHERE table_schema = DATABASE()\nORDER BY data_length DESC LIMIT 10;\n\"\n```\n\n## Backup & Restore\n\n### PostgreSQL\n\n```bash\n# Backup single database\npg_dump \"$CONN\" > backup_$(date +%Y%m%d).sql\n\n# Backup single table\npg_dump \"$CONN\" -t table_name > table_backup.sql\n\n# Restore\npsql \"$CONN\" < backup.sql\n\n# Backup with compression\npg_dump \"$CONN\" | gzip > backup_$(date +%Y%m%d).sql.gz\n```\n\n### MySQL\n\n```bash\n# Backup single database\nmysqldump -h host -u user -p dbname > backup_$(date +%Y%m%d).sql\n\n# Backup single table\nmysqldump -h host -u user -p dbname table_name > table_backup.sql\n\n# Restore\nmysql -h host -u user -p dbname < backup.sql\n```\n\n### SQLite\n\n```bash\n# Backup\nsqlite3 /path/to/db.db \".backup /tmp/backup.db\"\n\n# Or just copy\ncp /path/to/db.db /tmp/backup_$(date +%Y%m%d).db\n```\n\n## Data Migration Helpers\n\n### Copy table between databases\n\n```bash\n# PostgreSQL to CSV to MySQL\npsql \"$PG_CONN\" -c \"\\copy table_name TO '/tmp/export.csv' WITH CSV HEADER\"\nmysql \"$MYSQL_CONN\" -e \"LOAD DATA LOCAL INFILE '/tmp/export.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '\\\"' LINES TERMINATED BY '\\n' IGNORE 1 ROWS;\"\n```\n\n### Schema comparison\n\n```bash\n# Get PostgreSQL schema hash for comparison\npsql \"$CONN\" -c \"\nSELECT md5(string_agg(table_name || column_name || data_type, '' ORDER BY table_name, ordinal_position))\nFROM information_schema.columns\nWHERE table_schema = 'public';\n\"\n```\n\n## Pitfalls\n\n- **Connection strings with special chars** — URL-encode passwords containing @, :, /, etc.\n- **SSL requirements** — Many cloud databases (RDS, Cloud SQL, Supabase) require `?sslmode=require` or `--ssl-mode=REQUIRED`\n- **Timeout on large tables** — Always LIMIT unless user explicitly wants full export\n- **SQLite locking** — Only one writer at a time; use WAL mode for concurrent reads: `PRAGMA journal_mode=WAL;`\n- **MongoDB auth database** — Sometimes auth is on `admin` db, not the target db: `?authSource=admin`\n- **Redis SELECT** — Redis has 16 databases (0-15); check which one: `redis-cli INFO keyspace`\n\n## Verification\n\nAfter connecting:\n1. Run a simple query to confirm connection works\n2. List tables/collections to show the schema\n3. Run a count query on a key table to verify data access\n4. Check cache hit ratio (PostgreSQL) or slow queries (MySQL)\n5. Verify backup capability with a test dump\n\n## Environment Variables\n\nThe skill uses these if available:\n- `DATABASE_URL` — Full connection string (takes priority)\n- `DB_HOST`, `DB_PORT`, `DB_NAME`, `DB_USER`, `DB_PASSWORD` — Individual params\n- `DB_TYPE` — postgres/mysql/sqlite/mongo/redis\n","tags":{"latest":"2.0.0"},"stats":{"comments":0,"downloads":315,"installsAllTime":12,"installsCurrent":0,"stars":0,"versions":1},"createdAt":1778385742096,"updatedAt":1778492892498},"latestVersion":{"version":"2.0.0","createdAt":1778385742096,"changelog":"db-explorer-tool 2.0.0 introduces major documentation and workflow improvements for database exploration and management.\n\n- New SKILL.md extensively describes usage, safety rules, and common workflows.\n- Expanded examples for PostgreSQL, MySQL, SQLite, MongoDB, and Redis, including connection, schema inspection, data export, and diagnostics.\n- Emphasizes strict safety practices: read-only by default, explicit confirmation for writes, and result limiting.\n- Includes ready-to-use performance and health check queries for major databases.\n- Provides backup and restore recipes for PostgreSQL, MySQL, and SQLite.\n- Helps identify when and how to use the tool via clear activation guidance.","license":"MIT-0"},"metadata":{"setup":[],"os":null,"systems":null},"owner":{"handle":"lrg913427-dot","userId":"s175nn6ap9fe4ne23bws9svzm185ywrq","displayName":"lrg913427-dot","image":"https://avatars.githubusercontent.com/u/279154854?v=4"},"moderation":null}