SQLite Setup Guide

This guide provides instructions for setting up and using the SQLite adapter with the MCP Database Server.

Prerequisites

1. No additional installations required - SQLite is included with the MCP Database Server
2. Node.js 18 or later
3. A valid SQLite database file or a path to create a new one

Running the Server with SQLite

To connect to an SQLite database, use the following command:

node dist/src/index.js /path/to/your/database.db

If the database file doesn't exist, it will be created automatically.

Configuring Claude Desktop

Update your Claude configuration file to add SQLite support:

{
  "mcpServers": {
    "sqlite": {
      "command": "npx",
      "args": [
        "-y",
        "@executeautomation/database-server",
        "/path/to/your/database.db"
      ]
    }
  }
}

For local development:

{
  "mcpServers": {
    "sqlite": {
      "command": "node",
      "args": [
        "/absolute/path/to/mcp-database-server/dist/src/index.js", 
        "/path/to/your/database.db"
      ]
    }
  }
}

SQLite-Specific Features

In-Memory Databases

For temporary in-memory databases, use the special :memory: path:

node dist/src/index.js :memory:

Write-Ahead Logging (WAL)

By default, the server enables Write-Ahead Logging mode for better concurrency and performance.

Data Types

SQLite uses dynamic typing with the following storage classes:

• NULL
• INTEGER
• REAL
• TEXT
• BLOB

SQL Syntax Examples

-- Creating a table in SQLite
CREATE TABLE Products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  price REAL,
  category TEXT
);

-- Querying with LIMIT and OFFSET
SELECT * FROM Products LIMIT 10 OFFSET 20;

-- Date formatting
SELECT date('now') as today;
SELECT strftime('%Y-%m-%d', date_column) as formatted_date FROM Orders;

-- String concatenation
SELECT first_name || ' ' || last_name as full_name FROM Customers;

Troubleshooting

Database Locked

If you encounter "database is locked" errors:

1. Ensure no other connections are using the database file
2. Check file permissions
3. Wait a moment and retry the operation

File Access Issues

If you cannot access the database file:

1. Verify the file path is correct
2. Check that the directory exists and is writable
3. Ensure the Node.js process has appropriate permissions

Performance Tips

1. Use indexes for frequently queried columns
2. Keep transactions short
3. Use parameterized queries for better performance and security
4. Consider periodic VACUUM operations to reclaim space