How to Update a PostgreSQL Table with Node

Written by Data Pilot

October 24, 2019

Node.JS
PostgreSQL

Introduction to updating a PostgreSQL table in Node

If you’re developing applications in Node.js, you may find that some of your applications need to interact with a PostgreSQL database. Fortunately, it’s easy to perform a wide variety of database operations from a Node.js application. In this article, we’ll focus on one operation in particular– updating a table. We’ll walk through an example that demonstrates how to update a PostgreSQL table with Node.

Prerequisites to using PostgreSQL and Node.js

Before we can dive into our code examples, it’s important to review the prerequisites for the task:

Make sure that you have PostgreSQL installed on your machine. If you’re not sure whether the service is installed, use the command service postgresql status. This command will let you know if the status is active.
You’ll also need to make sure you have psql installed on your machine. To see whether you have this command-line interface for PostgreSQL installed already, use the command psql -V.

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

Verify that Node.js and its ‘npm’ package manager are installed

If you already have Node.js installed on your device, try typing the command node -v to see which version you have.

The output of this command will look like this:

v12.10.0

You can also use the same -v option to have Node return its version of the npm package manager:

npm -v

Use the Node Package Manager to install the ‘pg’ Postgres packages

Be sure to install the pg PostgreSQL client and add it to your dependencies if you haven’t done so already. Simply run the following command in your project’s directory:

npm i pg

NOTE: The command npm install pg will work as well.

This command will return a response like the one shown below:

1
2
3

+ pg@7.12.1
updated 1 package and audited 16 packages in 1.665s
found 0 vulnerabilities

Create a PostgreSQL database and table for the Node.JS app

Now that we have all the necessary components installed, let’s create a PostgreSQL database and table and insert some records into it. We’ll do this using the psql command-line interface.

The first step is to create a database:

1	CREATE DATABASE database_name;

Insert some records into the PostgreSQL table

Next, we’ll create a table. We’ll use it to test the Node.JS SELECT query later in this tutorial:

1	CREATE TABLE student(id SERIAL PRIMARY KEY, firstname TEXT, lastname TEXT, age INT, address VARCHAR(255), email VARCHAR(50));

Our last step will be to insert some records into the table using the INSERT INTO statement:

The syntax of a typical INSERT statement is shown below:

1 2	INSERT INTO TABLE_NAME(column1, column2, column3) VALUES(values1, values2, values3);

In our example, we’ll use the following command to insert some records:

1 2	INSERT INTO student(firstname, lastname, age, address, email) VALUES('Cory', 'Gibes', 23, '83649 W Belmont Ave', 'cory.gibes@gmail.com'), ('Danica', 'Bruschke', 22, '840 15th Ave', 'danica_bruschke@gmail.com'), ('Natalie', 'Fern', 25, '7140 University Ave', 'natalie.fern@hotmail.com');

We can then use the SELECT statement to display all the records that now exist in our PostgreSQL table.

1	SELECT * FROM student;

The results of the query will look like the following:

1
2
3
4
5
6

Create a JavaScript file for the Node.JS application

At this point, we can move on to creating our Node.js application. Using a text editor or an IDE editor that supports JavaScript syntax, we’ll create 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

You can do the same in Sublime with this command:

1	subl select-records.js

NOTE: Make sure you run the npm init -y command in a terminal window inside of your project directory; this will ensure that Node is initialized if you haven’t done so already. You’ll also need to execute the npm i pg command to install the PostgreSQL client as well.

Create a Postgres pool connection for the Node app

In our Javascript file, we’ll use the const statement to ‘require’ the pg package and to declare a new Pool connection object for the PostgreSQL connection:

update.js

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"
});

Update the PostgreSQL table using Node’s update() method

After creating the pool object, we can use it to perform a query by passing a string to the object’s query() method call. Let’s look at the following example where we pass an UPDATE SQL statement to the method call. This UPDATE statement is used to change a particular record’s age column value:

1
2
3
4

pool.query("UPDATE student SET age = 24 WHERE id = 3", (err, res) => {
console.log(err, res);
pool.end();
});

NOTE: We use a Node.JS connection pool for the pg client instance to manage the database connections and to recycle the cache of the database. We then call the pool’s end() method to close the object which will free the resources and increase the app’s performance.

Now, let’s go to the directory of the file that we have saved and created– it’s time to run the script. Use the node update.js command in a terminal window to execute the script on your server’s back end using Node.

Your output should look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

undefined Result {
command: 'UPDATE',
rowCount: 1,
oid: null,
rows: [],
fields: [],
_parsers: [],
_types: TypeOverrides {
_types: {
getTypeParser: [Function: getTypeParser],
setTypeParser: [Function: setTypeParser],
arrayParser: [Object],
builtins: [Object]
},
text: {},
binary: {}
},
RowCtor: null,
rowAsArray: false
}

We can perform a SELECT query using the psql command line interface to confirm that the ID=3 in the student table has been updated:

1	SELECT id, age FROM students;

The results are shown below:

1
2
3
4
5
6

id | age
----+-----
1 | 23
2 | 22
3 | 24
(3 ROWS)

Access the pool.query() method’s response attributes

Our next example using pool.query() will parse the response’s attributes after changing the ages of the two students:

1
2
3
4
5
6
7
8
9
10
11
12
13

// call the pool's query() method
pool.query("UPDATE student SET age = 22 WHERE age <= 23", (err, res) => {
pool.end();

// log error's to console
if (err != null) {
console.log("pool.query():", err);
} else {
// log the number of records updated
console.log("Total records updated:", res.rowCount);
console.log("fields:", res.fields);
}
});

The rowCount attribute contains an integer value representing the total number of documents updated. The code shown above should output something like this:

1 2	Total records updated: 3 fields: []

Conclusion on how to update a PostgreSQL table using Node

When you’re using Node.js to develop applications, it’s important to be able to communicate with a database. Using PostgreSQL and Node.js, it’s easy to perform all kinds of database operations within an application. This tutorial showed how to update a PostgreSQL table with Node. With our code examples to guide you, you should be able to develop your own Node.js applications that access and interact with PostgreSQL.

Just the code

Here’s the complete Node.js application we examined in this tutorial:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

const pool = new Pool({
user: "postgres",
host: "localhost",
database: "test",
password: "1234",
port: "5432"
});

// call the pool's query
pool.query("UPDATE student SET age = 24 where id = 3", (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