Guide · Data Persistence

MCP server Prisma

Prisma is the most widely used ORM for TypeScript/Node.js backends. In a conventional HTTP server, each request has a short lifetime — connection pool exhaustion from multiple PrismaClient instantiations is mostly a configuration nuisance. In an MCP server, SSE sessions are long-lived and concurrent: multiple sessions can be open simultaneously, each running tool calls that query the database. Two patterns are critical. First, the PrismaClient singleton: instantiating Prisma inside a tool handler creates a new connection pool on each call and exhausts database connections within minutes. A module-level singleton shares the pool across all sessions. Second, the $disconnect shutdown ordering: Prisma's connection pool must be closed after all active tool handler promises have resolved, not before — calling prisma.$disconnect() while a prisma.user.findUnique() is in flight throws PrismaClientKnownRequestError. This guide covers setup, the singleton pattern, type-safe tool queries, migrations on startup, and correct shutdown ordering.

TL;DR

Create a src/prisma.ts singleton file that exports a single PrismaClient instance. Run prisma migrate deploy in your startup sequence before signalling ready. In tool handlers, import the singleton and use isError: true to return Prisma errors to the LLM. On SIGTERM, drain active sessions, then call await prisma.$disconnect() before process.exit(0).

Installation and schema setup

npm install @prisma/client
npm install --save-dev prisma

# Initialise Prisma (creates prisma/schema.prisma and .env)
npx prisma init

Edit prisma/schema.prisma for your database. Prisma supports PostgreSQL, MySQL, SQLite, SQL Server, and MongoDB. For local development or single-server deployments, SQLite requires no external process:

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"      // or "postgresql"
  url      = env("DATABASE_URL")
}

model ToolCache {
  id        Int    @id @default(autoincrement())
  key       String @unique
  value     String
  expiresAt Int

  @@index([expiresAt])
  @@map("tool_cache")
}

model SessionLog {
  id         Int    @id @default(autoincrement())
  sessionId  String
  toolName   String
  durationMs Int
  createdAt  DateTime @default(now())

  @@index([sessionId])
  @@map("session_log")
}

# .env (for local development — never commit this file)
DATABASE_URL="file:./data/mcp.db"

# For PostgreSQL
# DATABASE_URL="postgresql://user:password@localhost:5432/mcp?schema=public"

PrismaClient singleton — the critical pattern

Prisma's documentation includes a warning about module caching: in development with hot-reload (ts-node, nodemon), the module cache is cleared on each reload, creating multiple PrismaClient instances. In production MCP servers without hot-reload, module caching works correctly — the singleton is instantiated once.

// src/prisma.ts — import this file everywhere you need the database
import { PrismaClient } from '@prisma/client';

// Module-level singleton: Node.js module system caches this after the first
// require/import. All tool handlers share one connection pool.
const prisma = new PrismaClient({
  log: process.env.NODE_ENV === 'development'
    ? ['query', 'info', 'warn', 'error']
    : ['warn', 'error'],
  datasources: {
    db: { url: process.env.DATABASE_URL }
  }
});

export default prisma;

The connection pool size defaults to num_cpus * 2 + 1 for PostgreSQL and MySQL, and 1 for SQLite (SQLite does not support concurrent writers). For an MCP server with many concurrent sessions each running short queries, the default pool size is typically adequate. For long-running tool queries (> 1s), increase the pool size via connection_limit in the database URL: postgresql://...?connection_limit=20. See MCP server connection pooling for the full analysis.

Migrations on startup

Run prisma migrate deploy during the startup sequence, before the server begins accepting connections. This ensures the database schema matches the current application version regardless of whether the database was just created or has prior migrations. Running it after accepting connections risks tool handler failures if a required column does not yet exist.

// src/server.ts — startup sequence
import { execSync } from 'child_process';
import prisma from './prisma.js';

async function start() {
  // Step 1: run pending migrations synchronously
  // prisma migrate deploy is idempotent — safe to run on every startup
  console.log('Running database migrations...');
  execSync('npx prisma migrate deploy', { stdio: 'inherit' });

  // Step 2: verify the connection is healthy
  await prisma.$connect();

  // Step 3: start the HTTP server and begin accepting connections
  const httpServer = await startMcpServer();

  // Step 4: signal ready to the process manager
  // PM2: process.send('ready')
  // systemd: sd_notify READY=1
  if (process.send) process.send('ready');

  console.log('MCP server ready');
}

start().catch(err => {
  console.error('Startup failed:', err);
  process.exit(1);
});

For PostgreSQL in a containerised environment, use prisma migrate deploy in an init container (Kubernetes) or as a Fly.io release_command rather than in the application startup sequence — this avoids multiple replicas racing to run migrations simultaneously. See MCP server database migrations for migration race condition patterns.

Type-safe tool handlers

Prisma's generated client provides full TypeScript types for every model, input, and output. Tool handlers receive untyped JSON from the LLM — validate inputs with Zod at the handler boundary before passing them to Prisma.

// src/tools/get-user.ts
import { z } from 'zod';
import { Prisma } from '@prisma/client';
import prisma from '../prisma.js';

const GetUserInput = z.object({
  userId: z.string().uuid('userId must be a valid UUID'),
});

export async function getUserTool(rawInput: unknown) {
  // Validate at the boundary — never trust raw LLM input
  const parsed = GetUserInput.safeParse(rawInput);
  if (!parsed.success) {
    return {
      isError: true,
      content: [{ type: 'text', text: `Invalid input: ${parsed.error.message}` }]
    };
  }

  try {
    const user = await prisma.user.findUniqueOrThrow({
      where: { id: parsed.data.userId },
      select: { id: true, email: true, name: true, createdAt: true }
    });
    return {
      content: [{ type: 'text', text: JSON.stringify(user) }]
    };
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === 'P2025') {
        // Record not found — return isError:true so the LLM can handle gracefully
        return {
          isError: true,
          content: [{ type: 'text', text: `User ${parsed.data.userId} not found` }]
        };
      }
    }
    // Unknown error — rethrow to let the session error handler catch it
    throw error;
  }
}

The isError: true field in the MCP tool result signals to the LLM that the tool call failed — the LLM can then decide to retry, ask for clarification, or report the error to the user. Do not throw from tool handlers for expected errors like "not found" — throw only for unexpected infrastructure failures that the LLM cannot meaningfully recover from.

Graceful shutdown — $disconnect ordering

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

  // Stop accepting new HTTP connections
  httpServer.close();

  // Wait for all active MCP sessions to finish their current tool call
  // Tool calls hold open Prisma query promises — $disconnect before they resolve
  // throws PrismaClientKnownRequestError on those in-flight queries
  const drainStart = Date.now();
  while (activeSessions.size > 0 && Date.now() - drainStart < DRAIN_TIMEOUT_MS) {
    await new Promise(resolve => setTimeout(resolve, 100));
  }

  // Sessions drained — now safe to close the Prisma connection pool
  await prisma.$disconnect();

  process.exit(0);
});

AliveMCP and Prisma health

A common Prisma failure mode in MCP servers is a stale connection pool: if the database server restarts or closes idle connections (PostgreSQL's idle_in_transaction_session_timeout, PgBouncer's pool timeout), Prisma connections in the pool become invalid. The next tool call that uses a stale connection throws a PrismaClientKnownRequestError with code P1001 or P1017. Prisma automatically reconnects on the next request after a pool error, but the failing tool call returns an error to the LLM. AliveMCP's 60-second probe cadence detects this as a failed tool response rather than a port-level health check pass — the server process is alive but the MCP protocol is failing. See MCP server health check for the pattern that surfaces Prisma errors in the /health response.