NodeJS and PostgreSQL Select Example
NodeJS and PostgreSQL Select Example Introduction
If you’re developing a web application using NodeJS, you may need to interact with a PostgreSQL database. Fortunately, it’s easy to query PostgreSQL from NodeJS using a simple SELECT
statement. In this article, we’ll look at a NodeJS and PostgreSQL SELECT example that will show you how to query your database from a web application.
Prerequisites to using PostgreSQL and NodeJS
You’ll need to make sure a couple of key prerequisites are in place before proceeding with this tutorial:
First, make sure that you have PostgreSQL installed on your machine. You can confirm that the status of the service is
active
by using the commandservice postgresql status
.You’ll also need to have
psql
, the interactive command-line interface for PostgreSQL, installed on your machine. You can use the commandpsql -V
to confirm that it’s installed on your machine; this command will also let you know which version you have installed.
Create a PostgreSQL database and table for the NodeJS app
Now that we’ve reviewed the prerequisites, let’s create a PostgreSQL database and table. We’ll insert some record data into our table using the psql
command-line interface.
The first step is to create a database using the following SQL statement:
1 | CREATE DATABASE some_database; |
Insert some records into the PostgreSQL table
Then, we’ll create a table that we can use to test our NodeJS SELECT
query:
1 | CREATE TABLE some_table (str_col VARCHAR(64), int_col INTEGER, bool_col BOOLEAN); |
Finally, we’ll insert some records into the table using the INSERT INTO
statement:
1 2 3 4 5 | INSERT INTO some_table (str_col, int_col, bool_col) VALUES('hello world', 123456, TRUE), ('ObjectRocket Tutorial', 42, FALSE),('Hello, world', 12345, FALSE), ('another string', 98765, TRUE); |
Create a JavaScript file for the NodeJS application
Once we’ve set up our database and table, we’ll start working on our NodeJS application. You can use a text editor or any IDE editor that supports JavaScript syntax, such as Visual Studio Code, Sublime and Atom. We’ll start by creating 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 |
Here’s how you would do it using Sublime:
1 | subl select-records.js |
NOTE: Be sure to run the npm init -y
command in a terminal window inside of your project directory– this ensures that Node is initialized if you haven’t done so already. You’ll also need to execute the npm i pg
command, using the npm
package installer, to install the PostgreSQL client.
Create a Postgres pool connection for the Node app
Now, let’s start adding some code to our file. We’ll use the const
JavaScript statement to ‘require’ the pg
package; we’ll also use it to 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: "objectrocket", host: "localhost", database: "some_database", password: "mypass", port: "5432" }); |
NOTE: We use a NodeJS connection pool for the pg
client instance to manage the database connections, which helps with recycling of the database cache and improves performance.
Use NodeJS and PostgreSQL to execute a ‘SELECT’ query
After you create the pool
object, you can use it to perform a query by passing a string to the object’s query()
method call. Let’s look at an example that uses Node to return all of the PostgreSQL records from a table:
1 2 3 4 | pool.query("SELECT * from some_table", (err, res) => { console.log(err, res); pool.end(); }); |
Now, let’s go to the directory where we saved our file. We can use the command node test.js
to test our SELECT
query.
The code will return a result that looks something like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | undefined Result { command: 'SELECT', rowCount: 4, oid: null, rows: [ { str_col: 'hello world', int_col: 123456, bool_col: true }, { str_col: 'ObjectRocket Tutorial', int_col: 42, bool_col: false }, { str_col: 'Hello, world', int_col: 12345, bool_col: false }, { str_col: 'another string', int_col: 98765, bool_col: true } ], fields: [ Field { ... ... ... |
Conclusion on using NodeJS to execute a PostgreSQL query
When you’re working with NodeJS to develop web applications, it’s important to know how to query and retrieve information from a PostgreSQL database. In this article, we learned how to perform a SELECT query in PostgreSQL using NodeJS. With this example as a guide, you’ll be able to construct your own web applications that interact with a PostgreSQL database.
Just the Code
Here’s the code we used for our NodeJS and PostgreSQL SELECT example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | const { Pool, Client } = require("pg"); const pool = new Pool({ user: "objectrocket", host: "localhost", database: "some_database", password: "mypass", port: "5432" }); pool.query("SELECT * from some_table", (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