Month: January 2018

Django And Heroku Postgres Databases

I’m using Heroku to run one of my Django applications. The application has a Heroku Postgres Add-on for storing data. I’d like to use this ‘live’ data when I’m working on the application on my local development set-up.

I can see two options – connect directly to the live database or retrieve a copy to use locally. Using the live database directly could lead to issues if I make a mistake so I’m going with the local copy.

PostgreSQL Mac Set-up

Initially my local app was using SQLite so the first thing I need to do is get my development Mac set up for PostgreSQL. As detailed in the Heroku docs I used the Postgres.app package.

To confirm it was running ok:

$ which psql
 /Applications/Postgres.app/Contents/Versions/latest/bin/psql

And verified:

$ psql -h localhost
 psql (10.1)
 Type "help" for help.

Making A Local Database Copy

The pg:pull command can be used to pull remote data from a Heroku Postgres database to a database on the local machine. I also want to use a user and password so the command I used is:

PGUSER=postgres PGPASSWORD=password heroku pg:pull DATABASE_URL bcmlocaldb

A few things to note:

  • This is run from the main application directory on my local machine.
  • PGUSER and PGPASSWORD set the authentication credentials for the local db.
  • My Django app has the Database URL stored under the DATABASE_URL environment variable. The URL can be viewed with the $ heroku config command or on the Heroku dashboard if you want to use it directly.
  • bcmlocaldb is the name of the new local db.

Once the command has successfully run I could then see the db in the PostgresSQL dashboard on my local machine.

PostgresSQL Dashboard

Django Settings To Use Local DB

Now I have a local copy of the db I just need to change the local apps settings to use this instead of the old Sqlite. I’m using the DJ-Database-URL utility to configure my environment variable so in my local .env file I changed:

DATABASE_URL=sqlite:///db.sqlite3

to:

DATABASE_URL=postgres://postgres:password@localhost:5432/bcmlocaldb

Now when I run the local application its using the new database!

Heroku has a lot of nice documentation and the Postgres info can be found here.

Alternative – Connect to live Heroku Postgres Database

To get the applications Postgres URI I just run the $ heroku config command, you will see something along the lines of:

$ heroku config

DATABASE_URL: postgres://your_username:the_password@host.amazonaws.com:5432/database

Now you just replace the local DATABASE_URL .env variable with the info above.

There’s some good documentation going into more detail about connecting from outside Heroku here.