Node.js & PostgreSQL Integration

Introduction

In this article, we will be talking about interacting with a PostgreSQL database using Node.js. One of the best ways to do this is using the node-postgres client. It is one of the most popular modules for this purpose and one of the more mature ones. It consists of the following modules:

Setup

You can install it using NPM:

1$ npm init

1$ npm install pg

Usage

There are two ways in which you can connect to a PostgreSQL database. One of them is to use a single client, and the other is to use a connection pool.

Connection pools are used when you are making frequent queries to your database. This is because of the following reasons:

client connection is slow, and it can take up to 30 milliseconds;
the number of clients connected to the PostgreSQL server is limited by server memory;
PostgreSQL can only process one query at a time on a single connected client in a first-in first-out manner.

Examples

Client connection:

1const { Client } = require('pg')
2const client = new Client()
3client.connect()

Client() uses environment variables by default to connect to a certain database. Here’s how those variables look like by default:

1PGHOST='localhost'
2PGUSER=process.env.USER
3PGDATABASE=process.env.USER
4PGPASSWORD=null
5PGPORT=5432

You can, of course, change these values.

Another way of specifying which database to connect to is bypassing a config object as a parameter:

1const client = new Client({
2    user: 'dbuser',
3    host: 'database.server.com',
4    database: 'dbname',
5    password: 'dbpassword',
6    port: 5432 // The client connects to this port automatically if it's not specified 
7});

Pool connection:

You can connect using a pool in the same ways - with or without environment variables. You have to use the Pool object instead of the Client object:

1const { Pool } = require('pg')
2
3const pool = new Pool({
4  user: 'dbuser',
5    host: 'database.server.com',
6    database: 'dbname',
7    password: 'dbpassword',
8    port: 5432
9})
10
11pool.connect()

Both the pool and the client can be initialized using a connection string because of the pg-connection-string module:

1const { Pool } = require('pg')
2const connectionString = 'postgresql://dbname:dbpassword@database.server.com:5432/mydb'
3
4const pool = new Pool({
5  connectionString: connectionString,
6})
7
8pool.connect()

1const { Client } = require('pg')
2const connectionString = 'postgresql://dbname:dbpassword@database.server.com:5432/mydb'
3
4const client = new Client({
5  connectionString: connectionString,
6})
7
8client.connect()

Queries

The way you interact with the connected database is with queries passed into the query() method. The parameter you pass into query() can be an SQL string, or if you want the query to have parameters, it is recommended that you give it an object. You will see how this is done later.

Querying can be done in 3 ways: using callbacks, using promises, and using async/await. Examples will be shown using the client as a connection.

Examples

Callback:

1const query = // SQL query
2client.query(query, (err, res) => {
3  if (err) {
4    console.log(err.stack)
5  } else {
6    console.log(res.rows[0])
7  }
8})

Promise:

1const query = // SQL query
2client
3  .query(query)
4  .then(res => console.log(res.rows[0]))
5  .catch(e => console.error(e.stack))

Await/Async:

1const query = // SQL query
2const res = await client.query(query)
3console.log(res.rows[0].message)

Parameters

If your query doesn’t have parameters, you don’t even need to use the query variable, and you can pass an SQL string directly to the query() method. But if you want it to have parameters, you shouldn’t use string concatenation. This is because it can lead to SQL injection vulnerabilities. For these reasons, node-postgres allows parameterized queries, like so:

1const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'
2const values = ['john', 'johndoe@gmail.com']
3
4client
5  .query(text, values)
6  .then(res => console.log(res.rows[0])) // { name: 'john', email: 'johndoe@gmail.com' }
7  .catch(e => console.error(e.stack))

You can also pass in a single query object instead of passing a string and an array:

1const query = {
2  text: 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *',
3  values: ['john', 'johndoe@gmail.com']
4}
5
6client
7  .query(query)
8  .then(res => console.log(res.rows[0]))
9  .catch(e => console.error(e.stack))

Conclusion

As you can see, node-postgres is pretty easy to use and is pretty useful. There are a few more features it has like:

transaction queries;
custom type serialization and parsing;
native bindings;
SSL connections;
query configuration (type parsers, prepared statements…).

You can check all of these out in the official documentation.

If you would like to see a more hands-on approach, I recommend watching this video.