SQLite MCP Server

A comprehensive Model Context Protocol (MCP) server implementation for SQLite databases, providing secure and controlled access to SQLite operations through a standardized interface.

🚀 Features

Core Functionality

MCP Protocol Compliance: Full implementation of the Model Context Protocol for seamless integration with MCP clients
SQLite Integration: Native SQLite support using better-sqlite3 for optimal performance
Granular Permissions: Fine-grained permission system with 9 distinct permission types
Security First: Comprehensive SQL injection protection and query validation
Schema Introspection: Complete database schema analysis and reporting
Connection Pooling: Efficient database connection management
Audit Logging: Detailed operation logging for security and compliance

Permission System

The server implements a granular permission system with the following types:

list - List tables and schemas
read - SELECT queries and data retrieval
create - INSERT operations
update - UPDATE operations
delete - DELETE operations
execute - Execute stored procedures/functions
ddl - Data Definition Language (CREATE, ALTER, DROP)
transaction - Transaction control (BEGIN, COMMIT, ROLLBACK)
utility - Utility operations (VACUUM, ANALYZE, PRAGMA, etc.)

Security Features

SQL Injection Prevention: Parameterized queries and pattern detection
Query Validation: Comprehensive query analysis and sanitization
Permission Enforcement: Operation-level permission checking
Rate Limiting: Configurable request rate limiting
Audit Trail: Complete operation logging with client tracking
Input Sanitization: Parameter validation and sanitization

Auto-Creation Features

Database Auto-Creation: Automatically creates database files if they don't exist
Directory Auto-Creation: Creates parent directories recursively as needed
Intelligent Initialization: Detects new vs existing databases and logs appropriately
Zero-Configuration Setup: Works out-of-the-box with any valid SQLite path

📦 Installation

NPX Usage (Recommended)

npx @berthojoris/mcp-sqlite-server sqlite:////path/to/your/database.sqlite list,read,utility

Global Installation

npm install -g @berthojoris/mcp-sqlite-server
mcp-sqlite-server sqlite:////path/to/your/database.sqlite list,read,create,update

Local Installation

npm install @berthojoris/mcp-sqlite-server

🔧 Configuration

MCP Client Configuration

Add to your MCP client configuration:

{
  "mcpServers": {
    "sqlite": {
      "command": "npx",
      "args": [
        "-y",
        "@berthojoris/mcp-sqlite-server",
        "sqlite:////absolute/path/to/your/database.sqlite",
        "list,read,create,update,delete,utility"
      ]
    }
  }
}

Connection String Formats

The server supports multiple SQLite connection string formats:

# Absolute path
sqlite:////absolute/path/to/database.sqlite

# Relative path  
sqlite://./relative/path/to/database.sqlite

# In-memory database
sqlite://:memory:

# Direct file path
/path/to/database.sqlite

Permission Combinations

Detailed Permission Descriptions

Permission	Description	Allowed Operations	Example Use Cases
`list`	View database structure and metadata	• List all tables • View table schemas • Check column information • View indexes and constraints • Access database metadata	• Database exploration • Schema documentation • Development planning • Data modeling
`read`	Execute SELECT queries and retrieve data	• SELECT statements • JOIN operations • Aggregate functions (COUNT, SUM, etc.) • Subqueries • View data content	• Data analysis • Reporting • Business intelligence • Read-only applications
`create`	Insert new records into tables	• INSERT statements • Bulk insert operations • Add new rows • Populate tables with data	• Data entry applications • ETL processes • User registration • Content creation
`update`	Modify existing records	• UPDATE statements • Bulk update operations • Modify existing data • Change field values	• Profile updates • Status changes • Data corrections • Content editing
`delete`	Remove records from tables	• DELETE statements • Bulk delete operations • Remove rows • Data cleanup	• User account deletion • Data archiving • Content removal • Cleanup operations
`execute`	Run stored procedures and functions	• Execute stored procedures • Call database functions • Run custom database logic • Execute complex operations	• Business logic execution • Complex calculations • Batch processing • Custom workflows
`ddl`	Modify database structure	• CREATE TABLE/INDEX • ALTER TABLE structure • DROP tables/indexes • Modify schema • Create/drop views	• Database migrations • Schema updates • Development setup • Structure modifications
`transaction`	Control transaction boundaries	• BEGIN transactions • COMMIT changes • ROLLBACK operations • Manage data consistency • Atomic operations	• Financial operations • Data integrity • Batch processing • Critical updates
`utility`	Perform maintenance and optimization	• VACUUM database • ANALYZE statistics • PRAGMA commands • Database backup • Performance optimization	• Database maintenance • Performance tuning • Backup operations • System administration

Common permission combinations for different use cases:

# Read-only access
list,read

# Basic CRUD operations
list,read,create,update,delete

# Full database access
list,read,create,update,delete,execute,ddl,transaction,utility

# Analytics/reporting
list,read,utility

# Development/testing
list,read,create,update,delete,ddl,transaction,utility

🛠️ CLI Usage

Start MCP Server

# Basic usage
mcp-sqlite-server sqlite:////path/to/db.sqlite list,read,utility

# With additional options
mcp-sqlite-server sqlite:////path/to/db.sqlite list,read,create,update \
  --read-only \
  --log-level debug \
  --max-connections 5

Schema Information

# View complete schema
mcp-sqlite-server schema sqlite:////path/to/db.sqlite

# View specific table
mcp-sqlite-server schema sqlite:////path/to/db.sqlite --table users

# JSON output
mcp-sqlite-server schema sqlite:////path/to/db.sqlite --format json

Database Backup

mcp-sqlite-server backup sqlite:////path/to/source.sqlite /path/to/backup.sqlite

Generate Configuration

# Basic configuration
mcp-sqlite-server config --output mcp-config.json

# Template with multiple servers
mcp-sqlite-server config --template --output mcp-template.json

🔌 Available Tools

The MCP server provides the following tools:

`sqlite_query`

Execute SELECT queries with full result sets.

Parameters:

query (string): SQL SELECT statement
parameters (array, optional): Query parameters for prepared statements

Required Permissions: read

Example:

SELECT * FROM users WHERE age > ? AND city = ?
Parameters: [25, "New York"]

`sqlite_insert`

Insert new records into tables.

Parameters:

query (string): SQL INSERT statement
parameters (array, optional): Values to insert

Required Permissions: create

Example:

INSERT INTO users (name, email, age) VALUES (?, ?, ?)
Parameters: ["John Doe", "[email protected]", 30]

`sqlite_update`

Update existing records.

Parameters:

query (string): SQL UPDATE statement
parameters (array, optional): Update values

Required Permissions: update

Example:

UPDATE users SET email = ? WHERE id = ?
Parameters: ["[email protected]", 123]

`sqlite_delete`

Delete records from tables.

Parameters:

query (string): SQL DELETE statement
parameters (array, optional): Condition parameters

Required Permissions: delete

Example:

DELETE FROM users WHERE last_login < ?
Parameters: ["2023-01-01"]

`sqlite_schema`

Get comprehensive database schema information.

Parameters:

table (string, optional): Specific table name

Required Permissions: list

Returns: Complete schema information including tables, columns, indexes, views, triggers, and foreign keys.

`sqlite_tables`

List all tables in the database.

Required Permissions: list

Returns: Array of table names with basic metadata.

`sqlite_transaction`

Execute multiple queries within a transaction.

Parameters:

queries (array): Array of query objects with query and optional parameters

Required Permissions: transaction + permissions for individual operations

Example:

{
  "queries": [
    {
      "query": "INSERT INTO accounts (name, balance) VALUES (?, ?)",
      "parameters": ["Alice", 1000]
    },
    {
      "query": "INSERT INTO accounts (name, balance) VALUES (?, ?)", 
      "parameters": ["Bob", 500]
    }
  ]
}

`sqlite_backup`

Create a backup of the database.

Parameters:

backupPath (string): Path for the backup file

Required Permissions: utility

`sqlite_bulk_insert`

Perform bulk insert operations with relational data support and progress tracking.

Parameters:

mainTable (string): Main table name to insert into
records (array): Array of records to insert
relatedData (object, optional): Related table data with foreign key mappings
options (object, optional): Bulk insert options
- batchSize (number): Batch size for processing (default: 1000)
- continueOnError (boolean): Continue processing on errors (default: false)
- validateForeignKeys (boolean): Validate foreign key constraints (default: true)
- insertRelatedData (boolean): Insert related table data first (default: true)

Required Permissions: create

Example:

{
  "mainTable": "posts",
  "records": [
    {"title": "Post 1", "content": "Content 1", "user_id": 1},
    {"title": "Post 2", "content": "Content 2", "user_id": 2}
  ],
  "relatedData": {
    "users": [
      {"id": 1, "name": "John", "email": "[email protected]"},
      {"id": 2, "name": "Jane", "email": "[email protected]"}
    ]
  },
  "options": {
    "batchSize": 500,
    "continueOnError": true,
    "validateForeignKeys": true
  }
}

`sqlite_bulk_update`

Perform bulk update operations with progress tracking.

Parameters:

table (string): Table name to update
updates (array): Array of update operations with data and where conditions
options (object, optional): Bulk update options
- batchSize (number): Batch size for processing (default: 1000)
- continueOnError (boolean): Continue processing on errors (default: false)
- validateForeignKeys (boolean): Validate foreign key constraints (default: true)

Required Permissions: update

Example:

{
  "table": "users",
  "updates": [
    {
      "data": {"name": "John Updated", "email": "[email protected]"},
      "where": {"id": 1}
    },
    {
      "data": {"status": "active"},
      "where": {"last_login": {"$gt": "2024-01-01"}}
    }
  ],
  "options": {
    "batchSize": 100,
    "continueOnError": false
  }
}

`sqlite_bulk_delete`

Perform bulk delete operations with cascading support and progress tracking.

Parameters:

table (string): Table name to delete from
conditions (array): Array of WHERE conditions for deletion
options (object, optional): Bulk delete options
- batchSize (number): Batch size for processing (default: 1000)
- continueOnError (boolean): Continue processing on errors (default: false)
- cascadeDelete (boolean): Enable cascade delete for related records (default: true)

Required Permissions: delete

Example:

{
  "table": "posts",
  "conditions": [
    {"created_at": {"$lt": "2023-01-01"}},
    {"status": "deleted"},
    {"user_id": {"$in": [1, 2, 3]}}
  ],
  "options": {
    "batchSize": 50,
    "cascadeDelete": true
  }
}

🔒 Security Guidelines

Best Practices

Principle of Least Privilege: Only grant necessary permissions
Use Parameterized Queries: Always use parameters for dynamic values
Regular Backups: Implement automated backup strategies
Monitor Audit Logs: Review operation logs regularly
Connection Limits: Set appropriate connection pool limits
Read-Only When Possible: Use read-only mode for reporting/analytics

Security Features

SQL Injection Protection: Automatic detection of dangerous patterns
Query Validation: Comprehensive query analysis before execution
Permission Enforcement: Operation-level access control
Rate Limiting: Configurable request throttling
Audit Logging: Complete operation tracking
Input Sanitization: Parameter validation and cleaning

Dangerous Operations

The server automatically blocks or restricts:

Multiple statement execution
Dangerous SQL patterns (UNION-based injections, etc.)
Unauthorized schema modifications
Excessive query complexity
Operations without proper permissions

🗺️ Roadmap

✅ Version 1.0.0 - Core Foundation (Completed)

✅ MCP Protocol Implementation: Full Model Context Protocol compliance
✅ SQLite Integration: Native SQLite support with better-sqlite3
✅ Permission System: Granular 9-tier permission system
✅ Security Features: SQL injection protection and query validation
✅ CLI Interface: Command-line interface with comprehensive options
✅ Connection Pooling: Efficient database connection management
✅ Audit Logging: Detailed operation logging for compliance
✅ Bulk Operations: Enhanced bulk insert/update/delete with progress tracking

✅ Version 1.0.1 - Auto-Creation & Documentation (Completed)

✅ Database Auto-Creation: Automatic database file creation
✅ Directory Auto-Creation: Recursive parent directory creation
✅ Enhanced Logging: Improved initialization and status logging
✅ Roadmap Documentation: Comprehensive development roadmap
✅ Cross-Platform Path Handling: Windows and Unix path resolution

🚧 Version 1.1.0 - Enhanced Query & Performance (Q1 2025)

Advanced Query Builder: Visual query builder with drag-and-drop interface
Query Optimization: Automatic query analysis and optimization suggestions
Performance Metrics: Real-time query performance monitoring and analytics
Connection Health Monitoring: Advanced connection pool health checks
Memory Usage Optimization: Improved memory management for large datasets

📋 Version 1.2.0 - Data Management & Migration (Q2 2025)

Database Migration Tools: Schema versioning and migration management
Data Import/Export: Support for CSV, JSON, XML data import/export
Data Validation: Custom validation rules and constraints
Backup Scheduling: Automated backup scheduling with retention policies

🔒 Version 1.3.0 - Advanced Security & Compliance (Q2 2025)

Role-Based Access Control (RBAC): Advanced user roles and permissions
Data Encryption: At-rest and in-transit data encryption
Compliance Reporting: GDPR, HIPAA, SOX compliance reporting tools
Advanced Audit Logging: Enhanced audit trails with data lineage tracking
Security Scanning: Automated security vulnerability scanning

🔌 Version 1.4.0 - Integration & Extensibility (Q3 2025)

Plugin System: Extensible plugin architecture for custom functionality
REST API Gateway: RESTful API layer for non-MCP clients
GraphQL Support: GraphQL query interface for flexible data access
Webhook Integration: Event-driven webhooks for database changes
Third-party Integrations: Slack, Discord, Teams notifications

🤝 Contributing

Fork the repository
Create a feature branch
Make your changes
Add tests for new functionality
Ensure all tests pass
Submit a pull request

📄 License

MIT License - see LICENSE file for details.

🆘 Support

For issues, questions, or contributions:

GitHub Issues: Repository Issues
Documentation: Full Documentation

🔄 Version History

v1.1.1

Bulk Operations: Advanced bulk insert, update, and delete operations with progress tracking
Relational Data Support: Bulk insert with foreign key mappings and related table data
Progress Tracking: Real-time progress monitoring for bulk operations with error handling
Performance Optimization: Batch processing with configurable batch sizes for large datasets
Enhanced Error Handling: Improved error reporting and continue-on-error options for bulk operations

v1.0.1

Auto-Creation Features: Database and directory auto-creation functionality
Enhanced Logging: Improved initialization logging with database status
Roadmap Documentation: Comprehensive roadmap for future development
Bug Fixes: Minor improvements and stability enhancements

v1.0.0

Initial release
Full MCP protocol implementation
Comprehensive permission system
Security features and audit logging
CLI interface and schema introspection
Connection pooling and performance optimization

Note: This server is designed for secure, controlled access to SQLite databases through the Model Context Protocol. Always follow security best practices and regularly review audit logs in production environments.