Deploy Postgres On Heroku

Connected Devices

18 Feb 2019

I asked some of the software developers for their opinions regarding SQL vs NoSQL databases and I got some differing opinions. Adrian Bautista highly recommended not using a non relational database. In his opinion the data I was logging such as time and temperature, a relational database would be more straightforward to query. Conversely Matt Ross recommended NoSQL databases for their flexibility, especially for ITP projects which sometimes scale laterally.

In the end I spent an afternoon playing around with tutorials on codecademy and the ilk to test out: Postgresql, MongoDB, and Redis. In the end I selected for my example Postgres since it was the most straightforward to integrate into my project for me.

I found a useful tutorial by logrocket that walked through setting up a RESTful api with Node.js and Postgres. When I set up the postgres locally I had some issues with the default credentials. It was a little confusing, but eventually I figured out. Some helfpul flags were -U <username>, -H <host *I used localhost>.

The end result was to make a simple API with 6 routes that I could:

  1. GET - / - show home
  2. GET - /users
  3. GET - /users/:id
  4. POST - users
  5. PUT - /users/:id
  6. DELETE - /users/:id

Other cool things I learned about was to keep the route functions in a separate file called query.js:

module.exports = {

I also learned a little more about es6 syntax. String replacements are cool ${str}.

After successfully getting the API running locally, I took the next step of deploying it on Heroku. Heroku CLI has an extra layer to interface with with postgres. After a while it is pretty straightforward once you get used to prepending heroku pg to your commands.

I was able to successfully push my local database info to the heroku postgres instance with

PGUSER=<local username> PGPASSWORD<local pass> heroku pg:push <localdb> DATABASE_URL -app <app_name>

Again the defaults for my local postgres is misconfigured so I have to specify my credentials.

With this my heroku database is populated, but I have to reconnect it with my express server as I deployed it while it was connected to my local database. To get the credentials for the heroku postgres instance type in:

heroku pg:credentials:url DATABASE

This returns the database name, database host, database user, and the database password.

Once I finished this configuration I could visit the browser url and view my db contents.

I think that this was a useful exercise and I am excited to play around with storing data to my database and also doing something with the gathered data. My next step is to send data to my database and seeing if there is any interesting things I can do for my thermometer project.

I want to play around with different database models to get an understanding about how to better structure my data.

useful links: