Paginate with Sequelize.js at SQL 2008 (that does not support FETCH)

Question

So, I'm using sequelize.js with tediousfor mssql support. MSSQL = SQL Server 2008

Well, So there is this big table named Clientes. I have to fetch data from it, but it is quite huge so I'll have to paginate trhough

I tried the 'right way', as in the docs:

app.use('/clientes', function(req, res){
      var page = req.params.page || 0;
      sequelize.Clientes.findAll({limit: 30, offset: 5  }) 
       .then(function(result) {
      res.json({result: result});
    });
});

It should work, BUT it uses a query that is something like

SELECT .......... from [Clientes] as [Clientes] ORDER BY [CNPJ] OFFSET 5 ROWS FETCH NEXT 30 ROWS ONLY; and the SQL Server return a terrible Invalid usage of the option NEXT in the FETCH statement!

So, after lots of search, I got to a point that apparently FETCH will not work with SQL 2008. That being said (and sad) I tried to force TDS version to 7_3_A. I did this using

dialectOptions: {
      tdsVersion: '7_3_A'
},

but the query sintax did not change with this :(

I'm not sure what to do anymore.... I even will use another way instead of paginate... I found a nice sintax to SQL pagination (involving row_number()) , but I was not able to perform it as a raw query on sequelyze. myModel.query('SQL QUERY HERE') returned undefined function oO'

Any helps will be appreciated

You need to call `sequelize.query` (the sequelize instance), not `myModel.query` (model). If you have a nice limit and offset implementation feel free to post it at https://github.com/sequelize/sequelize/issues or open a pull request — Jan Aagaard Meier, Sep 24 '15 at 13:48
@JanAagaardMeier, I'm sorry, I was wrong, i AM using sequelize.query('Select.....') and got the undefined function.. But I'll double check and let you know... Well what I have and should work is a simple SQL query that uses `ROW_NUMBER() OVER(ORDER BY...)` — user1576978, Sep 24 '15 at 16:54
Hey @JanAagaardMeier , I did `sequelize.query('SELECT * FROM (SELECT t.id, ROW_NUMBER() OVER (ORDER BY t.cnpj) as rownum FROM (SELECT t1.cnpj FROM Clientes t1) as t) AS u WHERE u.rownum > 5 AND u.rownum <= 10')` and got `typeError: undefined is not a function` I tried both for `require('sequelize')` and for my instance — user1576978, Sep 25 '15 at 13:01

score 1 · Accepted Answer · edited May 23 '17 at 12:16

Ok, so after lots of searching and debugging, I found 2 things.

1) My sequelize instance was sequelize.sequelize, due to a module.exports with lodash. My bad, I did not remember that, and did not even mention it in the question. I'm sorry about this.

2) The offset and limit do use fetch and thus generates invalid SQL sintax for SQL 2008.

I had to perform a raw query, using sequelize.sequelize.query() (since my instance was sequelize.sequelize).

The query used for pagination in mssql 2008 was found In this answer:

here's my full code, including express routing and ugly format for the query:

var express = require('express')
var app = express();

app.use('/tableName', function(req, res){
    var page = req.params.page || 2;
    var rowsPerPage = req.params.perpage || 30;

    if(rowsPerPage > 100){ 
        rowsPerPage = 100; //this limits how many per page
    }

    var theQuery = 'declare @rowsPerPage as bigint; '+
        'declare @pageNum as bigint;'+
        'set @rowsPerPage='+rowsPerPage+'; '+
        'set @pageNum='+page+';   '+
        'With SQLPaging As   ( '+
        'Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY ID asc) '+
        'as resultNum, * '+
        'FROM tableName )'+
        'select * from SQLPaging with (nolock) where resultNum > ((@pageNum - 1) * @rowsPerPage);';


    sequelize.sequelize.query(theQuery) 
     .spread(function(result) {
        res.json({result: result});
      });
});

And if you (as I did) wonder why sequelize.sequelize instead of only sequelize?

Well, this is because var sequelize is a require() from a file with the following module.exports:

 module.exports = lodash.extend({
  sequelize: sequelize, //reffers to new Sequelize(db,user,pass,{});
  Sequelize: Sequelize
 }, db)

So this is why just sequelize.query() was returned undefined, and sequelize.sequelize.query() works fine, because in true my sequelize variable is an Object with the sequelize property being the real sequelize connection instance.

Paginate with Sequelize.js at SQL 2008 (that does not support FETCH)

1 Answers1

Linked