A Breakdown of Databases Deployed to Heroku

After my last post about my experience with Heroku, I’m continuously piecing together my understanding of it, SQLite, and PostgreSQL 10. This week I worked with my fellow Flatiron Alumni, Mariel Frank, to do our best of breaking down databases for app deployments on Heroku. Here’s the breakdown:

Databases (specifically Relational Databases)

SQL (Structured Query Language) is a programming language for managing data in a database. It’s not used to build a web app, but it’s used to interact with the database that powers that app. If you’re not familiar with databases here are some examples.

1. Web apps that we use like Facebook saves user data and stores the associations you have to your friends. This means, when you first signed up to join the site, your email, password, and other details are saved to a database. Once you add a friend or a friend wants to add you, this relationship is also stored into the database. The next time you sign back in, the database knows your email and if you entered your password correctly, allows you into the site.
2. Databases are also needed for e-commerce sites like Amazon. The items you see, search, and buy from Amazon or any online store has a database to store details of these items. A store can set prices, tells you how many items are left, transaction history, and much more.

All these individual pieces of data are stored in the database as objects. In a Relational Database like SQLite the objects are stored in a table like structure with rows and columns. These tables and individual data can be created, modified, or deleted. So, when building an app that stores data, it’s important to set up a database. However, not all databases are made equal.

Some databases are client-server based or production database meaning users can interact with it (i.e. Facebook, Amazon, etc…). Others are linked to the app itself through the computer’s memory which isn’t production grade. Whatever actions or data you stored on a development database will only be shown on the computer you were working on. If you want to see that app again on another computer, the data you had won’t be available. This is important to know as we navigate with Heroku.

Heroku & Databases

With all my apps I’ve built so far, I’ve use Ruby and Ruby on Rails. Ruby on Rails and a few other frameworks are shipped with a simple database called sqlite3 by default. SQLite is often a development database. Though, I can deploy my app on Heroku, it won’t accept the default sqlite3. Heroku doesn’t play nice with SQLite’s development status. Instead, it offers production databases as a service via PostgreSQL which is a database that can be used by any language and framework. Heroku’s service comes as a free (hobby) or other paid plans.

SET UP PostgreSQL

Acting as a backend server on your computer, PostgreSQL is free to use and to install on your computer. Here’s a link to Heroku’s Dev Center on local-setup of Postgres on Mac - https://devcenter.heroku.com/articles/heroku-postgresql#local-setup . With its own set of commands, we can create a database for each application we build. These databases must also have a database user with role names acting as the “admin.” Upon first installation, PostgreSQL automatically creates a database user matching your username along with its own database user. These users or roles would be our starting point and connection towards creating our databases for our applications.

List of Users/Roles in Postgres with command \du

Create Users

1. Log into our postgres to verify any existing database users on our computer psql postgres.
2. Once inside postgres, enter command \du. We should see a similar chart like the own above.
3. Next, create the user with the below command. The name_of_app part of the command should be the name of your app in lowercase letters. This name is the user’s role name. This user’s will have a password which you’ll defined as 'app_password' in lowercase letters and between single quotes. Don’t forget that semicolon ;.

CREATE ROLE name_of_app WITH LOGIN PASSWORD 'app_password';

4. Now verify if the user was created with \du. For my app, I created a user named artphilia. Below you’ll see that its List of roles/Attributes is blank.

Create a User in postgres

5. With a User created, we must give them permissions or list of roles. For my app, I gave the user a permission to create a database.

Give a User permissions in postgres

Create Databases

With Postgres, you can create as many databases as you want. For Heroku, and perhaps other app deployment setup, we should have three separate databases. As mentioned above, databases can have three environments — development, test, production. These databases will be used to connect to the app’s Heroku connection.

Set up the three databases with the commands CREATE DATABASE name_of_database;. Each should have a label detailing which environment it should manage like _test.

Here’s how my Postgres interface looks with the newly added databases:

New databases in PostgreSQL 10

Connecting the Databases to the App

Depending on the language you use, the instructions detail how we can make that connection. I currently use Ruby and Rails, so the following examples are for those languages. In my app, I’ve configured a couple of files to ensure all things are up to ‘code’ with Heroku. Here’s one of the articles I followed for help — https://devcenter.heroku.com/articles/getting-started-with-rails5

• Configure Gemfile & database.yml

Gemfile

1. Add gem 'pg'
2. Specify your Ruby version ruby "2.X.X"
3. bundle install to download and resolve all dependencies

config/database.yml

1. Based on the Ruby on Rails guide this file specifies all the information needed to access the database. This .yml file needs to configure the three database environments similar to the below.

#config/database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000
  host: localhost
  username: database_user_name
  password: <%= ENV['SECRET_KEY_BASE'] %>
development:
  <<: *default
  database: app_name_development
# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: app_name_test
production:
  <<: *default
  database: app_name_production

• Each database environment should have its respective database name listed under database: app_name_
• Under default list the user (name_of_app) that we created earlier with postgres as the database_user_name.
• Create a .env file in your app to associate SECRET_KEY_BASE with the password ('app_password') we associated with the database user in postgres. Something like this: SECRET_KEY_BASE='app_password'.

The .env file are bits and pieces of code you would want to use for development to ensure what works. After configuration is complete, commit to git. Ensure that gem 'pg' was installed.

Deploy to Heroku

At this stage, you should have a Heroku account. If not, please do so now.

1. Log into your Heroku with heroku login
2. Create the connection with heroku create name_of_app
3. Verify that the remote was added to the app with git config --list | grep heroku
4. Deploy your code: git push heroku master
5. Migrate the database in the app with: heroku run rake db:migrate
6. Visit the app to ensure all is well: heroku open

NOTE: I ran into a problem while deploying my code where ES6 had to be uglified. The change I made were done in config/environments/production.rb. I had to commented out a previous code while adding a new configuration to the js_compressor.

# Compress JavaScripts and CSS.
  # config.assets.js_compressor = :uglifier
  config.assets.js_compressor = Uglifier.new(harmony: true)
  # config.assets.css_compressor = :sass

7. In the Heroku dashboard, we have to add our 'app_password' to the app’s Config Vars located in Settings.

Click on Reveal Config Vars. In the column next to SECRET_KEY_BASE enter your 'app_password' when we created the user.

8. Refresh your browser and play with your app to ensure all is well.

Configuration and deployments are very tricky. It’s something I’m glad I’ve found a fellow developer and alumni to sort this world out. I’ve also relied heavily on this codementor article. Though it’s dated 2016, the instructions/guidelines of setup Postgres, creating a user, and creating databases were very simple to follow and understand.

I hope this post helps any future developer seeking to deploy to Heroku. Thank you for reading. Let me know if there’s something I should include or have left out that’s crucial to the process.