Sequelize模型方法在SQL Server 2008异常，query方法正常求助

在PostgreSQL中使用Sequelize的findOne、findAll、create等模型方法完全正常，但连接SQL Server 2008调用这些方法时出错，sequelize.query却能正常运行。已做大量调研仍未解决，附上代码示例和报错信息，求协助。

代码示例 #

const Sequelize = require('sequelize');

const sequelize = new Sequelize(
    process.env.DB_DATBASE,
    process.env.DB_USERNAME,
    process.env.DB_PASSWORD,
    {
        host: process.env.DB_HOST,
        dialect: 'mssql',
        dialectOptions: {
            options: {
                encrypt: false,
                enableArithAbort: false,
            }
        },
    },);

try {
    sequelize
        .authenticate()
        .then(async () => {
            console.log('Connection has been established successfully.');
        })
        .catch((err) => {
            console.log('Unable to connect to the database:', err);
        });
} catch (err) {
    console.log('an error occured', err);
}
const highRiskModel = sequelize.define(
    'QuestionTypes',
    {
        QuestionTypeID: Sequelize.INTEGER,
        QuestionTypeName: Sequelize.STRING
        // ...modelCommonAttributes
    },
    {
        paranoid: false,
        timestamps: true,
        freezeTableName: true,
        schema: 'dba',
        // ...modelRenameDateAttributes
    },
);

highRiskModel.findAll().then((res)=>{
    console.log(res);
});

报错信息 #

Executing(default ): SELECT[id], [QuestionTypeID][QuestionTypeName], [createdAt], [updatedAt] FROM[QuestionTypes] AS[QuestionTypes];

node:internal/process/promises:279
triggerUncaughtException(err, true /* fromPromise */);
^

Error
    at Query.run(/var/www/html/project/db_migrate_conserve/node_modules/sequelize/lib/dialects/mssql/query.js:107:25)
    at /var/www/html/project/db_migrate_conserve/node_modules/sequelize/lib/sequelize.js:315:28
    at processTicksAndRejections(node:internal/process/task_queues:96:5)
    at async MSSqlQueryInterface.select(/var/www/html/project/db_migrate_conserve/node_modules/sequelize/lib/dialects/abstract/query-interface.js:407:12)
    at async Function.findAll(/var/www/html/project/db_migrate_conserve/node_modules/sequelize/lib/model.js:1140:21) {
  name: 'SequelizeDatabaseError',
  parent: AggregateError
    at RequestTokenHandler.onErrorMessage(/var/www/html/project/db_migrate_conserve/node_modules/tedious/lib/token/handler.js:379:30)
    at Readable.<anonymous>(/var/www/html/project/db_migrate_conserve/node_modules/tedious/lib/token/token-stream-parser.js:26:33)
    at Readable.emit(node:events:526:28)
    at Readable.emit(node:domain:475:12)
    at addChunk(node:internal/streams/readable:315:12)
    at readableAddChunk(node:internal/streams/readable:289:9)
    at Readable.push(node:internal/streams/readable:228:10)
    at next(node:internal/streams/from:98:31)
    at processTicksAndRejections(node:internal/process/task_queues:96:5) {
    sql: 'SELECT [id], [QuestionTypeID], [QuestionTypeName], [createdAt], [updatedAt] FROM [QuestionTypes] AS [QuestionTypes];',
    parameters: undefined
  },
  original: AggregateError
    at RequestTokenHandler.onErrorMessage(/var/www/html/project/db_migrate_conserve/node_modules/tedious/lib/token/handler.js:379:30)
    at Readable.<anonymous>(/var/www/html/project/db_migrate_conserve/node_modules/tedious/lib/token/token-stream-parser.js:26:33)
    at Readable.emit(node:events:526:28)
    at Readable.emit(node:domain:475:12)
    at addChunk(node:internal/streams/readable:315:12)
    at readableAddChunk(node:internal/streams/readable:289:9)
    at Readable.push(node:internal/streams/readable:228:10)
    at next(node:internal/streams/from:98:31)
    at processTicksAndRejections(node:internal/process/task_queues:96:5) {
    sql: 'SELECT [id], [QuestionTypeID], [QuestionTypeName], [createdAt], [updatedAt] FROM [QuestionTypes] AS [QuestionTypes];',
    parameters: undefined
  },
  sql: 'SELECT [id], [QuestionTypeID], [QuestionTypeName], [createdAt], [updatedAt] FROM [QuestionTypes] AS [QuestionTypes];',
  parameters: {}
}

问题分析与解决办法 #

1. 主键未明确指定（核心问题） #

Sequelize默认会给模型自动添加id作为主键，但你的表QuestionTypes实际用的是QuestionTypeID作为主键，导致生成的SQL里包含不存在的[id]字段，这是报错的直接原因。

修复：在模型定义中明确指定主键：

const highRiskModel = sequelize.define(
    'QuestionTypes',
    {
        QuestionTypeID: {
            type: Sequelize.INTEGER,
            primaryKey: true, // 标记为主键
            autoIncrement: false // 根据表结构调整，若QuestionTypeID是自增则设为true
        },
        QuestionTypeName: Sequelize.STRING
        // ...其他字段
    },
    {
        paranoid: false,
        timestamps: true,
        freezeTableName: true,
        schema: 'dba',
        // ...其他配置
    },
);

2. 时间戳字段不匹配 #

模型设置了timestamps: true，如果你的表QuestionTypes中没有createdAt和updatedAt这两个字段，也会导致SQL报错。

• 若表中确实没有这两个字段，直接将timestamps设为false；
• 若表中有对应的时间字段，比如CreateTime和UpdateTime，可以映射字段：

{
    // ...其他配置
    timestamps: true,
    createdAt: 'CreateTime',
    updatedAt: 'UpdateTime'
}

3. SQL Server 2008兼容性配置 #

SQL Server 2008是较老版本，需要添加TDS版本指定等兼容配置：

const sequelize = new Sequelize(
    // ...连接信息
    {
        host: process.env.DB_HOST,
        dialect: 'mssql',
        dialectOptions: {
            options: {
                encrypt: false,
                enableArithAbort: false,
                trustServerCertificate: true,
                tdsVersion: '7_3' // SQL Server 2008对应TDS 7.3版本
            }
        },
    },);

4. Schema生效问题 #

模型中指定了schema: 'dba'，但生成的SQL未包含schema，导致找不到表。可以在查询时显式指定schema：

highRiskModel.findAll({ schema: 'dba' }).then(res => console.log(res));

或者确认Sequelize版本是否支持模型级别的schema配置，若版本较老可能需要升级或在连接配置中全局指定schema。

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