How to Connect Node Postgres using NPM

on Data Integration, Database Management Systems, Node js, PostgreSQL • February 4th, 2022 • Write for Hevo

It is critical for software systems to have a unified communication system that allows them to share all relevant information. Thanks to Node.js, efficient communication is now possible.  The framework enables the development of basic APIs that can be deployed in minutes.

This article explains how to utilize PostgreSQL to build a communicative API that works with Node.js. You’ll start with the fundamentals and work your way through each step required to connect your Node Postgres using the node-postgres[NPM pg]. You will also explore the key features of Node.js and PostgreSQL. In addition, you will understand what is node-Postgres and the different types of Node Postgres query supported by it. So, read along to successfully leverage the benefit of the Node Postgres connection.

Table of Contents

Prerequisites

To successfully connect the Node Postgres using node-Postgres, you need to meet the following requirements:

  • Node.js installed.
  • PostgreSQL installed.

What is Node.js?

Node Postgres - Nodejs Logo
Image Source

Node.js is an Open-Source & Cross-Platfrom that executes JavaScript code outside of a web browser using the V8 engine. It’s typically used to create web pages and scalable applications. Node.js is an asynchronous technology, which indicates that data is sent over networks without time constraints.

Node.js’s event-driven runtime handles all forms of HTTP requests. This allows developers to create dynamic online content using JavaScript and server-side scripts before sending it to the user’s browser. Node.js is a “JavaScript Everywhere” paradigm that unifies online application development around a single programming language rather than separate languages for server-side and client-side scripts.

Key Features of Node.js

Node Postgres - Nodejs Features
Image Source

For constructing extremely scalable Server-Side JavaScript applications, Node.js provides an Event-driven, Non-Blocking (Asynchronous) I/O, and Cross-Platform runtime environment. Let’s explore these features in brief below:

  • Asynchronous and Event-Driven: The APIs of the Node.js library is completely asynchronous (non-blocking). A server designed in Node.JS never has to wait for data from an API. The server goes on to the next API after visiting one. It employs a notification system called Events to receive and monitor replies to earlier API queries.
  • Highly Scalable:  Node.js can control and manage concurrent requests efficiently. It contains a cluster module that handles load balancing across all active CPU cores. The ability of Node.js to horizontally split applications is its most enticing feature. It accomplishes this by using child processes. This lets businesses provide several app versions to different target groups, allowing them to accommodate customer preferences for personalization.
  • Cross-Platform Compatibility: Node.js is compatible with a wide range of operating systems, including Windows, Unix, Linux, Mac OS X, and mobile devices. It can be used in conjunction with the proper package to create a self-sufficient executable.
  • Single-Threaded: Node.js is scalable because it uses a single-threaded architecture with event looping. Unlike traditional servers that establish restricted threads to handle requests, the event mechanism allows the Node.js server to respond in a non-blocking and scalable manner. Node.js employs single-threaded software that can manage a far higher number of requests than traditional servers like Apache HTTP Server.
  • Faster Code Execution: The V8 JavaScript Runtime motor, which is also used by Google Chrome, is used by Node.js. Hub offers a wrapper for the JavaScript motor, allowing the runtime motor to execute more quickly. As a result, the preparation of requests in Node.js becomes more efficient.
  • Leverages JavaScript: The Node.js library uses JavaScript, which is another significant component of Node.js from an engineer’s standpoint. As a result, working with Node.js is significantly easier for a Developer who is comfortable with JavaScript.

Explore other key features, benefits, and lots more on the Node.js homepage.

What is PostgreSQL?

Node Postgres - PostgreSQL Logo
Image Source

PostgreSQL is an Open-Source Relational Database Management System (DBMS) created by a global volunteer community. SQL (relational) and JSON (non-relational) querying are both supported. All of the operating systems are well-supported by PostgreSQL. It can handle complex data types and optimization processes that are often supported in commercial databases like Oracle and SQL Server.

PostgreSQL is a very reliable Database that has been developed by the open-source community for over 20 years. Many online applications, as well as mobile and Analytics applications, use PostgreSQL as their primary Database. 

Key Features of PostgreSQL

Node Postgres - PostgreSQL Features
Image Source

PostgreSQL is leveraged by many prominent companies such as Apple, Cisco, Etsy, Red Hat, Spotify, and many more. Let’s discover the amazing features of PostgreSQL also referred to as Postgres below that makes it so popular among organizations:

  • Object-Relational DBMS: The PostgreSQL is an Object-Relational Database Management System (ORDBMS). This enables PostgreSQL to support both Object-Oriented and Relational Databases. It is also free and open-source.
  • Multiversion Concurrency Control: PostgreSQL uses multi-version concurrency control to handle concurrent requests. This provides each transaction a “snapshot” of the Database, enabling modifications to be performed without impacting other transactions. This obviates the requirement for reading locks and assures that the Database follows ACID properties.
  • ACID-Compliant Database: Write-ahead logging in PostgreSQL makes it a highly fault-tolerant Database. It has a built-in community support network due to its wide number of open source contributors. PostgreSQL is an ACID-compliant Database that supports foreign keys, joins, views, triggers, and stored procedures in a variety of languages.
  • Large Support Network: PostgreSQL has a strong community that is always willing to help. There are also private, third-party assistance services accessible. The PostgreSQL platform is updated by the community through the PostgreSQL Global Development Group.

You can explore more key features and other details of PostgreSQL here.

What is node-Postgres?

Node Postgres - node-postgres NPM pg
Image Source: Self

Node.js has a nonblocking PostgreSQL client called node-Postgres. node-Postgres is essentially a set of Node.js modules for interacting with a PostgreSQL Database.

Callbacks, Promises, async/await, connection pooling, prepared statements, cursors, rich type parsing, and C/C++ bindings are just a few of the capabilities supported by node-Postgres. Want to read more about Node Postgres, refer to the following node-postgres Documentation.

Read along to know how you can connect Node Postgres using NPM pg package.

Simplify PostgreSQL ETL & Data Analysis with Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline, helps load data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports 100+ Data Sources such as PostgreSQL,  including 40+ Free Sources. It is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. 

Hevo loads the data onto the desired Data Warehouse/destination in real-time and enriches the data and transforms it into an analysis-ready form without having to write a single line of code. Its completely automated pipeline, fault-tolerant, and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.

GET STARTED WITH HEVO FOR FREE

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled securely and consistently with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.

Simplify your PostgreSQL ETL & Data Analysis with Hevo today! 

SIGN UP HERE FOR A 14-DAY FREE TRIAL!

Steps to Set Up Node Postgres using the NPM

Now that you have an idea about why Node Postgres Integration is needed, in this section, you will understand how to set up your Node Postgres Database using pg and pgtools packages from NPM. Follow the steps below to get started with Node Postgres connection:

Step 1: Open your Command Prompt or terminal. Then create a Node Postgres project using the following command:

$ mkdir POSTGRES_NODEJS_APP

Make sure you check if the above directory is created using the list ls command.

Step 2: Now, change the current directory to your Node Postgres directory you created above using the following command:

$ cd POSTGRES_NODEJS_APP/

Step 3: Next, create a new Node.js project using the following command:

$ npm init -y

You will observe a package.json file is created which contains the following information:

{
  "name": "POSTGRES_NODEJS_APP",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo "Error: no test specified" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}

Step 4: Now, let’s create an index.js file for the Node.js project by using the following command:

$ touch index.js

Step 5: After creating the above file, install the pg and pgtools packages using the NPM command as shown below:

$ npm i pg pgtools

Step 6: Now, open the .js file in the editor and connect your Postgres database using the script below:

const pgtools = require("pgtools");
const config = {
  user: "postgres",
  host: "localhost",
  password: "some+random?password!",
  port: 5432
};

pgtools.createdb(config, "myFirstDb", function(err, res) {
  if (err) {
    console.error(err);
    process.exit(-1);
  }
  console.log(res);
});

Step 7: Now, you can test and run your Node Postgres .js file using the following command:

$ node index.js

You will observe a similar output like the one shown below:

Result {
command: 'CREATE',
rowCount: NaN,
oid: null,
rows: [],
fields: [],
_parsers: [],
RowCtor: null,
rowAsArray: false,
_getTypeParser: [Function: bound ]
}

Step 8: To check if your connection is successful, run the list command using the l command. You will get a list of the created database. This means that your Node Postgres connection was successful. You will find a similar output to the one shown below signifying the successful Node Postgres connection:

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
myFirstDb | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
postgres | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |

Good Job! You have successfully set up the connection to Node Postgres using the NPM pg and pgtools packages. Want to learn how you can use Postgres with Node.js on Ubuntu? Read the How To Use PostgreSQL With Node.js on Ubuntu 20.04 blog to know more.

Types of Node Postgres Queries

The node-Postgres API supports both callbacks and promises for executing queries. Let’s discover the various types of queries supported by node-postgres below along with their sample code:

1) Text Only

You do not need to add any arguments in the query method if your query does not have any. A sample code has been shown below for executing the Text only query:

// callback
client.query('SELECT NOW() as now', (err, res) => {
 if (err) {
   console.log(err.stack)
 } else {
   console.log(res.rows[0])
 }
})
// promise
client
 .query('SELECT NOW() as now')
 .then(res => console.log(res.rows[0]))
 .catch(e => console.error(e.stack))

2) Parameterized Query

To prevent SQL injection issues, you should avoid directly concatenating string arguments into the query text if you’re providing parameters to your queries. Parameterized queries are supported by node-postgres, which sends your query text and parameters to the PostgreSQL server, where the parameters are safely substituted into the query using battle-tested parameter substitution code within the server. A sample code has been shown below for executing the Parameterized query in Node Postgres.

const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'
const values = ['brianc', 'brian.m.carlson@gmail.com']
// callback
client.query(text, values, (err, res) => {
 if (err) {
   console.log(err.stack)
 } else {
   console.log(res.rows[0])
   // { name: 'brianc', email: 'brian.m.carlson@gmail.com' }
 }
})
// promise
client
 .query(text, values)
 .then(res => {
   console.log(res.rows[0])
   // { name: 'brianc', email: 'brian.m.carlson@gmail.com' }
 })
 .catch(e => console.error(e.stack))
// async/await
try {
 const res = await client.query(text, values)
 console.log(res.rows[0])
 // { name: 'brianc', email: 'brian.m.carlson@gmail.com' }
} catch (err) {
 console.log(err.stack)
}

3) Row Mode

By default, node-Postgres reads rows and gathers them into JavaScript objects, with keys matching column names and values matching each column’s corresponding row value. You can give rowMode: ‘array’ to a query object if you don’t require this pattern. This instructs the resulting parser to return each row as an array of values rather than aggregating rows into a JavaScript object. A sample code has been shown below for executing the Row Mode query.

const query = {
 text: 'SELECT $1::text as first_name, select $2::text as last_name',
 values: ['Brian', 'Carlson'],
 rowMode: 'array',
}
// callback
client.query(query, (err, res) => {
 if (err) {
   console.log(err.stack)
 } else {
   console.log(res.fields.map(field => field.name)) // ['first_name', 'last_name']
   console.log(res.rows[0]) // ['Brian', 'Carlson']
 }
})
// promise
client
 .query(query)
 .then(res => {
   console.log(res.fields.map(field => field.name)) // ['first_name', 'last_name']
   console.log(res.rows[0]) // ['Brian', 'Carlson']
 })
 .catch(e => console.error(e.stack))

4) Types

When processing the results of a query, you can provide a custom set of type parsers to use. The types property must follow the Types API guidelines. Consider the following scenario, in which each value is returned as a string:

const query = {
 text: 'SELECT * from some_table',
 types: {
   getTypeParser: () => val => val,
 },
}

Learn more about other Node Postgres queries such Query Config Object and Prepared Statements over here.

Conclusion

In this article, you gained a basic understanding of connecting your Node Postgres using the NPM pg and pgtools packages. You also learned the key features of Node.js and PostgreSQL. Moreover, you understood what is the node-postgres client and the different queries supported by Node Postgres.

However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, and Marketing Platforms to your PostgreSQL Database can seem to be quite challenging. This is where a simpler alternative like Hevo can save your day! Hevo Data is a No-Code Data Pipeline that offers a faster way to move data from 100+ Data Sources such as PostgreSQL and other 40+ Free Sources, into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of connecting Node Postgres using NPM pg in the comments section below!

No-Code Data Pipeline For PostgreSQL