Tedious or Sequelize uses the wrong syntax for `findOne ()`

Question

I am using Sequelize with Tedious to access SQL Server 2008.

When I do sequelizeModel.findOne() , I get this exception -

Raw reject SequelizeDatabaseError: Incorrect use of the NEXT option in the FETCH statement.

I know that SQL Server 2008 does not support OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY , and therefore an exception is thrown.

But I also explicitly installed tdsVersion in the tedious 7_3_B options.

As described here -
http://pekim.imtqy.com/tedious/api-connection.html

I have tried all versions of tds, and the query syntax that is generated always contains the FETCH/NEXT syntax.

Am I missing something?

Should the syntax be specific to the tds version?

I also confirmed that the tdsVersion parameter tdsVersion successfully passed to the tedious connection library from sequelize.

An example of the generated query syntax is

 SELECT [id], [FIRST_NAME], [LAST_NAME] FROM [USERs] AS [USERS] ORDER BY [id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY; 

Answer 1

This is a problem in Sequelize — it uses the OFFSET FETCH syntax, which is only supported in SQL Server 2012 and later.

I presented this as a problem on GitHub: https://github.com/sequelize/sequelize/issues/4404

The problem also affects the findById method. A workaround for this method is to use findAll with where to specify an identifier and just use only the first element from the returned array:

 Thing.findAll({ where: {id: id} }).then( function(things) { if (things.length == 0) { // handle error } doSomething(things[0]) }).catch( function(err) { // handle error }); 

Answer 2

If you can change the sequelize library in node modules. Please follow the instructions: Go to Node_modules → sequelize → lib → mssql → query-generator.js

You will find this line:

  fragment += ' OFFSET ${this.escape(offset)} ROWS'; 

Add a line above it:

  fragment += ' ORDER BY ${this.quoteTable(options.tableAs || model.name)}.${this.quoteIdentifier(model.primaryKeyField)}'; 

It should look like this:

  fragment += ' ORDER BY ${this.quoteTable(options.tableAs || model.name)}.${this.quoteIdentifier(model.primaryKeyField)}'; fragment += ' OFFSET ${this.escape(offset)} ROWS'; 

Answer 3

I have the same problem using Sequelize v4.42.0 and SQL Server 2008 R2 (SP1). Studying the source code of SequelizeJS, I found in the file lib / dialects / mssql / query-generator.js you have the selectFromTableFragment function on line 821 of this version. On line 826, there is an if statement that checks the version number of SQL Server using the databaseVersion parameter from the Sequelize parameter object.

 // Handle SQL Server 2008 with TOP instead of LIMIT if (semver.valid(this.sequelize.options.databaseVersion) && semver.lt(this.sequelize.options.databaseVersion, '11.0.0')) { 

This option is missing in the docs from http://docs.sequelizejs.com , I searched for this option and did not find it. In this option, I set the version number of my SQL Server ("10.50.2500" - the equivalent of 2008 R2 SP1), and it worked. A query is now created using SELECT TOP ... rather than OFFSET AND FETCH NEXT.

I expect this to help other people who have this problem, like me.

Answer 4

An exception to the "sequeIize.authenticate" method immediately after initialization solves the problem for me. I don’t know what is going on here, but I had the same error.

 SequelizeDatabaseError: Invalid usage of the option NEXT in the FETCH statement. at Query.formatError (C:\xampp\htdocs\Benoit\node_modules\sequelize\lib\dialects\mssql\query.js:315:12) at Request.connection.lib.Request [as userCallback] (C:\xampp\htdocs\Benoit\node_modules\sequelize\lib\dialects\mssql\query.js:107:25) at Request._this.callback (C:\xampp\htdocs\Benoit\node_modules\tedious\lib\request.js:60:27) at Connection.endOfMessageMarkerReceived (C:\xampp\htdocs\Benoit\node_modules\tedious\lib\connection.js:1922:20) at Connection.dispatchEvent (C:\xampp\htdocs\Benoit\node_modules\tedious\lib\connection.js:1004:38) at Parser.<anonymous> (C:\xampp\htdocs\Benoit\node_modules\tedious\lib\connection.js:805:18) at emitOne (events.js:116:13) at Parser.emit (events.js:211:7) at Parser.<anonymous> (C:\xampp\htdocs\Benoit\node_modules\tedious\lib\token\token-stream-parser.js:54:15) at emitOne (events.js:116:13) at Parser.emit (events.js:211:7) at addChunk (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_readable.js:291:12) at readableAddChunk (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_readable.js:278:11) at Parser.Readable.push (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_readable.js:245:10) at Parser.Transform.push (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_transform.js:148:32) at Parser.afterTransform (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_transform.js:91:10) at Parser._transform (C:\xampp\htdocs\Benoit\node_modules\tedious\lib\token\stream-parser.js:69:9) at Parser.Transform._read (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_transform.js:184:10) at Parser.Transform._write (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_transform.js:172:83) at doWrite (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_writable.js:428:64) at writeOrBuffer (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_writable.js:417:5) at Parser.Writable.write (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_writable.js:334:11) 

I got an error after a brief restructuring of my code in the project, and I understand that, simply because the history of my sublime kept it, I simply deleted all the extra code in the project, and the proven method was commented out.

I don’t know what is the relationship between the initial authentication method and the grammar and query syntax, I don’t even think it should be relevant, but what is it?

Answer 5

I am using select @@version ->

 Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17134: ) (Hypervisor) 

and got the same error. sequelize.authenticate() not sequelize.authenticate() this.

See also: https://github.com/tediousjs/tedious/issues/872

For me the root cause . Essentially, you need ORDER BY for OFFSET to make sense. @prayag's answer above adds that ORDER BY.