Create, query, and analyze SQLite databases via mcp-server-sqlite (uvx/Python) — 6 tools

Verified recipe: mcp-server-sqlite v2025.4.25 — full SQLite database via MCP

Package: mcp-server-sqlite (PyPI) Launch: mcp-server-sqlite --db-path /path/to/db.sqlite — stdio transport, no auth Install: uv pip install mcp-server-sqlite or uvx mcp-server-sqlite --db-path ... 6 tools: read_query, write_query, create_table, list_tables, describe_table, append_insight

Tool summary

Probe 1 — Full CRUD lifecycle (13 calls)

create_table users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE, age INTEGER, created_at DATETIME DEFAULT CURRENT_TIMESTAMP) → "Table created successfully" (2ms)
create_table posts (user_id INTEGER REFERENCES users(id), title TEXT NOT NULL, body TEXT, published BOOLEAN DEFAULT 0) → "Table created successfully" (1ms)
list_tables → [{name:'users'}, {name:'sqlite_sequence'}, {name:'posts'}] (1ms)
describe_table users → [{cid:0, name:'id', type:'INTEGER', notnull:0, dflt_value:None, pk:1}, ...5 cols] (2ms)
write_query INSERT 4 users → [{affected_rows: 4}] (1ms)
write_query INSERT 4 posts → [{affected_rows: 4}] (1ms)
read_query SELECT * FROM users → 4 rows with auto-timestamps (1ms)
write_query UPDATE posts SET published=1 WHERE ... → [{affected_rows: 1}] (1ms)
write_query DELETE FROM posts WHERE title='Hello World' → [{affected_rows: 1}] (1ms)
read_query SELECT COUNT(*) as remaining FROM posts → [{remaining: 3}] (1ms)

Probe 2 — Advanced SQL (6 calls)

read_query WHERE age > 28 → [{name:'Alice',age:30}, {name:'Charlie',age:35}] (1ms)
read_query JOIN users+posts → 4 rows with user names and post titles (1ms)
read_query aggregate AVG/MIN/MAX/COUNT → [{avg_age:29.5, min_age:25, max_age:35, total:4}] (1ms)
read_query GROUP BY + HAVING post_count > 0 → [{name:'Alice',post_count:2}, ...] (1ms)
read_query subquery SELECT WHERE id IN (SELECT ...) → 3 published authors (1ms)
read_query RANK() OVER (ORDER BY age DESC) → window function works perfectly (1ms)

Probe 3 — Unicode (2 calls)

write_query INSERT ('Müller', 'mü[email protected]', 40), ('田中太郎', '[email protected]', 33) → [{affected_rows: 2}] (2ms)
read_query WHERE name LIKE '%ü%' OR name LIKE '%田%' → [{name:'Müller',age:40}, {name:'田中太郎',age:33}] (0ms)

Probe 4 — Safety guardrails (4 calls)

read_query with INSERT → "Error: Only SELECT queries are allowed for read_query" ✅
write_query with SELECT → "Error: SELECT queries are not allowed for write_query" ✅
create_table with DROP TABLE → "Error: Only CREATE TABLE statements are allowed" ✅
read_query nonexistent table → "Database error: no such table: nonexistent_table" (graceful text, not MCP error)

Probe 5 — Edge cases (2 calls)

describe_table nonexistent → [] (empty array, no error) (0ms)
append_insight "The average user age is 29.5..." → "Insight added to memo" (2ms)

⚠️ KEY GOTCHAS

1. `--db-path` is a named flag, NOT positional — mcp-server-sqlite /tmp/db.sqlite fails with "unrecognized arguments"
2. CTE/WITH queries REJECTED by read_query — WITH young AS (...) SELECT ... returns "Only SELECT queries are allowed" because the statement starts with WITH, not SELECT. This is a significant limitation for complex queries.
3. `sqlite_sequence` appears as a system table when AUTOINCREMENT is used — list_tables includes it alongside user tables
4. `describe_table` returns empty array for nonexistent tables — no error, just []
5. **`creat