How to Use PostgreSQL With Node.js on Ubuntu 20.04

Learn how to use PostgreSQL with Node.js on Ubuntu 20.04. Our PostgreSQL Support team is here to answer queries and concerns.

How to Use PostgreSQL With Node.js on Ubuntu 20.04

If you are on the lookout for a guide to help you develop a Node.js application while using PostgreSQL as your database on Ubuntu 20.04, you are in luck!

This step-by-step guide will walk you through the full process, from setting up your environment to creating, querying, and updating data.

Step 1. Set Up the Node.js Project Directory

1. First, create a new directory for the Node.js project:

   mkdir node_pg_app
   cd node_pg_app
   Copy Code
2. Then, initialize the project with a default `package.json`:

   npm init -yCopy Code
3. Now, install the `pg` module, which is the PostgreSQL client for Node.js:

   npm install pgCopy Code

   With this, the Node.js environment is ready. Next, we will configure PostgreSQL.

Step 2. Create a PostgreSQL User and Database

PostgreSQL on Ubuntu uses `ident` authentication by default. In other words, the Ubuntu username must match the PostgreSQL role name to connect without a password.

1. Start by switching to the `postgres` user and opening the PostgreSQL shell:

   sudo -u postgres psqlCopy Code
2. Now, create a dedicated PostgreSQL user and a database:

   CREATE USER cat_user WITH PASSWORD 'password';
   CREATE DATABASE cat OWNER cat_user;
   q
   Copy Code
3. Then, create a matching Ubuntu user so the new role can be used to access the shell:

   sudo adduser cat_userCopy Code

Step 3. Open PostgreSQL Shell and Create a Table

1. Switch to the new user and access the database:

   sudo -u cat_user psql -d catCopy Code
2. Then, confirm the connection:

   conninfoCopy Code
3. Next, create a `persian` table:

   
   CREATE TABLE persian (
   id SERIAL PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   color VARCHAR(50) NOT NULL
   );
   Copy Code
4. Now, list tables to verify ownership:

   
   dt
   q
   Copy Code

Step 4. Connect to PostgreSQL Using Node.js

1. Now, create a new file named `db.js`:

   nano db.jsCopy Code
2. Then, add the following code to establish the connection:

   
   const { Pool } = require('pg');
   const pool = new Pool({
   user: 'cat_user',
   database: 'cat',
   password: 'password',
   port: 5432,
   host: 'localhost',
   });
   module.exports = { pool };
   Copy Code
3. Save and exit the file. This module will handle all database connections in the app.

Step 5. Insert Data Into PostgreSQL With Node.js

1. Create the insert script:

   nano insertData.jsCopy Code
2. Then, add this code:

   
   const { pool } = require("./db");
   async function insertData() {
   const [name, color] = process.argv.slice(2);
   try {
   const res = await pool.query(
   "INSERT INTO persian (name, color) VALUES ($1, $2)",
   [name, color]
   );
   console.log(`Added a persian with the name ${name}`);
   } catch (error) {
   console.error(error);
   }
   }
   insertData();
   Copy Code
3. Now, run the script to insert data:

   
   node insertData.js xavier grey
   node insertData.js pearl silver
   Copy Code

   We have now added two Persian cats to the database.

Step 6. Retrieve Data From PostgreSQL

1. Now, let’s fetch and view all records in the `Persian` table. Create `retrieveData.js`:

   nano retrieveData.jsCopy Code
2. Then, add this code:

   
   const { pool } = require("./db");
   async function retrieveData() {
   try {
   const res = await pool.query("SELECT * FROM persian");
   console.log(res.rows);
   } catch (error) {
   console.error(error);
   }
   }
   retrieveData();
   Copy Code
3. Run it:

   node retrieveData.jsCopy Code

   We will see the inserted Persian cat records printed to the console.

Step 7. Modify PostgreSQL Data Using Node.js

1. To update records, create `modifyData.js`:

   nano modifyData.jsCopy Code
2. Then, paste the following code:

   
   const { pool } = require("./db");
   async function modifyData() {
   const [id, name] = process.argv.slice(2);
   try {
   const res = await pool.query(
   "UPDATE persian SET name = $1 WHERE id = $2",
   [name, id]
   );
   console.log(`Updated the persian name to ${name}`);
   } catch (error) {
   console.error(error);
   }
   }
   modifyData();
   Copy Code
3. Now, run the script to update a Persian cat’s name:

   node modifyData.js 2 MellyCopy Code
4. Verify the change:

   node retrieveData.jsCopy Code

   We will now see that the name for the Persian cat with ID 2 is “Melly”.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

With the above steps, we learn how to effectively use PostgreSQL with Node.js on Ubuntu 20.04.

In brief, our Support Experts demonstrated how to use PostgreSQL with Node.js on Ubuntu 20.04.