Feathers: Invalid usage of the option NEXT in the FETCH statement

[frbuceta]

Steps to reproduce

1. Install FeathersJS with FeathersJS CLI (npx @feathersjs/cli generate app)

? Do you want to use JavaScript or TypeScript? JavaScript
? Project name test
? Description Test API
? What folder should the source files live in? src
? Which package manager are you using (has to be installed globally)? Yarn
? What type of API are you making? (Press <space> to select, <a> to toggle all, <i> to invert s
election)REST, Realtime via Socket.io
? Which testing framework do you prefer? Jest
? This app uses authentication Yes
? What authentication strategies do you want to use? (See API docs for all 180+ supported oAuth
 providers) (Press <space> to select, <a> to toggle all, <i> to invert selection)Username + Pas
sword (Local)
? What is the name of the user (entity) service? users
? What kind of service is it? Sequelize
? Which database are you connecting to? SQL Server
? What is the database connection string? mssql://root:password@localhost:1433/test

1. Configure authentication
2. Make call with Postman

Executing (default): SELECT count(DISTINCT([users].[id])) AS [count] FROM [users_dat] AS [users] WHERE [users].[login] = N'hey';
Executing (default): SELECT [id], [name_dat] AS [name], [login] AS [username], [password], [is_blocked] AS [isBlocked] FROM [users_dat] AS [users] WHERE [users].[login] = N'hey' OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

{
    "name": "GeneralError",
    "message": "Invalid usage of the option NEXT in the FETCH statement.",
    "code": 500,
    "className": "general-error",
    "errors": {}
}



md5-645561d3df136ec5270ae95150f7cba1



  "dependencies": {
    "@feathersjs/authentication": "^4.3.0",
    "@feathersjs/authentication-local": "^4.3.0",
    "@feathersjs/authentication-oauth": "^4.3.0",
    "@feathersjs/configuration": "^4.3.0",
    "@feathersjs/errors": "^4.3.0",
    "@feathersjs/express": "^4.3.0",
    "@feathersjs/feathers": "^4.3.0",
    "@feathersjs/socketio": "^4.3.0",
    "compression": "^1.7.4",
    "cors": "^2.8.5",
    "feathers-sequelize": "^6.0.1",
    "helmet": "^3.20.0",
    "mssql": "^5.1.0",
    "sequelize": "^5.16.0",
    "serve-favicon": "^2.5.0",
    "winston": "^3.0.0"
  },
  "devDependencies": {
    "axios": "^0.19.0",
    "eslint": "^6.2.2",
    "jest": "^24.9.0",
    "nodemon": "^1.19.1"
  }

NodeJS version:
v12.12.0

Operating System:
MacOS

Browser Version:
Google Chrome v77.0.3865.120

React Native Version:

Module Loader:

[marcusforsberg]

We're also seeing this error on a brand new CLI scaffolded project. Unfortunately we're being forced to use SQL Server for this project as much as we don't want to 😄The error appears to be caused by Sequelize, not Feathers or feathers-sequelize itself.

I found this StackOverflow answer from @daffl that has a (seemingly!) working workaround. Apparently, setting a sort order on all queries circumvents this error, so we went ahead and implemented a global beforehook (in app.hooks.js):

    async context => {
        if (!context.params.query) {
            context.params.query = {};
        }

        if (!context.params.query.$sort) {
            context.params.query.$sort = {
                createdAt: -1
            };
        }

        return context;
    }

This ensures there's always $sort parameter set on all queries. If some other sort order has been set in the query params, that will used instead.

With this in place, authentication works. Also listing/getting resources, which was also broken (eg. GET /users and GET /users/:id). This is as far as I've tested this but I'm hopeful this will hold together. Will report back should we find that this doesn't in fact solve this as the project takes shape.

EDIT: FWIW, I saw some comments about this being solved in SQL Server 2012 and later. In our case we're running the 2014 edition in a Docker container and still have this issue; so the version doesn't seem to matter.

[daffl]

I added documentation for this to feathers-sequelize at https://github.com/feathersjs-ecosystem/feathers-sequelize#working-with-mssql - decided against adding a default sort order since it is unsure how that would affect all the other databases that are working.