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.
An Overview:
- Step 1. Set Up the Node.js Project Directory
- Step 2. Create a PostgreSQL User and Database
- Step 3. Open PostgreSQL Shell and Create a Table
- Step 4. Connect to PostgreSQL Using Node.js
- Step 5. Insert Data Into PostgreSQL With Node.js
- Step 6. Retrieve Data From PostgreSQL
- Step 7. Modify PostgreSQL Data Using Node.js
Step 1. Set Up the Node.js Project Directory
- First, create a new directory for the Node.js project:
mkdir node_pg_app
cd node_pg_app
- Then, initialize the project with a default `package.json`:
npm init -y - Now, install the `pg` module, which is the PostgreSQL client for Node.js:
npm install pgWith 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.
Start by switching to the `postgres` user and opening the PostgreSQL shell:
sudo -u postgres psql- Now, create a dedicated PostgreSQL user and a database:
CREATE USER cat_user WITH PASSWORD 'password';
CREATE DATABASE cat OWNER cat_user;
q
- Then, create a matching Ubuntu user so the new role can be used to access the shell:
sudo adduser cat_user
Step 3. Open PostgreSQL Shell and Create a Table
- Switch to the new user and access the database:
sudo -u cat_user psql -d cat - Then, confirm the connection:
conninfo - Next, create a `persian` table:
CREATE TABLE persian (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
color VARCHAR(50) NOT NULL
);
- Now, list tables to verify ownership:
dt
q
Step 4. Connect to PostgreSQL Using Node.js
- Now, create a new file named `db.js`:
nano db.js - 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 };
- Save and exit the file. This module will handle all database connections in the app.
Step 5. Insert Data Into PostgreSQL With Node.js
- Create the insert script:
nano insertData.js - 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();
- Now, run the script to insert data:
node insertData.js xavier grey
node insertData.js pearl silver
We have now added two Persian cats to the database.
Step 6. Retrieve Data From PostgreSQL
- Now, let’s fetch and view all records in the `Persian` table. Create `retrieveData.js`:
nano retrieveData.js - 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();
- Run it:
node retrieveData.jsWe will see the inserted Persian cat records printed to the console.
Step 7. Modify PostgreSQL Data Using Node.js
- To update records, create `modifyData.js`:
nano modifyData.js - 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();
- Now, run the script to update a Persian cat’s name:
node modifyData.js 2 Melly - Verify the change:
node retrieveData.jsWe 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.
0 Comments