MCP Tools for TypeORM — DataSource lifecycle, connection pool sizing, transaction tool handlers

DataSource initialization — gate MCP tool registration on database readiness

TypeORM's DataSource holds the connection pool and all entity metadata. Before initialize() resolves, no repository method works. MCP tool handlers are registered synchronously at server construction time, but they execute asynchronously later — the window between registration and first call is where initialization errors hide.

import { DataSource } from 'typeorm';
import { User } from './entity/User';
import { Post } from './entity/Post';

// Create DataSource — does NOT connect yet
export const AppDataSource = new DataSource({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  entities: [User, Post],
  synchronize: false,        // Never true in production — use migrations
  logging: ['error', 'warn'],
  extra: {
    // Connection pool: max concurrent connections to the database
    // Default is 10 — size this to expected parallel tool call concurrency
    max: 20,
    // How long to wait for a pool slot before throwing (ms)
    // Default is 0 (wait indefinitely) — set a bound to fail fast
    connectionTimeoutMillis: 5_000,
    // How long a connection can sit idle before being closed (ms)
    idleTimeoutMillis: 30_000,
  },
});

// Main entry — initialize BEFORE MCP server start
async function main() {
  // Gate: database must be ready before tools are registered
  await AppDataSource.initialize();
  console.log('Database pool established');

  // Verify pool health before accepting agent traffic
  await AppDataSource.query('SELECT 1');
  console.log('Database query verified');

  // NOW register MCP tools and start the server
  const server = buildMcpServer(AppDataSource);
  await server.start();
}

// Graceful shutdown — return pool connections before process exits
process.on('SIGTERM', async () => {
  await AppDataSource.destroy();
  process.exit(0);
});

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

The synchronize: true option drops and recreates columns automatically on startup. Never enable it in production — use TypeORM migrations instead. Synchronize is useful in local development but will silently delete data or alter column types if your entity definitions diverge from the schema.

If the MCP server runs in a context with rapid restarts (containerized, serverless-adjacent), guard against double-initialization:

// Safe re-entrant initialization for environments with multiple boot paths
export async function ensureDataSource(): Promise<DataSource> {
  if (!AppDataSource.isInitialized) {
    await AppDataSource.initialize();
  }
  return AppDataSource;
}

// In tool handlers — never call AppDataSource directly without this guard
async function handleGetUser(userId: string) {
  const ds = await ensureDataSource();
  return ds.getRepository(User).findOneBy({ id: userId });
}

Connection pool sizing — concurrent tool invocations and pool exhaustion

Each MCP tool call that touches the database needs a connection from the pool for the duration of its query execution. When an agent invokes multiple tools in parallel (common in agent loops), all those tool calls compete for the same pool. A pool of 10 can serve 10 simultaneous queries; the 11th waits for a slot. If connectionTimeoutMillis expires before a slot is available, TypeORM throws Error: timeout exceeded when trying to connect.

import { DataSource } from 'typeorm';

// Sizing the pool for an MCP server that supports parallel tool calls
const AppDataSource = new DataSource({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  entities: [__dirname + '/entity/**/*.{ts,js}'],
  synchronize: false,
  extra: {
    // Rule of thumb for MCP servers: set max to
    //   (expected parallel tool calls) * (avg queries per tool) * 1.25
    // A server that handles 8 parallel tool calls, each issuing 2 queries:
    //   8 * 2 * 1.25 = 20 connections
    max: parseInt(process.env.DB_POOL_MAX ?? '20', 10),
    min: 2,                          // Keep 2 warm connections alive
    connectionTimeoutMillis: 5_000,  // Fail fast — don't let calls pile up
    idleTimeoutMillis: 60_000,       // Close idle connections after 1 minute
    // For PostgreSQL: detect stale connections at checkout
    // (useful after database failover or long idle periods)
    allowExitOnIdle: false,
  },
});

// Instrument pool usage in tool handlers to detect sizing problems
async function queryWithPoolDiagnostics<T>(
  label: string,
  fn: () => Promise<T>
): Promise<T> {
  const pool = (AppDataSource.driver as any).master;
  const waiting = pool?.waitingCount ?? 0;
  if (waiting > 0) {
    // Emit as a metric — waiting > 0 consistently = pool too small
    console.warn(`[pool] ${label}: ${waiting} queries waiting for connection`);
  }
  return fn();
}

// Usage in a tool handler:
server.tool('list_users', async ({ filter }) => {
  return queryWithPoolDiagnostics('list_users', () =>
    AppDataSource.getRepository(User).find({
      where: filter ? { role: filter.role } : {},
      take: 50,
    })
  );
});

PostgreSQL has a server-side connection limit (max_connections, default 100). If multiple MCP server instances share the database, multiply per-instance pool size by instance count to verify you're within budget. Use PgBouncer in transaction pooling mode to multiplex MCP server pools onto fewer server-side connections in high-instance-count deployments.

N+1 query prevention — eager loading relations in tool results

TypeORM relations are not loaded by default in v0.3+. A tool that returns a list of posts with author names issues one query for the posts, then one additional query per post to load the author — the classic N+1 pattern. With 50 posts in the result, that is 51 queries. Under concurrent agent tool calls this saturates the pool quickly.

import { AppDataSource } from './data-source';
import { Post } from './entity/Post';

// Bad: N+1 — 1 query for posts, then 1 per post to load author
async function listPostsBad() {
  const posts = await AppDataSource.getRepository(Post).find();
  // TypeORM does NOT auto-load relations — author is undefined unless loaded
  return posts.map(p => ({ id: p.id, title: p.title, author: p.author?.name }));
}

// Good: single JOIN query — load author in the same query
async function listPostsGood() {
  return AppDataSource.getRepository(Post).find({
    relations: ['author'],               // LEFT JOIN users ON posts.authorId = users.id
    select: {
      id: true,
      title: true,
      author: { id: true, name: true }, // Limit columns fetched from the joined table
    },
    take: 50,
    order: { createdAt: 'DESC' },
  });
}

// Alternative: QueryBuilder for conditional or complex joins
async function listPostsWithQueryBuilder(authorId?: string) {
  const qb = AppDataSource
    .getRepository(Post)
    .createQueryBuilder('post')
    .leftJoinAndSelect('post.author', 'author')
    .leftJoinAndSelect('post.tags', 'tag')
    .orderBy('post.createdAt', 'DESC')
    .take(50);

  if (authorId) {
    qb.where('author.id = :authorId', { authorId });
  }

  return qb.getMany();
}

// Deep nesting: load nested relations with dot notation
async function listPostsDeep() {
  return AppDataSource.getRepository(Post).find({
    relations: ['author', 'author.profile', 'comments', 'comments.author'],
    take: 20, // Keep small — deep loading multiplies rows
  });
}

// For large datasets: separate (subquery-style) loading is more efficient
// than a JOIN that multiplies rows for one-to-many
import { FindManyOptions } from 'typeorm';
const opts: FindManyOptions<Post> = {
  relations: { comments: true }, // TypeORM 0.3+ object form
  relationLoadStrategy: 'query', // Separate SELECT instead of JOIN — avoids row multiplication
  take: 100,
};

For tools that return paginated lists, always set take (equivalent to SQL LIMIT). Without a limit, a tool called on a table with 100,000 rows fetches all rows into memory, serializes them into the MCP response, and potentially OOMs the agent process.

Transaction management in MCP tool handlers

MCP tool handlers that perform multiple writes — create a record, update a balance, emit an event — must wrap them in a transaction or leave the database in a partially-updated state when any step fails. TypeORM's dataSource.transaction() is the safest entry point: it begins, commits on success, and rolls back on any thrown error.

import { AppDataSource } from './data-source';
import { User } from './entity/User';
import { AuditLog } from './entity/AuditLog';

// Atomic tool handler — both writes succeed or both roll back
server.tool('transfer_credits', async ({ fromUserId, toUserId, amount }) => {
  return AppDataSource.transaction(async (manager) => {
    // All operations inside use the SAME connection and transaction
    const fromUser = await manager.findOneByOrFail(User, { id: fromUserId });
    const toUser   = await manager.findOneByOrFail(User, { id: toUserId });

    if (fromUser.credits < amount) {
      throw new Error(`Insufficient credits: ${fromUser.credits} available, ${amount} requested`);
    }

    fromUser.credits -= amount;
    toUser.credits   += amount;

    await manager.save(fromUser);
    await manager.save(toUser);

    // Audit log written in the same transaction — rolls back if credits update fails
    await manager.insert(AuditLog, {
      action: 'transfer_credits',
      fromUserId,
      toUserId,
      amount,
      at: new Date(),
    });

    return { ok: true, fromCredits: fromUser.credits, toCredits: toUser.credits };
    // Transaction commits here — all three writes land atomically
  });
  // On any thrown error, TypeORM rolls back all three writes automatically
});

// For read-consistency: wrap reads in a transaction with REPEATABLE READ
server.tool('get_account_snapshot', async ({ userId }) => {
  return AppDataSource.transaction('REPEATABLE READ', async (manager) => {
    const user    = await manager.findOneByOrFail(User, { id: userId });
    const balance = await manager.sum(Transaction, 'amount', { userId });
    // Both reads see the same snapshot — no interleaved writes between them
    return { userId, credits: user.credits, transactionSum: balance };
  });
});

// Savepoint pattern for nested partial failures
server.tool('bulk_create_posts', async ({ posts }) => {
  const results: Array<{ id: string; status: 'created' | 'skipped'; reason?: string }> = [];

  await AppDataSource.transaction(async (manager) => {
    for (const postData of posts) {
      try {
        // TypeORM wraps nested transactions in SAVEPOINTs automatically
        await manager.transaction(async (innerManager) => {
          const post = innerManager.create(Post, postData);
          await innerManager.save(post);
          results.push({ id: post.id, status: 'created' });
        });
      } catch (err) {
        // SAVEPOINT is rolled back — outer transaction continues
        results.push({ id: postData.clientId, status: 'skipped', reason: String(err) });
      }
    }
  });

  return { results };
});

Never pass the outer AppDataSource repository inside a transaction callback — only use the manager parameter. The manager carries the transactional connection; the outer repository uses a different connection from the pool and writes will not be part of the transaction.

Health probe — TypeORM DataSource reachability from an MCP server

TypeORM does not expose a built-in health endpoint. Use DataSource.query('SELECT 1') as the liveness probe — it checks that the pool can acquire a connection and the database can respond. Verify DataSource.isInitialized first to distinguish initialization failures from connectivity failures.

async function checkTypeOrmHealth(): Promise<{
  healthy: boolean;
  latencyMs?: number;
  error?: string;
}> {
  const start = Date.now();
  try {
    if (!AppDataSource.isInitialized) {
      return { healthy: false, error: 'DataSource not initialized' };
    }

    // SELECT 1 acquires a connection, round-trips to the database, returns it
    await AppDataSource.query('SELECT 1');

    return { healthy: true, latencyMs: Date.now() - start };
  } catch (err) {
    return { healthy: false, error: String(err) };
  }
}

// Startup gate — refuse to serve tool calls until database is confirmed healthy
async function startMcpServer() {
  const health = await checkTypeOrmHealth();
  if (!health.healthy) {
    console.error('TypeORM DataSource unhealthy at startup:', health.error);
    process.exit(1);
  }
  console.log(\`TypeORM ready (\${health.latencyMs}ms). Registering MCP tools.\`);
}

// Optional: expose as a tool for agent-driven diagnostics
server.tool('db_health', async () => {
  return checkTypeOrmHealth();
});

AliveMCP monitors your MCP server's database-backed tools by probing the health endpoint on a 60-second interval and alerting when latency exceeds your SLA threshold or when a probe returns an error — catching pool exhaustion, database failovers, and connection leaks before your users encounter tool call failures.