How to Use NodeJS to Insert Into a PostgreSQL Table

Introduction to using NodeJs to insert into PostgreSQL

When you’re working with Node.js, you may find yourself needing to access a database from your application. With PostgreSQL, it’s simple to connect to a database and perform a number of different database operations using Node. In this article, we’ll focus on the task of inserting data into a table. We’ll show you how you can create a simple query app that uses Node.JS to insert into a PostgreSQL table.

Prerequisites to using PostgreSQL and Node.JS

Before we start examining our code examples, let’s take a moment to look at some prerequisites that are necessary for this task:

• First, you’ll need to have PostgreSQL installed on your device. To see whether you have this installed, simply use the command service postgresql status. The output will let you know if the status is active.

• Next, you’ll need to have the command-line interface psql installed. Use the command psql -V to check if you already have it installed on your machine.

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

If you already have Node installed on your machine, you can type node -v to see the version of Node.js that you have.

The output should look something like this:

You can also find out which version of npm you have using the same -v option:

Use the Node Package Manager npm to install the pg Postgres packages

Another thing you’ll need to install is the pg client, which you’ll need to add to your dependencies. Run the following command in your project directory if you don’t have this client installed already:

Create a PostgreSQL table for the Node.JS app

In this section, we’ll create a Postgres table, and then we’ll insert some record data into the table using our Node.js app.

We can create a table using this statement:

Create a JavaScript file for the Node.JS application

Using a text editor or any IDE editor that supports JavaScript syntax, such as Sublime or Visual Studio Code, create a file with a .js extension.

NOTE: Don’t forget to run the npm init -y command in a terminal window within your project directory. This will make sure that Node is initialized. You’ll also need to run the command npm i pg. This command uses the npm package installer to install the Postgres client.

Create a Postgres pool connection for the Node.js app

Now, let’s turn our attention to our Javascript code. We’ll need to use the const JavaScript statement to ‘require’ the pg client package. We’ll also use it to declare a new Pool object, which is used for the PostgreSQL connection:

NOTE: A Node.JS connection pool is used to manage the database connections. This assists with the recycling of the database cache and helps boost the performance of the commands.

Use Node.Js to ‘INSERT’ records into the PostgreSQL table

After we create our new pool object, we’ll use it to execute a PostgreSQL query. This is accomplished by passing a string to the object’s query() method call. Let’s check out a simple example of how we can use Node to insert records into a table:

Let’s try running this script. We’ll go to the directory of the file that we just saved. Then, we’ll type node insert.js in a terminal window to execute the script.

The script will return results that look something like the following:

Now, let’s use a SELECT statement to display the newly inserted records in our table:

Pass a string object to Postgres query() method

In our next INSERT example, we’ll see how the ` character can be used for multi-line JavaScript strings in order to break up a long SQL statement into shorter lines of code. The string object is then passed to the pool.query() method:

Using the multi-line string backtick will help make your SQL statements more readable in your Javascript code.

Parsing the error object returned by the ‘INSERT’ response

You can use JavaScript’s Object.keys() method to return the keys, or attributes, of an object. These attributes can be particularly helpful for understanding error responses. For example, if the pool.query() method returns an error and you were logging the keys for the object response, you would get the following output:

NOTE: If the method doesn’t return an error, the value of the err object will be set to undefined. You can use the === or !== comparison operators to see if it returned an error (e.g. if (err !== undefined) {}).

The information contained in the keys can be useful for debugging errors that occur while inserting records into a PostgreSQL table using Node. Let’s imagine that you want to access the position key’s value to figure out the exact spot where your SQL statement went wrong.

The following example logs the error response’s position attribute to the console:

NOTE: If you try to run your Node script again after it successfully inserted documents, you might get a duplicate key value violates unique constraint error response. You can access the error object’s detail attribute to get more specific information about the error.

Parse the Postgres response object from Node’s ‘pg’ library

Accessing an object’s keys can be useful for more than just error scenarios. We can use the Object.keys() method to get the res response object’s keys as well. Here’s how to access the response object’s keys:

The output should look something like this:

You can look at the response’s rowCount attribute (res.rowCount) to confirm how many records were inserted.

Conclusion to using Node.js to insert into PostgreSQL

If you’re planning to interact with a PostgreSQL database from Node.js, one thing your application may need to do is insert records into a table. Fortunately, this task is an easy one to accomplish. In this tutorial, we showed you how to use Node.js to insert into a PostgreSQL table. Using our code examples as a guide, you’ll be able to incorporate INSERT statements into your own Node.js applications.

Just the Code

Throughout this article, we looked at our Javascript code one section at a time. Here’s the complete Javascript application: