MCP server SQLite

Library choice: better-sqlite3 vs. node:sqlite vs. node-sqlite3

For most MCP servers, better-sqlite3 is the right choice. SQLite queries on a local file complete in microseconds to low milliseconds — not long enough to block the event loop meaningfully. The synchronous API avoids async/await boilerplate in every tool handler. node-sqlite3 is worth considering only when individual queries take tens of milliseconds (large analytical scans), because offloading to a thread pool worker keeps the event loop responsive to new SSE connections.

npm install better-sqlite3
npm install --save-dev @types/better-sqlite3

Opening the database with WAL mode

// src/db.ts
import Database from 'better-sqlite3';
import path from 'path';

const DB_PATH = process.env.DB_PATH ?? path.join(process.cwd(), 'data', 'mcp.db');

export function openDatabase(): Database.Database {
  const db = new Database(DB_PATH);

  // WAL mode: readers do not block the writer; the writer does not block readers.
  // Essential for concurrent SSE sessions — without WAL, tool calls queue behind
  // each other waiting for the exclusive lock on the default DELETE journal mode.
  db.pragma('journal_mode = WAL');

  // Busy timeout: if a write lock is held by another process (e.g., a migration
  // script running alongside the server), wait up to 5s before throwing SQLITE_BUSY.
  db.pragma('busy_timeout = 5000');

  // Foreign key enforcement is off by default in SQLite — turn it on.
  db.pragma('foreign_keys = ON');

  // Synchronous=NORMAL with WAL: safe against OS crash; trades full fsync on
  // every WAL frame for a 2–5x write speedup. Power-loss still durable with WAL.
  db.pragma('synchronous = NORMAL');

  return db;
}

export function initSchema(db: Database.Database): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS tool_cache (
      key        TEXT PRIMARY KEY,
      value      TEXT NOT NULL,
      expires_at INTEGER NOT NULL
    );

    CREATE INDEX IF NOT EXISTS idx_tool_cache_expires
      ON tool_cache (expires_at);

    CREATE TABLE IF NOT EXISTS session_log (
      id         INTEGER PRIMARY KEY AUTOINCREMENT,
      session_id TEXT NOT NULL,
      tool_name  TEXT NOT NULL,
      duration_ms INTEGER NOT NULL,
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );

    CREATE INDEX IF NOT EXISTS idx_session_log_session
      ON session_log (session_id);
  `);
}

The DB_PATH environment variable lets you point to a persistent volume path on Fly.io or a host-mounted Docker volume without changing application code. The data directory must exist before opening the database — create it in your startup script or Dockerfile.

Prepared statements in tool handlers

Prepare statements once at startup, not inside the tool handler function. Preparing inside the handler re-parses and re-compiles the SQL on every tool call — typically 5–20µs overhead per call that accumulates across thousands of calls per session.

// src/statements.ts
import type Database from 'better-sqlite3';

export interface PreparedStatements {
  getCached: Database.Statement;
  setCached: Database.Statement;
  expireCached: Database.Statement;
  logToolCall: Database.Statement;
}

export function prepareStatements(db: Database.Database): PreparedStatements {
  return {
    getCached: db.prepare(
      'SELECT value FROM tool_cache WHERE key = ? AND expires_at > unixepoch()'
    ),
    setCached: db.prepare(`
      INSERT INTO tool_cache (key, value, expires_at)
      VALUES (?, ?, unixepoch() + ?)
      ON CONFLICT (key) DO UPDATE SET value = excluded.value, expires_at = excluded.expires_at
    `),
    expireCached: db.prepare(
      'DELETE FROM tool_cache WHERE expires_at <= unixepoch()'
    ),
    logToolCall: db.prepare(
      'INSERT INTO session_log (session_id, tool_name, duration_ms) VALUES (?, ?, ?)'
    ),
  };
}

// In a tool handler — no SQL strings, no injection risk, no re-compilation:
export function getCachedResult(
  stmts: PreparedStatements,
  key: string
): string | null {
  const row = stmts.getCached.get(key) as { value: string } | undefined;
  return row?.value ?? null;
}

Never concatenate user-controlled strings into SQL. Prepared statement parameters (? positional or :name named) handle escaping automatically. Tool inputs arrive from LLM agents — treat them with the same distrust as direct user input.

Transactions for multi-step tool operations

better-sqlite3 exposes a db.transaction(fn) helper that wraps a function in BEGIN / COMMIT / ROLLBACK. The function is called synchronously and retried automatically on SQLITE_BUSY when combined with the busy timeout pragma. Use transactions any time a tool call modifies more than one row — partial writes leave the database in an inconsistent state if the server crashes mid-operation.

// Atomic multi-step write — either both rows are written or neither
const recordToolResult = db.transaction((
  sessionId: string,
  toolName: string,
  cacheKey: string,
  result: string,
  durationMs: number,
  cacheTtlSeconds: number
) => {
  stmts.setCached.run(cacheKey, result, cacheTtlSeconds);
  stmts.logToolCall.run(sessionId, toolName, durationMs);
});

// Call it like a normal function — automatically wrapped in a transaction
recordToolResult(session.id, 'fetch_user', cacheKey, JSON.stringify(data), elapsed, 300);

Graceful shutdown — connection ordering

The database connection must close after all tool handlers have returned. The correct shutdown sequence mirrors the MCP server graceful shutdown state machine with an extra step for the database:

1. Stop accepting new connections — httpServer.close()
2. Signal draining state — /health returns 503 so load balancer removes the instance
3. Wait for active sessions to close — poll activeSessions.size === 0 with a timeout
4. Close the database — db.close() after sessions drain
5. Exit — process.exit(0)

process.on('SIGTERM', async () => {
  serverState = 'draining';

  // Step 1+2: stop HTTP server (new connections refused, /health returns 503)
  httpServer.close();

  // Step 3: wait for active MCP sessions to finish their current tool call
  const drainStart = Date.now();
  while (activeSessions.size > 0 && Date.now() - drainStart < DRAIN_TIMEOUT_MS) {
    await new Promise(resolve => setTimeout(resolve, 100));
  }

  // Step 4: close database only after sessions are gone
  // Closing earlier produces SQLITE_INTERRUPT on any in-flight queries
  db.close();

  process.exit(0);
});

File placement and persistent volumes

SQLite databases are single files. On a VPS with systemd, the data directory lives at a path outside the application directory so it survives application redeployments that rsync dist/. On Fly.io, the database must be on a Fly volume — the ephemeral machine filesystem is discarded on machine restart. On Docker, bind-mount a host directory rather than using a named volume if you need easy backup access.

# fly.toml — persistent volume for SQLite
[mounts]
  source = "mcp_data"
  destination = "/data"

# Environment variable pointing the application to the volume path
[env]
  DB_PATH = "/data/mcp.db"

The WAL mode creates two auxiliary files alongside the main database (mcp.db-wal and mcp.db-shm). Both must be included in backups — a backup of mcp.db alone without the WAL file may be in a partial-commit state. Use VACUUM INTO '/path/to/backup.db' to create a consistent single-file snapshot without stopping the server.

AliveMCP and SQLite health

AliveMCP probes your MCP server's initialize and tools/list endpoints every 60 seconds. A common failure mode is a SQLite lock on startup: if a previous process crashed while holding a WAL write lock, the new process may fail to open the database. This presents to AliveMCP as a failed initialize response — the server process starts and binds the port, but the MCP handshake fails because the startup database check throws. The probe catches this within 60 seconds; a process monitor like PM2 or systemd only sees the port is bound and reports healthy. See MCP server health check for the /health endpoint pattern that AliveMCP uses.

Related questions

Further reading

• MCP server database migrations — schema versioning and safe migration execution
• MCP server Drizzle ORM — type-safe queries over this SQLite database
• MCP server Prisma — ORM alternative with auto-generated client
• MCP server Redis — distributed cache layer above SQLite persistence
• MCP server connection pooling — pooling patterns for external databases
• MCP server graceful shutdown — shutdown ordering that safely closes the DB connection
• MCP server Fly.io — persistent volume configuration for SQLite on Fly
• MCP server health check — detecting SQLite startup failures before they affect users
• AliveMCP — external MCP protocol monitoring that catches database-induced failures