Optimizing SQLite for MCP Servers: WAL Mode, Singleton Connections, and Index Design

When implementing an MCP server with TypeScript, SQLite is often the preferred choice for a data store. It is lightweight, requires zero dependencies, and provides sufficient performance for production environments. However, if used with default settings, you will experience dramatically worse latency when an agent calls tools in parallel.

Here are three optimizations I am actually applying in KanseiLink (156 services, 21 tools, 5 tables).

1. WAL Mode is Essential

// src/db/connection.ts
db = new Database(resolvedPath);
db.pragma("journal_mode = WAL");
db.pragma("foreign_keys = ON");

SQLite uses the DELETE journal mode by default. This makes reading and writing completely exclusive, meaning that if search_services (read) and report_outcome (write) are called at the same time, one of them will be blocked.

By switching to WAL mode:

• Reads do not block writes (Writer-Reader parallelism).
• Reads after a commit always refer to the latest view.
• For MCP purposes, latency can be improved by 50-70% in actual measurements.

2. Maintain a Singleton DB Connection

let db: Database.Database | null = null;

export function getDb(dbPath?: string): Database.Database {
  if (db) return db;

  db = new Database(resolvedPath);
  db.pragma("journal_mode = WAL");
  return db;
}

export function closeDb(): void {
  if (db) {
    db.close();
    db = null;
  }
}

MCP servers run in a single Node.js process. If you call new Database() for every tool invocation:

• Multiple file handles are opened.
• You lose the benefits of WAL (as each connection is treated separately).
• Memory usage increases linearly.

By using a singleton, you only incur the connection cost once at startup. Use the closeDb() function in SIGINT/SIGTERM handlers to ensure a clean shutdown.

// src/index.ts
process.on("SIGINT", () => { closeDb(); process.exit(0); });
process.on("SIGTERM", () => { closeDb(); process.exit(0); });

3. Create Indexes in Bulk at Startup using IF NOT EXISTS

// src/db/schema.ts
CREATE INDEX IF NOT EXISTS idx_services_category ON services(category);
CREATE INDEX IF NOT EXISTS idx_outcomes_service ON outcomes(service_id);
CREATE INDEX IF NOT EXISTS idx_outcomes_created ON outcomes(created_at);
CREATE INDEX IF NOT EXISTS idx_inspections_status ON inspections(status);

Create all indexes at startup in initializeDb(). Since IF NOT EXISTS is used, it skips existing indexes and is safe to call repeatedly.

Index design strategy:

• Category column: Almost all queries include a category filter.
• Foreign keys: Reduces JOIN full scans to O(log n).
• created_at: Essential for time-series aggregation (e.g., the last 7 days).

Bonus: Leverage better-sqlite3 Synchronous API

better-sqlite3 uses a synchronous API. Because it avoids the overhead of asynchronous loops, simple queries are surprisingly fast.

// NG: Meaningless await
const rows = await db.prepare("SELECT ...").all();

// OK: Use synchronously (limited to short-duration queries that do not block the event loop)
const rows = db.prepare("SELECT ...").all() as ServiceRow[];

KanseiLink's search_services is a complex query involving FTS5 JOIN + category boosting + ranking, but by combining WAL + indexes, it achieves an average of 3–8ms.

Summary

Optimization	Effect
WAL Mode	Read/Write parallelism, 50-70% latency improvement
Singleton Connection	Saves file handles, enables WAL benefits
Startup Indexes	O(log n) queries, eliminates full scans
Synchronous API usage	Zero event loop overhead

SQLite is powerful enough as an MCP server backend if used correctly. Start with the single line journal_mode = WAL.