Node Postgres Parameterized Query
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 thepostgres -V
andpsql -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)); }); |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started