NodeJS and PostgreSQL Select Example

Written by Data Pilot
October 24, 2019
  • NodeJS
  • PostgreSQL

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:

1
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:

1
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:

1
2
3
4
5
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:

1
code select-records.js

Here’s how you would do it using Sublime:

1
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:

1
2
3
4
5
6
7
8
9
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:

1
2
3
4
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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();
});
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

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

Get Started
Related Topics:
Additional Information:

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.