#!/usr/bin/env node
const fs = require('fs');
const path = require('path');
const os = require('os');
// PANIC LOGGING: Catch crashes early
const PANIC_LOG_PATH = path.join(os.tmpdir(), 'sqlite-mcp-panic.log');
function logPanic(error, context) {
const msg = `[${new Date().toISOString()}] [PANIC] [${context}] ${error.stack || error}\n`;
try {
fs.appendFileSync(PANIC_LOG_PATH, msg);
} catch (e) {
// Last resort: print to stderr
process.stderr.write('Failed to write panic log: ' + e.message + '\n');
}
process.stderr.write(msg); // Stderr is safe for MCP
}
process.on('uncaughtException', (err) => {
logPanic(err, 'uncaughtException');
process.exit(1);
});
process.on('unhandledRejection', (reason, promise) => {
logPanic(reason, 'unhandledRejection');
});
require('dotenv').config();
const sqlite3 = require('sqlite3').verbose();
const { Server } = require('@modelcontextprotocol/sdk/server/index.js');
const { StdioServerTransport } = require('@modelcontextprotocol/sdk/server/stdio.js');
const {
CallToolRequestSchema,
ListToolsRequestSchema,
} = require('@modelcontextprotocol/sdk/types.js');
const LOG_DIR = path.join(os.tmpdir(), 'sqlite-mcp-logs');
// Ensure log directory exists
try {
if (!fs.existsSync(LOG_DIR)) {
fs.mkdirSync(LOG_DIR);
}
} catch (err) {
console.error(`Failed to create log directory at ${LOG_DIR}: ${err.message}`);
// Continue without file logging or exit? Better to continue but maybe warn.
}
// Function to log messages to a daily file
function logMessage(message, type = 'info') {
const now = new Date();
const date = now.toISOString().split('T')[0]; // YYYY-MM-DD
const logFileName = `${date}.log`;
const logFilePath = path.join(LOG_DIR, logFileName);
const timestamp = now.toISOString();
const logEntry = `[${timestamp}] [${type.toUpperCase()}] ${message}\n`;
fs.appendFile(logFilePath, logEntry, (err) => {
// Fail silently
});
}
// Initialize Database State
let db = null;
let currentDbPath = null;
function connectToDatabase(dbPath) {
return new Promise((resolve, reject) => {
// Close existing connection if any
if (db) {
db.close((err) => {
if (err) logMessage(`Error closing previous database: ${err.message}`, 'error');
});
}
const newDb = new sqlite3.Database(dbPath, (err) => {
if (err) {
logMessage(`Error opening database ${dbPath}: ${err.message}`, 'error');
reject(err);
} else {
db = newDb;
currentDbPath = dbPath;
logMessage(`Connected to SQLite database: ${dbPath}`, 'info');
resolve();
}
});
});
}
// Initial connection if ENV is provided (backward compatibility)
if (process.env.SQLITE_DB_PATH) {
connectToDatabase(process.env.SQLITE_DB_PATH).catch(err => {
console.error(`Failed to connect to initial DB: ${err.message}`);
});
} else {
// Optional: Connect to default if desired, or leave null to require explicit connection
// connectToDatabase(path.join(__dirname, 'database.sqlite'));
}
// Helper for SELECT queries
function dbQuery(sql, params = []) {
return new Promise((resolve, reject) => {
if (!db) return reject(new Error('No database connected. You MUST call the "connect_database" tool first to connect to or create a database file.'));
db.all(sql, params, (err, rows) => {
if (err) reject(err);
else resolve(rows);
});
});
}
// Helper for INSERT, UPDATE, DELETE, CREATE, DROP
function dbRun(sql, params = []) {
return new Promise((resolve, reject) => {
if (!db) return reject(new Error('No database connected. You MUST call the "connect_database" tool first to connect to or create a database file.'));
db.run(sql, params, function (err) {
if (err) reject(err);
else resolve({ lastID: this.lastID, changes: this.changes });
});
});
}
// Initialize MCP Server
const server = new Server(
{
name: 'sqlite-mcp',
version: '1.0.0',
},
{
capabilities: {
tools: {},
},
}
);
// List Tools Handler
server.setRequestHandler(ListToolsRequestSchema, async () => {
return {
tools: [
{
name: 'connect_database',
description: 'Connect to a specific SQLite database file. Creates the file if it does not exist.',
inputSchema: {
type: 'object',
properties: {
db_path: {
type: 'string',
description: 'The ABSOLUTE path to the SQLite database file (e.g., /Users/user/project/db.sqlite). Relative paths will be created in the MCP server directory, which is likely NOT what you want.',
},
},
required: ['db_path'],
},
},
{
name: 'read_query',
description: 'Execute a SELECT query on the SQLite database',
inputSchema: {
type: 'object',
properties: {
query: {
type: 'string',
description: 'The SQL SELECT query to execute',
},
},
required: ['query'],
},
},
{
name: 'write_query',
description: 'Execute an INSERT, UPDATE, DELETE, or CREATE query',
inputSchema: {
type: 'object',
properties: {
query: {
type: 'string',
description: 'The SQL query to execute',
},
},
required: ['query'],
},
},
{
name: 'list_tables',
description: 'List all tables in the database',
inputSchema: {
type: 'object',
properties: {},
},
},
{
name: 'describe_table',
description: 'Get the schema information for a specific table',
inputSchema: {
type: 'object',
properties: {
table_name: {
type: 'string',
description: 'The name of the table to describe',
},
},
required: ['table_name'],
},
},
{
name: 'get_schema_ddl',
description: 'Returns the full DDL (CREATE TABLE statements) of the database to understand the complete schema structure.',
inputSchema: {
type: 'object',
properties: {},
},
},
{
name: 'get_custom_instructions',
description: 'Returns custom guidelines, best practices, and example queries defined in instructions.md.',
inputSchema: {
type: 'object',
properties: {},
},
},
],
};
});
// Call Tool Handler
server.setRequestHandler(CallToolRequestSchema, async (request) => {
const { name, arguments: args } = request.params;
try {
if (name === 'connect_database') {
const { db_path } = args;
logMessage(`Executing connect_database: ${db_path}`, 'info');
await connectToDatabase(db_path);
// Auto-inject instructions
const instructionsPath = path.join(__dirname, 'instructions.md');
let message = `Successfully connected to database at ${db_path}`;
if (fs.existsSync(instructionsPath)) {
const instructions = fs.readFileSync(instructionsPath, 'utf8');
message += `\n\n--- AUTOMATIC INSTRUCTIONS ---\n${instructions}`;
}
return {
content: [
{
type: 'text',
text: message,
},
],
};
}
if (name === 'read_query') {
const { query } = args;
logMessage(`Executing read_query: ${query}`, 'info');
// Basic safety check (very primitive, ideally use a read-only connection or strict parsing)
if (!query.trim().toLowerCase().startsWith('select') && !query.trim().toLowerCase().startsWith('pragma')) {
throw new Error('read_query only supports SELECT or PRAGMA statements.');
}
const rows = await dbQuery(query);
return {
content: [
{
type: 'text',
text: JSON.stringify(rows, null, 2),
},
],
};
}
if (name === 'write_query') {
const { query } = args;
logMessage(`Executing write_query: ${query}`, 'info');
const result = await dbRun(query);
let responseText = JSON.stringify(result, null, 2);
// Active Reinforcement: Check if a table was created and remind about documentation
if (query.toLowerCase().includes('create table')) {
responseText += `\n\n⚠️ ARCHITECTURE CHECK: You just created a table. If you haven't already, you MUST now insert a row into '_architecture_notes'.\nYour note MUST explain:\n1. The table's Purpose.\n2. Key columns (especially status/state flags).\n3. Relationships to other data.\n\nExample: INSERT INTO _architecture_notes (note) VALUES ('Table "queue" stores pending jobs. "status" column tracks progress. Linked to "users" via user_id.');`;
}
return {
content: [
{
type: 'text',
text: responseText,
},
],
};
}
if (name === 'list_tables') {
logMessage('Executing list_tables', 'info');
const query = "SELECT name FROM sqlite_master WHERE type='table'";
const rows = await dbQuery(query);
return {
content: [
{
type: 'text',
text: JSON.stringify(rows, null, 2),
},
],
};
}
if (name === 'describe_table') {
const { table_name } = args;
logMessage(`Executing describe_table: ${table_name}`, 'info');
const query = `PRAGMA table_info(${table_name})`;
const rows = await dbQuery(query);
return {
content: [
{
type: 'text',
text: JSON.stringify(rows, null, 2),
},
],
};
}
if (name === 'get_schema_ddl') {
logMessage('Executing get_schema_ddl', 'info');
const query = "SELECT sql FROM sqlite_master WHERE type='table' AND sql IS NOT NULL";
const rows = await dbQuery(query);
// Extract the SQL statements and join them
const ddl = rows.map(row => row.sql).join('\n\n');
return {
content: [
{
type: 'text',
text: ddl || 'No tables found.',
},
],
};
}
if (name === 'get_custom_instructions') {
logMessage('Executing get_custom_instructions', 'info');
const instructionsPath = path.join(__dirname, 'instructions.md');
let instructionsContent = 'No instructions.md file found.';
if (fs.existsSync(instructionsPath)) {
instructionsContent = fs.readFileSync(instructionsPath, 'utf8');
}
return {
content: [
{
type: 'text',
text: instructionsContent,
},
],
};
}
throw new Error(`Tool not found: ${name}`);
} catch (error) {
logMessage(`Error executing ${name}: ${error.message}`, 'error');
return {
content: [
{
type: 'text',
text: `Error: ${error.message}`,
},
],
isError: true,
};
}
});
// Connect transport
async function run() {
try {
const transport = new StdioServerTransport();
await server.connect(transport);
logMessage('SQLite MCP Server running on stdio', 'info');
} catch (error) {
logMessage(`Failed to start MCP server: ${error.message}`, 'error');
console.error(`Fatal error starting server: ${error.message}`);
process.exit(1);
}
}
run().catch((error) => {
logMessage(`Fatal error: ${error}`, 'error');
process.exit(1);
});
