Access to a PostgreSQL Database from Node.js Application

PostgreSQL and Node.js are two powerful technologies that, when integrated, can form the backbone of highly efficient, scalable, and robust web applications. 

One of the most popular open-source relational database systems, PostgreSQL is well known for its sophisticated features, dependability, and adherence to SQL standards. It provides sophisticated querying capabilities, broad data integrity, and a wide range of functional extensions. Because of its robust indexing, full-text search capabilities, support for ACID transactions, and capacity to manage massive data volumes, PostgreSQL is a popular database management system.

Designed to create scalable network applications, Node.js is a JavaScript runtime based on the V8 JavaScript engine found in Chrome. Its lightweight and efficient I/O model is event-driven and non-blocking. Applications requiring real-time functionality and the ability to manage numerous concurrent connections with little overhead are especially well suited for Node.js.

The JSON-centric approach of Node.js is enhanced by PostgreSQL's support for JSON and JSONB data types, which facilitates seamless data interchange and manipulation. PostgreSQL's advanced features, like full-text search, spatial data support with PostGIS, and custom data types, can greatly improve Node.js applications. Comprehensive security features are offered by both technologies. Strong data integrity, access control, and encryption are provided by PostgreSQL, and a range of security techniques and plugins are available in Node.js to safeguard application data.

Importance of database access in applications

Accessing a PostgreSQL database from a Node.js application is a key aspect of modern web and mobile application development. Through this integration, developers may take advantage of the strengths of both platforms to create scalable, secure, and effective applications. Here’s how to access PostgreSQL database from Node.js application: 

Requirements

• A cloud server or a local machine running Ubuntu

• Root access or user with sudo privilege

Node.js and npm installation

After all requirements are met, proceed with the Node.js and npm installation. There are several ways to install Node.js and npm on Ubuntu. In this tutorial, the Node.js and npm will be installed on Ubuntu using the Ubuntu apt repository.

1. Login to the terminal and run the command below.

sudo apt update && apt upgrade 

2.  Run the command below to install Node.js and npm.

sudo apt install nodejs npm 

3. After successful installation, verify the version by running the commands below respectively:

nodejs --version

npm --version

PostgreSQL setup

Install PostgreSQL and configure the database using the instructions below.

1. Install the postgresql package along with its additional utilities.

sudo apt install postgresql postgresql-contrib

2. Use the systemctl start command to make sure the service is up and running.

sudo systemctl start postgresql.service

3. Verify the status by running the command below.

sudo systemctl status postgresql.service

4. Once the package is installed and up, login to PostgreSQL database using the default postgres user. 

sudo -u postgres psql

5. Create a new database.  

CREATE DATABASE mydatabase;

Enter your database name instead of mydatabase.

6. Switch to the new database that has been created:

\c mydatabase

7. Create additional user to be used in the new database. 

CREATE USER myuser WITH PASSWORD 'mypassword';

8. To enable the newly created user 'myuser' to be utilized, create an equivalent OS user. Execute the following command and respond to the prompts accordingly.

adduser myuser

9. Lastly, test the connection of the database created using the new user. Run the commands below respectively:

sudo su - myuser

psql -d mydatabase

Creating a Node.js Project

After successfully installing and configuring the PostgreSQL database, proceed to create the Node.js project. This project will facilitate the creation of a PostgreSQL database and interaction with npm configuration files.

1. Create directory by running the command below:

mkdir nodejs-pg-mydir

2. Go to the newly created directory named nodejs-pg-mydir. Run the command below:

cd nodejs-pg-mydir

3. Initialize the directory by running the command below:

npm init -y

Installing Necessary Packages (pg)

After creating the Node.js project, it's time to install the node-postgres module using the following command.

npm install pg

 

Configuration Details

To access the database using the application, follow these steps:

1. Before verifying the connection, create a table on the database by running the command below.

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

 

Testing the Example Connection

1. Exit the database. The command prompt will go back to the project directory. Create a file called database.js in the nodejs-pg-mydir folder using nano virtual editor. Run the command nano database.js and paste the following code into it then save and exit.

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

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

module.exports = { pool };

Note: Make sure to change 'user', 'database', and 'password' with the real database credentials. The module.exports = { pool }; line at the end of the file means that the pool variable can be used anywhere in the app.

2. Test the connection by inserting data into the PostgreSQL database using the connection pool database.js, that was created on the previous step. Create a file datainsert.js and copy the below code. Save the file and exit.

nano datainsert.js

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

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

datainsert()

3. Use the node command below to execute the file datainsert.js with argument name and color.

node datainsert.js jcbrian brown

The connection was successful if the output is similar to above. This means that the application can access the database with the table name mytable.

5. To perform another test, try to retrieve data from the Postgres database and log them into the console. Create a file name dataretrieve.js and put the following code. Save and exit.

nano dataretrieve.js

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

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

dataretrieve()

6. Run the command below to query on the database using Node.js.

node dataretrieve.js

 An output similar to above indicates that the query is completed without any error.

Conclusion

In conclusion, developers have an extensive number of options when integrating a PostgreSQL database with a Node.js application. Robust and scalable web applications can be developed because of the efficient data storage, retrieval, and manipulation made possible by the smooth connection between these technologies. Developers can leverage the flexibility of Node.js and the powerful features of PostgreSQL to create dynamic, data-driven apps that fit the needs of modern software development by following the methods suggested in this article. Accessing PostgreSQL via Node.js offers a significant benefit in developing high-performance applications when the appropriate tools and skills are applied.