NodeJs and PostgreSQL Query Example
NodeJs PostgreSQL Query Example Introduction
This tutorial will explain how to create a simple NodeJs PostgreSQL query example app with the INSERT
SQL statement. The NodeJs is a group of modules used to interface with a PostgreSQL database. NodeJs supports many features including pool connection and inserting records into a PostgreSQL table.
Prerequisites for using PostgreSQL and NodeJs
- PostgreSQL must be properly installed. Execute the
service postgresql status
command to confirm the status isactive
. Press the CTRL + C keys to exit. Next, execute thepsql -V
command to verify the interactive PSQL command-line for PostgreSQL is properly installed and working. The results should resemble the following:
Verify the Node is and its ‘npm’ Package Manager are Installed
If NodeJs is already installed, execute the node -v
command to see the currently installed version. The output will resemble the following:
1 | v12.10.0 |
As shown below, the same -v
option can be used to instruct Node to return the installed version of npm
:
1 | npm -v |
Use the Node Package Manager to install the ‘pg’ Postgres packages
The pg
client must be installed and added to the dependencies. Accomplish this by running the following command in the project directory:
1 | npm i pg |
Create a PostgreSQL database and table for the NodeJs app
A PostgreSQL database and table must be created and record data must then be inserted using the NodeJs. The first step is to create a database using the following code:
1 | CREATE DATABASE database_name; |
Here is an example:
1 | CREATE DATABASE test; |
Now create a table, to hold record data, with the following command for testing the NodeJs INSERT
query:
1 2 | CREATE TABLE TABLE_NAME (COLUMN_NAME + DATATYPE + CONSTRAINTS[OPTIONAL]); |
An example follows:
1 2 | 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 NodeJs application
Using a text editor or IDE editor that supports JavaScript syntax, such as Sublime or Visual Studio Code, create a file with an extension of (.js
) for inputting codes.
For Visual Studio Code, create a new JavaScript file in the terminal using the following command:
1 | code insert.js |
For Sublime use:
1 | subl insert.js |
NOTE: Execute the npm init -y
command in a terminal window, inside the project directory, to initialize Node. The npm i pg
command must also be executed using the npm package installer to install the Postgres client.
Create a Postgres Pool Connection for the Node App
Execute the below const
JavaScript statement to ‘require’ the pg
package and declare a new Pool
connection object 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 NodeJs connection pool for the pg
client instance is used to manage the database connections for recycling of the cache of the database and to increase performance of the commands.
Use NodeJs to ‘INSERT’ Records into the PostgreSQL Table
A query can be made by passing a string to the object’s query()
method call after the pool
object has been created.
Here is an example of how to use Node to return all of the PostgreSQL records in a query:
1 2 3 | 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) }) |
Interpolate values into a PostgreSQL statement using template strings
Because long queries can become illegible and cumbersome, following is a screenshot of an example that can break the query string into multiple lines:
JavaScript allows you to use multi-line strings, or “template strings,” to pass or interpolate variables into strings. The marker for the template literal uses backticks (`) and data can be interpolated into the string using the
${}` syntax. The following JavaScript string declaration example demonstrates how to use template strings:
1 2 3 | const myName = 'ObjectRocket'; console.log(`Hello, my name is ${myName}!`); // should log 'Hello, my name is ObjectRocket!' to console |
NOTE: Template strings are a new JavaScript development since 2015 and requires at least ES6 (ECMAScript 6). However, this is typically not a problem with up-to-date browsers.
Execute a PostgreSQL parameterized query using JavaScript Template strings
The below example concatenates an SQL string for a Postgres query by interpolating variable values. This example declares a template literal with the $1
variable substitute and then passes the string to the pg module’s query()
method:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | // String for the PostgreSQL table name const tableName = 'student' // Array for the Postgres query's substitute values const values = [ 'Mary Ann', 'Wilters', 20, '74 S Westgate St', 'mroyster@royster.com' ] // Declare a Template literal string for the SQL statement let sqlString = ` INSERT INTO ${tableName} (firstname, lastname, age, address, email) VALUES ($1, $2, $3, $4, $5)` |
NOTE: Because they are target expression, there must be an equal number of substitute values in the array, such as $1
and $2
, in the string literal.
Now the sqlString
string literal and the values
array must be passed to the pool connection instance’s query()
method to make the API call to insert the string into the PostgreSQL table as a record. This is shown here:
1 2 3 4 5 6 7 8 9 | pool.query(sqlString, values, (err, res) => { if (err) { console.log('pool.query():', err) } if (res) { console.log('pool.query():', res) } }) |
Now navigate to the directory of the file that was created via the terminal. Type node insert.js
in the terminal window to execute the script using Node.
Using the ‘SELECT’ Statement to Create a PostgreSQL Query in NodeJs
Execute the select statement to log the newly inserted table record into the console terminal. Declare another template literal for the SELECT
statement query and then declare a parameter for the query as follows:
1 2 | const ageQuery = 20 const selectQuery = `SELECT * FROM ${tableName} WHERE age = $1` |
Now just pass the variables to the pool’s query()
method a second time to execute a SELECT
query to the PostgreSQL database. Don’t forget to pass the integer variable as an array by enclosing it in square brackets ([]
):
1 2 3 4 5 6 7 8 9 | pool.query(selectQuery, [ageQuery], (err, res) => { if (err) { console.log('SELECT pool.query():', err) } if (res) { console.log('SELECT pool.query():', res) } }) |
NOTE: As shown the following screenshot, the query result object’s rowCount
integer attribute can be accessed to obtain the total number of records returned by the query.
End the PostgreSQL pool object instance to free resources
It is good practice to call the pool instance’s end()
method after all the API calls are finished to free up system resources so the app doesn’t end up with memory leaks. Accomplish this with the following command:
1 | pool.end() |
Conclusion to the NodeJs PostgreSQL Query Example
This tutorial explained how to create a simple NodeJs PostgreSQL query example app with the INSERT
SQL statement. The article explained how to use the node package manager to install the ‘pg’ Postgres packages, create a PostgreSQL database, table and file for the NodeJs application and how to create a Postgres pool connection for the node app. The tutorial also covered how to use NodeJs to insert records into the PostgreSQL table, interpolate values into a PostgreSQL statement using template strings and execute a PostgreSQL parameterized query using JavaScript template strings. Remember to call the pool instance’s end()
method after all the API calls are finished to free up system resources.
Just the Code
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 | const {Pool,Client} = require('pg') const pool = new Pool({ user: "postgres", host: "localhost", database: "test", password: "1234", port: "5432" }) // String for the PostgreSQL table name const tableName = 'student' // Array for the Postgres query's substitute values const values = [ 'Mary Ann', 'Wilters', 20, '74 S Westgate St', 'mroyster@royster.com' ] // Declare a Template literal string for the SQL statement let sqlString = ` INSERT INTO ${tableName} (firstname, lastname, age, address, email) VALUES ($1, $2, $3, $4, $5)` // Pass the string and array to the pool's query() method pool.query(sqlString, values, (err, res) => { if (err) { console.log('pool.query():', err) } if (res) { console.log('pool.query():', res) } }) const ageQuery = 20 const selectQuery = `SELECT * FROM ${tableName} WHERE age = $1` // Pass the string and integer to the pool's query() method pool.query(selectQuery, [ageQuery], (err, res) => { if (err) { console.log('SELECT pool.query():', err) } if (res) { console.log('SELECT pool.query():', res) } }) pool.end() |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started