CS 313 - Preparation Material

10 Prepare : Using Heroku Databases

Overview

This document explains how to connect to a remote Heroku database from a local Node.js server.

Ultimately, you will want your database to be running out at Heroku, and you will want our Node.js application also running at Heroku. During development, however, it may be convenient to run your Node.js application on your local computer for quick turnaround in your development process. If you are happy pushing your code to Heroku after each change, you do not need to use these steps.

One approach to local development would be to use a local database server for your local development. This is commonly done by professional developers, and is what some of the instructions reference. Then, when pushing your code to Heroku, you also need to update your Heroku database to make sure the code their matches. This has caused problems for several students in the past, so an alternative approach is described here, where you will only have one database (at Heroku), and connect to it from both Heroku, and from your local environment.

Creating the Application

First, create the node application itself:

In a new, empty directory run npm init, create a new server.js file, etc.
Set up this directory as a Git repository:

git init
Create a file: ".gitignore" with "node_modules/" in it, so we don't get prompted to add that directory to git.
Create a new Heroku application:

heroku create
Help Heroku know how to start your application:

Create a file: "Procfile" with the following in it: web: node server.js

Then, update your package.json file to include a start script:
```
...
"scripts": {
    "start": "node server.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
...
```

Adding a Database to your Heroku application

First, create the database at Heroku:

heroku addons:create heroku-postgresql:hobby-dev

Learn the database connection string URL for your new database:

heroku config -s

This gives you back something like:


DATABASE_URL='postgres://cgojnkckkncmxf:e510ef76e9ff323231afa499c4d2bd748c1f2ea77e9da50f906dbe8f8c3e1@ec2-23-21-184-181.compute-1.amazonaws.com:5432/dfnorfi59oivff'

Create the necessary tables in your database. First connect with:

heroku pg:psql

Then, issue the SQL statements to create the necessary tables.

Accessing a Heroku database from your local Node.js server:

Create a connection string variable to hold this information (of course using your connection string, not this exact one):
```
const connectionString = process.env.DATABASE_URL || "postgres://cgojnkckkncmxf:e510ef76e9ff323231afa499c4d2bd748c1f2ea77e9da50f906dbe8f8c3e1@ec2-23-21-184-181.compute-1.amazonaws.com:5432/dfnorfi59oivff";
```
This says to use a connection string from the environment variable DATABASE_URL if it is present, otherwise to use the one we just found that is defined directly in this file.

IMPORTANT: Make sure to add ?ssl=true at the end of this, otherwise Heroku will reject the connection:


const connectionString = process.env.DATABASE_URL || "postgres://cgojnkckkncmxf:e510ef76e9ff323231afa499c4d2bd748c1f2ea77e9da50f906dbe8f8c3e1@ec2-23-21-184-181.compute-1.amazonaws.com:5432/dfnorfi59oivff?ssl=true";

When you create the Pool object, tell it to use this connection string:

const pool = new Pool({connectionString: connectionString});

Verify that your connection to the database is working, by running a very basic query—perhaps not even part of a Web request at this point, just by running a simple script at the console containing a query like this:


var sql = "SELECT * FROM some_table_here";

pool.query(sql, function(err, result) {
    // If an error occurred...
    if (err) {
        console.log("Error in query: ")
        console.log(err);
    }

    // Log this to the console for debugging purposes.
    console.log("Back from DB with result:");
    console.log(result.rows);


});

Excluding the sensitive information from Git

While the above code would work just fine, it does have a problem. It includes the sensitive connection string information in your source code which will be included in your Git repository.

A possible solution to this is to put your database connection string in an environment variable on your local computer, just like Heroku does out at their server. This way, your code will always just get the information from the environment, regardless of where it is running.

To include this in an environment variable, you could put it into a system environment variable just like you would with the "PATH" or any other environment variable. This section uses a different approach, where we add an environment variable to a file, and then load this file into the environment as part of running our node application, by use of the "dotenv" Node.js package. This is nice because now this variable will only be present for this application.

Create a new file: .env, and put the following in it (of course using your connection string, not this exact one):


DATABASE_URL='postgres://cgojnkckkncmxf:e510ef76e9ff323231afa499c4d2bd748c1f2ea77e9da50f906dbe8f8c3e1@ec2-23-21-184-181.compute-1.amazonaws.com:5432/dfnorfi59oivff?ssl=true'

Add this file to your .gitignore file, by adding .env to the list of file and directory names in the .gitignore file.
Use the dotenv package to load everything from your .env file into the environment variables in your system.

First, install the new package:

npm install dotenv --save

Then, include a call to it at the top of your server.js file with:

require('dotenv').config();
Change your connection string variable above to only pull from the environment variable now:

const connectionString = process.env.DATABASE_URL;
Test your application and celebrate!