How to access a PostgreSQL from Node.js application ?

Objective

In this tutorial, you will learn how to access a PostgreSQL database from a Node.js application.

Node.js is one of the most famous asynchronous event-driven JavaScript runtimes. Its wide adoption in the past years makes it an unavoidable platform in the development world. To have more information and the capabilities of the Node.js platform see the official documentation.

PostgreSQL is one of the most famous databases in the world. Its simplicity of use and the Open Source approach are major points to its large adoption. To have more information and the capabilities of PostgreSQL see the official documentation.

Requirements

This tutorial assumes that you have an Ubuntu 22.04, running in an OVHcloud Compute Instance for example, and some basic knowledge of how to operate it. If you don’t have a running Ubuntu 22.04, follow the guide to use an OVHcloud Compute Instance.

To know how to install Node.js platform see the tutorial How to install Node.js on Ubuntu 22.04.
To know how to install PostgreSQL see the tutorial How to install PostgreSQL on Ubuntu 22.04.

Instructions

In this tutorial, you will, first, install the Node.js node-postgres library, then, you will use it in a Node.js application.

Initialize the project

First, create a folder nodejs-pg-example and run the NPM init command:

mkdir nodejs-pg-example cd nodejs-pg-example npm init

The output should be like this:

$ mkdir nodejs-pg-example
$ cd nodejs-pg-example
$:~/nodejs-pg-example$ npm init
This utility will walk you through creating a package.json file.
It only covers the most common items and tries to guess sensible defaults.

See `npm help init` for definitive documentation on these fields
and exactly what they do.

Use `npm install ` afterwards to install a package and
save it as a dependency in the package.json file.

Press ^C at any time to quit.
package name: (nodejs-pg-example) 
version: (1.0.0) 
description: Example project to access PostgreSQL from a Node.js application
entry point: (index.js) 
test command: 
git repository: 
keywords: 
author: OVHcloud
license: (ISC) 
About to write to /home/ubuntu/nodejs-pg-example/package.json:

{
  "name": "nodejs-pg-example",
  "version": "1.0.0",
  "description": "Example project to access PostgreSQL from a Node.js application",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "OVHcloud",
  "license": "ISC"
}


Is this OK? (yes) yes

A package.json file has been created in the nodejs-pg-example folder:

{ "name": "nodejs-pg-example", "version": "1.0.0", "description": "Example project to access PostgreSQL from a Node.js application", "main": "index.js", "scripts": { "test": "echo \"Error: no test specified\" && exit 1" }, "author": "OVHcloud", "license": "ISC" }

Create PostgreSQL elements

First, create a table named example_table:

CREATE TABLE example_table (id INT PRIMARY KEY NOT NULL, message CHAR(50));

The output should be like this:

foo@ubuntu:~$ psql -d example
psql (14.2 (Ubuntu 14.2-1ubuntu1))
Type "help" for help.

example=> CREATE TABLE example_table (id INT PRIMARY KEY NOT NULL, message CHAR(50));
CREATE TABLE

Then, insert some data:

INSERT INTO example_table (id, message) VALUES (1, '👋 Hello World.'); INSERT INTO example_table (id, message) VALUES (2, '👋 Hola, mundo.');

The output should be like this:

example=> INSERT INTO example_table (id, message) VALUES (1, '👋 Hello World.');
INSERT 0 1
example=> INSERT INTO example_table (id, message) VALUES (2, '👋 Hola, mundo.');
INSERT 0 1
exit
foo@ubuntu:~$ 

Configure the application to access to PostgreSQL database

First, add the dependency to the node-postgres library in the package.json file. To do that, go in the nodejs-pg-example folder and use npm command to add the library:

npm install pg

The output should be like this:

~/nodejs-pg-example$ npm install pg

added 15 packages, and audited 16 packages in 2s

found 0 vulnerabilities

The package.json file has been updated by NPM:

{ "name": "nodejs-pg-example", "version": "1.0.0", "description": "Example project to access PostgreSQL from a Node.js application", "main": "index.js", "scripts": { "test": "echo \"Error: no test specified\" && exit 1" }, "author": "OVHcloud", "license": "ISC", "dependencies": { "pg": "^8.7.3" } }

Now, you are ready to write the Javascript code that will access the database.

Access to the database with the application

First, create a file HelloWorld.js in the folder nodejs-pg-example and paste the following code:

const { Client } = require('pg') async function sayHello() { const client = new Client({ user: 'foo', password: 'bar', database: 'example' }) await client.connect() const res = await client.query('SELECT * FROM example_table') console.log(res.rows[0].message) // 👋 Hello world. console.log(res.rows[1].message) // 👋 Hola, mundo. await client.end() } sayHello()

Then, execute the code:

node HelloWorld.js

The output should be like this:

$ node HelloWorld.js 
👋 Hello World.
👋 Hola, mundo.

That’s it, you have successfully written your first Node.js application to access to a PostgreSQL database.

Go further

Check the offers of public cloud instances on OVHcloud.