How to Setup PostgreSQL with Node.js

Have you ever wanted to add a PostgreSQL database to the backend of your web application? If so, by the end of this tutorial, you should have a PostgreSQL database up and running with your Node.js web application. PostgreSQL is a popular open source relational database. This tutorial assumes that you have Node and NPM installed on your machine; if you need help installing that, check out this link. First, let's download PostgreSQL.

PostgreSQL

I am writing and testing this tutorial on a Mac, so it will primarily caterMac, but I will include links in the reference section for downloading PostgreSQL on select Linux distributions and Windows as well.

If you are on a Mac, however, you can follow these steps.
First, you must have Homebrew. If you do not have it, you may install it byfollowing the directions here. Once Homebrew is installed and working, you can run the following:

$ brew update
$ brew install postgres

This downloads and installs PostgreSQL for you.

Command-line Setup

Now, open a new instance of a terminal by pressing Command+T. Once you have the new tab, you can start a PostgreSQL server with the command: postgres -D /usr/local/var/postgres.This allows you to use Postgres locally and gives you a logger for all of the commands you run on your databases.

Next, open a new instance of a terminal with Command+Tand enter$ psql.

This is similar to a command center for Postgres. It allows you to create things in your database and plenty more. You can manually enter commands here to set up your environment.

For this example, we willcreatea database called example. To do that, while in the terminal tab with psql running, enter CREATE DATABASE example;. To confirm that the database was made, you should seeCREATE DATABASE as the output. Also, to list all databases, you would usel. Then, we will want to connect to our new database with the command connect example. This should give you the following message telling you that you are connected:

You are connected to database "example" as user

In order to store things in this database, we need to create a table. Enter the following:

CREATE TABLE numbers(   age integer   );

So, this format is probably confusing if you have never seen it before. This is telling Postgres to create a table in this database called numbers, with one column called age, and all items in the age column will be of the data type integer. Now, this should give us the output CREATE TABLE,but if you want to list all tables in a database, you shoulduse the dt command.

For the sake of this example, we are going to add a row to the table so that we have some data to play with and prove that this works. When you want to add something to a database in PostgreSQL, you use the INSERT command. Enter this command to have the first row in the table equal to 732:

INSERT INTO numbers VALUES (732);

This should give you an output of INSERT 0 1. To check the contents of the table, simply typeTABLE numbers;.

Now that we have a database up and running with a table with a value, we can setup our code to access this table and pull the value from it.

Code Setup

In order to follow this example, you will need to make sure that you have the following packages: pg, pg-format, and express. Enter the project directory you plan on working in (and where you have Node and NPMinstalled).

For pg, usenpm install -pg.This is a Postgres client for Node.

For pg-format, usenpm install pg-format.This allows us to safely make dynamic SQL queries.

For express,use npm install express --save.This allows us to create a quick and basic server.

Now that those packages are installed, we can code!

Actual Code

Let's create a file called app.js for this as the main point in our program. At the top, establish your variables:

const express = require('express')
const app = express()
var pg = require('pg')
var format = require('pg-format')
var PGUSER = 'yourUserName'
var PGDATABASE = 'example'
var age = 732

The first two lines allow us to use the package express and help us make our server. The next two lines allow us to use the packages pg and pg-format. PGUSER is a variable that holds the user to your database. Enter your username here in place of yourUserName. PGDATABASE is a variable to hold the database name that we wouldlike to connect to. Then, the last variable is to hold the number that we stored in the database. Next, add this:

var config = {
  user: PGUSER, // name of the user account
  database: PGDATABASE, // name of the database
  max: 10, // max number of clients in the pool
  idleTimeoutMillis: 30000 // how long a client is allowed to remain idle before being closed
}

var pool = new pg.Pool(config)
var myClient

Here, we establish a config object that allows pg to know that we want to connect to the database specified as the user specified, with a maximum of 10 clients in a pool of clients with a time out of 30,000 milliseconds of how long a client can be idle before disconnected from the database. Then, we create a new pool of clients according to that config file. Afterwards, we create a variable called myClient to store the client we get from the database in the next step. Now, enter the last bit of code here:

pool.connect(function (err, client, done) {
  if (err) console.log(err)
  app.listen(3000, function () {
    console.log('listening on 3000')
  })
  myClient = client
  var ageQuery = format('SELECT * from numbers WHERE age = %L', age)
  myClient.query(ageQuery, function (err, result) {
    if (err) {
      console.log(err)
    }
    console.log(result.rows[0])
  })
})

This tries to connect to the database with one of the clients from the pool. If a client successfully connects to the database, we start our server by listening on a port (here, I use 3000). Then, we get access to our client. Next,we create a variable called ageQuery to make a dynamic SQL query. A query is a command to a database. Here, we are making a SELECT query to the database, checking all rows in the table called numbers where the age column is equal to 732. If that is a successful query (meaning, it finds a row with 732 as the value), then we will log the answer.

It's now time to test all your hard work! Save the file and run the command in a terminal:

node app.js

Your output should look like this:

listening on 3000
{ age: 732 }

Conclusion

There you go! You now have a PostgreSQL database connected to your web app. To summarize our work, here is a quick breakdown of what happened:

1. We installed PostgreSQL through Homebrew.
2. We started our Local PostgreSQL server.
3. We opened psql in a terminal to use commands manually.
4. We created a database called example.
5. We created a table in that database called numbers.
6. We added a value to that table.
7. We installed pg, pg-format, and express.
8. We used Express to create a server.
9. We created a pool of clients using a config object to access the database.
10. We queried the table in the database for 732.
11. We logged the value.

Check out the code for this tutorial on GitHub.

About the Author

Antonio Cucciniello is a software engineer with a background in C, C++, and Javascript (Node.Js) from New Jersey. His most recent project called Edit Docs is an Amazon Echo skill that allows users to edit Google Drive files using our voice. He loves building cool things with software and reading books on self-help and improvement, finance, and entrepreneurship. You can find Antonio on Twitter @antocucciniello and on GitHub.