Guide to Node.js PostgreSQL Pagination

Pagination is a common technique used in web development to manage and display large datasets in smaller, more manageable chunks. In this guide, we'll explore how to implement pagination using Node.js and PostgreSQL

Prerequisites

Before you begin, make sure you have the following installed:

• Node.js: Download and install Node.js.
• PostgreSQL: Download and install PostgreSQL.

Additionally, you may want to use a package manager like npm (Node Package Manager) to manage your project dependencies.

Setting Up a Node.js Project

Create a new directory for your project:

mkdir node-postgresql-pagination
cd node-postgresql-pagination

Initialize a new Node.js project:

npm init

Install necessary packages:

npm install express pg

Setting Up PostgreSQL Database

Create a PostgreSQL database and a table for demonstration purposes. Open a PostgreSQL shell and run:

CREATE DATABASE your_database_name;

Replace your_database_name with the desired name for your database.

Connect to the newly created database:

\c your_database_name;

Create a sample table and insert some data:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO users (name, email) VALUES
    ('John Doe', 'john@example.com'),
    ('Jane Doe', 'jane@example.com'),
    -- Add more data as needed
    ;

Creating a Node.js Server

Create a file named index.js:

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

const app = express();
const port = 3000;

const pool = new Pool({
    user: 'your_postgres_user',
    host: 'localhost',
    database: 'your_database_name',
    password: 'your_postgres_password',
    port: 5432,
});

app.get('/users', async (req, res) => {
    const page = req.query.page || 1;
    const pageSize = 10; // Adjust as needed

    const offset = (page - 1) * pageSize;

    try {
        const result = await pool.query(
            'SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2',
            [pageSize, offset]
        );

        res.json(result.rows);
    } catch (error) {
        console.error('Error executing query', error);
        res.status(500).send('Internal Server Error');
    }
});

app.listen(port, () => {
    console.log(`Server is running at http://localhost:${port}`);
});

In this code snippet:

• The page parameter is extracted from the query string (req.query.page). If not provided, it defaults to page 1.
• The offset is calculated based on the page number and page size to determine the starting point for fetching records from the database.
• The actual database query is executed using the pool.query method. The result is sent as a JSON response (res.json(result.rows)).
• If an error occurs during the database query, an error message is logged, and a 500 Internal Server Error response is sent.

Lastlyt, you can run your Node.js server:

node index.js

Visit http://localhost:3000/users?page=1 in your browser to see the first page of users. Change the page query parameter to navigate through the pages.

Conclusion

You have successfully implemented basic pagination using Node.js and PostgreSQL. This is a simple example, and you can extend it based on your project requirements.

Thank you for reading