Db Explorer — Db 资源管理器

DB 资源管理器

Connect to databases, 运行 queries, explore 模式s, and 导出 data — all from the terminal.

When to Use

Activate this 技能 when the user:

Says "检查 the database", "查询 the DB", "show me the data" Wants to see table structure, row counts, or sample data Needs to 导出 data to CSV/JSON Wants to find slow queries or 检查 DB 健康 Mentions a database connection string or DB name Supported Databases Database 命令行工具 工具 安装 (macOS) 安装 (Linux) PostgreSQL psql brew 安装 postgresql apt 安装 postgresql-命令行工具ent MySQL mysql brew 安装 mysql apt 安装 mysql-命令行工具ent SQLite sqlite3 (built-in on macOS) apt 安装 sqlite3 MongoDB mongosh brew 安装 mongosh See mongodb.com/docs/shell Redis redis-命令行工具 brew 安装 redis apt 安装 redis-工具s Quick 启动

• Identify the Database

Ask the user for:

Database type (postgres/mysql/sqlite/mongo/redis) Connection string OR host/port/database/user/password For SQLite: just the file path

• Connect and Explore

# PostgreSQL psql "postgresql://user:password@host:5432/dbname" -c "\dt" # 列出 tables psql "postgresql://user:password@host:5432/dbname" -c "\d table_name" # describe table psql "postgresql://user:password@host:5432/dbname" -c "SELECT count() FROM table_name;"

# MySQL mysql -h host -u user -p dbname -e "SHOW TABLES;" mysql -h host -u user -p dbname -e "DESCRIBE table_name;" mysql -h host -u user -p dbname -e "SELECT count() FROM table_name;"

# SQLite sqlite3 /path/to/db.db ".tables" # 列出 tables sqlite3 /path/to/db.db ".模式 table_name" # describe table sqlite3 /path/to/db.db "SELECT count() FROM table_name;"

# MongoDB mongosh "mongodb://user:password@host:27017/dbname" --eval "db.获取CollectionNames()" mongosh "mongodb://user:password@host:27017/dbname" --eval "db.collection_name.countDocuments()"

# Redis redis-命令行工具 -h host -p 6379 -a password 信息 keyspace redis-命令行工具 -h host -p 6379 -a password DBSIZE redis-命令行工具 -h host -p 6379 -a password KEYS ""

• Safety Rules

ALWAYS follow these rules:

Read-only by default — Never 运行 INSERT/更新/删除/DROP without explicit user confirmation Limit 结果s — Always 添加 LIMIT 100 (or equivalent) to SELECT queries unless user asks for all Show before 执行 — For any write operation, show the exact SQL/command and ask for confirmation No passwords in 历史 — Use 环境 variables or connection strings, don't echo passwords Transaction safety — For writes, wrap in BEGIN/回滚 first, show 结果s, then ask to COMMIT

• 模式 Exploration 工作流

When user says "explore the database" or "show me the 模式":

# Step 1: 列出 all tables # Step 2: For each table, show columns, types, and constrAInts # Step 3: Show row counts # Step 4: Show foreign key relationships # Step 5: Summarize as a readable 模式 map

PostgreSQL full 模式 dump:

psql "$CONN" -c " SELECT table_name, column_name, data_type, is_nullable, column_default FROM in格式化ion_模式.columns WHERE table_模式 = 'public' ORDER BY table_name, ordinal_position; "

MySQL full 模式 dump:

mysql "$CONN" -e " SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM IN格式化ION_模式.COLUMNS WHERE TABLE_模式 = DATABASE() ORDER BY TABLE_NAME, ORDINAL_POSITION; "

• 导出 格式化s

导出 查询 结果s to common 格式化s:

# CSV (PostgreSQL) psql "$CONN" -c "\copy (SELECT FROM table_name) TO '/tmp/导出.csv' WITH CSV HEADER"

# CSV (MySQL) mysql "$CONN" -e "SELECT FROM table_name" | sed 's/\t/,/g' > /tmp/导出.csv

# JSON (PostgreSQL) psql "$CONN" -t -c "SELECT json_agg(t) FROM (SELECT FROM table_name LIMIT 100) t;" > /tmp/导出.json

# SQLite to CSV sqlite3 /path/to/db.db ".mode csv" ".headers on" ".输出 /tmp/导出.csv" "SELECT FROM table_name;" ".quit"

• Common Diagnostic Queries

-- PostgreSQL: Table sizes SELECT 模式name, tablename, pg_size_pretty(pg_total_relation_size(模式name||'.'||tablename)) FROM pg_tables WHERE 模式name = 'public' ORDER BY pg_total_relation_size(模式name||'.'||tablename) DESC;

-- PostgreSQL: Active connections SELECT pid, usename, 应用_name, 命令行工具ent_添加r, 状态, 查询_启动, 查询 FROM pg_stat_activity WHERE 状态 != 'idle';

-- PostgreSQL: Slow queries (> 1s) SELECT pid, now() - pg_stat_activity.查询_启动 AS duration, 查询 FROM pg_stat_activity WHERE 状态 = 'active' AND now() - pg_stat_activity.查询_启动 > interval '1 second';

-- MySQL: Table sizes SELECT table_name, ROUND(data_length/1024/1024, 2) AS data_mb, table_rows FROM in格式化ion_模式.tables WHERE table_模式 = DATABASE() ORDER BY data_length DESC;

-- MySQL: Process 列出 SHOW FULL PROCESS列出;

Performance Analysis PostgreSQL Performance # Slow queries (active for > 1s) psql "$CONN" -c " SELECT pid, now() - 查询_启动 AS duration, 查询 FROM pg_stat_activity WHERE 状态 = 'active' AND now() - 查询_启动 > interval '1 second' ORDER BY duration DESC; "

# 索引 usage psql "$CONN" -c " SELECT 模式name, tablename, 索引name, idx_扫描