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.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

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:

CREATE DATABASE some_database;

Insert some records into the PostgreSQL table

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

CREATE TABLE some_table (str_col VARCHAR(64), int_col INTEGER, bool_col BOOLEAN);

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

INSERT INTO
some_table (str_col, int_col, bool_col)
VALUES('hello world', 123456, TRUE),
('ObjectRocket Tutorial', 42, FALSE),('Hello, world', 12345, FALSE),
('another string', 98765, TRUE);

Screenshot of psql creating a PostgreSQL table and inserting records

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:

code select-records.js

Here’s how you would do it using Sublime:

subl select-records.js

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:

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

const pool = new Pool({
  user: "objectrocket",
  host: "localhost",
  database: "some_database",
  password: "mypass",
  port: "5432"
});

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:

pool.query("SELECT * from some_table", (err, res) => {
  console.log(err, res);
  pool.end();
});

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:

undefined Result {
command: 'SELECT',
rowCount: 4,
oid: null,
rows: [
{ str_col: 'hello world', int_col: 123456, bool_col: true },
{ str_col: 'ObjectRocket Tutorial', int_col: 42, bool_col: false },
{ str_col: 'Hello, world', int_col: 12345, bool_col: false },
{ str_col: 'another string', int_col: 98765, bool_col: true }
],
fields: [
Field {
...
...
...

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:

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

const pool = new Pool({
  user: "objectrocket",
  host: "localhost",
  database: "some_database",
  password: "mypass",
  port: "5432"
});

pool.query("SELECT * from some_table", (err, res) => {
  console.log(err, res);
  pool.end();
});

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.