首页 文章

羽毛和Sequelize SQL Server错误

提问于
浏览 次
0

我遇到了几个sql server错误，尝试使用羽毛js和sequelize开始使用简单的rest api . 我使用的是SQL Server 2012（快速）版本 .

首先我的代码：

const { db } = require('../database');
const Sequelize = require('sequelize');
const service = require('feathers-sequelize');
const app = require('../app');

const Model = db.define('workorder', {
  id: { type: Sequelize.INTEGER, primaryKey: true },
  created_by: Sequelize.STRING,
  modified_by: Sequelize.STRING,

  status_name: Sequelize.STRING,
  date_completed: Sequelize.DATE,
  category: Sequelize.STRING,
  location: Sequelize.STRING,
  details: Sequelize.TEXT,
  crew_name: Sequelize.STRING,
  assigned_to: Sequelize.STRING,
}, {
  schema: 'dbo',
  freezeTableName: true,
  createdAt: 'date_created',
  updatedAt: 'date_modified',
});

app.use('/api/workorders', service({
  Model,
  id: 'id',
  paginate: {
    default: 10,
    max: 100,
  },
}));

当我点击api endpoints /api/workorders 时出错：

SequelizeDatabaseError：FETCH语句中NEXT选项的使用无效 .

我看到sql生成了：

Executing (default): SELECT [id], [created_by], [modified_by], [status_name], [date_completed], [category], [location], [details], [crew_name], [assigned_to], [date_created], [date_modified] FROM [dbo].[workorder] AS [workorder] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

似乎sql server需要一些ORDER BY子句 . 所以我尝试将一个参数发送到 endpoints /api/workorders?$sort=id

这也错了：

SequelizeDatabaseError：无效的列名称“1” .

SQL Generated看起来像这样：

Executing (default): SELECT [id], [created_by], [modified_by], [status_name], [date_completed], [category], [location], [details], [crew_name], [assigned_to], [date_created], [date_modified] FROM [dbo].[workorder] AS [workorder] ORDER BY [workorder].[0] DESC, [workorder].[1] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

这个错误更明显，没有列1或0 .

有人遇到过这些问题吗？

sql-server sequelize.js feathersjs feathers-sequelize

2 回答

  • 1

    这似乎是Sequelize与this issue中提到的旧版MSSQL服务器的不兼容性 . 一个Feathers特定的解决方案提到了in this issue comment来添加 $sort 和 $limit 这样：

    query: {
        $limit: 5,
        $sort: {createdAt: -1}
      }
    回复于
  • 1

    正如Daff所提到的，我也发现对于SQL Server 2014，Sequelize仍然没有生成MSSQL 2014兼容代码 . 因此，鉴于建议here指定一个小于11.0.0的MSSQL版本，我在某种程度上任意指定了10.0.0的数据库版本 .

    这对我有用 - Sequelize现在生成2014（及更早版本）兼容的tsql代码 . 我在从feat-cli生成的'sequelize.js'文件中生成Sequelize实例时传递的options对象中的databaseVersion属性进行了更改，如下所示：

    const sequelize = new Sequelize(database, username, password, {
  dialect: 'mssql',
  host: hostname,
  logging: console.log,
  define: {
    freezeTableName: true
  },
  // from https://github.com/sequelize/sequelize/issues/4404
  // use earlier SQL Server version to assure tsql compatible code generation
  databaseVersion: '10.0.0' 
});
    回复于

相关问题