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

Question

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

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

Unhandled rejection SequelizeDatabaseError: Invalid usage of the option NEXT in the FETCH statement.

I know SQL Server 2008 doesn't support OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY and that is why the exception is thrown.

But I have also explicitly set the tdsVersion in the tedious options to 7_3_B.

As described here -
http://pekim.github.io/tedious/api-connection.html

I've tried all the tds versions and the query syntax that is generated always contains the FETCH/NEXT syntax.

Am I missing something?

Shouldn't the syntax be specific to the tds version?

I've also verified that the tdsVersion option is being passed successfully to the tedious connection library from sequelize.

Example of query syntax generated -

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 an issue in Sequelize -- it uses the OFFSET FETCH syntax, which is only supported in SQL Server 2012 and newer.

I submitted this as an issue on GitHub: https://github.com/sequelize/sequelize/issues/4404

The issue also affects the findById method. A workaround for that method is to use findAll with a where to specify the ID, and just only use 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
});