Adding pagination to knex.js

knex-pagination.js

module.exports = function(dbConfig) {
	var knex = require('knex')(dbConfig);

	var KnexQueryBuilder = require('knex/lib/query/builder');

	KnexQueryBuilder.prototype.paginate = function (per_page, current_page) {
		var pagination = {};
		var per_page = per_page || 10;
		var page = current_page || 1;
		if (page < 1) page = 1;
		var offset = (page - 1) * per_page;
		return Promise.all([
				this.clone().count('* as count').first(),
				this.offset(offset).limit(per_page)
			])
			.then(([total, rows]) => {
				var count = total.count;
				var rows = rows;
				pagination.total = count;
				pagination.per_page = per_page;
				pagination.offset = offset;
				pagination.to = offset + rows.length;
				pagination.last_page = Math.ceil(count / per_page);
				pagination.current_page = page;
				pagination.from = offset;
				pagination.data = rows;
				return pagination;
			});
	};

	knex.queryBuilder = function () {
		return new KnexQueryBuilder(knex.client);
	};

	return knex;
}

Hi, how to use this function?

This didn't exactly work for me as it's injecting the count into the actual query. I ended up with something that references the top level knex function .. which I'd like to be able to reference in a more relational fashion, so if you see a way to do that please comment.
(didn't format it in the same manner, but you'll see the idea)

import KnexQueryBuilder from 'knex/lib/query/builder'
import pg from 'pg'

const config = { ... } // your config

const knexConn = require('knex')(config)

KnexQueryBuilder.prototype.paginate = function (per_page, current_page) {
  const page = Math.max(current_page || 1, 1)
  const offset = (page - 1) * per_page
  const clone = this.clone()

  return Promise.all([
      this.offset(offset).limit(per_page),
      knexConn.count('*').from(clone.as('t1')),
    ])
    .then(([rows, total]) => {
      const count = parseInt(total.length > 0 ? total[0].count : 0)
      return {
        total: parseInt(count),
        per_page: per_page,
        offset: offset,
        to: offset + rows.length,
        last_page: Math.ceil(count / per_page),
        current_page: page,
        from: offset,
        data: rows,
      }
    })
}

knexConn.queryBuilder = function () {
  return new KnexQueryBuilder(knexConn.client)
}

export const knex = knexConn
}

You can use it doing

knex('books').select('id', 'title').orderBy('title').paginate(10, 1)

Why do we need to re-instantiate knex's querybuilder? (i.e. this code below)

knex.queryBuilder = function () {
  return new KnexQueryBuilder(knex.client);
};

Doesn't changing the prototype for the QueryBuilder affect the existing instance?
And it seems to be working fine without this last part!

Thanks, everyone here is my own implementation

/*import your knex config*/
const db = require('../data/dbConfig');

/*create a function for the database model for the table you want to paginate
in this case our table name is `sampleTable`
here `perPage` is the number of data we want per page and `currentPage` specifies the page number*/
const functionName = async (perPage, currentPage) => {
    const pagination = {};
    const limitPerPage = perPage || 10;
    const page = Math.max(currentPage || 1, 1);
    const offset = (page - 1) * perPage;
    return Promise.all([
      await db('sampleTable')
        .clone()
        .count('* as count')
        .first(),
      await db('sampleTable')
        .limit(limitPerPage)
        .offset(offset)
    ]).then(([total, rows]) => {
      const { count } = total;
      pagination.total = parseInt(count, 10);
      pagination.perPage = perPage;
      pagination.offset = offset;
      pagination.to = offset + rows.length;
      pagination.last_page = Math.ceil(count / perPage);
      pagination.currentPage = page;
      pagination.from = offset;
      pagination.data = rows;
      return pagination.data;
    });
  };

No need to make 2 requests, this thing could work:

db('sampleTable')
.limit(perPage)
.offset(offset)
.select(['*'])
.select(knex.raw('count(id) OVER() as total'));

Hi,
I'm try this with knex 0.20.14 and i got error:
TypeError: Cannot assign to read only property 'queryBuilder'
on the line:
knex.queryBuilder = function queryBuilder()

I changed this to:
knex.context.queryBuilder = function queryBuilder()
and now it works.

Place it inside whenever folder name u want, in my case, under util folder
util / search.js

class Search {
constructor(query, queryString) {
this.query = query;
this.queryString = queryString;
this.tableName = this.query.and._single.table;
}
pagination(resPerPage) {
let { queryString, query, tableName } = this;

let { page } = queryString;
if (page < 1) page = 1;
let offset = (page - 1) * resPerPage;

query
  .select("name", "price", "description", "ratings", "stock")
  .from(`${tableName}`)
  .offset(offset)
  .limit(resPerPage);

return this;

}
}

module.exports = SearchQuery;

on the other part:
// under the controller folder, create a file. "in my case, it is product.js"

const query = require("../config/database/database"); // this is came from knex configuration
const SearchQuery = require("../utils/searchQuery");

exports.getProducts = catchAsyncErrors(async (req, res, next) => {
const resPerPage = 5;
const search = new SearchQuery(query("products"), req.query)
.pagination(resPerPage);

const products = await search.query;
res.status(200).json({
success: true,
products,
});
});

this is working. kindly see screenshot below.