Node Postgres Parameterized Query

Written by Data Pilot
November 04, 2019
  • PostgreSQL

Introduction to the Node Postgres parameterized query

When you’re building applications that interact with a database, you may find it helpful to use parameterized queries in your code. A parameterized query is also known as a “prepared statement”; put simply, it’s a way of compiling a SQL statement query in advance and preparing the parameters before the statement is executed. One key benefit of using a parameterized query is that it helps to prevent SQL injection attacks. In this article, we’ll show how to create an application using Node with a Postgres parameterized query.

Prerequisites to the Node Postgres param

Before we begin looking at our code in detail, there are a few important prerequisites that must be in place for this project:

  • You’ll need to have both the psql command-line interface and the Postgres server installed and running on your machine. You can use the postgres -V and psql -V commands to verify that they’re installed.

Make sure you have added a few records to a PostgreSQL table in a database that you have permissions to modify.

You can use the following bash command to connect to psql:

1
psql -U objectrocket -h 127.0.0.1 -d some_database

When you execute this command, be sure to change the user (-U) and database (-d) name to match yours.

Create a JavaScript file for the ‘UPDATE’ PostgreSQL Table Node app

Now that we’ve reviewed the prerequisites, let’s dive into our JavaScript code. Using a text editor or an IDE editor that supports JavaScript syntax, create a new file with an extension of .js. We’ll be using this file to store the code for the Node application.

Next, open a terminal window in your Node project directory. If you’re using Visual Studio Code, you can use the code command to create a new file; Sublime users can create a new JavaScript file using the subl command.

NOTE: Don’t forget to run the npm init -y command in a terminal window inside of your project directory to make sure that Node is initialized. You’ll also need to execute the npm i pg command to install the Postgres client module for Node.

Create a Postgres pool connection for the Node app

At this point, we’re ready to edit our JavaScript file. The first thing we’ll do is use the const JavaScript statement to ‘require’ the pg package. After that, we’ll 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: "postgres",
  host: "localhost",
  database: "test",
  password: "1234",
  port: "5432"
});

NOTE: We use a Node.JS connection pool to manage the database connections in our code. This assists with the recycling of the database cache and improves the code’s performance.

Creating a parameterized query using Node.JS with PostgreSQL

Next, we’ll try using Node with a Postgres parameterized query. One advantage of using a parameterized query in your code is that it can be updated easily to mirror a new search to query a different set of records.

app.js

In the following section of code, we declare constants for the Node.js connection to Postgres with the pg module:

1
2
3
4
5
const pg = require("pg");
const cs = "postgres://postgres:1234@localhost:5432/some_db";
const client = new pg.Client(cs);

client.connect();

Using substitute parameters to concatenate a parameterized SQL query

The substitute parameter ($1) in the code shown below acts as a substitute for a given parameter. It can be passed to the string using an array ([]) object to hold the substitute values:

1
2
3
4
5
6
7
const sql = "SELECT * FROM employee WHERE salary > $1";
const values = [55000];

client.query(sql, values).then(res => {
  const data = res.rows;
  data.forEach(row => console.log(row));
});

The output of this code would look like the following:

1
2
3
{ id: 1, emp_name: 'Janet Baker', salary: 60000 }
{ id: 4, emp_name: 'Ravi Patel', salary: 57000 }
{ id: 5, emp_name: 'Daisy Rogers', salary: 58123 }

Using multiple parameter substitutions in a Node.js Postgres parameterized query

Let’s look at another example using Node and a Postgres parameterized query. In this example, we use multiple substitutions in conjunction with the AND SQL keyword:

1
2
const sql = "SELECT * FROM employee WHERE salary > $1 AND id == $2";
const values = [55000, 1];

When the SQL statement shown above is executed, it should return just one record. The 1 is inserted into the $2 placeholder.

Conclusion to the Node Postgres parameterized query

If you’re building applications that include SQL queries, there are some compelling reasons to use parameterized queries instead of ad-hoc queries in your code. Not only do parameterized queries reduce the risk of SQL injection attacks, but they also improve performance because they don’t need to be prepared every time they’re executed. In this article, we looked at an example using Node with a Postgres parameterized query. With this example to guide you, you’ll be able to implement parameterized queries in your own Node applications.

Just the Code

Here’s the complete JavaScript code we examined in this tutorial:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
const pg = require("pg");
const cs = "postgres://postgres:1234@localhost:5432/some_db";
const client = new pg.Client(cs);

client.connect();

const sql = "SELECT * FROM employee WHERE salary > $1";
const values = [55000];
console.log(`SQL statement #1: ${sql}`);

client.query(sql, values).then(res => {
  const data = res.rows;
  data.forEach(row => console.log(row));
});

const sql = "SELECT * FROM employee WHERE salary > $1 AND id == $2";
const values = [55000, 1];
console.log(`\nSQL statement #2: ${sql}`);

client.query(sql, values).then(res => {
  const data = res.rows;
  data.forEach(row => console.log(row));
});
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.