# Directory Structure ``` ├── .dockerignore ├── .gitattributes ├── .gitignore ├── Dockerfile ├── index.ts ├── LICENSE ├── package.json ├── pnpm-lock.yaml ├── README.md ├── test_db.sqlite3 ├── tsconfig.json └── utils ├── parseArguments.ts ├── SqliteDatabase.ts ├── types.ts └── version.ts ``` # Files -------------------------------------------------------------------------------- /.dockerignore: -------------------------------------------------------------------------------- ``` node_modules ``` -------------------------------------------------------------------------------- /.gitattributes: -------------------------------------------------------------------------------- ``` index.ts linguist-language=TypeScript package-lock.json linguist-vendored ``` -------------------------------------------------------------------------------- /.gitignore: -------------------------------------------------------------------------------- ``` package-lock.json # Logs logs *.log npm-debug.log* yarn-debug.log* yarn-error.log* lerna-debug.log* .pnpm-debug.log* # Diagnostic reports (https://nodejs.org/api/report.html) report.[0-9]*.[0-9]*.[0-9]*.[0-9]*.json # Runtime data pids *.pid *.seed *.pid.lock # Directory for instrumented libs generated by jscoverage/JSCover lib-cov # Coverage directory used by tools like istanbul coverage *.lcov # nyc test coverage .nyc_output # node-waf configuration .lock-wscript # Compiled binary addons (https://nodejs.org/api/addons.html) build/Release # Dependency directories node_modules/ jspm_packages/ # Snowpack dependency directory (https://snowpack.dev/) web_modules/ # TypeScript cache *.tsbuildinfo # Optional npm cache directory .npm # Optional eslint cache .eslintcache # Optional stylelint cache .stylelintcache # Optional REPL history .node_repl_history # Output of 'npm pack' *.tgz # Yarn Integrity file .yarn-integrity # dotenv environment variable files .env .env.development.local .env.test.local .env.production.local .env.local # parcel-bundler cache (https://parceljs.org/) .cache .parcel-cache # Serverless directories .serverless/ # FuseBox cache .fusebox/ # DynamoDB Local files .dynamodb/ # TernJS port file .tern-port # Stores VSCode versions used for testing VSCode extensions .vscode-test # yarn v2 .yarn/cache .yarn/unplugged .yarn/build-state.yml .yarn/install-state.gz .pnp.* build/ gcp-oauth.keys.json .*-server-credentials.json # Byte-compiled / optimized / DLL files __pycache__/ *.py[cod] *$py.class # C extensions *.so # Distribution / packaging .Python build/ develop-eggs/ dist/ downloads/ eggs/ .eggs/ lib/ lib64/ parts/ sdist/ var/ wheels/ share/python-wheels/ *.egg-info/ .installed.cfg *.egg MANIFEST # PyInstaller # Usually these files are written by a python script from a template # before PyInstaller builds the exe, so as to inject date/other infos into it. *.manifest *.spec # Installer logs pip-log.txt pip-delete-this-directory.txt # Unit test / coverage reports htmlcov/ .tox/ .nox/ .coverage .coverage.* .cache nosetests.xml coverage.xml *.cover *.py,cover .hypothesis/ .pytest_cache/ cover/ # Translations *.mo *.pot # Django stuff: *.log local_settings.py db.sqlite3 db.sqlite3-journal # Flask stuff: instance/ .webassets-cache # Scrapy stuff: .scrapy # Sphinx documentation docs/_build/ # PyBuilder .pybuilder/ target/ # Jupyter Notebook .ipynb_checkpoints # IPython profile_default/ ipython_config.py # pyenv # For a library or package, you might want to ignore these files since the code is # intended to run in multiple environments; otherwise, check them in: # .python-version # pipenv # According to pypa/pipenv#598, it is recommended to include Pipfile.lock in version control. # However, in case of collaboration, if having platform-specific dependencies or dependencies # having no cross-platform support, pipenv may install dependencies that don't work, or not # install all needed dependencies. #Pipfile.lock # poetry # Similar to Pipfile.lock, it is generally recommended to include poetry.lock in version control. # This is especially recommended for binary packages to ensure reproducibility, and is more # commonly ignored for libraries. # https://python-poetry.org/docs/basic-usage/#commit-your-poetrylock-file-to-version-control #poetry.lock # pdm # Similar to Pipfile.lock, it is generally recommended to include pdm.lock in version control. #pdm.lock # pdm stores project-wide configurations in .pdm.toml, but it is recommended to not include it # in version control. # https://pdm.fming.dev/latest/usage/project/#working-with-version-control .pdm.toml .pdm-python .pdm-build/ # PEP 582; used by e.g. github.com/David-OConnor/pyflow and github.com/pdm-project/pdm __pypackages__/ # Celery stuff celerybeat-schedule celerybeat.pid # SageMath parsed files *.sage.py # Environments .env .venv env/ venv/ ENV/ env.bak/ venv.bak/ # Spyder project settings .spyderproject .spyproject # Rope project settings .ropeproject # mkdocs documentation /site # mypy .mypy_cache/ .dmypy.json dmypy.json # Pyre type checker .pyre/ # pytype static type analyzer .pytype/ # Cython debug symbols cython_debug/ .DS_Store # PyCharm # JetBrains specific template is maintained in a separate JetBrains.gitignore that can # be found at https://github.com/github/gitignore/blob/main/Global/JetBrains.gitignore # and can be added to the global gitignore or merged into this file. For a more nuclear # option (not recommended) you can uncomment the following to ignore the entire idea folder. #.idea/ ``` -------------------------------------------------------------------------------- /README.md: -------------------------------------------------------------------------------- ```markdown # SQLite MCP Server A Model Context Protocol (MCP) server implementation using TypeScript for interacting with an SQLite database. This server provides an interactive interface for executing SQL queries, managing database schemas, and synthesizing business insights—all within an extensible protocol framework. Not familiar with MCP? Check out the [What is an MCP?](#whats-an-mcp) section below. ## Features | Command | Description | Example | |---------|-------------|---------| | `list_tables` | List all tables on the SQLite database | - | | `read_query` | Execute SELECT queries on the SQLite database | `SELECT * FROM users WHERE age > 18` | ## Installation & Setup ```bash git clone https://github.com/javsanmar5/mcp-server.sqlite.git cd mcp-server.sqlite ``` Since this hasn't been published as an npm package yet, we'll focus on the Docker installation method: ### 1. Build the Docker image ```bash docker build -t mcp/sqlite . ``` ### 2. Configure your AI client Add the following to your AI client's configuration file: ```json "mcpServers": { "sqlite": { "command": "docker", "args": [ "run", "--rm", "-i", "-v", "mcp-test:/mcp", "mcp/sqlite", "--db-path", "test_db.sqlite3" ] } } ``` If you don't know what is that json file you might want to see the [Client Configuration Guide](#tutorial-setting-up-with-claude-desktop). ### 3. Restart your AI client After restarting, the MCP Tools should be available in your AI client's interface. _On Windows, you may need to manually kill the process for the changes to take effect._ ## Documentation ### What's an MCP? Model Context Protocol (MCP) is a standardized way for AI models to interact with external tools and services. It allows AI assistants to perform actions like running database queries, accessing external APIs, or manipulating files through a defined protocol interface. MCPs extend AI capabilities beyond conversation by providing structured access to tools and data sources without requiring direct integration into the AI model itself. ### Tutorial: Setting up with Claude Desktop Claude Desktop is one of many AI clients that support MCP servers. Here's how to set it up on Windows: 1. Press `Windows Key + R` to open the Run dialog 2. Type `%appdata%\Claude` and press Enter 3. Create a new file called `claude_desktop_config.json` if it doesn't exist already 4. Add the configuration from step 2 of the setup instructions above 5. Save the file and restart Claude Desktop 6. You should now see the SQLite tools available in your Claude interface ## License This project is licensed under the MIT License. ## Contributing This project was created primarily for learning purposes. However, if you'd like to contribute, feel free to submit a Pull Request and I'll review it. Thanks for your interest! ``` -------------------------------------------------------------------------------- /utils/version.ts: -------------------------------------------------------------------------------- ```typescript export const VERSION = "0.0.1"; ``` -------------------------------------------------------------------------------- /utils/types.ts: -------------------------------------------------------------------------------- ```typescript import { z } from "zod"; export const ReadQuerySchema = z.object({ query: z.string().describe("SELECT SQL query to execute"), }); ``` -------------------------------------------------------------------------------- /utils/parseArguments.ts: -------------------------------------------------------------------------------- ```typescript export function getArgumentValue(flag: string): string | undefined { const index = process.argv.indexOf(flag); return index !== -1 && index + 1 < process.argv.length ? process.argv[index + 1] : undefined; } ``` -------------------------------------------------------------------------------- /tsconfig.json: -------------------------------------------------------------------------------- ```json { "compilerOptions": { "target": "ES2019", "module": "commonjs", "strict": true, "esModuleInterop": true, "outDir": "./dist", "resolveJsonModule": true }, "include": [ "./**/*.ts" ], "exclude": ["node_modules"] } ``` -------------------------------------------------------------------------------- /Dockerfile: -------------------------------------------------------------------------------- ```dockerfile FROM node:22.12 AS builder RUN apt-get update && apt-get install -y libsqlite3-dev # Sorry for installing pnpm. I've tried to do it w npm but couldn't RUN npm install -g pnpm WORKDIR /app COPY package.json pnpm-lock.yaml ./ ENV npm_config_build_from_source=true ENV npm_config_sqlite=/usr RUN pnpm install --ignore-scripts # sqlite3 package was being failing to be installed. I have to rebuilt it here RUN cd node_modules/sqlite3 && pnpm rebuild COPY . . RUN pnpm run build FROM node:22.12 AS release WORKDIR /app COPY --from=builder /app/dist /app/dist COPY --from=builder /app/node_modules /app/node_modules COPY --from=builder /app/package.json /app/package.json ENTRYPOINT ["node", "dist/index.js"] ``` -------------------------------------------------------------------------------- /package.json: -------------------------------------------------------------------------------- ```json { "name": "sqlite_server", "version": "0.0.1", "description": "MCP Server for SQLite. Implemented with Typescript", "main": "index.ts", "bin": { "mcp-server-sqlite": "dist/index.js" }, "bugs": "https://github.com/javsanmar5/mcp-server.sqlite/issues", "files": [ "dist" ], "scripts": { "build": "tsc && shx chmod +x dist/*.js", "prepare": "npm run build", "watch": "tsc --watch", "test": "test" }, "author": "Javier Santos Martín", "license": "MIT", "packageManager": "pnpm@latest", "dependencies": { "@modelcontextprotocol/sdk": "^1.6.1", "@types/node": "^22.13.9", "sqlite": "^5.1.1", "sqlite3": "^5.1.7", "ts-node": "^10.9.2", "typescript": "^5.8.2", "zod": "^3.24.2", "zod-to-json-schema": "^3.24.3" }, "devDependencies": { "shx": "^0.3.4" } } ``` -------------------------------------------------------------------------------- /utils/SqliteDatabase.ts: -------------------------------------------------------------------------------- ```typescript import * as fs from "fs"; import * as path from "path"; import os from "os"; import sqlite3 from "sqlite3"; import { open, Database } from "sqlite"; export class SqliteDatabase { private dbPath: string; constructor(dbPath: string) { if (dbPath.startsWith("~")) { dbPath = path.join(os.homedir(), dbPath.slice(1)); } this.dbPath = dbPath; const dir = path.dirname(this.dbPath); fs.mkdirSync(dir, { recursive: true }); } async initDatabase(): Promise<Database> { const db = await open({ filename: this.dbPath, driver: sqlite3.Database, }) return db; } // Params is there for the future. async executeQuery(query: string, params?: any[]): Promise<any[]> { const db = await this.initDatabase(); try { const result = await db.all(query, params); return result; } catch (error) { console.error("Database error:", error); throw error; } finally { await db.close(); } } } ``` -------------------------------------------------------------------------------- /index.ts: -------------------------------------------------------------------------------- ```typescript import { Server } from "@modelcontextprotocol/sdk/server/index.js"; import { CallToolRequestSchema, ListToolsRequestSchema } from "@modelcontextprotocol/sdk/types.js"; import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js"; import zodToJsonSchema from "zod-to-json-schema"; import { z } from "zod"; import { VERSION } from "./utils/version.js"; import * as types from './utils/types.js'; import { SqliteDatabase } from "./utils/SqliteDatabase.js"; import { getArgumentValue } from "./utils/parseArguments.js"; const dbPath: string | undefined = getArgumentValue("--db-path"); if (!dbPath) throw new Error("A dbpath must be specified by the argument: --db-path"); const db: SqliteDatabase = new SqliteDatabase(dbPath); const server = new Server( { name: "sqlite-mcp-server", version: VERSION, }, { capabilities: { tools: {}, } } ); server.setRequestHandler(ListToolsRequestSchema, async () => { return { tools: [ { name: "read_query", description: "Execute a SELECT query on a table of the SQLite database", inputSchema: zodToJsonSchema(types.ReadQuerySchema) }, { name: "list_tables", description: "List all tables on the SQLite database", inputSchema: zodToJsonSchema(types.ReadQuerySchema) } ] } }); server.setRequestHandler(CallToolRequestSchema, async (request) => { const { name, arguments: args } = request.params; try { if (!args) throw new Error("Arguments are required"); switch (name) { // switch-case because there will be multiple options case "read_query": { const { query, params } = args as { query: string; params?: any[] }; if (typeof query !== "string") throw new Error("Invalid arguments: expected 'query' to be a string"); if (!query.trim().toUpperCase().startsWith("SELECT")) throw new Error("Only SELECT queries are allowed for read_query"); const results = await db.executeQuery(query, params); return { content: [{ type: "text", text: JSON.stringify(results, null, 2) }], }; } case "list_tables": { const query = "SELECT name FROM sqlite_master WHERE type='table';" const results = await db.executeQuery(query); return { content: [{ type: "text", text: JSON.stringify(results, null, 2) }], }; } default: throw new Error(`Unknown tool: ${request.params.name}`); } } catch (error) { if (error instanceof z.ZodError) { throw new Error(`Invalid input: ${JSON.stringify(error.errors)}`); } throw error; } }); async function runServer() { const transport = new StdioServerTransport(); await server.connect(transport); } runServer().catch((error) => { console.error("Fatal error in main():", error); process.exit(1); }); ```