How To Use PostgreSQL With Node.js

PostgreSQL is a powerful, open source object-relational database system. To connect and use PostgreSQL from NodeJS, we will be using node-postgres and pg-cursor library.

In this blog, we will explain

1. How to connect to PostgreSQL
2. How to use connection pool
3. How to create a transcation
4. How to retrieve large data set using cursor.

node-postgres is a non-blocking PostgreSQL client for Node.js. It has support for callbacks, promises, async/await, connection pooling, prepared statements, cursors, streaming results, C/C++ bindings and lot more. node-postgres is not a ORM library, It is a direct client to work with Postgres. This means, if you are good in SQL and capable to write SQL queries then using this library will get to data faster from the database.

Lets start by installing the dependency.

npm install pg

In this blog, we connect to PostgreSQL using node-postgres (pg) and will insert, retrieve student records.

In PostgreSQL, create a database studentdb with student table.

CREATE TABLE student(
  id SERIAL PRIMARY KEY,
  name text,
  email text,
  city text, 
  created_on timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL	
);

CREATE TABLE student_score(
  id int,
  score int	
);

First try to connect to database, execute the query and then close the connection. Below code will fetch student data and display it in the console.

// Client.js

import pg from 'pg';
const { Client } = pg;

const config = {
    host: '127.0.0.1',
    port: 5432,
    database: 'studentdb',
    user: '<dbuser>',
    password: '<dbpassword>'
}

const client = new Client(config)
await client.connect();

const res = await client.query('SELECT * FROM student');
console.log(res.rows[0]);
await client.end();

The above code has some drawbacks, It connects and disconnects for every query or set of queries. Creating client and managing database connection object is a costly operation. We should not do it more often.

To overcome the scenario, we need to create a connection pool, where a pool of connections will be created and it will be available for us to use. In an asyncronous scenario, where any function could make a call to DB, maintaing connection pool is ideal where Pool object first acquires a connection, executes the query and later releases it back to the pool.

Let's create ConnectionPool

// ConnectionPool.js

import pg from 'pg';
const { Pool } = pg;

const config = {
    host: '127.0.0.1',
    port: 5432,
    database: 'studentdb',
    user: '<dbuser>',
    password: '<dbpassword>',
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
}

const pool = new Pool(config)

Config Parameters

host: Host name or IP where PostgreSQL is running

port: Port number of PostgreSQL server

database: Name of the database to connect.

user: Database user.

password: Database password

max: Maximum number of clients the pool should contain

idleTimeoutMillis: Number of milliseconds a client must sit idle in the pool and not be checked out before it is disconnected from the backend and discarded

connectionTimeoutMillis: Number of milliseconds to wait before timing out when connecting a new client by default this is 0 which means no timeout

 

Execute Query

Now let's try to insert data in to the table.

async function createStudent() {

    const studentParams = [
        ['Sammy',  'sammy@example.com',    'Chennai'],
        ['kishore','kishore@example.com', 'Bangalore'],
        ['Vikram', 'vikram@example.com',   'Pune']
    ]

    const SQL = `INSERT into student(name, email, city) VALUES($1, $2, $3)`

    for (const param of studentParams) {

        const result = await pool.query(SQL, param);

        console.log(`Total rows inserted : ${result.rowCount}`);

    }
   
}

Once the student data is inserted, now lets try to retrieve it.

async function getStudents() {

    const resultSet = await pool.query('SELECT * FROM student');

    console.log(`Total Rows: ${resultSet.rowCount}`);

    console.log(`ID --  Name --     Email --    City`)
    
    for (const student of resultSet.rows) {

        console.log(`${student.id} -- ${student.name} -- ${student.email} -- ${student.city}`)

    }

}

If we have more number of records then we need to retrieve in batches. Below is the traditional way to get data in batches.

async function getStudentsInBatch() {

    const BATCH_LIMIT = 10;

    let start = 0;

    let rowCount = 0;
    
    do {

        const resultSet = await pool.query('SELECT * FROM student offset $1 limit $2', [start, BATCH_LIMIT]);

        rowCount = resultSet.rowCount;

        if (rowCount == 0) break;

        for (const student of resultSet.rows) {

            console.log(`${student.id} -- ${student.name} -- ${student.email} -- ${student.city}`)

        }

        start += BATCH_LIMIT;

    }while (rowCount == BATCH_LIMIT);

}

Connection pool advantage is basically handle multiple queries in parallel. Each query will get executed in different connection.

What if we need to execute a transcation? We want all sequence of query to be executed using same connection object, so that we can commit or rollback. Below code helps to achieve transcation.

async function transcation() {

    const client = await pool.connect()
 
    try {
        await client.query('BEGIN')
        const SQL = `INSERT into student(name, email, city) VALUES($1, $2, $3) RETURNING id`
        const res = await client.query(SQL, ['kapoor', 'kapoor@gmail.com', 'Delhi'])
        
        const studentScoreSql = 'INSERT INTO student_score(id, score) VALUES ($1, $2)'
        await client.query(studentScoreSql, [res.rows[0].id, 91])
        await client.query('COMMIT')
    } 
    catch (e) {
        await client.query('ROLLBACK')
        throw e
    } 
    finally {
        client.release()
    }
}

In the above example, we are first insert student record in student table which returns an id. Using the student id we are inserting their score in student_score table. Once both statements are executed fine, then commit the transcation otherwise do rollback. From the connection pool, acquire a connection and execute sequence of queries in the transcation and later release it back to the pool.

Use Cursor to retrieve large result set

Consider you have large dataset and you want to retireve them. The standard way of retrieving records in batches will lead to out of memory error as it has to keep all data in memory. To resolve that, we need to use cursor to retrieve or stream large result sets.

In order to use cursor, use pg-cursor library.

npm install pg-cursor

// Cursor.js

import pg from 'pg';
import Cursor from 'pg-cursor'

const { Pool } = pg;

const config = {
    host: '127.0.0.1',
    port: 5432,
    database: 'studentdb',
    user: '<dbuser>',
    password: '<dbpassword>',
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
}

const pool = new Pool(config)

const client = await pool.connect()

const cursor = client.query(new Cursor('select * from student'))

const batchSize = 10;

let rows

do {
    rows = await cursor.read(batchSize);
 
   if (rows.length == 0) break;
    
    for (const student of rows) {

        console.log(`${student.id} -- ${student.name} -- ${student.email} -- ${student.city}`)

    }
}
while(rows && rows.length == batchSize);

client.release();

1) We are retrieving a client object from the pool and creating a cursor using the client.

2) Read records in batches using the cursor, it will be reading stream of records.

3) Once reading all the records, release the client back to the pool.

Conclusion

PostgreSQL is a powerful database and using node-postgres and pg-cursor will connect natively and executes the queries faster. If we use any ORM, there are chances of degrading the performance. If you want solid performance and you are good in SQL then prefer node-postgres and pg-cursor.

Source code available in Github