Express.js / PostgreSQL - rows pagination

In this article, we would like to show you how to paginate data rows in PostgreSQL using Express.js.

Note:

Pagination consists in dividing data into pages and sending only those rows that the client currently needs - we do not have to send all data at once.

Note: at the end of this article you can find database preparation SQL queries.

Practical example

const express = require('express');
const { Pool } = require('pg');

const app = express();

const pool = new Pool({
    host: '127.0.0.1',
    user: 'my_username',
    database: 'my_database',
    password: 'my_password',
    port: 5432,
    max: 10, // max number of clients in the pool
    idleTimeoutMillis: 30000, // how long a client is allowed to remain idle before being closed
    connectionTimeoutMillis: 2000
});

app.get('/users', async (req, res) => {
    try {
        const client = await pool.connect(); // creates connection
        const { page, size } = req.query;
        const query = `
                SELECT *
                FROM "users"
                ORDER BY "users"."id"
                LIMIT $2
                OFFSET (($1 - 1) * $2);
        `;
        try {
            const { rows } = await client.query(query, [page, size]); // sends query
            res.status(200).json(rows);
        } finally {
            await client.release(); // releases connection
        }
    } catch (error) {
        return res.status(500).json(error);
    }
});

app.listen(3000, () => {
    console.log('listening at http://localhost:3000');
});

Database preparation

create_tables.sql file:

CREATE TABLE "users" (
	"id" SERIAL,
	"name" VARCHAR(50) NOT NULL,
	"surname" VARCHAR(50) NOT NULL,
	"department_id" INTEGER,
    "salary" DECIMAL(15,2) NOT NULL,
	PRIMARY KEY ("id")
);

insert_data.sql file:

INSERT INTO "users"
	( "name", "surname", "department_id", "salary")
VALUES
	('John', 'Stewart', 1, '2000.00'),
	('Chris', 'Brown', 3, '2000.00'),
	('Chris', 'Lewis', 3, '2000.00'),
	('Kate', 'Lewis', 3, '2000.00'),
	('Kate', 'Stewart', 3, '2000.00'),
	('Ailisa', 'Lewis', 3, '2000.00'),
	('Ailisa', 'Gomez', 3, '3000.00'),
	('Gwendolyn', 'James', 2, '3000.00'),
	('Simon', 'James', 2, '2000.00'),
	('Simon', 'Brown', 3, '2000.00'),
	('Simon', 'Collins', 3, '3000.00');