mcp-sqlite-tools

mcp-sqlite-tools

A Model Context Protocol (MCP) server that provides comprehensiveSQLite database operations for LLMs. This server enables AI assistantsto interact with local SQLite databases safely and efficiently, withbuilt-in security features, advanced transaction support, and clearseparation between read-only and destructive operations.

Features

🗄️ Database Management

• Open/Create Database: Open existing databases or create new ones
• Close Database: Properly close database connections
• List Databases: Discover database files in directories
• Database Info: Get comprehensive database metadata andstatistics

📊 Table Operations

• List Tables: View all tables and views in a database
• Describe Table: Get detailed schema information for tables
• Create Table: Create new tables with custom column definitions
• Drop Table: Remove tables (with safety warnings)

🔍 Query Operations

• Execute Read Query: Safe SELECT, PRAGMA, and EXPLAIN queries
• Execute Write Query: INSERT, UPDATE, DELETE operations
• Execute Schema Query: DDL operations (CREATE, ALTER, DROP)
• Bulk Insert: Efficient batch insertion of multiple records

💾 Transaction Management

• Begin Transaction: Start database transactions with savepointsupport
• Commit Transaction: Commit changes with nested transactionhandling
• Rollback Transaction: Safely rollback changes and nestedsavepoints
• Auto-cleanup: Automatic cleanup of stale transactions

📋 Schema Operations

• Export Schema: Export database schema to SQL or JSON format
• Import Schema: Import and execute schema from SQL or JSON
• Selective Export: Export specific tables or entire databasestructure

🛠️ Database Maintenance

• Backup Database: Create database backups with timestamps
• Vacuum Database: Optimize database storage and performance
• Connection Pooling: Advanced connection management with healthmonitoring

⚠️ Security Features

This server implements multiple layers of security:

• Query Classification: Automatic separation of read-only, write,schema, and transaction operations
• Path Validation: Prevents directory traversal attacks
• Configurable Path Restrictions: Control access to absolute paths
• Input Validation: Comprehensive parameter validation usingValibot
• Advanced Connection Pooling: Connection limits, healthmonitoring, and idle timeout
• Transaction Safety: Automatic stale transaction cleanup andnested savepoint support
• Resource Cleanup: Graceful cleanup on server shutdown withmaintenance scheduling

Tool Separation for Hook-Based Safety

The tools are intentionally separated into distinct categories toenable fine-grained approval control in MCP clients like Claude Code:

✓ SAFE Tools (Read-only operations):

• execute_read_query - SELECT, PRAGMA, EXPLAIN queries
• list_tables, describe_table, database_info
• export_schema, backup_database

These tools can be auto-approved or approved once, allowing the AI tofreely explore your database structure and read data.

⚠️ DESTRUCTIVE Tools (Data modification):

• execute_write_query - INSERT, UPDATE, DELETE
• bulk_insert - Batch insertions
• import_csv - CSV data import
• drop_table - Permanent table deletion

These tools should require individual approval for each operation,giving you visibility into what data will be modified before ithappens.

⚠️ SCHEMA CHANGE Tools (Structure modification):

• execute_schema_query - CREATE, ALTER, DROP statements
• create_table - Table creation
• import_schema - Schema import
• import_csv - Can create missing tables from CSV headers

These tools modify database structure and should require individualapproval to prevent unintended schema changes.

⚠️ FILE WRITE Tools:

• export_csv - Writes CSV files, including absolute paths

🔒 TRANSACTION Tools:

• begin_transaction, commit_transaction, rollback_transaction

Can be configured based on your workflow needs.

Example Claude Code Hook Configuration:

// In your Claude Code hooks
export function toolApproval(tool) {
	// Auto-approve safe read operations
	if (
		tool.name.includes('read') ||
		tool.name.includes('list') ||
		tool.name.includes('describe') ||
		tool.name.includes('export') ||
		tool.name.includes('backup') ||
		tool.name.includes('info')
	) {
		return 'auto-approve';
	}

	// Require approval for destructive operations
	if (
		tool.name.includes('write') ||
		tool.name.includes('delete') ||
		tool.name.includes('drop') ||
		tool.name.includes('insert') ||
		tool.name.includes('schema')
	) {
		return 'require-approval';
	}

	return 'require-approval'; // Default to safe
}

This separation ensures you maintain control over destructiveoperations while allowing the AI to work efficiently with read-onlyqueries.

Installation

From npm (when published)

npm install -g mcp-sqlite-tools

From source

git clone <repository-url>
cd mcp-sqlite-tools
pnpm install
pnpm run build

Configuration

Environment Variables

The server can be configured using environment variables:

# Default directory for SQLite databases (relative to project root)
SQLITE_DEFAULT_PATH=.

# Allow absolute paths for database files (security setting)
SQLITE_ALLOW_ABSOLUTE_PATHS=true

# SQLite lock busy timeout in milliseconds (not wall-clock query runtime)
SQLITE_BUSY_TIMEOUT=30000

# Default backup directory for database backups
SQLITE_BACKUP_PATH=./backups

# Enable debug logging
DEBUG=false

MCP Client Configuration

Option 1: Global User Configuration (Recommended)

Configure once in your VS Code user settings to work across allworkspaces. Add this to your global mcp.json file(%APPDATA%\Code\User\mcp.json on Windows):

For VS Code global configuration, edit ~/.config/Code/User/mcp.json(or equivalent Windows location):

{
	"servers": {
		"sqlite-tools": {
			"command": "npx",
			"args": ["-y", "mcp-sqlite-tools"]
		}
	}
}

For WSL users, use this format in your global config:

{
	"servers": {
		"sqlite-tools": {
			"command": "wsl.exe",
			"args": ["bash", "-c", "npx -y mcp-sqlite-tools"]
		}
	}
}

Benefits:

• ✅ One configuration works everywhere - no per-project setupneeded
• 📁 Automatically uses current workspace - databases created inwhatever project you have open
• 🔄 Always up to date - uses latest published version via npx

Option 2: Workspace-Specific Configuration

For teams that want to share database configuration via versioncontrol, create a .vscode/mcp.json file in your workspace:

{
	"servers": {
		"sqlite-tools": {
			"command": "npx",
			"args": ["-y", "mcp-sqlite-tools"],
			"env": {
				"SQLITE_DEFAULT_PATH": "${workspaceFolder}/databases",
				"SQLITE_ALLOW_ABSOLUTE_PATHS": "true",
				"SQLITE_BACKUP_PATH": "${workspaceFolder}/backups"
			}
		}
	}
}

Benefits:

• � Team sharing - configuration committed to version control
• 📂 Organized structure - databases in dedicated /databasesfolder
• �️ Project isolation - each project has its own databaseconfiguration

Claude Desktop / Cline Configuration

Add this to your MCP client configuration:

{
	"mcpServers": {
		"mcp-sqlite-tools": {
			"command": "npx",
			"args": ["-y", "mcp-sqlite-tools"],
			"env": {
				"SQLITE_DEFAULT_PATH": ".",
				"SQLITE_ALLOW_ABSOLUTE_PATHS": "true",
				"SQLITE_BUSY_TIMEOUT": "30000",
				"SQLITE_BACKUP_PATH": "./backups"
			}
		}
	}
}

Environment Variables

The following environment variables can be used to configure the MCPserver:

SQLITE_MAX_QUERY_TIME is still accepted as a deprecated alias forSQLITE_BUSY_TIMEOUT; it is not a wall-clock query runtime limit.

Path Resolution:

• Relative paths are resolved from the default path
• Use ${workspaceFolder} in VS Code for workspace-relative paths
• Set SQLITE_ALLOW_ABSOLUTE_PATHS=true to enable absolute pathoperations

Development Configuration

For development with the MCP inspector:

pnpm run build
pnpm run dev

API Reference

Database Management Tools

open_database

Opens or creates a SQLite database file.

Parameters:

• path (string, required): Path to the database file
• create (boolean, optional): Create if doesn't exist (default:true)

Example:

{
	"path": "my-app.db",
	"create": true
}

close_database

Closes a database connection.

Parameters:

• database (string, optional): Database path to close

list_databases

Lists available database files in a directory.

Parameters:

• directory (string, optional): Directory to search

database_info

Gets comprehensive information about a database.

Parameters:

• database (string, optional): Database path

Table Operations

list_tables

Lists all tables and views in a database.

Parameters:

• database (string, optional): Database path

describe_table

Gets schema information for a table.

Parameters:

• table (string, required): Table name
• database (string, optional): Database path
• verbosity (string, optional): 'summary' or 'detailed' (default:'detailed')

Example Request:

{
	"table": "users",
	"verbosity": "detailed"
}

Example Response:

{
	"database": "/tmp/demo.db",
	"table": "users",
	"columns": [
		{
			"name": "id",
			"type": "INTEGER",
			"nullable": true,
			"default_value": null,
			"primary_key": true
		},
		{
			"name": "name",
			"type": "TEXT",
			"nullable": false,
			"default_value": null,
			"primary_key": false
		},
		{
			"name": "email",
			"type": "TEXT",
			"nullable": true,
			"default_value": null,
			"primary_key": false
		},
		{
			"name": "created_at",
			"type": "TIMESTAMP",
			"nullable": true,
			"default_value": "CURRENT_TIMESTAMP",
			"primary_key": false
		}
	],
	"verbosity": "detailed",
	"column_count": 4
}

create_table

Creates a new table with specified columns.

Parameters:

• name (string, required): Table name
• columns (array, required): Column definitions
• database (string, optional): Database path

Column Definition:

{
	"name": "column_name",
	"type": "TEXT|INTEGER|REAL|BLOB",
	"nullable": true,
	"primary_key": false,
	"default_value": null
}

Example:

{
	"name": "users",
	"columns": [
		{
			"name": "id",
			"type": "INTEGER",
			"primary_key": true,
			"nullable": false
		},
		{
			"name": "name",
			"type": "TEXT",
			"nullable": false
		},
		{
			"name": "email",
			"type": "TEXT",
			"nullable": true
		}
	]
}

drop_table

Permanently deletes a table and all its data.

Parameters:

• table (string, required): Table name to delete
• database (string, optional): Database path

Query Operations

execute_read_query

Executes read-only SQL queries (SELECT, PRAGMA, EXPLAIN).

Parameters:

• query (string, required): SQL query
• params (object, optional): Query parameters
• database (string, optional): Database path
• limit (number, optional): Maximum rows to return (default: 10000)
• offset (number, optional): Number of rows to skip (default: 0)
• verbosity (string, optional): 'summary' or 'detailed' (default:'detailed')

Example Request:

{
	"query": "SELECT * FROM users ORDER BY id",
	"verbosity": "detailed"
}

Example Response:

{
	"database": "/tmp/demo.db",
	"query": "SELECT * FROM users ORDER BY id LIMIT 10000",
	"result": {
		"rows": [
			{
				"id": 1,
				"name": "Alice Johnson",
				"email": "[email protected]",
				"created_at": "2025-10-03 09:42:04"
			},
			{
				"id": 3,
				"name": "Carol White",
				"email": "[email protected]",
				"created_at": "2025-10-03 09:42:10"
			}
		],
		"changes": 0,
		"last_insert_rowid": 0
	},
	"row_count": 2,
	"pagination": {
		"limit": 10000,
		"offset": 0,
		"returned_count": 2,
		"has_more": false
	},
	"verbosity": "detailed"
}

execute_write_query

Executes SQL that modifies data (INSERT, UPDATE, DELETE).

Parameters:

• query (string, required): SQL query
• params (object, optional): Query parameters
• database (string, optional): Database path

Example Request:

{
	"query": "INSERT INTO users (name, email) VALUES ('Alice Smith', '[email protected]')"
}

Example Response:

{
	"database": "/tmp/demo.db",
	"query": "INSERT INTO users (name, email) VALUES ('Alice Smith', '[email protected]')",
	"result": {
		"rows": [],
		"changes": 1,
		"last_insert_rowid": 1
	},
	"message": "⚠️ DESTRUCTIVE OPERATION COMPLETED: Data modified in database '/tmp/demo.db'. Rows affected: 1"
}

execute_schema_query

Executes DDL queries (CREATE, ALTER, DROP).

Parameters:

• query (string, required): DDL SQL query
• params (object, optional): Query parameters
• database (string, optional): Database path

Example Request:

{
	"query": "CREATE TABLE users (\n  id INTEGER PRIMARY KEY AUTOINCREMENT,\n  name TEXT NOT NULL,\n  email TEXT UNIQUE,\n  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n)"
}

Example Response:

{
	"database": "/tmp/demo.db",
	"query": "CREATE TABLE users (\n  id INTEGER PRIMARY KEY AUTOINCREMENT,\n  name TEXT NOT NULL,\n  email TEXT UNIQUE,\n  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n)",
	"result": {
		"rows": [],
		"changes": 0,
		"last_insert_rowid": 0
	},
	"message": "⚠️ SCHEMA CHANGE COMPLETED: Database structure modified in '/tmp/demo.db'. Changes: 0"
}

bulk_insert

Insert multiple records in batches.

Parameters:

• table (string, required): Target table name
• data (array, required): Array of objects to insert
• batch_size (number, optional): Records per batch (default: 1000)
• database (string, optional): Database path

Example Request:

{
	"table": "users",
	"data": [
		{ "name": "David Lee", "email": "[email protected]" },
		{ "name": "Emma Davis", "email": "[email protected]" },
		{ "name": "Frank Miller", "email": "[email protected]" }
	]
}

Example Response:

{
	"success": true,
	"database": "/tmp/demo.db",
	"table": "users",
	"inserted": 3,
	"batches": 1,
	"total_time": 0,
	"message": "⚠️ DESTRUCTIVE OPERATION COMPLETED: 3 records inserted into table 'users' in database '/tmp/demo.db'"
}

CSV Operations

import_csv

Import a headered CSV file into a table. If the table does not exist,it is created from CSV headers with inferred SQLite column types.Values are coerced by default (""/null to NULL, numbers tonumbers, booleans to 1/0). Row-level insert errors are reported andsuccessful rows continue unless fail_fast is true.

Parameters:

• table (string, required): Target table name
• file_path (string, required): CSV file path; absolute pathsallowed
• database_name (string, optional): Database path or current contextname
• create_table (boolean, optional): Create missing table (default:true)
• batch_size (number, optional): Rows per batch (default: 1000)
• fail_fast (boolean, optional): Stop on first row error (default:false)
• max_errors (number, optional): Max row errors returned(default: 100)
• coerce_types (boolean, optional): Coerce CSV strings (default:true)
• delimiter, quote, escape, encoding (optional): CSV parsingoptions

export_csv

Export either a full table or a read-only query result to CSV. Provideexactly one of table or query.

Parameters:

• file_path (string, required): Output CSV path; absolute pathsallowed
• table (string, optional): Table to export
• query (string, optional): Read-only query to export
• database_name (string, optional): Database path or current contextname
• delimiter, record_delimiter, encoding (optional): CSV outputoptions
• always_quote (boolean, optional): Quote every field (default:false)
• append (boolean, optional): Append to existing file (default:false)

Transaction Management

begin_transaction

Start a database transaction with optional savepoint support.

Parameters:

• database (string, optional): Database path

Returns: Transaction ID for tracking

commit_transaction

Commit the current transaction or release a savepoint.

Parameters:

• database (string, optional): Database path

rollback_transaction

Rollback the current transaction or revert to a savepoint.

Parameters:

• database (string, optional): Database path

Schema Operations

export_schema

Export database schema to SQL or JSON format.

Parameters:

• database (string, optional): Database path
• format (string, optional): Output format - "sql" or "json"(default: "sql")
• tables (array, optional): Specific tables to export

Example:

{
	"format": "json",
	"tables": ["users", "orders"]
}

import_schema

Import and execute schema from SQL or JSON.

Parameters:

• database (string, optional): Database path
• schema (string, required): Schema content to import
• format (string, optional): Input format - "sql" or "json"(default: "sql")

Database Maintenance

backup_database

Creates a consistent SQLite backup using SQLite's online backup API,including committed data that may still be in WAL files.

Parameters:

• source_database (string, optional): Source database path
• backup_path (string, optional): Backup file path (auto-generatedif not provided)

vacuum_database

Optimizes database storage by reclaiming unused space.

Parameters:

• database (string, optional): Database path

Safety Guidelines

Tool Classification

The server automatically classifies tools into safety categories:

1. ✓ SAFE: Read-only operations (SELECT, PRAGMA, EXPLAIN, databaseinfo, backups)
2. ⚠️ DESTRUCTIVE: Data modification (INSERT, UPDATE, DELETE, bulkinsert, CSV import)
3. ⚠️ SCHEMA CHANGE: Structure modification (CREATE, ALTER, DROP,schema import, CSV table creation)
4. ⚠️ FILE WRITE: Export operations that write files, includingabsolute CSV paths
5. ⚠️ TRANSACTION: Transaction control (BEGIN, COMMIT, ROLLBACK)
6. ✓ MAINTENANCE: Optimization operations (VACUUM, connectionmanagement)

Best Practices

1. Always use parameterized queries to prevent SQL injection
2. Use transactions for multi-step operations to ensure dataconsistency
3. Review destructive operations before execution
4. Create backups before major schema changes
5. Use bulk_insert for inserting large datasets efficiently
6. Review CSV absolute paths before import/export file operations
7. Export schemas before major structural changes
8. Use appropriate tools for different operation types
9. Monitor connection pool usage in high-traffic scenarios

Development

Building

pnpm run build

Development Mode

pnpm run dev

Cleaning

pnpm run clean

Architecture

The server is built with a modular architecture:

Core Modules

• src/index.ts: Main server entry point
• src/config.ts: Configuration management with Valibotvalidation

Database Clients

• src/clients/connection-manager.ts: Advanced connection poolingwith health monitoring
• src/clients/query-executor.ts: SQL execution, bulk operations,and query utilities
• src/clients/transaction-manager.ts: ACID transactionmanagement with savepoints
• src/clients/schema-manager.ts: Schema export/importfunctionality
• src/clients/sqlite.ts: Main SQLite client interface andutilities

Tool Handlers

• src/tools/handler.ts: Tool registration orchestrator
• src/tools/admin-tools.ts: Database and table management tools
• src/tools/query-tools.ts: Query execution and bulk operationtools
• src/tools/transaction-tools.ts: Transaction management tools
• src/tools/schema-tools.ts: Schema export/import tools
• src/tools/csv-tools.ts: CSV import/export tools
• src/tools/context.ts: Database context management

Common Utilities

• src/common/types.ts: TypeScript type definitions
• src/common/errors.ts: Error handling utilities
• src/common/sql.ts: SQL identifier and literal helpers
• src/common/schema-sql.ts: SQLite schema statement parsing

This modular design provides:

• Separation of Concerns: Each module has a single responsibility
• Maintainability: Easy to test, debug, and extend individualcomponents
• Scalability: New features can be added without affectingexisting code
• Type Safety: Comprehensive TypeScript coverage throughout

Dependencies

• tmcp: Modern TypeScriptMCP framework
• better-sqlite3:High-performance SQLite driver
• valibot: Lightweight validation libraryfor type-safe inputs
• csv-parser: CSVimport parsing
• csv-writer: CSV exportwriting

Key Features Provided by Dependencies

• tmcp: Streamlined MCP server development with excellentTypeScript support
• better-sqlite3: Synchronous SQLite operations with superiorperformance
• valibot: Runtime type validation for all tool parameters
• csv-*: Headered CSV import/export with type coercion androw-level import error reporting

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

MIT License - see the LICENSE file for details.

Acknowledgments

• Built on theModel Context Protocol
• Inspired bymcp-turso-cloud
• Uses better-sqlite3for high-performance SQLite operations