How to Use PostgreSQL with Node.js

Getting Started

Because the code we're writing for this tutorial is "standalone" (meaning it's not part of a bigger app or project), we're going to create a Node.js project from scratch. If you don't already have Node.js installed on your computer, read this tutorial first and then come back here.

Once you have Node.js installed on your computer, from your projects folder on your computer (e.g., ~/projects), create a new folder for our work:

Terminal

mkdir postgresql

Next, cd into that directory and create an index.js file (this is where we'll write our code for the tutorial):

Terminal

cd postgresql && touch index.js

Next, we want to install two dependencies, pg and express:

Terminal

npm i pg express

The first will give us access to the Node.js driver for PostgreSQL (what we'll use to connect to the database in our code) and the second, Express, will be used to spin up a demo server.

One last step: in the package.json file that was created for you, make sure to add the field "type": "module" as a property. This will enable ESModules support and allow us to use the import statements shown in the code below.

With that in place, we're ready to get started.

Installing and configuring PostgreSQL

Before we jump into writing code, we need to make sure that you have PostgreSQL installed on your machine and that PostgreSQL is correctly added to your command line's PATH variable (this creates shortcuts to folders on your computer and makes them accessible from any location/directory in your command line).

The best starting place is on the PostgreSQL downloads page. From here, select your operating system and on the next page, locate the red "Download the installer" link near the top of the page.

After you've downloaded the installer, run it, and complete the on screen steps. Make sure to install all of the necessary dependencies (whatever is checked by default in the installer UI is preferred to avoid issues).

Note: if you're on MacOS, this should be all you need to do before continuing on to the next part of the tutorial.

If you're on Windows you need to complete one additional step: adding the PostgreSQL version folder to your PATH.

To do this, in the "Type here to search" box on your launch bar, type "env" and click the link for the "Edit the system environment variables" result. In the resulting "System Properties" window, locate and click the "Environment Variables..." button in the bottom-right of the window.

In the box labeled "User variables for <username>," locate the "Path" row, click to highlight it, and then press the "Edit..." button beneath the list.

In the "Edit environment variable" window that pops up, click the "New" button on the right-side of the window and in the text field that appears, type C:\Program Files\PostgreSQL\14\bin. Keep in mind: the 14 in this path represents the latest version of PostgreSQL that should be installed on your computer (as of writing). This may need to be adjusted based on when you read this tutorial. It's recommended that you navigate to the C:\Program Files\PostgreSQL folder and locate the newsest/highest version number in that folder to use in this path.

Once set, click "OK" on each of the windows that popped up until now. It's recommended that you restart your computer after doing this to ensure that the variables load properly into your command line.

After you've restarted your computer, you're ready to continue with the tutorial.

Adding PostgreSQL helper commands to package.json

First, we need to make sure that we have a running PostgreSQL server and a database on that server. To make this a bit easier, we're going to begin by opening up the package.json file at the root of our project.

/package.json

{
  "name": "ny290syhfjifjekd",
  "type": "module",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "pg:init": "pg_ctl init -D data",
    "pg:start": "pg_ctl -D data start",
    "pg:createdb": "createdb -h 127.0.0.1 app",
    "pg:stop": "pg_ctl -D data stop",
    "start": "NODE_ENV=development && node index.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.3",
    "pg": "^8.7.3"
  }
}

Our goal here is to add a few "helper" commands to the scripts section of our package.json file. Specifically, we need to add five commands:

1. pg:init which will initialize the data directory where PostgreSQL will store our data.
2. pg:start which will start the PostgreSQL server.
3. pg:createdb which will create a PostgreSQL database on the server.
4. pg:stop which will stop the PostgreSQL server.
5. start which we'll use to start our demo server via Express.

Notice that for the pg prefixed scripts, we're using a command pg_ctl or createdb. Earlier, when we installed PostgreSQL, these commands were exposed to your PATH variable, meaning, they're globally accessible in your terminal (if you're on Windows, make sure you completed the additional steps above to make this work).

Once you have these in place, we want to run the following via the terminal, from the root of the project folder we created earlier:

Terminal

mkdir data

Next, to initialize our PostgreSQL server, run:

Terminal

npm run pg:init

You should see a message about this being successful after a few seconds. Next, we want to start the server up:

Terminal

npm run pg:start

This will show some output and signal that the server was successfully started. Finally, to create our database we want to run:

Terminal

npm run pg:createdb

This will create a database called app on the server you just started which is the database we will use in our examples below.

Creating a connection pool

Assuming that all of that worked, now, we need to set up our connection to PostgreSQL via our Node.js code. To begin, we want to create a new file at the root of our project, postgresql.js:

/postgresql.js

import postgresql from 'pg';

const { Pool } = postgresql;

export default (callback = null) => {
  // We'll handle our connection to PostgreSQL here...
};

In this new file, to start, we want to import the default export from the pg package we installed earlier as postgresql. Next, just beneath the import, we want to "pluck off" the Pool (case-sensitive) class using JavaScript destructuring (denoted by the {} after the const).

Here, Pool is a class which allows us to create a pooled connection to our PostgreSQL database. This is important. In a multi-user web app, we want to be as efficient as possible when talking to our database. Creating one connection per request means that we can very easily overwhelm our database, potentially leading to downtime.

When using PostgreSQL, we can use the pooling feature which creates a "pool" of connections which can be temporarily occupied by a user and then returned to the pool when finished (think of this like a library book being checked out and then returned later).

Terminal

import postgresql from 'pg';
import os from 'os';

const { Pool } = postgresql;

export default (callback = null) => {
  // NOTE: PostgreSQL creates a superuser by default on localhost using the OS username.
  const pool = new Pool({
    user: process.env.NODE_ENV === 'development' && (os.userInfo() || {}).username || '',
    database: 'app',
    password: '',
    host: '127.0.0.1',
    port: 5432,
  });

  // We'll handle making the connection accessible in our app here...
};

With the Pool class accessible, inside of the function we're exporting from our file, we want to create a new instance of it and assign it to the variable pool (lowercase, here). This variable, pool will contain the "pool instance" and is what we'll use as the starting point for connecting to our PostgreSQL database.

To the new Pool() class, we pass an options object which contains the connection information for the database. Here, because we just started our PostgreSQL server locally, we set the host to 127.0.0.1 (the IP address version of localhost) and the port to 5432 (the default port for PostgreSQL).

We also set the database to "app" (the one we just created with the db:createdb script) and set the password to an empty string. For the user, we do something interesting.

By default, PostgreSQL creates a superuser locally for us using the current username on the operating system (e.g., my username is rglover on my laptop, so PostgreSQL created the username rglover).

While we could hardcode this, it makes our code fairly inflexible. To get around this, up top, we've added an additional import for the os package which is a core Node.js module which gives us access to information about the operating system. Here, assuming our NODE_ENV is development (you'll notice we set this as part of the start script we defined earlier), we call to the os.userInfo() function which we expect to return an object describing the current operating system user.

On that object, the username field will match the currently logged in user on the computer (the same value that PostgreSQL will use to create the superuser). The (os.userInfo() || {}).username part here is a safety trick: on the off-chance that os.userInfo() didn't return anything, we want to fall back to an empty object so that we don't cause an accidental runtime error if we get back null or undefined (you don't have to do this, but it does make our code a bit more tolerant to errors).

With this, now we have our pool connection, but we're not done yet. In order to make this connection useful, we need to make it accessible to our entire app/codebase.

Terminal

import postgresql from 'pg';
import os from 'os';

const { Pool } = postgresql;

export default (callback = null) => {
  // NOTE: PostgreSQL creates a superuser by default on localhost using the OS username.
  const pool = new Pool({
    user: process.env.NODE_ENV === 'development' && (os.userInfo() || {}).username || '',
    database: 'app',
    password: '',
    host: '127.0.0.1',
    port: 5432,
  });

  const connection = {
    pool,
    query: (...args) => {
      return pool.connect().then((client) => {
        return client.query(...args).then((res) => {
          client.release();
          return res.rows;
        });
      });
    },
  };

  process.postgresql = connection;

  if (callback) {
    callback(connection);
  }

  return connection;
};

Just beneath our call to new Pool(), here, we've added a new variable connection set equal to an object with two values on it: pool (our new pool instance returned by new Pool()) and query.

Beneath this object definition, notice that on the Node.js process, we're adding a property postgresql and assigning it to this connection object. This will give us global access to our pool connection throughout our entire app (the process object is accessible throughout our entire codebase).

Focusing back on the query part, this is a special function being added as a convenience by us. When using a connection pool, every time we want to perform a query, we need to connect to that pool, run our query, and then return or "release" the connection back to the pool.

While it's perfectly fine to do this, it can be cumbersome. To make our work a bit easier, here, the query property we're setting on connection "automates" that connection and release process. First, we assign query to a plain JavaScript function and use the JavaScript rest operator ... to say "scoop up any arguments passed to this function in a variable called args whose scope is the body of the function we're defining."

Inside of that function, we return a call to pool.connect() which itself returns a JavaScript Promise. When that Promise is resolved, we expect it to pass a client connection. On that connection, we can perform queries to our PostgreSQL database, so we call client.query() passing the ...args value from our wrapper function. Here, ...args is referred to as a "spread" as we're "spreading out" the value of args as the arguments being passed to client.query().

So, assuming we called the function we're assigning to query like query('SELECT * FROM books'), we would effectively be writing client.query('SELECT * FROM books'). The ...args part just automates the process of moving all of the arguments passed to a function and then "handing them off" to another function (or object).

After client.query() is called, we expect it to return a response from the database, and on that response, a property called rows which is an array of rows from our database matching our query (if there are any).

Just like pool.connect() we expect client.query() to return a JavaScript Promise. Here, in the .then() callback function (what gets called after the Promise is resolved/our query completes), we make a call to client.release() to put our connection back in the pool and then return res.rows. This ensures that the value of res.rows "bubbles up" to the original call to query.

Following the pattern here, we'd expect to be able to do something like this:

const rows = await process.postgresql.query('SELECT * FROM books');
console.log(rows);
/*
  [{ id: 1, title: 'The Best Book Ever', author: 'Author McAuthorstuff' }]
*/

This is exactly what we intend to wire up next. Before we finish up with this file, we want to call attention to the callback argument being passed to the function we're exporting from this file. If it's defined, after we've set our connection onto process, we want to call that function and pass it our connection object. We'll see why next.

Setting up a demo app

Now we're ready to put our connection to use. To do it, we're going to set up a barebones Express.js application, seed our database with some test data, and then wire up an Express endpoint where we can test a call to the query function we just defined above.

/index.js

import express from 'express';
import postgresql from 'postgresql';

postgresql();

const app = express();

app.get('/books', async (req, res) => {
  const rows = await process.postgresql.query('SELECT * FROM books');
  res.status(200).send(JSON.stringify(rows));
});

app.listen(3000, () => {
  console.log('App running at http://localhost:3000');
});

This is all we need. Here, we import express from the express package we installed earlier and create a new instance of it by calling it as a function express(), storing it in the variable app.

Next, focusing on the bottom first, we call to app.listen() which tells Express to start listening for HTTP requests on port 3000 of our computer (to signal that this process has completed, we add a callback function to this to log out a message letting us know the server started).

Above this, we define an HTTP GET route at the URL /books (this will be accessible in the browser at http://localhost:3000/books). Inside of that route's callback handler, we assume we'll have access to our process.postgresql value we assigned in /postgresql.js (what we're importing up top and calling the exported function of above our call to express()).

Finally, with the resulting rows we expect back from our query convenience function, we respond to the initial request, sending back a stringified copy of rows.

In your terminal, from the root of our project, if we run npm start, we should see the "App running..." message printed to the console. If we visit that route http://localhost:3000/books in a browser, we should see an empty array printed to the screen.

If you do, this signifies that our connection to PostgreSQL is working and, technically speaking, our work is complete.

Before we wrap up, though, it'd be helpful to see some real data. To do that, we need to leverage the callback function we anticipated in /postgresql.js.

Seeding the database

In an app, the process of generating test data is loosely known as "seeding the database." Typically, you will create a "fixture" which is some code that automates the seeding process (behave yourself).

/index.js

import express from 'express';
import postgresql from './postgresql.js';

postgresql(async (connection) => {
  await connection.query('CREATE TABLE IF NOT EXISTS books (id bigserial primary key, title text, author text);');
  await connection.query('CREATE UNIQUE INDEX IF NOT EXISTS title ON books (title);');

  const books = [
    { title: 'Mastering the Lightning Network', author: 'Andreas Antonopoulos' },
    { title: 'Load Balancing with HAProxy', author: 'Nick Ramirez' },
    { title: 'Silent Weapons for Quiet Wars', author: 'Unknown' },
  ];

  for (let i = 0; i < books.length; i += 1) {
    const book = books[i];
    await connection.query(`INSERT INTO books (title, author) VALUES ('${book.title}', '${book.author}') ON CONFLICT DO NOTHING;`);
  }

  console.log('PostgreSQL database seeded!');
});

const app = express();

app.get('/books', async (req, res) => {
  const rows = await process.postgresql.query('SELECT * FROM books');
  res.status(200).send(JSON.stringify(rows));
});

app.listen(3000, () => {
  console.log('App running at http://localhost:3000');
});

Here, we've added a callback function to our call to postgresql(), expecting a connection object to be passed as an argument. On that connection, we need to run three queries:

1. A query to create a table called books in our database if it doesn't exist.
2. A query to create a unique index on the title column of our books table.
3. For each book that we want to "seed" the database with, a query to insert that book if it doesn't already exist.

The specific queries and code here are not terribly important. The main thing to call attention to is our usage of the query convenience function we wired up. Not only can we call it to get data back in return, but we can also use it to run arbitrary queries not expecting a return value.

Here, we do just that, setting up our table and a unique index on our title (this prevents restarts of the app from creating duplicates) and then looping over our array of books, performing an INSERT query for each book into the books table.

Now, if we restart our app and then load up the http://localhost:3000/books route in the browser, we should see our three books returned.

Wrapping up

In this tutorial, we learned how to set up and connect to a PostgreSQL database using Node.js. We learned how to get PostgreSQL working on our computer, how to write some NPM scripts to help us setup our database, and how to write a module with a convenience function for connecting to our PostgreSQL server as a connection pool and running queries. Finally, we learned how to seed the database with some test data and set up a tester route in Express.js to verify our convenience function was working.