NodeJS and PostgreSQL Select Example

NodeJS and PostgreSQL Select Example Introduction

If you’re developing a web application using NodeJS, you may need to interact with a PostgreSQL database. Fortunately, it’s easy to query PostgreSQL from NodeJS using a simple SELECT statement. In this article, we’ll look at a NodeJS and PostgreSQL SELECT example that will show you how to query your database from a web application.

Prerequisites to using PostgreSQL and NodeJS

You’ll need to make sure a couple of key prerequisites are in place before proceeding with this tutorial:

• First, make sure that you have PostgreSQL installed on your machine. You can confirm that the status of the service is active by using the command service postgresql status.

• You’ll also need to have psql, the interactive command-line interface for PostgreSQL, installed on your machine. You can use the command psql -V to confirm that it’s installed on your machine; this command will also let you know which version you have installed.

Create a PostgreSQL database and table for the NodeJS app

Now that we’ve reviewed the prerequisites, let’s create a PostgreSQL database and table. We’ll insert some record data into our table using the psql command-line interface.

The first step is to create a database using the following SQL statement:

Insert some records into the PostgreSQL table

Then, we’ll create a table that we can use to test our NodeJS SELECT query:

Finally, we’ll insert some records into the table using the INSERT INTO statement:

Create a JavaScript file for the NodeJS application

Once we’ve set up our database and table, we’ll start working on our NodeJS application. You can use a text editor or any IDE editor that supports JavaScript syntax, such as Visual Studio Code, Sublime and Atom. We’ll start by creating a file with an extension of .js.

If you’re using Visual Studio Code, you can create a new JavaScript file in the terminal using the following command:

Here’s how you would do it using Sublime:

NOTE: Be sure to run the npm init -y command in a terminal window inside of your project directory– this ensures that Node is initialized if you haven’t done so already. You’ll also need to execute the npm i pg command, using the npm package installer, to install the PostgreSQL client.

Create a Postgres pool connection for the Node app

Now, let’s start adding some code to our file. We’ll use the const JavaScript statement to ‘require’ the pg package; we’ll also use it to declare a new Pool connection object for the PostgreSQL connection:

NOTE: We use a NodeJS connection pool for the pg client instance to manage the database connections, which helps with recycling of the database cache and improves performance.

Use NodeJS and PostgreSQL to execute a ‘SELECT’ query

After you create the pool object, you can use it to perform a query by passing a string to the object’s query() method call. Let’s look at an example that uses Node to return all of the PostgreSQL records from a table:

Now, let’s go to the directory where we saved our file. We can use the command node test.js to test our SELECT query.

The code will return a result that looks something like this:

Conclusion on using NodeJS to execute a PostgreSQL query

When you’re working with NodeJS to develop web applications, it’s important to know how to query and retrieve information from a PostgreSQL database. In this article, we learned how to perform a SELECT query in PostgreSQL using NodeJS. With this example as a guide, you’ll be able to construct your own web applications that interact with a PostgreSQL database.

Just the Code

Here’s the code we used for our NodeJS and PostgreSQL SELECT example: