database-scout
Read-only exploration tool for SQLite / PostgreSQL databases — inspect table schemas, preview data, generate ER diagrams, and run safe queries.
Feature Overview
| Feature |
Description |
| List all tables |
Show tables and views in the database, with row counts |
| Inspect table schema |
Column names, types, constraints (PK/FK/NOT NULL), indexes, defaults |
| Data preview |
View the first N rows of a table |
| ER diagram generation |
Output Mermaid erDiagram syntax, ready to render |
| Safe read-only queries |
Only SELECT/WITH/EXPLAIN allowed; write operations are blocked |
Security Mechanisms
- Connection-level read-only: SQLite opens with
?mode=ro URI; PostgreSQL uses SET SESSION READ ONLY
- SQL whitelist: Only statements starting with SELECT / WITH / EXPLAIN / PRAGMA / SHOW are allowed
- Dangerous keyword blocking: INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, and 30+ other keywords are blocked
- Multi-statement blocking: Semicolon-separated multiple SQL statements are rejected (prevents injection)
- Identifier escaping: Table names are double-quote escaped to prevent SQL injection
Quick Start
# List all tables in a SQLite database
python3 scripts/db_explorer.py --db-path data.db list-tables
# Inspect table schema
python3 scripts/db_explorer.py --db-path data.db describe users
# Preview data (default 20 rows)
python3 scripts/db_explorer.py --db-path data.db preview orders --limit 10
# Generate Mermaid ER diagram
python3 scripts/db_explorer.py --db-path data.db er-diagram
# Run a read-only query
python3 scripts/db_explorer.py --db-path data.db query "SELECT name, age FROM users WHERE age > 18 LIMIT 10"
PostgreSQL
# Connect to PostgreSQL
python3 scripts/db_explorer.py --db-type postgres --dsn "host=localhost dbname=mydb user=reader" list-tables
# Inspect table schema
python3 scripts/db_explorer.py --db-type postgres --dsn "host=localhost dbname=mydb user=reader" describe orders
Detailed Usage
Parameters
| Parameter |
Required |
Default |
Description |
--db-type |
No |
sqlite |
Database type: sqlite or postgres |
--db-path |
Yes (for SQLite) |
— |
Path to the SQLite database file |
--dsn |
Yes (for PostgreSQL) |
— |
PostgreSQL connection string |
Subcommands
| Command |
Description |
Example |
list-tables |
List all tables/views |
list-tables |
describe <table> |
Show detailed table schema |
describe users |
preview <table> [--limit N / -n N] |
Preview the first N rows |
preview orders --limit 5 |
er-diagram |
Generate Mermaid ER diagram |
er-diagram |
query "<sql>" |
Run a read-only SQL query |
query "SELECT count(*) FROM users" |
Output Examples
list-tables
[
{"name": "users", "type": "table", "row_count": 1500},
{"name": "orders", "type": "table", "row_count": 8200},
{"name": "user_stats", "type": "view", "row_count": 1500}
]
describe
{
"table": "orders",
"row_count": 8200,
"columns": [
{"cid": 0, "name": "id", "type": "INTEGER", "notnull": true, "default": null, "primary_key": true},
{"cid": 1, "name": "user_id", "type": "INTEGER", "notnull": true, "default": null, "primary_key": false},
{"cid": 2, "name": "amount", "type": "REAL", "notnull": false, "default": "0.0", "primary_key": false}
],
"foreign_keys": [
{"from": "user_id", "to_table": "users", "to_column": "id"}
],
"indexes": [
{"name": "idx_orders_user_id", "unique": false, "columns": ["user_id"]}
]
}
er-diagram (Mermaid)
erDiagram
users {
INTEGER id PK
TEXT name
TEXT email
INTEGER age
}
orders {
INTEGER id PK
INTEGER user_id FK
REAL amount
TEXT created_at
}
users ||--o{ orders : "user_id"
Dependencies
- Python 3.8+ (
sqlite3 is a built-in module)
- PostgreSQL support requires:
pip install psycopg2-binary
1---2name: database-scout3description: Explore SQLite and PostgreSQL databases: list tables, inspect schemas (columns/types/constraints), preview data, generate Mermaid ER diagrams, and run safe read-only queries. Triggered by requests to explore a database, view table structures, describe tables, generate diagrams, or query data, and by keywords like database exploration, schema, ER diagram, or SQL query.4license: MIT5---6
7# database-scout
8
9Read-only exploration tool for SQLite / PostgreSQL databases — inspect table schemas, preview data, generate ER diagrams, and run safe queries.
10
11## Feature Overview
12
13| Feature | Description |
14|---------|-------------|
15| List all tables | Show tables and views in the database, with row counts |
16| Inspect table schema | Column names, types, constraints (PK/FK/NOT NULL), indexes, defaults |
17| Data preview | View the first N rows of a table |
18| ER diagram generation | Output Mermaid erDiagram syntax, ready to render |
19| Safe read-only queries | Only SELECT/WITH/EXPLAIN allowed; write operations are blocked |
20
21## Security Mechanisms
22
23- **Connection-level read-only**: SQLite opens with `?mode=ro` URI; PostgreSQL uses `SET SESSION READ ONLY`
24- **SQL whitelist**: Only statements starting with SELECT / WITH / EXPLAIN / PRAGMA / SHOW are allowed
25- **Dangerous keyword blocking**: INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, and 30+ other keywords are blocked
26- **Multi-statement blocking**: Semicolon-separated multiple SQL statements are rejected (prevents injection)
27- **Identifier escaping**: Table names are double-quote escaped to prevent SQL injection
28
29## Quick Start
30
31```bash
32# List all tables in a SQLite database
33python3 scripts/db_explorer.py --db-path data.db list-tables
34
35# Inspect table schema
36python3 scripts/db_explorer.py --db-path data.db describe users
37
38# Preview data (default 20 rows)
39python3 scripts/db_explorer.py --db-path data.db preview orders --limit 10
40
41# Generate Mermaid ER diagram
42python3 scripts/db_explorer.py --db-path data.db er-diagram
43
44# Run a read-only query
45python3 scripts/db_explorer.py --db-path data.db query "SELECT name, age FROM users WHERE age > 18 LIMIT 10"
46```
47
48### PostgreSQL
49
50```bash
51# Connect to PostgreSQL
52python3 scripts/db_explorer.py --db-type postgres --dsn "host=localhost dbname=mydb user=reader" list-tables
53
54# Inspect table schema
55python3 scripts/db_explorer.py --db-type postgres --dsn "host=localhost dbname=mydb user=reader" describe orders
56```
57
58## Detailed Usage
59
60### Parameters
61
62| Parameter | Required | Default | Description |
63|-----------|----------|---------|-------------|
64| `--db-type` | No | sqlite | Database type: sqlite or postgres |
65| `--db-path` | Yes (for SQLite) | — | Path to the SQLite database file |
66| `--dsn` | Yes (for PostgreSQL) | — | PostgreSQL connection string |
67
68### Subcommands
69
70| Command | Description | Example |
71|---------|-------------|---------|
72| `list-tables` | List all tables/views | `list-tables` |
73| `describe <table>` | Show detailed table schema | `describe users` |
74| `preview <table> [--limit N / -n N]` | Preview the first N rows | `preview orders --limit 5` |
75| `er-diagram` | Generate Mermaid ER diagram | `er-diagram` |
76| `query "<sql>"` | Run a read-only SQL query | `query "SELECT count(*) FROM users"` |
77
78## Output Examples
79
80### list-tables
81
82```json
83[
84 {"name": "users", "type": "table", "row_count": 1500},
85 {"name": "orders", "type": "table", "row_count": 8200},
86 {"name": "user_stats", "type": "view", "row_count": 1500}
87]
88```
89
90### describe
91
92```json
93{
94 "table": "orders",
95 "row_count": 8200,
96 "columns": [
97 {"cid": 0, "name": "id", "type": "INTEGER", "notnull": true, "default": null, "primary_key": true},
98 {"cid": 1, "name": "user_id", "type": "INTEGER", "notnull": true, "default": null, "primary_key": false},
99 {"cid": 2, "name": "amount", "type": "REAL", "notnull": false, "default": "0.0", "primary_key": false}
100 ],
101 "foreign_keys": [
102 {"from": "user_id", "to_table": "users", "to_column": "id"}
103 ],
104 "indexes": [
105 {"name": "idx_orders_user_id", "unique": false, "columns": ["user_id"]}
106 ]
107}
108```
109
110### er-diagram (Mermaid)
111
112```mermaid
113erDiagram
114 users {
115 INTEGER id PK
116 TEXT name
117 TEXT email
118 INTEGER age
119 }
120 orders {
121 INTEGER id PK
122 INTEGER user_id FK
123 REAL amount
124 TEXT created_at
125 }
126 users ||--o{ orders : "user_id"
127```
128
129## Dependencies
130
131- Python 3.8+ (`sqlite3` is a built-in module)
132- PostgreSQL support requires: `pip install psycopg2-binary`
majiayu000