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

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
});

Checking code node_modules/sequelize/lib/dialects/mssql/query-generator.js saw that part

const dbVersion = this.sequelize.options.databaseVersion;
const isSQLServer2008 = semver.valid(dbVersion) && semver.lt(dbVersion, '11.0.0');

So I just added my connection configuration:

production: {
        dialect: 'mssql',
        databaseVersion: '10.50.6000',
        host: process.env.DB_HOST,
        username: process.env.DB_USER,
        password: process.env.DB_PASS,
        database: process.env.DB_NAME,
        dialectOptions: {
            options: {
                useUTC: false,
                dateFirst: 1,
                enableArithAbort: true,
                encrypt: false,
            },
        },
    },

I have the same problem using sequelize v4.42.0 and SQL Server 2008 R2 (SP1). Inspecting the source code of SequelizeJS, I found in file lib/dialects/mssql/query-generator.js you have selectFromTableFragment function in line 821 of that version. In 826 line exists an if statement that verify the version number of SQL Server via the databaseVersion option, from the Sequelize options 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 not present in the docs from http://docs.sequelizejs.com, I searched for this option and did not found. In this option I setup my SQL Server version number ('10.50.2500' - Equivalent to 2008 R2 SP1) and this did works. The query now is created using SELECT TOP ... and not OFFSET AND FETCH NEXT.

I expect that helps other people that are having this issue like me.