Languages
[Edit]
EN

Express.js / PostgreSQL - rows pagination

0 points
Created by:
Aaron1
568

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.

Node.js - PostgreSQL - rows pagination
Data used in the example - HeidiSQL

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');
});
Node.js / Express.js - rows pagination
http://localhost:3000/users?page=1&size=3 - fetch 3 rows from page 1.
http://localhost:3000/users?page=3&size=2 - fetch 2 rows from page 3.

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');
Donate to Dirask
Our content is created by volunteers - like Wikipedia. If you think, the things we do are good, donate us. Thanks!
Join to our subscribers to be up to date with content, news and offers.

Node.js / Express.js

Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join