Guide · ORM Integration

MCP Tools for Sequelize — connection pool lifecycle, association eager loading, transaction hooks

Three Sequelize behaviours catch MCP server authors off-guard: Sequelize creates the connection pool lazily and does not verify database connectivity at construction time — calling new Sequelize(url) always succeeds even if the database is unreachable; the first failing findAll() or authenticate() in a tool handler is where you discover the connectivity problem, which is after agent traffic has started arriving; associations are not loaded by default and passing { include: { all: true } } loads the entire object graph recursively — a Post with Comments, each Comment with its Author, each Author with their Posts produces unbounded recursive loading that joins every related table and returns a combinatorial explosion of rows; and Sequelize model hooks (afterCreate, afterSave) run inside the same transaction as the triggering query when that query uses a managed transaction — if the hook sends an email or calls an external API and the outer transaction later rolls back, the side effect has already been dispatched with no way to cancel it.

TL;DR

Call sequelize.authenticate() at startup to verify connectivity before the MCP server starts. Set pool: { max: concurrencyBudget, acquire: 5000 }. Use include: [{ model: User, attributes: ['id', 'name'] }] not include: { all: true }. Wrap multi-step writes in sequelize.transaction(async (t) => { ... }). Push side-effect hooks (email, webhooks) to an after-commit listener, not a model hook. Health probe: sequelize.authenticate().

Connection pool lifecycle — authenticate before accepting tool calls

Sequelize's constructor validates the options object but does not connect. sequelize.authenticate() opens a connection from the pool, executes SELECT 1+1 AS result, and closes it. Call this at startup to detect unreachable databases, wrong credentials, and TLS certificate issues before the MCP server starts handling agent requests.

import { Sequelize, DataTypes, Model } from '@sequelize/core';

// Create Sequelize instance — pool is lazy, no connection yet
const sequelize = new Sequelize(process.env.DATABASE_URL!, {
  dialect: 'postgres',
  logging: false, // Disable SQL logging in production
  pool: {
    max: 20,           // Max connections in the pool
    min: 2,            // Keep 2 warm connections alive
    acquire: 5_000,    // ms to wait for a pool slot before throwing
    idle: 30_000,      // ms before an idle connection is released
    evict: 60_000,     // Run eviction check every 60s
  },
  dialectOptions: {
    // PostgreSQL: validate connection health before using it from pool
    // Equivalent to pool_pre_ping in SQLAlchemy — catches stale connections
    keepAlive: true,
    keepAliveInitialDelayMillis: 0,
    connectTimeout: 10_000,
  },
});

// Startup gate — verify connectivity before registering MCP tools
async function initDatabase(): Promise<void> {
  try {
    await sequelize.authenticate();
    console.log('Sequelize: database connection verified');
  } catch (err) {
    console.error('Sequelize: cannot connect to database:', err);
    process.exit(1);
  }
}

// Graceful shutdown — drain pool before process exits
process.on('SIGTERM', async () => {
  await sequelize.close();
  process.exit(0);
});

// Main entry
async function main() {
  await initDatabase();

  // Define models after successful connection
  defineModels(sequelize);

  // Start MCP server only after database is ready
  const server = buildMcpServer(sequelize);
  await server.start();
}

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

Sequelize v7 (the @sequelize/core package) changed the import path and removed many deprecated patterns from v6. If you're still on sequelize v6, the pool.handleDisconnects option is deprecated — use dialectOptions.keepAlive instead and let the pool's eviction logic handle stale connections.

Association eager loading — explicit includes prevent N+1 and runaway queries

Sequelize does not load associations by default. Accessing post.user on an instance without having included User in the query returns undefined, not a lazy-loaded value. This means N+1 patterns require an explicit for loop over results, making them visible — but also making { include: { all: true } } tempting as a shortcut. That shortcut is dangerous.

import { Op } from '@sequelize/core';

// Models defined with associations
class User extends Model {
  declare id: string;
  declare name: string;
  declare email: string;
}
class Post extends Model {
  declare id: string;
  declare title: string;
  declare userId: string;
}
class Comment extends Model {
  declare id: string;
  declare body: string;
  declare postId: string;
  declare userId: string;
}

User.hasMany(Post, { foreignKey: 'userId', as: 'posts' });
Post.belongsTo(User, { foreignKey: 'userId', as: 'author' });
Post.hasMany(Comment, { foreignKey: 'postId', as: 'comments' });
Comment.belongsTo(User, { foreignKey: 'userId', as: 'commenter' });

// BAD: includes entire object graph — recursive, unbounded row multiplication
async function getPostBad(postId: string) {
  return Post.findByPk(postId, {
    include: { all: true },  // Loads Post → Comments → Authors → their Posts → recursion
  });
}

// GOOD: explicit includes with specific attributes
async function getPostGood(postId: string) {
  return Post.findByPk(postId, {
    attributes: ['id', 'title', 'createdAt'],
    include: [
      {
        model: User,
        as: 'author',
        attributes: ['id', 'name'],  // Only fetch the columns you need
      },
      {
        model: Comment,
        as: 'comments',
        attributes: ['id', 'body', 'createdAt'],
        limit: 20,                   // Always limit one-to-many sub-includes
        order: [['createdAt', 'DESC']],
        include: [
          {
            model: User,
            as: 'commenter',
            attributes: ['id', 'name'],
          },
        ],
      },
    ],
  });
}

// List query: paginated with shallow include
async function listPosts(page = 1, pageSize = 20) {
  const offset = (page - 1) * pageSize;
  return Post.findAndCountAll({
    attributes: ['id', 'title', 'createdAt'],
    include: [
      {
        model: User,
        as: 'author',
        attributes: ['id', 'name'],
        required: true,  // INNER JOIN — exclude posts without authors
      },
    ],
    limit: pageSize,
    offset,
    order: [['createdAt', 'DESC']],
    // separate: true for has-many includes prevents row multiplication
    // (Sequelize issues a separate subquery instead of JOIN)
    // Use for Comment includes on large datasets
  });
}

// Separate subquery loading — avoids row multiplication for hasMany
async function getPostWithCommentsSeparate(postId: string) {
  return Post.findByPk(postId, {
    include: [
      {
        model: Comment,
        as: 'comments',
        separate: true,    // SELECT * FROM comments WHERE postId IN (postId) — no row multiplication
        limit: 50,
        order: [['createdAt', 'ASC']],
      },
    ],
  });
}

When you use findAndCountAll with a hasMany include (not separate: true), Sequelize generates a GROUP BY + LIMIT subquery. Without subQuery: false, Sequelize wraps the query in a subquery that may not use your indexes. For paginated lists of posts with comment counts, a raw sequelize.query() with a hand-written LEFT JOIN + COUNT + GROUP BY is often faster.

Transaction management and hook safety in MCP tool handlers

Sequelize's managed transaction (sequelize.transaction(async (t) => { ... })) automatically commits on resolver return and rolls back on any thrown error. All model operations inside must receive { transaction: t } — without it they run outside the transaction on a different connection.

import { Transaction } from '@sequelize/core';

// Atomic tool handler — all writes commit or all roll back
server.tool('purchase_item', async ({ userId, itemId, price }) => {
  return sequelize.transaction(async (t) => {
    // SELECT ... FOR UPDATE — row-level lock prevents concurrent purchases
    const user = await User.findByPk(userId, {
      lock: Transaction.LOCK.UPDATE,
      transaction: t,
    });
    const item = await Item.findByPk(itemId, {
      lock: Transaction.LOCK.UPDATE,
      transaction: t,
    });

    if (!user || !item) throw new Error('User or item not found');
    if (user.balance < price) throw new Error('Insufficient balance');
    if (!item.available) throw new Error('Item no longer available');

    await user.decrement('balance', { by: price, transaction: t });
    await item.update({ available: false, purchasedBy: userId }, { transaction: t });

    const receipt = await Receipt.create({
      userId,
      itemId,
      amount: price,
    }, { transaction: t });

    return { receiptId: receipt.id, newBalance: user.balance - price };
    // t.commit() called implicitly — no need to call it manually
  });
});

// Hook safety: afterCreate fires inside the transaction — don't send emails there
// BAD: email sent before transaction commits; if commit fails, email was wasted
User.addHook('afterCreate', async (user) => {
  await sendWelcomeEmail(user.email); // DANGEROUS inside a transaction
});

// GOOD: use afterCommit — fires only after the transaction successfully commits
// Sequelize calls afterCommit hooks on the transaction object, not the model
server.tool('create_user', async ({ name, email }) => {
  return sequelize.transaction(async (t) => {
    const user = await User.create({ name, email }, { transaction: t });

    // Register after-commit side effect on the transaction, not the model
    t.afterCommit(async () => {
      // This runs only if the transaction commits successfully
      await sendWelcomeEmail(email);
      await enqueueOnboardingJob(user.id);
    });

    return { id: user.id, name: user.name };
  });
});

// Savepoint pattern for bulk operations with partial failures
server.tool('bulk_import_tags', async ({ tags }) => {
  const results: Array<{ name: string; status: string; reason?: string }> = [];

  await sequelize.transaction(async (outerT) => {
    for (const name of tags) {
      const sp = await outerT.savepoint(\`sp_tag_\${name.replace(/\W/g, '_')}\`);
      try {
        await Tag.create({ name: name.trim().toLowerCase() }, { transaction: outerT });
        await sp.commit();
        results.push({ name, status: 'created' });
      } catch (err) {
        await sp.rollback(); // Rollback to savepoint — outer transaction continues
        results.push({ name, status: 'skipped', reason: (err as Error).message });
      }
    }
  });

  return { results };
});

Pass { isolationLevel: Transaction.ISOLATION_LEVELS.READ_COMMITTED } to sequelize.transaction() for tools that read then update — the default SERIALIZABLE isolation level can cause transaction serialization failures under concurrent writes, which surface as cryptic could not serialize access due to concurrent update errors. Use SERIALIZABLE only for financial operations that require strict consistency.

Health probe — Sequelize database reachability from an MCP server

sequelize.authenticate() is the standard health check — it opens a connection from the pool, runs SELECT 1+1 AS result, and releases the connection. It verifies pool availability, network reachability, and credential validity in one call.

async function checkSequelizeHealth(): Promise<{
  healthy: boolean;
  latencyMs?: number;
  error?: string;
}> {
  const start = Date.now();
  try {
    await sequelize.authenticate();
    return { healthy: true, latencyMs: Date.now() - start };
  } catch (err) {
    return { healthy: false, error: String(err) };
  }
}

// Pool diagnostics — surface as metrics or logs
function poolStats() {
  const pool = (sequelize as any).pool;
  return {
    size:     pool?.size     ?? 0,
    available: pool?.available ?? 0,
    waiting:  pool?.waiting   ?? 0,
    using:    pool?.using     ?? 0,
  };
}

// Startup gate
async function startup() {
  const health = await checkSequelizeHealth();
  if (!health.healthy) {
    console.error('Sequelize health check failed:', health.error);
    process.exit(1);
  }
  console.log(\`Sequelize ready (\${health.latencyMs}ms)\`);
  const stats = poolStats();
  console.log(\`Pool: size \${stats.size}, \${stats.waiting} waiting\`);
}

// Expose health as MCP tool for agent-driven diagnostics
server.tool('db_health', async () => {
  const health = await checkSequelizeHealth();
  return { ...health, pool: poolStats() };
});

AliveMCP pings your MCP server endpoints every 60 seconds and surfaces latency trends, connection pool saturation, and availability history in a public dashboard — so you know about pool exhaustion or database failover before your users notice tool calls failing in their agents.

TL;DR

Connection pool lifecycle — authenticate before accepting tool calls

Association eager loading — explicit includes prevent N+1 and runaway queries

Transaction management and hook safety in MCP tool handlers

Health probe — Sequelize database reachability from an MCP server

Related guides