How To Use PostgreSQL With Node.js on Ubuntu 20.04

The Node.js ecosystem provides a set of tools for interfacing with databases. One of those tools is node-postgres, which contains modules that allow Node.js to interface with the PostgreSQL database. Using node-postgres, you will be able to write Node.js programs that can access and store data in a PostgreSQL database.

In this tutorial, you’ll use node-postgres to connect and query the PostgreSQL (Postgres in short) database. First, you’ll create a database user and the database in Postgres. You will then connect your application to the Postgres database using the node-postgres module. Afterwards, you will use node-postgres to insert, retrieve, and modify data in the PostgreSQL database.

To complete this tutorial, you will need:

• A non-root user account with sudo privileges and a firewall enabled on Ubuntu 20.04. Follow our tutorial Initial Server Setup with Ubuntu 20.04 to setup your server.

• Node.js installed on Ubuntu. If you don’t have Node.js installed, follow How To Install Node.js on Ubuntu 20.04.

• PostgreSQL installed on your server. Follow the guide How To Install and Use PostgreSQL on Ubuntu 20.04 to install PostgreSQL on Ubuntu.

• Basic knowledge of how to write queries in PostgreSQL, see An Introduction to Queries in PostgreSQL for more details.

• Basics on how to write a Node.js program, see How To Write and Run Your First Program in Node.js.

• Basic understanding of how to write asynchronous functions in JavaScript. Read through our Understanding the Event Loop, Callbacks, Promises, and Async/Await in JavaScript tutorial to learn the basics.

In this step, you will create the directory for the node application and install node-postgres using npm. This directory is where you will work on building your PostgreSQL database and configuration files to interact.

Create the directory for your project using the mkdir command:

mkdir node_pg_app

Navigate into the newly created directory using the cd command:

cd node_pg_app

Initialize the directory with a package.json file using the npm init command:

 npm init -y

The -y flag creates a default package.json file.

Next, install the node-postgres module with npm install:

npm install pg

You’ve now set up the directory for your project and installed node-postgres as a dependency. You’re now ready to create a user and a database in Postgres.

In this step, you’ll create a database user and the database for your application.

When you install Postgres on Ubuntu for the first time, it creates a user postgres on your system, a database user named postgres, and a database postgres. The user postgres allows you to open a PostgreSQL session where you can do administrative tasks such as creating users and databases.

PostgreSQL uses ident authentication connection scheme which allows a user on Ubuntu to login to the Postgres shell as long as the username is similar to the Postgres user. Since you already have a postgres user on Ubuntu and a postgres user in PostgreSQL created on your behalf, you’ll be able to log in to the Postgres shell.

To login, switch the Ubuntu user to postgres with sudo and login into the Postgres shell using the psql command:

sudo -u postgres psql

The command’s arguments represents:

• -u: a flag that switches the user to the given user on Ubuntu. Passing postgres user as an argument will switch the user on Ubuntu to postgres.
• psql: a Postgres interactive terminal program where you can enter SQL commands to create databases, roles, tables, and many more.

Once you login into the Postgres shell, your terminal will look like the following:

postgres is the name of the database you’ll be interacting with and the # denotes that you’re logged in as a superuser.

For the Node application, you’ll create a separate user and database that the application will use to connect to Postgres.

To do that, create a new role with a strong password:

CREATE USER fish_user WITH PASSWORD 'password';

A role in Postgres can be considered as a user or group depending on your use case. In this tutorial, you’ll use it as a user.

Next, create a database and assign ownership to the user you created:

CREATE DATABASE fish OWNER fish_user;

Assigning the database ownership to fish_user grants the role privileges to create, drop, and insert data into the tables in the fish database.

With the user and database created, exit out of the Postgres interactive shell:

\q

To login into the Postgres shell as fish_user, you need to create a user on Ubuntu with a name similar to the Postgres user you created.

Create a user with the adduser command:

sudo adduser fish_user

You have now created a user on Ubuntu, a PostgreSQL user, and a database for your Node application. Next, you’ll log in to the PostgreSQL interactive shell using the fish_user and create a table.

In this section, you’ll open the Postgres shell with the user you created in the previous section on Ubuntu. Once you login into the shell, you’ll create a table for the Node.js app.

To open the shell as the fish_user, enter the following command:

 sudo -u fish_user psql -d fish

sudo -u fish_user switches your Ubuntu user to fish_user and then runs the psql command as that user. The -d flag specifies the database you want to connect to, which is fish in this case. If you don’t specify the database, psql will try to connect to fish_user database by default, which it won’t find and it will throw an error.

Once you’re logged in the psql shell, your shell prompt will look like the following:

fish denotes that you’re now connected to the fish database.

You can verify the connection using the \conninfo command:

\conninfo

You will receive output similar to the following:

Output
You are connected to database "fish" as user "fish_user" via socket in "/var/run/postgresql" at port "5432".

The output confirms that you have indeed logged in as a fish_user and you’re connected to the fish database.

Next, you’ll create a table that will contain the data your application will insert.

The table you’ll create will keep track of shark names and their colors. When populated with data, it will look like the following:

Using the SQL create table command, create a table:

CREATE TABLE shark(
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
color VARCHAR(50) NOT NULL);

The CREATE TABLE shark command creates a table with 3 columns:

• id: an auto-incrementing field and primary key for the table. Each time you insert a row, Postgres will increment and populate the id value.

• name and color: fields that can store 50 characters. NOT NULL is a constraint that prevents the fields from being empty.

Verify if the table has been created with the right owner:

\dt

The \dt command list all tables in the database.

When you run the command, the output will resemble the following:

         List of relations
 Schema | Name  | Type  |   Owner
--------+-------+-------+-----------
 public | shark | table | fish_user
(1 row)

The output confirms that the fish_user owns the shark table.

Now exit out of the Postgres shell:

\q

It will take you back to the project directory.

With the table created, you’ll use the node-postgres module to connect to Postgres.

In this step, you’ll use node-postgres to connect your Node.js application to the PostgreSQL database. To do that, you’ll use node-postgres to create a connection pool. A connection pool functions as a cache for database connections allowing your app to reuse the connections for all the database requests. This can speed up your application and save your server resources.

Create and open a db.js file in your preferred editor. In this tutorial, you’ll use nano, a terminal text editor:

nano db.js

In your db.js file, require in the node-postgres module and use destructuring assignment to extract a class Pool from node-postgres.

const { Pool } = require('pg')

Next, create a Pool instance to create a connection pool:

const { Pool} = require('pg')

const pool = new Pool({
  user: 'fish_user',
  database: 'fish',
  password: 'password',
  port: 5432,
  host: 'localhost',
})

When you create the Pool instance, you pass a configuration object as an argument. This object contains the details node-postgres will use to establish a connection to Postgres.

The object defines the following properties:

• user: the user you created in Postgres.
• database: the name of the database you created in Postgres.
• password: the password for the user fish_user.
• port: the port Postgres is listening on. 5432 is the default port.
• host: the Postgres server you want node-postgres to connect to. Passing it localhost will connect the node-postgres to the Postgres server installed on your system. If your Postgres server resided on another droplet, your host would look like this:host: server_ip_address.

Once you create the instance, the database connection is established and the Pool object is stored in the pool variable. To use this anywhere in your app, you will need to export it. In your db.js file, require in and define an instance of the Pool object, and set its properties and values:

const { Pool } = require("pg");

const pool = new Pool({
  user: "fish_user",
  database: "fish",
  password: "password",
  port: 5432,
  host: "localhost",
});

module.exports = { pool };

Save the file and exit nano by pressing CTRL+X. Enter y to save the changes, and confirm your file name by pressing ENTER or RETURN key on Mac.

Now that you’ve connected your application to Postgres, you’ll use this connection to insert data in Postgres.

In this step, you’ll create a program that adds data into the PostgreSQL database using the connection pool you created in the db.js file. To ensure that the program inserts different data each time it runs, you’ll give it functionality to accept command-line arguments. When running the program, you’ll pass it the name and color of the shark.

Create and open insertData.js file in your editor:

nano insertData.js

In your insertData.js file, add the following code to make the script process command-line arguments:

const { pool } = require("./db");

async function insertData() {
  const [name, color] = process.argv.slice(2);
  console.log(name, color);
}

insertData();

First, you require in the pool object from the db.js file. This allows your program to use the database connection to query the database.

Next, you declare the insertData() function as an asynchronous function with the async keyword. This lets you use the await keyword to make database requests asynchronous.

Within the insertData() function, you use the process module to access the command-line arguments. The Node.js process.argv method returns all arguments in an array including the node and insertData.js arguments.

For example, when you run the script on the terminal with node insertData.js sammy blue, the process.argv method will return an array: ['node', 'insertData.js', 'sammy', 'blue'] (the array has been edited for brevity).

To skip the first two elements: node and insertData.js, you append JavaScript’s slice() method to the process.argv method. This returns elements starting from index 2 onwards. These arguments are then destructured into name and color variables.

Save your file and exit nano with CTRL+X. Run the file using node and pass it the arguments sammy, and blue:

node insertData.js sammy blue

After running the command, you will see the following output:

Output
sammy blue

The function can now access the name and shark color from the command-line arguments. Next, you’ll modify the insertData() function to insert data into the shark table.

Open the insertData.js file in your text editor again and add the highlighted code:

const { pool } = require("./db");

async function insertData() {
  const [name, color] = process.argv.slice(2);
  const res = await pool.query(
      "INSERT INTO shark (name, color) VALUES ($1, $2)",
      [name, color]
    );
  console.log(`Added a shark with the name ${name}`);
}

insertData();

Now, the insertData() function defines the name and color of the shark. Next, it awaits the pool.query method from node-postgres that takes an SQL statement INSERT INTO shark (name, color) ... as the first argument. The SQL statement inserts a record into the shark table. It uses what’s called a parameterized query. $1, and $2 corresponds to the name and color variables in the array provided in the pool.query() method as a second argument: [name, color]. When Postgres is executing the statement, the variables are substituted safely protecting your application from SQL injection. After the query executes, the function logs a success message using console.log().

Before you run the script, wrap the code inside insertData() function in a try...catch block to handle runtime errors:

const { pool } = require("./db");

async function insertData() {
  const [name, color] = process.argv.slice(2);
  try {
    const res = await pool.query(
      "INSERT INTO shark (name, color) VALUES ($1, $2)",
      [name, color]
    );
    console.log(`Added a shark with the name ${name}`);
  } catch (error) {
    console.error(error)
  }
}

insertData()

When the function runs, the code inside the try block executes. If successful, the function will skip the catch block and exit. However, if an error is triggered inside the try block, the catch block will execute and log the error in the console.

Your program can now take command-line arguments and use them to insert a record into the shark table.

Save and exit out of your text editor. Run the insertData.js file with sammy and blue as command-line arguments:

node insertData.js sammy blue

You’ll receive the following output:

Output
Added a shark with the name sammy

Running the command insert’s a record in the shark table with the name sammy and the color blue.

Next, execute the file again with jose and teal as command-line arguments:

node insertData.js jose teal

Your output will look similar to the following:

Output
Added a shark with the name jose

This confirms you inserted another record into the shark table with the name jose and the color teal.

You’ve now inserted two records in the shark table. In the next step, you’ll retrieve the data from the database.

In this step, you’ll retrieve all records in the shark table using node-postgres, and log them into the console.

Create and open a file retrieveData.js in your favorite editor:

nano retrieveData.js

In your retrieveData.js, add the following code to retrieve data from the database:

const { pool } = require("./db");

async function retrieveData() {
  try {
    const res = await pool.query("SELECT * FROM shark");
    console.log(res.rows);
  } catch (error) {
    console.error(error);
  }
}

retrieveData()

The retrieveData() function reads all rows in the shark table and logs them in the console. Within the function try block, you invoke the pool.query() method from node-postgres with an SQL statement as an argument. The SQL statement SELECT * FROM shark retrieves all records in the shark table. Once they’re retrieved, the console.log() statement logs the rows.

If an error is triggered, execution will skip to the catch block, and log the error. In the last line, you invoke the retrieveData() function.

Next, save and close your editor. Run the retrieveData.js file:

node retrieveData.js

You will see output similar to this:

Output
[
  { id: 1, name: 'sammy', color: 'blue' },
  { id: 2, name: 'jose', color: 'teal' }
]

node-postgres returns the table rows in a JSON-like object. These objects are stored in an array.

You can now retrieve data from the database. You’ll now modify data in the table using node-postgres.

In this step, you’ll use node-postgres to modify data in the Postgres database. This will allow you to change the data in any of the shark table records.

You’ll create a script that takes two command-line arguments: id and name. You will use the id value to select the record you want in the table. The name argument will be the new value for the record whose name you want to change.

Create and open the modifyData.js file:

nano modifyData.js

In your modifyData.js file, add the following code to modify a record in the shark table:

const { pool } = require("./db");

async function modifyData() {
  const [id, name] = process.argv.slice(2);
  try {
    const res = await pool.query("UPDATE shark SET name = $1 WHERE id = $2", [
      name,
      id,
    ]);
    console.log(`Updated the shark name to ${name}`);
  } catch (error) {
    console.error(error);
  }
}

modifyData();

First, you require the pool object from the db.js file in your modifyData.js file.

Next, you define an asynchronous function modifyData() to modify a record in Postgres. Inside the function, you define two variables id and name from the command-line arguments using the destructuring assignment.

Within the try block, you invoke the pool.query method from node-postgres by passing it an SQL statement as the first argument. On the UPDATE SQL statement, the WHERE clause selects the record that matches the id value. Once selected, SET name = $1 changes the value in the name field to the new value.

Next, console.log logs a message that executes once the record name has been changed. Finally, you call the modifyData() function on the last line.

Save and exit out of the file using CTRL+X. Run the modifyData.js file with 2 and san as the arguments:

node modifyData.js 2 san

You will receive the following output:

Output
Updated the shark name to san

To confirm that the record name has been changed from jose to san, run the retrieveData.js file:

node retrieveData.js

You will get output similar to the following:

Output
output
[
  { id: 1, name: 'sammy', color: 'blue' },
  { id: 2, name: 'san', color: 'teal' }
]

You should now see that the record with the id 2 now has a new name san replacing jose.

With that done, you’ve now successfully updated a record in the database using node-postgres.

In this tutorial, you used node-postgres to connect and query a Postgres database. You began by creating a user and database in Postgres. You then created a table, connected your application to Postgres using node-postgres, and inserted, retrieved, and modified data in Postgres using the node-postgres module.

For more information about node-postgres, visit their documentation. To improve your Node.js skills, you can explore the How To Code in Node.js series.