UGC bg

How to access a PostgreSQL from Node.js application


Access a PostgreSQL database from a 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 over the past years makes it an unavoidable platform in the development world. To learn more about the capabilities of the Node.js platform refer to the official documentation.

PostgreSQL is one of the most famous databases in the world. Its simplicity of use and open source approach are major points to its large adoption. To learn more about the capabilities of PostgreSQL refer to the official documentation.

 

Requirements

This tutorial assumes that you have an OVHcloud Public Cloud Compute Instance, VPS, or bare metal server running Ubuntu 22.04 and basic knowledge using the command line. In this tutorial, we've used a Public Cloud Compute instance. If you need help setting up a Public Cloud instance with Ubuntu 22.04, follow this guide: Creating and connecting to your first Public Cloud instance.

As a prerequisite, you will also need to have Node.js and Postgre installed on your instance. If needed, the following guides can help you.

How to install Node.js on Ubuntu 22.04.
How to install PostgreSQL on Ubuntu 22.04.

You can use an IDE to facilitate the manipulation of source files outlined in this tutorial. Have a look at VS Code or WebStorm.

 

Instructions

Install the Node.js node-postgres library so that it can be used by a Node.js application.

At the time of creating this tutorial, the latest LTS version of node-postgres library available was 8.7.3.

 

Initialize the project

Create a folder named nodejs-pg-example and run the NPM init command:

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

The output should look 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

We assume that you have already installed your PostgreSQL database.

Create a table named example_table:

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

The output should look 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 look 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 the PostgreSQL database

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

npm install pg

The output should look 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 the database with the application

Create a file named  HelloWorld.js in the nodejs-pg-example folder and paste the following code into the file:

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 following code:

node HelloWorld.js

The output should look like this:

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

Congratulations, you have successfully written your first Node.js application able to access a PostgreSQL database.