如何在Node.js中监听SQL Server数据库表CRUD变更并推送至客户端

SQL Server没有提供类似Oracle驱动原生的subscribe订阅接口，需要结合SQL Server自带的变更捕获能力配合Node.js侧逻辑实现需求，以下是3种可落地的实现方案：

核心可选方案 #

方案1：变更数据捕获（CDC）+ 定时轮询 #

该方案适合大部分常规场景，对业务代码无侵入：

• 前提要求：SQL Server 2008及以上版本，2016 SP1之后的标准版也可支持，之前版本需要企业版/开发版
• 第一步开启数据库CDC能力：执行SQL命令 USE 你的数据库名; EXEC sys.sp_cdc_enable_db;
• 第二步开启目标表的CDC捕获：执行SQL命令 EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'你的目标表名', @role_name = NULL;
• 第三步Node.js侧定时查询CDC自动生成的变更记录表 cdc.<表名>_CT，表中__$operation字段对应操作类型：1=删除、2=插入、3=更新前数据、4=更新后数据
• 第四步每次查询后记录本次的最大LSN（日志序列号），下次查询从该LSN之后拉取，避免重复获取变更
• 第五步拿到结构化变更数据后，直接通过socket.io推送给客户端即可

优点：不需要修改业务代码，性能损耗极低，能捕获所有直接操作数据库的变更；缺点：延迟取决于轮询间隔，一般为秒级，不适合要求毫秒级实时的场景。

方案2：SQL Server查询通知 #

该方案适合要求低延迟的场景，变更触发主动推送无需轮询：

• 第一步开启数据库的Service Broker：执行SQL命令 ALTER DATABASE 你的数据库名 SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
• 第二步Node.js侧使用mssql包的查询通知能力，执行监听查询时指定通知选项，当查询结果集发生变化时SQL Server会主动推送通知到Node.js服务
• 第三步收到通知后拉取最新变更数据，通过socket.io推送给客户端

注意：查询通知对SQL语句有严格限制，必须使用两部分表名（如dbo.表名），不能使用*、聚合函数、临时表等，不符合要求的查询不会触发通知。
优点：实时性高，无轮询开销；缺点：配置复杂，查询限制多，高并发变更场景下可能出现通知丢失或性能下降。

方案3：触发器 + 变更队列表 #

该方案兼容性最高，所有SQL Server版本都可支持：

• 第一步新建一张变更队列表，字段至少包含：自增主键、操作类型（insert/update/delete）、变更数据（建议用JSON格式存储）、创建时间、处理状态
• 第二步给目标表创建insert、update、delete三个触发器，每次操作触发时把变更数据写入变更队列表
• 第三步Node.js侧轮询变更队列表，拉取未处理的变更记录，处理完成后标记为已处理，再推送给socket.io客户端

优点：实现简单，无版本限制，无查询语法限制；缺点：有一定侵入性，需要新增表和触发器，高并发写入场景下触发器会增加数据库性能开销。

参考代码示例（CDC方案） #

首先安装依赖：

npm install mssql socket.io

Node.js服务端代码示例：

const sql = require('mssql');
const { Server } = require('socket.io');

// SQL Server连接配置
const sqlConfig = {
  user: '数据库用户名',
  password: '数据库密码',
  server: '数据库地址',
  database: '数据库名',
  options: {
    encrypt: true,
    trustServerCertificate: true
  }
}

// 记录上次查询的最大LSN，避免重复拉取变更
let lastLSN = null;

// 初始化数据库连接和轮询任务
async function init() {
  await sql.connect(sqlConfig);
  // 先获取初始LSN，只监听服务启动后的变更
  const lsnRes = await sql.query`SELECT MAX(__$start_lsn) as maxLsn FROM cdc.你的目标表名_CT`;
  lastLSN = lsnRes.recordset[0].maxLsn;

  // 每1秒轮询一次，可根据实时性要求调整间隔
  setInterval(pollChanges, 1000);
}

// 拉取变更处理逻辑
async function pollChanges() {
  if (!lastLSN) return;
  const res = await sql.query`
    SELECT * FROM cdc.你的目标表名_CT 
    WHERE __$start_lsn > ${lastLSN}
    ORDER BY __$start_lsn ASC
  `;
  if (res.recordset.length > 0) {
    // 更新最新的LSN
    lastLSN = res.recordset[res.recordset.length - 1].__$start_lsn;
    // 格式化变更数据
    const changes = res.recordset.map(item => {
      let opType;
      switch(item.__$operation) {
        case 1: opType = 'delete'; break;
        case 2: opType = 'insert'; break;
        case 4: opType = 'update'; break;
        default: return null;
      }
      if (!opType) return null;
      // 提取业务字段，过滤CDC系统字段
      const data = {};
      Object.keys(item).forEach(key => {
        if (!key.startsWith('__$')) data[key] = item[key];
      });
      return { type: opType, data };
    }).filter(Boolean);
    // 推送给所有连接的socket.io客户端
    io.emit('db:change', changes);
  }
}

// 启动socket.io服务，端口可自行修改
const io = new Server(3000, {
  cors: { origin: "你的客户端域名" }
});

init().catch(console.error);

注意事项 #

• 使用CDC方案时要注意定期清理CDC日志表，避免占用过多磁盘空间，SQL Server默认自带清理任务，也可手动调整数据保留周期
• 生产环境不要配置通配符CORS源，要替换为实际的客户端访问域名
• 轮询间隔可根据业务的实时性要求和数据库负载情况灵活调整，平衡实时性和性能开销

内容的提问来源于stack exchange，提问作者Darrell