NodeJs and PostgreSQL Query Example

NodeJs PostgreSQL Query Example Introduction

This tutorial will explain how to create a simple NodeJs PostgreSQL query example app with the INSERT SQL statement. The NodeJs is a group of modules used to interface with a PostgreSQL database. NodeJs supports many features including pool connection and inserting records into a PostgreSQL table.

Prerequisites for using PostgreSQL and NodeJs

• PostgreSQL must be properly installed. Execute the service postgresql status command to confirm the status is active. Press the CTRL + C keys to exit. Next, execute the psql -V command to verify the interactive PSQL command-line for PostgreSQL is properly installed and working. The results should resemble the following:

Verify the Node is and its ‘npm’ Package Manager are Installed

If NodeJs is already installed, execute the node -v command to see the currently installed version. The output will resemble the following:

As shown below, the same -v option can be used to instruct Node to return the installed version of npm:

Use the Node Package Manager to install the ‘pg’ Postgres packages

The pg client must be installed and added to the dependencies. Accomplish this by running the following command in the project directory:

Create a PostgreSQL database and table for the NodeJs app

A PostgreSQL database and table must be created and record data must then be inserted using the NodeJs. The first step is to create a database using the following code:

Here is an example:

Now create a table, to hold record data, with the following command for testing the NodeJs INSERT query:

An example follows:

Create a JavaScript file for the NodeJs application

Using a text editor or IDE editor that supports JavaScript syntax, such as Sublime or Visual Studio Code, create a file with an extension of (.js) for inputting codes.

For Visual Studio Code, create a new JavaScript file in the terminal using the following command:

For Sublime use:

NOTE: Execute the npm init -y command in a terminal window, inside the project directory, to initialize Node. The npm i pg command must also be executed using the npm package installer to install the Postgres client.

Create a Postgres Pool Connection for the Node App

Execute the below const JavaScript statement to ‘require’ the pg package and declare a new Pool connection object for the PostgreSQL connection:

NOTE: A NodeJs connection pool for the pg client instance is used to manage the database connections for recycling of the cache of the database and to increase performance of the commands.

Use NodeJs to ‘INSERT’ Records into the PostgreSQL Table

A query can be made by passing a string to the object’s query() method call after the pool object has been created.

Here is an example of how to use Node to return all of the PostgreSQL records in a query:

Interpolate values into a PostgreSQL statement using template strings

Because long queries can become illegible and cumbersome, following is a screenshot of an example that can break the query string into multiple lines:

JavaScript allows you to use multi-line strings, or “template strings,” to pass or interpolate variables into strings. The marker for the template literal uses backticks (`) and data can be interpolated into the string using the${}` syntax. The following JavaScript string declaration example demonstrates how to use template strings:

NOTE: Template strings are a new JavaScript development since 2015 and requires at least ES6 (ECMAScript 6). However, this is typically not a problem with up-to-date browsers.

Execute a PostgreSQL parameterized query using JavaScript Template strings

The below example concatenates an SQL string for a Postgres query by interpolating variable values. This example declares a template literal with the $1 variable substitute and then passes the string to the pg module’s query() method:

NOTE: Because they are target expression, there must be an equal number of substitute values in the array, such as $1 and $2, in the string literal.

Now the sqlString string literal and the values array must be passed to the pool connection instance’s query() method to make the API call to insert the string into the PostgreSQL table as a record. This is shown here:

Now navigate to the directory of the file that was created via the terminal. Type node insert.js in the terminal window to execute the script using Node.

Using the ‘SELECT’ Statement to Create a PostgreSQL Query in NodeJs

Execute the select statement to log the newly inserted table record into the console terminal. Declare another template literal for the SELECT statement query and then declare a parameter for the query as follows:

Now just pass the variables to the pool’s query() method a second time to execute a SELECT query to the PostgreSQL database. Don’t forget to pass the integer variable as an array by enclosing it in square brackets ([]):

NOTE: As shown the following screenshot, the query result object’s rowCount integer attribute can be accessed to obtain the total number of records returned by the query.

End the PostgreSQL pool object instance to free resources

It is good practice to call the pool instance’s end() method after all the API calls are finished to free up system resources so the app doesn’t end up with memory leaks. Accomplish this with the following command:

Conclusion to the NodeJs PostgreSQL Query Example

This tutorial explained how to create a simple NodeJs PostgreSQL query example app with the INSERT SQL statement. The article explained how to use the node package manager to install the ‘pg’ Postgres packages, create a PostgreSQL database, table and file for the NodeJs application and how to create a Postgres pool connection for the node app. The tutorial also covered how to use NodeJs to insert records into the PostgreSQL table, interpolate values into a PostgreSQL statement using template strings and execute a PostgreSQL parameterized query using JavaScript template strings. Remember to call the pool instance’s end() method after all the API calls are finished to free up system resources.

Just the Code