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 isactive
.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 commandpsql -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:
1 | v12.10.0 |
You can also use the same -v
option to have Node return its version of the npm
package manager:
1 | 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:
1 | 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 | id | firstname | lastname | age | address | email ----+-----------+----------+-----+---------------------+--------------------------- 1 | Cory | Gibes | 23 | 83649 W Belmont Ave | cory.gibes@gmail.com 2 | Danica | Bruschke | 22 | 840 15th Ave | danica_bruschke@gmail.com 3 | Natalie | Fern | 25 | 7140 University Ave | natalie.fern@hotmail.com (3 ROWS) |
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