Sequelize v7 Alpha连接SQL Server时Where子句查询报错解决

问题现象 #

基于React+Node.js+Sequelize v7 Alpha版本开发应用，连接SQL Server数据库。手动创建数据表后，插入操作及无Where条件的findAll查询可正常执行，但使用带Where子句的findAll、findOne及findByPk方法时均报错。

报错信息 #

{  
  err: BaseError [SequelizeBaseError]: Invalid value received for the "where" option. Refer to the sequelize documentation to learn which values the "where" option accepts.  
  Value: { email: 'Science' }      
  at WhereSqlBuilder.formatWhereOptions (C:\web\EvolveChat\server\node_modules\@sequelize\mssql\node_modules\@sequelize\core\lib\abstract-dialect\where-sql-builder.js:164:13)      
  at MsSqlQueryGenerator.whereItemsQuery (C:\web\EvolveChat\server\node_modules\@sequelize\mssql\node_modules\@sequelize\core\lib\abstract-dialect\query-generator-typescript.js:554:33)      
  at MsSqlQueryGenerator.selectQuery (C:\web\EvolveChat\server\node_modules\@sequelize\mssql\node_modules\@sequelize\core\lib\abstract-dialect\query-generator.js:973:28)      
  at MsSqlQueryInterface.select (C:\web\EvolveChat\server\node_modules\@sequelize\mssql\node_modules\@sequelize\core\lib\abstract-dialect\query-interface.js:500:37)      
  at User.findAll (C:\web\EvolveChat\server\node_modules\@sequelize\core\lib\model.js:1047:47)      
  at process.processTicksAndRejections (node:internal/process/task_queues:105:5)      
  at async User.findOne (C:\web\EvolveChat\server\node_modules\@sequelize\core\lib\model.js:1195:12)      
  at async login (file:///C:/web/EvolveChat/server/controllers/authController.js:55:22) {    
    [cause]: TypeError: Expected type to be a string, a DataType class, or a DataType instance, but got STRING {      
      usageContext: undefined,      
      options: { length: undefined, binary: false }    
    }.        
    at normalizeDataType (C:\web\EvolveChat\server\node_modules\@sequelize\core\lib\abstract-dialect\data-types-utils.js:62:11)        
    at Sequelize.normalizeDataType (C:\web\EvolveChat\server\node_modules\@sequelize\core\lib\sequelize-typescript.js:843:58)        
    at Sequelize.validateValue (C:\web\EvolveChat\server\node_modules\@sequelize\core\lib\sequelize-typescript.js:836:17)        
    at MsSqlQueryGenerator.escape (C:\web\EvolveChat\server\node_modules\@sequelize\mssql\node_modules\@sequelize\core\lib\abstract-dialect\query-generator-typescript.js:654:20)        
    at WhereSqlBuilder.formatBinaryOperation (C:\web\EvolveChat\server\node_modules\@sequelize\mssql\node_modules\@sequelize\core\lib\abstract-dialect\where-sql-builder.js:528:105)        
    at C:\web\EvolveChat\server\node_modules\@sequelize\mssql\node_modules\@sequelize\core\lib\abstract-dialect\where-sql-builder.js:288:21        
    at #handleRecursiveNotOrAndNestedPathRecursive (C:\web\EvolveChat\server\node_modules\@sequelize\mssql\node_modules\@sequelize\core\lib\abstract-dialect\where-sql-builder.js:566:14)        
    at WhereSqlBuilder.formatPojoWhere (C:\web\EvolveChat\server\node_modules\@sequelize\mssql\node_modules\@sequelize\core\lib\abstract-dialect\where-sql-builder.js:249:60)        
    at C:\web\EvolveChat\server\node_modules\@sequelize\mssql\node_modules\@sequelize\core\lib\abstract-dialect\where-sql-builder.js:160:23        
    at C:\web\EvolveChat\server\node_modules\@sequelize\mssql\node_modules\@sequelize\core\lib\abstract-dialect\where-sql-builder.js:230:16  
  }
}

相关代码 #

User模型代码 #

import { Sequelize, DataTypes, Model } from '@sequelize/core';
import { Attribute, PrimaryKey, AutoIncrement, NotNull, Table } from '@sequelize/core/decorators-legacy';
import { MsSqlDialect } from '@sequelize/mssql';
import { genSalt, hash } from "bcrypt"

const sequelize = new Sequelize({
  dialect: MsSqlDialect,
  server: process.env.SERVER,
  port: process.env.PORT,
  database: process.env.DB,
  authentication: {
      type: "ntlm",
      options: {
          domain: process.env.DOMAIN,
          userName: process.env.USER,
          password: process.env.PASSWORD,
        },
  },
  trustServerCertificate:true
});

export class User extends Model {}

User.init(  {
  pk_user:  {
    type:DataTypes.INTEGER,
    allowNull:false,
    autoIncrement:true,
    primaryKey: true
  },
  email:  {
    type:DataTypes.STRING,
    index: {unique:true},
    //NotNull:true,
    allowNull:false
  },
  password:  {
    type:DataTypes.STRING,
    //index: {unique:true},
    //`NotNull:true,
    allowNull:false
  },
  first_name:  {
    type:DataTypes.STRING,
  },
  last_name:  {
    type:DataTypes.STRING,
  },
  image:  {
    type:DataTypes.STRING,
  },
  color:  {
    type:DataTypes.STRING,
  },
  profile_setup:  {
    type:DataTypes.BOOLEAN,
    NotNull: true,
    defaultValue: false
  },
  createdAt:  {
    type:DataTypes.DATE,
    //NotNull: true,
    //defaultValue: false
  },
  updatedAt:  {
    type:DataTypes.DATE,
    //NotNull: true,
    //defaultValue: false
  }
}, {
  sequelize,
})

User.hooks.addListener('beforeSave', async function(User)  {
    const salt = await genSalt();
    User.password = await hash(User.password, salt);
  
})

export default User;

查询调用代码 #

export const login = async (request, response, next) =>    {    
    try{
        //await User.sequelize.sync();
        const {email,password} = request.body;
        if(!email || !password) {
            return response.status(400).send("Email and Password is required.")
        }
     
        const user = await User.findOne({
            where: {
              email: 'Science',
            },
            order: [[ 'pk_user', 'DESC' ]],
        });
        if (!user)  {
            return response.status(404).send("User with the given email not found.")
        }
        console.log({user});
        const auth = await compare(password, user.password);
        if (!auth)  {
            return response.status(400).send("Incorrect or invalid password.") 
        }
        response.cookie("jwt", createToken(email,user.pk_user, {
            maxAge,
            secure:true,
            sameSite:"None"
        }));
        return response.status(200).json({user:{
            pk_user:user.pk_user,
            email:user.email,
            first_name:user.first_name,
            last_name: user.last_name,
            image:user.image,
            color:user.color,
            profile_setup:user.profile_setup
        }})
    } catch(err)    {
        console.log({err});
        return response.status(500).send("Internal Server Error");
    }
}

无效尝试 #

• 调整查询参数格式
• 更换查询方法（如用findByPk替代findOne）
• 降级Sequelize至稳定版本

解决方法 #

卸载所有Sequelize相关依赖，仅安装@sequelize/mssql，问题得以解决。

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