#
tokens: 3742/50000 12/12 files
lines: off (toggle) GitHub
raw markdown copy
# 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);
});

```