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

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

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", "john@example.com", 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: ["newemail@example.com", 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": "john@example.com"},
      {"id": 2, "name": "Jane", "email": "jane@example.com"}
    ]
  },
  "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": "john.new@example.com"},
      "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

1. Principle of Least Privilege: Only grant necessary permissions
2. Use Parameterized Queries: Always use parameters for dynamic values
3. Regular Backups: Implement automated backup strategies
4. Monitor Audit Logs: Review operation logs regularly
5. Connection Limits: Set appropriate connection pool limits
6. 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

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests for new functionality
5. Ensure all tests pass
6. 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.