stoolap-mcp

MCP (Model Context Protocol) server for Stoolap, an embedded SQL database. Lets AI assistants query, manage, and analyze Stoolap databases with full access to all SQL features.

Works with any MCP-compatible AI client: Claude Desktop, Claude Code, Cursor, Windsurf, Cline, and others.

Quick Start

Claude Desktop

Add to your Claude Desktop configuration (~/Library/Application Support/Claude/claude_desktop_config.json on macOS):

{
  "mcpServers": {
    "stoolap": {
      "command": "npx",
      "args": ["-y", "@stoolap/mcp", "--path", "./mydata"]
    }
  }
}

Claude Code

claude mcp add stoolap -- npx -y @stoolap/mcp --path ./mydata

In-memory (no persistence)

{
  "mcpServers": {
    "stoolap": {
      "command": "npx",
      "args": ["-y", "@stoolap/mcp"]
    }
  }
}

Read-only mode

{
  "mcpServers": {
    "stoolap": {
      "command": "npx",
      "args": ["-y", "@stoolap/mcp", "--path", "./mydata", "--read-only"]
    }
  }
}

Tools (30)

Query and Analysis

Tool	Description
query	Run SELECT, SHOW, DESCRIBE queries. Supports JOINs, CTEs, window functions, subqueries, set operations, JSON operators, vector search, temporal queries (AS OF), and all aggregate/scalar functions. Returns results as JSON.
execute	Run INSERT, UPDATE, DELETE with parameter binding ($1, $2, ...). Supports ON DUPLICATE KEY UPDATE (upsert), RETURNING clause, and expression-based updates. Returns affected row count.
execute_batch	Execute the same SQL with multiple parameter sets in a single atomic transaction. Parses SQL once, reuses for every row. All rows succeed or all are rolled back.
explain	Show query execution plan. Set analyze=true to run the query and show actual runtime stats (row counts, timing, join algorithms).

Transaction Control

Tool	Description
begin_transaction	Begin a new transaction with optional isolation level (read_committed or snapshot). Only one active transaction at a time.
transaction_execute	Execute a DML statement within the active transaction. Sees uncommitted changes. Supports RETURNING clause.
transaction_query	Run a SELECT query within the active transaction. Sees uncommitted changes. Supports aggregates, JOINs, GROUP BY, window functions, CTEs, and subqueries.
transaction_execute_batch	Execute the same SQL with multiple parameter sets within the active transaction.
commit_transaction	Commit the active transaction. All changes become permanent.
rollback_transaction	Rollback the active transaction. All changes are discarded.
savepoint	Create a named savepoint within the active transaction.
rollback_to_savepoint	Rollback to a savepoint, undoing changes after it without aborting the transaction.
release_savepoint	Release (remove) a savepoint. Changes are kept.

Schema Inspection

Tool	Description
list_tables	List all tables
list_views	List all views
describe_table	Show columns, types, nullability, keys, defaults, and extras (AUTO_INCREMENT, foreign keys)
show_create_table	Get the full CREATE TABLE DDL including all constraints
show_create_view	Get the full CREATE VIEW DDL
show_indexes	Show all indexes on a table (type, columns, uniqueness)
get_schema	Get the complete database schema: all tables with columns, indexes, DDL, plus all views

Schema Modification

Tool	Description
create_table	Create a table with INTEGER, FLOAT, TEXT, BOOLEAN, TIMESTAMP, JSON, VECTOR(N) columns. Supports PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK, AUTO_INCREMENT, REFERENCES (foreign keys), IF NOT EXISTS, and CREATE TABLE AS SELECT.
create_index	Create BTREE, HASH, BITMAP, or HNSW indexes. Supports UNIQUE and composite (multi-column). HNSW accepts m, ef_construction, ef_search, metric (l2/cosine/ip) params.
create_view	Create a read-only view (persists across restarts)
alter_table	ADD COLUMN, DROP COLUMN, RENAME COLUMN, MODIFY COLUMN (change type), RENAME TO
drop	Drop a table, view, or index (supports IF EXISTS)

Database Administration

Tool	Description
analyze_table	Collect optimizer statistics (histograms, distinct counts, min/max) for better query plans
vacuum	Clean up deleted rows, old MVCC versions, and compact indexes
pragma	Get/set database config: sync_mode, snapshot_interval, keep_snapshots, wal_flush_trigger. Trigger manual snapshot or vacuum.
version	Get the Stoolap engine version
list_functions	List all 130+ built-in SQL functions with signatures, grouped by category (aggregate, window, string, math, datetime, json, hash, conditional, type, vector, system)

Auto-injected Instructions

The server provides built-in MCP instructions that are automatically sent to the AI during the connection handshake. This means any AI client receives a comprehensive Stoolap SQL reference on connect, covering data types, supported syntax, all operator categories, index types, vector search, transaction isolation levels, and known limitations, without the user needing to configure anything. The AI can write correct Stoolap SQL from the first query.

For deeper reference (live schema + full function signatures), attach the sql-assistant prompt.

Resources

URI	Description
stoolap://schema	Full database schema with all tables, views, columns, indexes, and DDL statements
stoolap://sql-reference	Live database schema plus complete Stoolap SQL reference: data types, 130+ functions with signatures, operators, joins, indexes, window functions, CTEs, transactions, temporal queries, vector search, and known limitations

Prompts

Prompt	Description
sql-assistant	Same content as stoolap://sql-reference delivered as an MCP prompt. Use whichever your client supports.

SQL Coverage

The MCP server exposes the full Stoolap SQL surface through the query and execute tools:

• 7 data types: INTEGER, FLOAT, TEXT, BOOLEAN, TIMESTAMP, JSON, VECTOR(N)
• Joins: INNER, LEFT, RIGHT, FULL OUTER, CROSS, NATURAL, self-joins, multi-table
• Subqueries: scalar, IN/NOT IN, EXISTS/NOT EXISTS, ANY/SOME/ALL, correlated, derived tables
• CTEs: WITH, WITH RECURSIVE, multiple CTEs, column aliases
• Window functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LEAD, LAG, FIRST_VALUE, LAST_VALUE, NTH_VALUE, PERCENT_RANK, CUME_DIST (plus all aggregates with OVER)
• GROUP BY extensions: ROLLUP, CUBE, GROUPING SETS, GROUPING()
• Aggregates: COUNT, SUM, AVG, MIN, MAX, MEDIAN, STRING_AGG, ARRAY_AGG, STDDEV, VARIANCE, and more
• 100+ scalar functions: string, math, date/time, JSON, hash, conditional, vector, type conversion
• Operators: arithmetic, comparison, logical, bitwise, LIKE/ILIKE/GLOB/REGEXP, JSON (->/->>), vector (<=>), BETWEEN, IN, IS [NOT] DISTINCT FROM, INTERVAL
• Transactions: BEGIN with isolation levels (READ COMMITTED, SNAPSHOT), COMMIT, ROLLBACK, SAVEPOINT
• Temporal queries: AS OF TIMESTAMP, AS OF TRANSACTION
• Index types: BTree, Hash, Bitmap, HNSW (vector), Unique, Composite
• Vector search: k-NN with L2, cosine, inner product distances and HNSW indexing
• EXPLAIN / EXPLAIN ANALYZE for query plan inspection

Options

Flag	Default	Description
--path <path>	:memory:	Database path. Use :memory: for in-memory or a file path for persistence.
--read-only	false	Disable write operations. Read-only transactions (begin, query, commit) are still allowed for consistent reads.

Requirements

• Node.js >= 18
• The @stoolap/node package (installed automatically as a dependency)

The Stoolap native addon is bundled with @stoolap/node via prebuilt binaries for Linux (x64, arm64) and macOS (x64, arm64).

Building from Source

git clone https://github.com/stoolap/stoolap-mcp.git
cd stoolap-mcp
npm install
npm run build
node build/index.js --path ./mydata

License

Apache-2.0