SQLite MCP Server

Exposes SQLite database operations as MCP tools through a standardized JSON-RPC 2.0 transport layer, enabling LLM clients to discover and invoke database capabilities via the MCP protocol primitives. The server implements the MCP Tools interface, registering discrete database operations (query execution, schema inspection, table creation) as callable tools with JSON Schema definitions for input validation and type safety.

Executes arbitrary SQL queries against a SQLite database file and returns results as structured JSON objects, with built-in error handling for malformed SQL, constraint violations, and database locking. The implementation parses query results into row-based JSON format, enabling LLMs to reason over tabular data natively. Supports both SELECT queries (returning result sets) and DML operations (INSERT, UPDATE, DELETE) with transaction semantics.

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 tools to inspect SQLite database schema including table definitions, column types, constraints, indexes, and relationships. The implementation queries SQLite's built-in metadata tables (sqlite_master, PRAGMA table_info, PRAGMA foreign_key_list) and returns structured schema information as JSON, enabling LLMs to understand database structure before constructing queries. Supports discovery of all tables, views, and their column definitions with type information.

Allows LLMs to create new tables in the SQLite database by executing CREATE TABLE statements with full DDL support including column definitions, constraints (PRIMARY KEY, UNIQUE, NOT NULL, CHECK), and indexes. The implementation validates DDL syntax and enforces schema constraints before execution, preventing invalid table definitions. Supports both simple table creation and complex schemas with foreign keys and composite keys.

Supports SQLite transactions (BEGIN, COMMIT, ROLLBACK) to ensure ACID properties for multi-step data operations. The implementation manages transaction state and allows LLMs to group multiple SQL operations into atomic units, rolling back all changes if any operation fails. Enables data consistency guarantees for complex workflows like data imports or multi-table updates.

Enables LLMs to execute analytical SQL queries including GROUP BY, aggregation functions (COUNT, SUM, AVG, MIN, MAX), JOINs, and window functions to perform data analysis directly on SQLite. The implementation returns aggregated results as JSON, allowing LLMs to derive insights from structured data without exporting to external tools. Supports complex queries with subqueries and CTEs (Common Table Expressions).

Exposes SQLite database files as MCP resources, allowing LLM clients to read database file contents or metadata through the MCP Resources interface. The implementation registers database files with MIME types and optional descriptions, enabling clients to discover available databases and access them via the standardized resource protocol. Supports both direct file access and metadata-only exposure depending on security configuration.

Implements comprehensive error handling for SQL syntax errors, constraint violations, type mismatches, and database locking conditions, returning detailed error messages and SQLite error codes to LLM clients. The implementation validates SQL before execution where possible and provides actionable error information enabling LLMs to correct queries or handle failures gracefully. Supports error recovery suggestions and constraint violation details.

Supports operations across multiple SQLite database files, allowing LLM clients to select or switch between databases for queries and operations. The implementation maintains database connections for multiple files and routes tool calls to the appropriate database based on context or explicit selection. Enables workflows that require data coordination across separate SQLite databases.

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.