CODEWITHSUNDEEP
postgresqlsequelize.js
user5262021
user5262021

Reputation:

How to implement cursor pagination using Sequelize

Ive been looking for a way to implement cursor pagination using Sequelize and Postgres, I can't find any clear basic examples as to how to do this. I know that there are npm packages which can help but there doesn't seem to be any clear instruction on how to use them.

Upvotes: 8

Views: 6161

Answers (4)

b9s
b9s

Reputation: 557

The sequelize-cursor-pagination package emulates a cursor using pagination.
According to the Postgres documentation, this does not provide a stable result if an ORDER BY clause is not given, and can be inefficient in case of a large OFFSET.

I haven't found any existing package that uses a cursor for sequelize, but I was able to get it to work using some workarounds.

import { Client } from 'pg';
import { Attributes, FindOptions, Model, ModelStatic } from 'sequelize';
import Cursor = require('pg-cursor');

export async function* findAllBatched<M extends Model>(
  model: ModelStatic<M>,
  options: FindOptions<Attributes<M>>,
  batchSize: number = SEQUELIZE_BATCH_SIZE,
) {
  let client: Client;
  let cursor: Cursor<M>;
  try {
    const queryGenerator: any = sequelize.getQueryInterface().queryGenerator;
    client = (await sequelize.connectionManager.getConnection({ type: 'SELECT' } as any)) as Client;
    delete options.raw;
    const query = queryGenerator.selectQuery(model.tableName, options, model) as string;
    cursor = client.query(new Cursor<M>(query));
    while (true) {
      const queryResult = await cursor.read(batchSize);
      if (queryResult.length === 0) {
        break;
      }
      yield queryResult;
    }
  } finally {
    await cursor?.close();
    sequelize.connectionManager.releaseConnection(client);
  }
}

I use Sequelize to provide a Postgres Client and to generate the query based on the provided Model class and the findOptions.
Instead of letting Sequelize run the query, I run the query directly on the pg Client using pg-cursor.
I then read the rows in batches, and yield them to create an async iterator.

Usage could be as follows:

for await (const rows of findAllBatched(MyModel, {
  where: {
    foo: 'bar',
  },
  raw: true,
})) {

  await doSomethingWithResult(rows);
}

In my case, I didn't need Sequelize model instances, so I've returned the raw result. If you need model instance, you could pass them through MyModel.bulkBuild.

My use case also didn't require any of the more complex sequelize features, so I haven't tested the full extent of what is supported with this method, but at least it should be able to reduce the memory load when dealing with very large tables.

Upvotes: 0

tqvthu
tqvthu

Reputation: 1

sequelize-cursor-pagination. is a quite package for pagination. But it is not good for performance now, because it consumes 3 queries,especially is query used to count all row.

Upvotes: 0

mrded
mrded

Reputation: 5290

There is a package for that sequelize-cursor-pagination.

Upvotes: 0

Jeremy Warren
Jeremy Warren

Reputation: 43

I know that I'm a little late, but I was able to implement cursor pagination by doing something like this.

const Sequelize = require('sequelize');
const Op = Sequelize.Op;

const getUsers = async (lastId, limit) => {
    const cursor = lastId || 0;
    return await User.findAll({
        limit: limit
        where: {
            id: {
                [Op.gt]: cursor
            }
        }
    });
}

Upvotes: 3

Related Questions