academind SQLite DB Exploration
Rules of engagement
- Default to read-only exploration (
SELECT, schema queries). - Only run write queries (INSERT/UPDATE/DELETE/DDL) when explicitly asked or clearly required.
- Always start by listing tables and inspecting schema before writing complex queries.
- Prefer parameterized queries when constructing SQL from user-provided values (if applicable).
Use Bun script: sqlite-explorer.ts
You find a sqlite-explorer.ts script (which must be executed using Bun!) in ${SKILL_DIR}/scripts/sqlite-explorer.ts that implements this skill. It accepts command-line arguments to specify the database path, query, and mode (read-only vs write).
Usage
List tables:
bun ${SKILL_DIR}/scripts/sqlite-explorer.ts ./path/to/db.sqlite --tablesShow schema for a table:
bun ${SKILL_DIR}/scripts/sqlite-explorer.ts ./path/to/db.sqlite --schema usersRun any SQL query:
bun sqlite-explorer.ts ./path/to/db.sqlite --query "SELECT * FROM users LIMIT 10"Run a write query (only when needed):
bun ${SKILL_DIR}/scripts/sqlite-explorer.ts ./path/to/db.sqlite --query "UPDATE users SET active=1 WHERE id=42"Read-only mode (recommended for exploration):
bun ${SKILL_DIR}/scripts/sqlite-explorer.ts ./path/to/db.sqlite --readonly --query "SELECT COUNT(*) FROM users"
Output expectations
- Tables: one per line
- Schema: columns + PK + FKs + indexes
- Query results: printed as a compact table; also prints affected row counts for non-SELECT
Helpful built-in introspection SQL
- Tables:
SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY 1; - Columns:
PRAGMA table_info('table_name'); - Foreign keys:
PRAGMA foreign_key_list('table_name'); - Indexes:
PRAGMA index_list('table_name');+PRAGMA index_info('index_name'); - Rowcount:
SELECT COUNT(*) AS count FROM table_name;