How to Connect Node Postgres using NPM?

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.

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.js is an Open-Source & Cross-platform 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.

What is PostgreSQL?

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.

What is node-Postgres?

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.

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

How 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 a 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.

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:

• Text Only
• Parameterized Query
• Row Mode
• Types

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,
 },
}

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!

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