Heroku and PostgreSQL

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you’re using the Heroku platform to build and run your apps, you may want some of your applications to make use of a PostgreSQL database. Fortunately, it’s easy to use Heroku and PostgreSQL together to store an app’s data. In this article, we’ll show you how to set up a Heroku app and a PostgreSQL database for the app’s backend data.

Prerequisites

Before following along with the instructions in this tutorial, make sure to first sign up for a Heroku account using a valid email address.

Create a new Heroku application

Once you’ve created a Heroku account, you can create a unique app name. Be sure to use lowercase words and hyphens instead of spaces. You can also use the heroku create command to have Heroku generate a name and a new app for you.

Install PostgreSQL on your machine

PostgreSQL will need to be installed on your machine in order to follow along with this tutorial. The which psql command can be used to see if the psql command-line interface for Postgres is already installed. You can also use the postgres -V command to get the current installed version of Postgres.

Install Postgres on Debian Linux

If you’re using a Debian-based Linux machine or server, use the following apt-get command to install Postgres:

1
sudo apt-get install postgresql

Install Postgres on macOS X

If you’re doing your local development on macOS, you can use Homebrew to install PostgreSQL using the following brew command:

1
brew install postgresql

Install Git on your local machine

Heroku allows you to deploy your applications using git and stores your application on a github repo, so you’ll need to have git installed on your machine as well. Run the following command to confirm that git is installed and working:

1
git --version

Heroku CLI and Heroku-Repo

You’ll need to install the ‎Heroku command-line interface (CLI) so that you can manage the repo for your app on your local machine. The CLI will also allow you to test apps locally for development purposes.

Install the Heroku CLI on your local machine

To set up the CLI on your local machine, download the appropriate interactive installer for your operating system; otherwise, you can use the following Homebrew command if you’re developing locally on a Mac:

1
brew install heroku

If you have the SNAP package manager installed on your Linux server or machine, you can also install Heroku CLI using the following snap install command:

1
sudo snap install --classic heroku

You can then use the following command to confirm that Heroku installed correctly:

1
heroku --version

Install the heroku-repo package

Next, let’s install the heroku-repo plugin for the Heroku CLI. This will provide additional commands to alter your app’s repo:

1
heroku plugins:install heroku-repo

NOTE: To use the plugin to manage your repository, simply use the following command: heroku repo:COMMAND.

Set up the Heroku project

Now that we’ve installed all the tools needed to work with Heroku and PostgreSQL, let’s try creating a new project folder for our Heroku app. In your terminal or command prompt window, run the mkdir command followed by the folder name for your project, and then change into that directory. You can accomplish both of these tasks using the following commands:

1
mkdir heroku-app && cd heroku-app

You’ll need to create a Procfile for the app so that Heroku will know which commands and processes to run when building the app. The command shown below will create the Procfile in your project directory and deploy a special HTTP Server:

1
echo "web: gunicorn app:app" > Procfile

We’ll also need a requirements.txt file so that Heroku knows what applications and packages the app will use. For the time being, we’ll just instruct it to install gunicorn:

1
echo "gunicorn" > requirements.txt

Log into Heroku

At this point, we’re ready to complete the authentication. Make sure you are already signed into Heroku on your default browser before executing the command shown below:

1
heroku login

NOTE: If you’d rather authenticate and login interactively in your terminal instead of using the browser, you can run the following command: heroku login -i. This command will prompt you for a password.

Deploy the Heroku app

If you haven’t yet created one, you can now use the heroku create command to have Heroku automatically generate a name for an app and create one; otherwise, you can use the heroku access --app {SOME_APP_NAME} command to connect to the app you already created in the browser.

Set the repo for the Heroku app

Next, we’ll use the following git:remote command to set the current repo and directory to the correct Heroku app for our account:

heroku git:remote -a {SOME_APP_NAME}

Once everything has been created and set, we can use the git remote -v command to confirm that git has initialized to the correct Heroku application.

Screenshot of setting Heroku app repo in terminal

Clone the Heroku app

The following command can be used to download and clone an app from your remote repo into your local project directory if needed:

1
heroku repo:clone -a {APP_NAME}

NOTE: It’s not recommended to store or back up your Heroku app on its Git repository, since the main purpose of this repo is to deploy the app. Instead, back up your app project directory on another, dedicated github repository, server or hard drive.

Heroku and Postgres

We’ll need to access the psql PostgreSQL command-line interface in order to create tables and record data for your app’s data.

Install the PostgreSQL add-on for the Heroku app

We can install the heroku-postgresql add-on using the following command. This will allow us to have access to the Heroku app’s psql interface:

1
heroku addons:create heroku-postgresql:hobby-dev

NOTE: The hobby-dev billing tier is the free PostgreSQL database plan for hobbyists and smaller apps. If you’d like more support and storage for your app data, you’ll need to upgrade the billing plan for your app on the Heroku website.

The response from the create heroku-postgresql command will look like this:

1
Created postgresql-SOME_URL as DATABASE_URL

If you’re not sure of your current billing tier, use the heroku pg:info command to get more information about your Postgres database plan for your Heroku app.

Get the Heroku PostgreSQL database credentials

Next, let’s navigate to the data management section of Heroku’s website. Once you’re in this section, click on the heroku-postgresql service for the application that you just created.

After you access the PostgreSQL database service, you’ll need to navigate to the Settings tab and then click View Credentials to see your Postgres username and password.

Screenshot of Heroku website view PostgreSQL database credentials for app

NOTE: Heroku will periodically change the psql username and password for your PostgreSQL databases. If you get a permission denied error while accessing your app’s psql interface, navigate back to Heroku’s data management page to check your credentials.

Copy the Heroku CLI psql command for Postgres

At the end of your Postgres database credentials you should see a heroku pg:psql command; this value is found under the Heroku CLI field. Copy that psql command and paste it into a terminal window, and this will allow you to access your Heroku-Postgres database using the credentials defined in the database credentials.

Creating PostgreSQL credentials for a Heroku app

We can now use the following command syntax to create Postgres credentials for our app:

1
heroku pg:credentials:create postgresql-SOME_URL --name orkb -a appname

NOTE: Keep in mind that you’re not allowed to create your own credentials if you only have the free hobby-dev Postgres add-on.

If you need to get your Heroku PostgreSQL credentials, use the command shown below:

1
heroku pg:credentials:url postgresql-SOME_URL

Heroku’s psql interface

The heroku pg:psql command listed on your database’s credentials page will look something like this:

1
2
3
heroku pg:psql
    postgresql-APP-URL-12345
    --app some-unique-name

NOTE: If you try to enter the psql interface without using the correct arguments for --app and for the database URL, you’ll encounter an ERROR: syntax error at or near "..." error whenever you attempt to alter the database.

Execute a PostgresSQL statement

There’s no need to create a database with the CREATE DATABASE SQL statement– Heroku has already taken care of that for you, and it doesn’t allow you to create roles or databases anyway. However, you can create tables and row data in the heroku pg psql interface.

Create a table for your Heroku app Postgres data

If we try to execute the \dt command in psql, we’ll get the response shown below:

1
Did not find any relations.

The reason we get this response is because we don’t have any tables for our app yet. Let’s use the following SQL command to create a simple table. This will allow us to test if we can successfully execute SQL statements to alter the app’s database:

1
CREATE TABLE test_table (id INTEGER PRIMARY KEY);

The above command should return a CREATE TABLE response if successful.

Screenshot of heroku pg:psql command in a terminal creating Heroku PostgreSQL table for Heroku app

Now, let’s execute the \dt command once again. This time we should see the test_table PostgreSQL table listed. We can then use the \q command to quit the interface.

Conclusion

When you’re doing app development with Heroku, it’s important to know how to use PostgreSQL to store the application’s data. In this tutorial, we showed you how to create and set up and app that uses both Heroku and PostgreSQL. With our step-by-step instructions and examples, you’ll be able to build your own applications and use a PostgreSQL database in your work.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.