mcp-server-sqlite 1.0.0

<div align="center">
  <h1><code>mcp-server-sqlite</code></h1>

  <p>
    <strong>A production-grade MCP server for SQLite with fine-grained access control</strong>
  </p>

  <p>
    <a href="https://github.com/0xOmarA/mcp-server-sqlite/actions/workflows/ci.yml"><img src="https://github.com/0xOmarA/mcp-server-sqlite/actions/workflows/ci.yml/badge.svg" alt="CI"></a>
    <a href="https://crates.io/crates/mcp-server-sqlite"><img src="https://img.shields.io/crates/v/mcp-server-sqlite.svg" alt="crates.io"></a>
    <a href="https://crates.io/crates/mcp-server-sqlite"><img src="https://img.shields.io/crates/d/mcp-server-sqlite.svg" alt="downloads"></a>
    <a href="LICENSE-APACHE"><img src="https://img.shields.io/badge/license-Apache--2.0%20OR%20MIT-blue.svg" alt="License"></a>
  </p>
</div>

## Why this exists

Every existing MCP server for SQLite falls short in at least one critical way. The [official reference server][official-server] from Anthropic is a ~300-line Python script with [known unpatched SQL injection vulnerabilities][sql-injection-issue], no parameterized queries, no transactions, no read-only mode, and a hardcoded demo prompt. It was archived in May 2025 with Anthropic declining to fix the security issues, yet it was forked over 5,000 times. The community alternatives each solve one or two problems but none solves them comprehensively.

This server was built to be the SQLite MCP server you can actually trust in production. It is written in Rust, ships as a single binary with no runtime dependencies, and uses SQLite's own [`sqlite3_set_authorizer`][sqlite-authorizer] API to enforce access control at the query-preparation level, before any SQL ever executes. The access control system supports column-level granularity, specificity-based rule resolution, and five built-in presets ranging from deny-everything to allow-everything.

[official-server]: https://github.com/modelcontextprotocol/servers-archived/tree/main/src/sqlite
[sql-injection-issue]: https://github.com/modelcontextprotocol/servers/issues/1348
[sqlite-authorizer]: https://www.sqlite.org/c3ref/set_authorizer.html

## Features

- **Authorizer-based access control.** Uses SQLite's native authorizer callback to intercept every operation at prepare time. Supports 30+ selector types covering reads, writes, DDL, pragmas, functions, transactions, attach/detach, and virtual tables, all with per-table, per-column, and per-function granularity.
- **Five permission presets.** `deny-everything`, `read-only` (default), `read-write`, `full-ddl`, and `allow-everything`. Layer `--allow` and `--deny` overrides on top with a specificity-based resolution algorithm where deny wins ties.
- **13 tools.** A single `execute` tool for arbitrary SQL, plus purpose-built tools for schema introspection, full-text search, query planning, backups, and database maintenance.
- **Full-text search.** First-class FTS5 support with dedicated tools for creating indexes and searching with BM25 ranking and highlighted snippets.
- **Query timeouts.** Optional per-server timeout that uses SQLite's progress handler to abort runaway queries before they can tie up resources.
- **Connection pooling.** Built on `r2d2` for connection reuse with per-connection authorizer and timeout installation.
- **Init scripts.** Seed schemas and data on first run with `--init-sql` files that are skipped when the database already exists.
- **Single binary.** Compiles SQLite statically via `rusqlite`'s `bundled` feature. No Python, no Node, no runtime dependencies.

## Installation

### From crates.io

```bash
cargo install mcp-server-sqlite
```

### From source

```bash
git clone https://github.com/0xOmarA/mcp-server-sqlite.git
cd mcp-server-sqlite
cargo install --path .
```

## Quick start

Start a read-only server with an in-memory database:

```bash
mcp-server-sqlite
```

Start a read-write server on a persistent database with a schema init file:

```bash
mcp-server-sqlite --database ./app.db --preset read-write --init-sql schema.sql
```

### Claude Desktop configuration

Add the following to your Claude Desktop MCP config file:

```json
{
  "mcpServers": {
    "sqlite": {
      "command": "mcp-server-sqlite",
      "args": [
        "--database", "./my-database.db",
        "--preset", "read-only"
      ]
    }
  }
}
```

## Tools

The server exposes 13 tools over MCP. Each tool validates inputs, enforces the configured access control policy, and returns structured typed output.

### `execute`

Runs any SQL query against the database. This is the primary tool. It handles SELECT, INSERT, UPDATE, DELETE, DDL, and everything else SQLite supports. Returns typed result rows (preserving NULL, Integer, Real, Text, and Blob types) and a count of rows changed.

```
execute({ "query": "SELECT name, age FROM students WHERE age > 20" })
```

Access control is enforced at the SQLite authorizer level, so denied operations never execute. If the configured policy denies any part of the query (a table read, a column access, a function call), the entire statement is rejected with a clear error message.

### `list_tables`

Lists all tables in the database with their names and `CREATE TABLE` definitions.

### `describe_table`

Returns column metadata for a specific table: column name, declared type, NOT NULL constraint, default value, and whether it is part of the primary key.

```
describe_table({ "table_name": "students" })
```

### `list_indexes`

Lists indexes in the database. Optionally filter by table. Returns the index name, table name, uniqueness flag, ordered column list, and the WHERE clause for partial indexes.

```
list_indexes({ "table_name": "students" })
```

### `list_foreign_keys`

Returns foreign key constraints for a given table, including the local column, referenced table and column, and the ON UPDATE/ON DELETE actions.

```
list_foreign_keys({ "table_name": "enrollments" })
```

### `list_views`

Lists all views with their names and defining SQL.

### `list_triggers`

Lists triggers in the database. Optionally filter by table. Returns the trigger name, attached table, event (INSERT/UPDATE/DELETE), timing (BEFORE/AFTER/INSTEAD OF), and full SQL definition.

```
list_triggers({ "table_name": "students" })
```

### `explain_query`

Returns the `EXPLAIN QUERY PLAN` output for a query without executing it. Shows how SQLite will access tables, which indexes it will use, and how it will join data. Useful for understanding query performance.

```
explain_query({ "query": "SELECT * FROM students WHERE name = 'Omar'" })
```

### `backup`

Creates a full backup of the database to a file path using SQLite's online backup API. Runs incrementally in pages so it does not block other operations for long. Returns the number of pages copied and the destination path.

```
backup({ "destination": "/tmp/app-backup.db" })
```

### `create_fts_index`

Creates an FTS5 virtual table over specified columns of an existing table. The virtual table name defaults to `{table}_fts` but can be customized.

```
create_fts_index({
  "table_name": "articles",
  "columns": ["title", "body"]
})
```

### `search_fts`

Runs a full-text search query against an FTS5 virtual table. Returns results ranked by BM25 relevance with highlighted snippets showing where the query matched. Configurable result limit, snippet length, and highlight markers.

```
search_fts({
  "fts_table": "articles_fts",
  "query": "sqlite AND indexing",
  "limit": 5
})
```

### `vacuum`

Runs the SQLite `VACUUM` command to rebuild the database file, reclaim unused space, and defragment storage.

### `database_info`

Returns database metadata: SQLite version, page size, page count, total size in bytes, journal mode, WAL checkpoint status (when in WAL mode), freelist page count, and the number of tables and indexes.

## Access control

The access control system is the core differentiator of this server. It is built on SQLite's [`sqlite3_set_authorizer`][sqlite-authorizer] callback, which means SQLite itself parses every query and reports every operation it will perform. The server then evaluates each operation against the configured policy. Denied operations cause the entire statement to be rejected at prepare time, before any data is read or modified.

This approach has several important properties:

- **Zero parsing gaps.** SQLite does the parsing, not a third-party SQL parser. Every valid SQLite statement is handled correctly, including edge cases like triggers, views, CTEs, and virtual tables.
- **Column-level granularity.** The `Read` and `Update` selectors accept both a table name and a column name, so you can deny access to specific columns (e.g. `--deny Read(Users.ssn)`).
- **Indirect operation tracking.** If a trigger on table A fires an INSERT into table B, the authorizer catches the INSERT on table B. Operations hidden inside triggers and views are not invisible to the policy.
- **Fail-closed by default.** The default preset is `read-only`. When allow and deny rules conflict at the same specificity level, deny wins.

### Presets

Presets define the baseline permissions before any `--allow`/`--deny` overrides are applied.

| Preset | Allows | Denies |
|---|---|---|
| `deny-everything` | Nothing | Everything |
| `read-only` (default) | Read, Select, Transaction, Function, Recursive, Pragma | Insert, Update, Delete, all DDL, Attach, Detach |
| `read-write` | Extends read-only with Insert, Update, Delete, Savepoint, Analyze, Reindex, temp objects | Permanent DDL, Attach, Detach |
| `full-ddl` | Extends read-write with CreateTable, DropTable, AlterTable, CreateIndex, DropIndex, CreateTrigger, DropTrigger, CreateView, DropView | Attach, Detach, virtual tables |
| `allow-everything` | Everything | Nothing |

### Selectors

Selectors identify the operation and, optionally, the exact resources a rule applies to. The syntax is `Action` or `Action(field1.field2)` where `*` is a wildcard.

**Data operations:**

| Selector | Fields | Example |
|---|---|---|
| `Read` | `table_name.column_name` | `Read(Users.email)` |
| `Insert` | `table_name` | `Insert(AuditLog)` |
| `Update` | `table_name.column_name` | `Update(Users.password)` |
| `Delete` | `table_name` | `Delete(Sessions)` |
| `Select` | (none) | `Select` |

**Transactions:**

| Selector | Fields | Example |
|---|---|---|
| `Transaction` | `operation` | `Transaction(BEGIN)` |
| `Savepoint` | `operation.savepoint_name` | `Savepoint(*.my_sp)` |

**DDL (permanent):**

| Selector | Fields | Example |
|---|---|---|
| `CreateTable` | `table_name` | `CreateTable(TempData)` |
| `DropTable` | `table_name` | `DropTable(OldRecords)` |
| `AlterTable` | `database_name.table_name` | `AlterTable(*.Users)` |
| `CreateIndex` | `table_name.index_name` | `CreateIndex(Users.idx_email)` |
| `DropIndex` | `table_name.index_name` | `DropIndex` |
| `CreateView` | `view_name` | `CreateView(ActiveUsers)` |
| `DropView` | `view_name` | `DropView` |
| `CreateTrigger` | `table_name.trigger_name` | `CreateTrigger(Users.*)` |
| `DropTrigger` | `table_name.trigger_name` | `DropTrigger` |

**DDL (temporary):**

| Selector | Fields | Example |
|---|---|---|
| `CreateTempTable` | `table_name` | `CreateTempTable` |
| `DropTempTable` | `table_name` | `DropTempTable` |
| `CreateTempIndex` | `table_name.index_name` | `CreateTempIndex` |
| `DropTempIndex` | `table_name.index_name` | `DropTempIndex` |
| `CreateTempView` | `view_name` | `CreateTempView` |
| `DropTempView` | `view_name` | `DropTempView` |
| `CreateTempTrigger` | `table_name.trigger_name` | `CreateTempTrigger` |
| `DropTempTrigger` | `table_name.trigger_name` | `DropTempTrigger` |

**Other operations:**

| Selector | Fields | Example |
|---|---|---|
| `Pragma` | `pragma_name` | `Pragma(journal_mode)` |
| `Function` | `function_name` | `Function(count)` |
| `Attach` | `filename` | `Attach` |
| `Detach` | `database_name` | `Detach` |
| `Reindex` | `index_name` | `Reindex` |
| `Analyze` | `table_name` | `Analyze(Users)` |
| `CreateVtable` | `table_name.module_name` | `CreateVtable(*.fts5)` |
| `DropVtable` | `table_name.module_name` | `DropVtable` |
| `Recursive` | (none) | `Recursive` |

### Specificity and resolution

Each selector has a specificity equal to the number of fields pinned to concrete values (not `*`). When SQLite asks whether an operation is allowed:

1. All matching rules are collected. Globs match anything; concrete values must match exactly.
2. Rules are evaluated from the highest specificity to the lowest. The first level with at least one matching rule determines the outcome.
3. If both allow and deny rules match at the same specificity, deny wins.
4. If no rule matches at any level, the per-action default from the preset applies.

**Example: read-only with a sensitive column denied**

```bash
mcp-server-sqlite --database ./app.db --deny Read(Users.ssn)
```

The `read-only` preset allows all reads. The `--deny Read(Users.ssn)` rule has specificity 2 (both table and column pinned), which beats the preset's blanket allow at specificity 0. Reading `Users.name` is fine. Reading `Users.ssn` is denied.

**Example: deny a table but allow one column**

```bash
mcp-server-sqlite --database ./app.db \
  --deny Read(Secrets) \
  --allow Read(Secrets.id)
```

The deny on `Read(Secrets)` has specificity 1. The allow on `Read(Secrets.id)` has specificity 2. Reads on `Secrets.id` are allowed because specificity 2 beats specificity 1. All other columns in `Secrets` remain denied.

**Example: deny everything, allow only specific functions**

```bash
mcp-server-sqlite --preset deny-everything \
  --allow Read \
  --allow Select \
  --allow Transaction \
  --allow Function(count) \
  --allow Function(sum)
```

Starting from a deny-everything baseline, only reads and the `count()` and `sum()` SQL functions are permitted. All other functions (e.g. `load_extension`) remain denied.

## CLI reference

```
Usage: mcp-server-sqlite [OPTIONS]

Options:
      --database <DATABASE>
          The SQLite database URI. Defaults to a shared in-memory database.
          Use a file URI for persistence (e.g. `file:./app.db`). Query
          parameters like `?mode=ro` and `?cache=shared` are supported.
          [default: file::memory:?cache=shared]

      --init-sql <INIT_SQL>
          Paths to SQL files executed once when creating a new database.
          Skipped entirely if the database file already exists. Use this
          to set up schemas and seed data on first run. May be specified
          multiple times.

  -p, --preset <PRESET>
          The baseline permission preset.
          [default: read-only]
          [possible values: deny-everything, read-only, read-write,
           full-ddl, allow-everything]

  -a, --allow <ALLOW>
          Allow a specific SQL operation. Accepts a selector in the form
          Action or Action(field1.field2) where * is a wildcard. More
          specific rules override less specific ones. May be specified
          multiple times.

  -d, --deny <DENY>
          Deny a specific SQL operation. Same selector syntax as --allow.
          When an allow and deny rule match at the same specificity level,
          deny wins. May be specified multiple times.

      --timeout-ms <TIMEOUT_MS>
          Maximum time in milliseconds that any single SQL operation is
          allowed to run before being interrupted. Omit for no timeout.

  -h, --help
          Print help (including more examples with --help)

  -V, --version
          Print version
```

## Examples

### Read-only analytics database

Expose a database for read-only analytics, denying access to PII columns:

```bash
mcp-server-sqlite \
  --database ./analytics.db \
  --deny Read(*.email) \
  --deny Read(*.phone) \
  --deny Read(*.ssn)
```

### Application backend with restricted writes

Allow reads and writes, but protect the audit log and prevent schema changes:

```bash
mcp-server-sqlite \
  --database ./app.db \
  --preset read-write \
  --deny Delete(AuditLog) \
  --deny Update(AuditLog)
```

### Locked-down server for untrusted agents

Start from deny-everything and allow only what is needed:

```bash
mcp-server-sqlite \
  --database ./data.db \
  --preset deny-everything \
  --allow Read(Products) \
  --allow Read(Categories) \
  --allow Select \
  --allow Transaction \
  --allow Function(count) \
  --allow Function(sum) \
  --allow Function(avg) \
  --timeout-ms 5000
```

### Development server with full access

For local development where convenience matters more than safety:

```bash
mcp-server-sqlite \
  --database ./dev.db \
  --preset allow-everything \
  --init-sql schema.sql \
  --init-sql seed.sql
```

### Full-text search workflow

Set up a database with FTS5 for document search:

```bash
mcp-server-sqlite \
  --database ./docs.db \
  --preset full-ddl \
  --allow CreateVtable(*.fts5)
```

Then through the MCP tools:

```
create_fts_index({ "table_name": "articles", "columns": ["title", "body"] })

search_fts({ "fts_table": "articles_fts", "query": "rust AND sqlite" })
```

## Logging

The server logs to stderr using the `tracing` framework. Control the log level with the `RUST_LOG` environment variable:

```bash
# Default (info)
mcp-server-sqlite --database ./app.db

# Verbose (includes tool inputs/outputs)
RUST_LOG=debug mcp-server-sqlite --database ./app.db

# Quiet (errors only)
RUST_LOG=error mcp-server-sqlite --database ./app.db
```

## Comparison with existing servers

| Feature | Official (Python) | jparkerweb (JS) | sqlite-explorer (Python) | dbhub (TS) | **This server** |
|---|---|---|---|---|---|
| Authorizer-based access control | No | No | No | No | **Yes** |
| Column-level permissions | No | No | No | No | **Yes** |
| Permission presets | No | No | No | Read-only toggle | **5 presets + overrides** |
| Query timeouts | No | No | No | Yes | **Yes** |
| Full-text search (FTS5) | No | No | No | No | **Yes** |
| Query plan explanation | No | No | No | No | **Yes** |
| Database backup | No | No | No | No | **Yes** |
| Schema introspection depth | Tables + columns | Tables + columns | Tables + columns | Tables + columns | **Tables, columns, indexes, foreign keys, views, triggers** |
| SQL injection protection | [Vulnerable][sql-injection-issue] | N/A | Parameter binding | N/A | **Authorizer rejects at prepare time** |
| Connection pooling | No | No | No | No | **Yes (r2d2)** |
| Init scripts | No | No | No | No | **Yes** |
| Single binary, no runtime | No (Python) | No (Node.js) | No (Python) | No (Node.js) | **Yes (Rust)** |

## License

Licensed under either of [Apache License, Version 2.0](LICENSE-APACHE) or [MIT License](LICENSE-MIT) at your option.