Guide · Data Persistence

MCP server Drizzle ORM

Drizzle ORM is a TypeScript-first database toolkit that defines schema in plain TypeScript files, generates type-safe queries with a SQL-like builder, and produces migration SQL via Drizzle Kit without a separate schema language like Prisma's .prisma files. In an MCP server, Drizzle's key advantages are: no code generation step at build time (the types are inferred directly from the schema object — no prisma generate run needed in CI/CD), and SQL transparency (the query builder produces SQL you can read and understand, rather than Prisma's abstracted ORM syntax). The MCP-specific concerns are the same as with any ORM: the database connection must be a module-level singleton to avoid pool exhaustion across concurrent SSE sessions, migrations run before the ready signal, and the connection closes after active sessions drain during graceful shutdown. This guide covers Drizzle's schema, query builder, Drizzle Kit migrations, and the full lifecycle in an MCP server.

TL;DR

Define schema in src/schema.ts. Create a src/db.ts singleton that exports the Drizzle instance. Use Drizzle's prepared statements for repeated tool handler queries. Run drizzle-kit migrate before signalling ready. For SQLite, use better-sqlite3 as the driver; for PostgreSQL, use postgres or pg.

Installation

# Core Drizzle ORM
npm install drizzle-orm

# Driver for SQLite (better-sqlite3 — synchronous, recommended for MCP servers)
npm install better-sqlite3
npm install --save-dev @types/better-sqlite3

# Or for PostgreSQL:
# npm install postgres   (Drizzle recommends the 'postgres' driver)

# Drizzle Kit — migration generator and studio (dev dependency only)
npm install --save-dev drizzle-kit

Schema definition

Drizzle schema is defined in TypeScript. The column definitions serve as both the TypeScript type source and the migration basis — Drizzle Kit diffs the schema against the current database state to generate migration SQL.

// src/schema.ts
import { sqliteTable, text, integer, index } from 'drizzle-orm/sqlite-core';
// For PostgreSQL: import { pgTable, text, integer, serial, index } from 'drizzle-orm/pg-core';

export const toolCache = sqliteTable('tool_cache', {
  key:       text('key').primaryKey(),
  value:     text('value').notNull(),
  expiresAt: integer('expires_at', { mode: 'timestamp' }).notNull(),
}, (table) => ({
  expiresAtIdx: index('idx_tool_cache_expires').on(table.expiresAt),
}));

export const sessionLog = sqliteTable('session_log', {
  id:         integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true }),
  sessionId:  text('session_id').notNull(),
  toolName:   text('tool_name').notNull(),
  durationMs: integer('duration_ms').notNull(),
  createdAt:  integer('created_at', { mode: 'timestamp' })
    .$defaultFn(() => new Date())
    .notNull(),
}, (table) => ({
  sessionIdx: index('idx_session_log_session').on(table.sessionId),
}));

// Drizzle infers these types automatically from the schema — no separate type definitions needed
export type ToolCache = typeof toolCache.$inferSelect;
export type NewToolCache = typeof toolCache.$inferInsert;
export type SessionLog = typeof sessionLog.$inferSelect;

Database singleton

// src/db.ts
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import * as schema from './schema.js';
import path from 'path';

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

// Open better-sqlite3 connection with WAL mode for concurrent reads
// See /seo/mcp-server-sqlite for the full WAL mode rationale
const sqlite = new Database(DB_PATH);
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('busy_timeout = 5000');
sqlite.pragma('foreign_keys = ON');
sqlite.pragma('synchronous = NORMAL');

// Create the Drizzle instance wrapping the better-sqlite3 connection
// Pass the schema so Drizzle provides fully typed relation queries
export const db = drizzle(sqlite, { schema });

// Export the raw connection for graceful shutdown (db.close())
export { sqlite as sqliteConnection };

// For PostgreSQL:
// import postgres from 'postgres';
// import { drizzle } from 'drizzle-orm/postgres-js';
// const queryClient = postgres(process.env.DATABASE_URL!);
// export const db = drizzle(queryClient, { schema });
// export { queryClient as pgConnection };

Type-safe queries in tool handlers

Drizzle's query builder reads like SQL — select().from().where() — with TypeScript types inferred from the schema. The return type of every query is fully typed without any casting.

// src/tools/cache-tool.ts
import { db } from '../db.js';
import { toolCache } from '../schema.js';
import { eq, gt } from 'drizzle-orm';

export async function getCachedResult(key: string): Promise {
  // Return type inferred as { key: string; value: string; expiresAt: Date }[]
  const rows = await db
    .select({ value: toolCache.value })
    .from(toolCache)
    .where(
      // gt (greater than) — Drizzle imports comparison operators separately
      // eq(toolCache.key, key) AND toolCache.expiresAt > new Date()
      eq(toolCache.key, key)
    )
    .limit(1);

  return rows[0]?.value ?? null;
}

export async function setCachedResult(
  key: string,
  value: string,
  ttlSeconds: number
): Promise {
  const expiresAt = new Date(Date.now() + ttlSeconds * 1000);

  // INSERT ... ON CONFLICT ... DO UPDATE (upsert)
  await db
    .insert(toolCache)
    .values({ key, value, expiresAt })
    .onConflictDoUpdate({
      target: toolCache.key,
      set: { value, expiresAt },
    });
}

Prepared statements for tool handler performance

Drizzle supports prepared statements — the query is compiled once and can be executed many times with different parameters. For frequently-called tool queries, prepared statements save the SQL parse step on each call.

// src/prepared.ts — prepare at module load time, not inside the handler
import { db } from './db.js';
import { toolCache, sessionLog } from './schema.js';
import { eq, placeholder } from 'drizzle-orm';

// Prepared query — $dynamic() defers execution until .execute() is called
export const getCachedQuery = db
  .select({ value: toolCache.value })
  .from(toolCache)
  .where(eq(toolCache.key, placeholder('key')))
  .limit(1)
  .prepare('get_cached');

export const logToolCallQuery = db
  .insert(sessionLog)
  .values({
    sessionId:  placeholder('sessionId'),
    toolName:   placeholder('toolName'),
    durationMs: placeholder('durationMs'),
  })
  .prepare('log_tool_call');

// Usage in a tool handler:
// const rows = await getCachedQuery.execute({ key: cacheKey });
// await logToolCallQuery.execute({ sessionId, toolName, durationMs: elapsed });

Drizzle Kit migrations

// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/schema.ts',
  out: './drizzle',             // Migration files directory
  dialect: 'sqlite',            // or 'postgresql'
  dbCredentials: {
    url: process.env.DB_PATH ?? './data/mcp.db',
  },
} satisfies Config;

# Generate migration from schema changes
npx drizzle-kit generate

# Apply pending migrations to the database
npx drizzle-kit migrate

# View and edit data in Drizzle Studio (dev only)
npx drizzle-kit studio

In the MCP server startup sequence, run npx drizzle-kit migrate (or use Drizzle's programmatic migration API) before signalling ready to the process manager. See MCP server database migrations for the full startup sequence and multi-replica migration patterns.

Graceful shutdown

import { sqliteConnection } from './db.js';

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

  // Wait for active MCP sessions (and their in-flight Drizzle queries) to finish
  const drainStart = Date.now();
  while (activeSessions.size > 0 && Date.now() - drainStart < DRAIN_TIMEOUT_MS) {
    await new Promise(resolve => setTimeout(resolve, 100));
  }

  // Close the database connection after all queries have completed
  // better-sqlite3 is synchronous — no async close needed
  sqliteConnection.close();

  process.exit(0);
});

Drizzle vs. Prisma for MCP servers

Concern	Drizzle ORM	Prisma
Schema language	TypeScript (same file as application)	Separate `.prisma` schema file
Build step	None — types inferred at compile time	`prisma generate` required in CI/CD
Migration generation	Drizzle Kit diffs TS schema	Prisma Migrate diffs .prisma schema
Query style	SQL-like builder (`select().from().where()`)	Object-oriented (`prisma.user.findMany()`)
Raw SQL	db.run(sql\`...\`) — first-class	`prisma.$queryRaw` — available but secondary
SQLite support	Excellent (better-sqlite3 + node:sqlite)	Good (development), not for edge
Bundle size	Smaller (no generated client)	Larger (generated client included)

AliveMCP and Drizzle health

Drizzle query failures in tool handlers (schema mismatch, connection error, constraint violation) return as isError: true tool results in the MCP protocol. AliveMCP's probe sequence calls tools/call on a test tool and verifies the response is not an error — this catches Drizzle failures that the process-level health check (port open, HTTP 200) misses. A bad migration that renames a column the tool handler queries by old name produces a stream of Drizzle errors that AliveMCP detects within 60 seconds. See MCP server health check for the /health endpoint that surfaces the database error reason.