How to Update a PostgreSQL Table with Node

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.

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:

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

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:

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

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

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:

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:

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:

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

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

The results of the query will look like the following:

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:

You can do the same in Sublime with this command:

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

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:

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:

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:

The results are shown below:

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:

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

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: