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 isactive
.Next, you’ll need to have the command-line interface
psql
installed. Use the commandpsql -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:
1 | v12.10.0 |
You can also find out which version of npm
you have using the same -v
option:
1 | npm -v |
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:
1 | npm i pg |
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:
1 | CREATE TABLE student(id SERIAL PRIMARY KEY, firstname TEXT, lastname TEXT, age INT NOT NULL, address VARCHAR(255), email VARCHAR(50)); |
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:
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: 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:
1 2 3 4 5 6 7 | pool.query( "INSERT INTO student(firstname, lastname, age, address, email)VALUES('Mary Ann', 'Wilters', 20, '74 S Westgate St', 'mroyster@royster.com')", (err, res) => { console.log(err, res); pool.end(); } ); |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | undefined Result { command: 'INSERT', rowCount: 1, oid: 0, rows: [], fields: [], _parsers: [], _types: TypeOverrides { _types: { getTypeParser: [Function: getTypeParser], setTypeParser: [Function: setTypeParser], arrayParser: [Object], builtins: [Object] }, text: {}, binary: {} }, RowCtor: null, rowAsArray: false } |
Now, let’s use a SELECT
statement to display the newly inserted records in our table:
1 2 3 4 5 6 7 | 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 | 24 | 7140 University Ave | natalie.fern@hotmail.com 4 | Mary Ann | Wilters | 20 | 74 S Westgate St | mroyster@royster.com (4 ROWS) |
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:
1 2 3 4 5 6 7 8 | var queryString = `INSERT INTO student( firstname, lastname, age, address, email ) VALUES( 'Mary Ann', 'Wilters', 20, '74 S Westgate St', 'mroyster@royster.com' )` pool.query(queryString, (err,res)=>{ } |
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:
1 2 3 4 5 6 7 8 9 10 11 | keys for Postgres error: [ 'name', 'length', 'severity', 'code', 'detail', 'hint', 'position', 'internalPosition', 'internalQuery', 'where', 'schema', 'table', 'column', 'dataType', 'constraint', 'file', 'line', 'routine' ] |
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:
1 | console.log("Postgres error position:", err.position); |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | pool.query(queryString, (err, res) => { // check if the response is not 'undefined' if (res !== undefined) { // log the response to console console.log("Postgres response:", res); // get the keys for the response object var keys = Object.keys(res); // log the response keys to console console.log("\nkeys type:", typeof keys); console.log("keys for Postgres response:", keys); } }); |
The output should look something like this:
1 2 3 4 5 6 7 | keys for Postgres response: [ 'command', 'rowCount', 'oid', 'rows', 'fields', '_parsers', '_types', 'RowCtor', 'rowAsArray' ] |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | const { Pool, Client } = require("pg"); const pool = new Pool({ user: "postgres", host: "localhost", database: "test", password: "1234", port: "5432" }); pool.query( "INSERT INTO student(firstname, lastname, age, address, email)VALUES('Mary Ann', 'Wilters', 20, '74 S Westgate St', 'mroyster@royster.com')", (err, res) => { console.log(err, res); pool.end(); } ); // create a string object for Postgres SQL statement var queryString = `INSERT INTO some_table( id, str, int, bool ) VALUES( 'f73664b6ed53493eaffcc8ca68a41fda', 'this is a string value', 7777333, true )`; pool.query(queryString, (err, res) => { if (err !== undefined) { // log the error to console console.log("Postgres INSERT error:", err); // get the keys for the error var keys = Object.keys(err); console.log("\nkeys for Postgres error:", keys); // get the error position of SQL string console.log("Postgres error position:", err.position); } // check if the response is not 'undefined' if (res !== undefined) { // log the response to console console.log("Postgres response:", res); // get the keys for the response object var keys = Object.keys(res); // log the response keys to console console.log("\nkeys type:", typeof keys); console.log("keys for Postgres response:", keys); if (res.rowCount > 0) { console.log("# of records inserted:", res.rowCount); } else { console.log("No records were inserted."); } } }); |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started