Bootcamp
Search…
3.6.2: Heroku Postgres

Introduction

To use our app on the internet, we need to provision a Heroku Postgres server, replicate our database schema on the server, and configure our Express app to communicate with the DB.

Install Heroku Postgres Add-On

Setup

Add the Postgres add-on from its Heroku add-ons page. Choose the free version.
When Heroku creates the DB server it will set an environment variable DATABASE_URL on all dynos that tells your system where to find the database and how to connect to it. This happens by default, no additional action required.
The env var value will look something like the following.postgres://lxiysalsaijdaj:[email protected]4-235-62-201.compute-1.amazonaws.com:5432/dknsdfa4bqg32
This value contains several pieces of information.
  1. 1.
    the URL location of the server
  2. 2.
    the HTTP port
  3. 3.
    the password
  4. 4.
    the user name
  5. 5.
    the protocol (postgres)

Run Commands on Heroku Postgres With pg:sql

To verify the contents of our deployed Postgres DB, we can connect to the database through the Heroku command line interface. Read more about pg:sql here.​
heroku pg:psql
We can also use Postico or PGAdmin to connect to our Heroku PG DB, but it may be easier to connect with pg:sql to run migrations and seeders below.

Update Express Database Configs

We need to update Express' index.js to access the DATABASE_URL env var from Heroku. We still need to connect to our local Postgres when running our app locally, so we'll use a condition to see if the DATABASE_URL env var is set.

index.js

// ...
​
let pgConnectionConfigs;
​
// test to see if the env var is set. Then we know we are in Heroku
if (process.env.DATABASE_URL) {
// pg will take in the entire value and use it to connect
pgConnectionConfigs = {
connectionString: process.env.DATABASE_URL,
ssl: {
rejectUnauthorized: false
}
};
} else {
// this is the same value as before
pgConnectionConfigs = {
user: '<MY_UNIX_USERNAME>',
host: 'localhost',
database: '<MY_UNIX_USERNAME>',
port: 5432,
};
}
const pool = new Pool(pgConnectionConfigs);
​
// ...

Set Up Database Schema (Pre-Module 4)

Run our table setup commands in init_tables.sql on the Heroku Postgres DB. The < operator pipes content from the file on the right into the command on the left.
heroku pg:psql < init_tables.sql
Seed our DB by running seed.sql on the Heroku PG DB.
heroku pg:psql < seed.sql
Test that we created tables and seed data by logging into the database and running a SELECT command.
heroku pg:psql
SELECT * FROM cats;
The above is superseded by migrations and seeders after we learn ORMs in Module 4. After Module 4, we will run migration and seeder files with Sequelize on our Heroku server instead of raw SQL commands.

Set Up Database Schema (Post-Module 4)

The following section is only relevant after learning about Sequelize in Module 4.
After we learn about ORMs in Module 4, we'll stop writing raw SQL to set up our databases. Instead we'll run migration and seeder files to set up DB schema and seed data respectively.
To run migrations and seeders in Heroku, we'll still have to set up Postgres as per the steps above, but instead of running raw SQL in pg:sql, we'll run migrations and seeders on the backend server itself.
Step 1: Open the console in the Heroku dashboard.
Step 2: Enter bash and click Run Console to open up a console window running Bash for your server.
Step 3: Run npx sequelize-cli db:migrate and npx sequelize-cli db:seed:all in the console to execute migrations and seeds for your app.

Query Heroku PG DB from Express

We'll test our database by writing a query route in Express.js:
app.get('/cats', (request, response) => {
console.log('request came in');
pool.query('SELECT * from cats').then((result) => {
console.log(result.rows[0].name);
response.send(result.rows);
}).catch((error) => {
console.error('Error executing query', error.stack);
response.status(503).send(result.rows);
});;
});

Debug

When errors happen in our app running on Heroku, we can see our app's console.log output in Heroku's logs to diagnose errors.
We may be tempted to debug our code, deploy to Heroku again, and verify if the error went away in the logs. This is an inefficient way of debugging. When debugging bugs in deployed apps, almost always try to reproduce and fix the error locally before verifying if we fixed the bug in our deployed app. This is because the iteration cycle of debugging locally is much faster than that of debugging in deployment, because of the time required for each deploy.

Docs

Find comprehensive docs on Heroku Postgres here.