SQLite MCP Server

Executes arbitrary SQL queries against local SQLite database files through the Model Context Protocol's JSON-RPC 2.0 transport layer. The server implements the MCP tool-calling interface, accepting SQL statements as tool arguments and returning query results as structured JSON responses. Uses the official MCP TypeScript SDK to handle protocol serialization, request routing, and error marshaling, enabling seamless integration with MCP-compatible clients (Claude Desktop, custom agents) without custom transport code.

Exposes a dedicated MCP tool that queries SQLite's internal schema tables (sqlite_master, pragma table_info, pragma foreign_key_list) to return structured metadata about database tables, columns, indexes, and constraints. The server parses SQLite's pragma output and formats it as JSON objects describing column names, types, nullability, primary keys, and foreign key relationships. This enables LLM clients to understand database structure without executing exploratory queries, reducing token usage and improving query generation accuracy.

Executes SELECT queries with JOIN clauses (INNER, LEFT, RIGHT, FULL OUTER) across multiple tables, returning flattened result sets with columns from all joined tables. The server handles SQLite's join semantics, including NULL propagation in outer joins and duplicate row handling. This enables LLM agents to correlate data across tables without understanding join syntax, by specifying tables and join conditions as parameters.

Provides MCP tools to create indexes on table columns and retrieve query execution plans (EXPLAIN QUERY PLAN output) to help optimize slow queries. The server accepts index definitions (table, columns, uniqueness) and generates CREATE INDEX statements, then validates that indexes are created successfully. For query optimization, the server executes EXPLAIN QUERY PLAN and returns the execution plan in a structured format, allowing LLM agents to understand query performance and suggest index creation.

Provides an MCP tool that accepts table name, column definitions (name, type, constraints), and optional indexes as structured parameters, then generates and executes the corresponding CREATE TABLE SQL statement. The server validates column types against SQLite's type affinity system (TEXT, INTEGER, REAL, BLOB, NULL) and enforces constraint syntax before execution. This allows LLM agents to programmatically define new tables without writing raw SQL, with the server handling syntax validation and error reporting.

Provides an MCP tool that accepts table name and row data as JSON objects, then validates values against the table's schema (column types, NOT NULL constraints, unique constraints) before executing INSERT statements. The server performs type coercion (e.g., converting string '123' to INTEGER if the column is INTEGER type) and reports validation errors without executing partial inserts. This enables LLM agents to insert data safely without understanding SQLite's type affinity rules or constraint semantics.

Executes SELECT queries and returns results with inferred column types (INTEGER, REAL, TEXT, BLOB, NULL) and formatted output suitable for LLM analysis. The server inspects result set metadata (column names, declared types from the query context) and applies formatting rules (e.g., rounding floats to 2 decimal places, truncating long text) to make results human-readable. This enables LLM agents to analyze data without post-processing and to reason about result types for downstream operations.

Exposes SQLite database files as MCP resources, allowing clients to discover available databases and request their contents through the MCP resource protocol. The server implements resource URIs in the format 'sqlite:///<database_path>' and supports resource templates to enable pattern-based discovery (e.g., 'sqlite:///data/*.db'). This integrates database access into MCP's broader resource model, enabling clients to reason about available data sources and request specific databases without hardcoding paths.

Catches SQLite errors during query execution and returns structured error responses that include the SQLite error code (e.g., SQLITE_CONSTRAINT, SQLITE_SYNTAX), human-readable error message, and context about which operation failed (table name, column name, constraint type). The server maps SQLite's numeric error codes to semantic categories (constraint violation, syntax error, access denied) and includes suggestions for remediation (e.g., 'check column type' for type mismatch). This enables LLM clients to understand failures and retry with corrected queries.

Exposes MCP tools for BEGIN, COMMIT, and ROLLBACK to enable multi-statement transactions. The server maintains transaction state per client connection and ensures that all queries within a transaction are atomic — either all succeed or all fail. This allows LLM agents to group related operations (e.g., insert parent record, insert child records) and roll back if any step fails, preventing partial data corruption.

Supports parameterized queries using SQLite's parameter binding mechanism (? placeholders or named parameters :name), preventing SQL injection attacks by separating query structure from data values. The server accepts query templates with parameter placeholders and a separate array/object of parameter values, then binds parameters using SQLite's native prepared statement API. This allows LLM agents to construct queries dynamically without risk of injection, even if parameter values contain SQL-like syntax.

Executes SELECT queries with aggregate functions (COUNT, SUM, AVG, MIN, MAX) and GROUP BY/HAVING clauses, returning grouped results with aggregate values. The server handles SQLite's aggregate semantics, including NULL handling, type coercion in aggregates, and HAVING clause filtering. This enables LLM agents to perform analytical queries without understanding SQL aggregate syntax, by specifying grouping columns and aggregate functions as parameters.