PostgreSQL Setup

A Quick Recap

In the previous article we installed Ruby on Rails and initiated a new Rails API.

You are now ready for the next step: setting up the database. Feel free to refer to the part 2 branch of the GitHub repository if needed.

This is the 2nd installment of my Developing A Cross-Platform iOS & Android Social Media App series.

Configuring The Database.yml Config

Now that we have our API initiated, we need to prepare the database for Rails to insert records into.

Lets checkout the database config file that Rails created for us. Open config/database.yml in your preferred code editor.

You'll notice a lot of information in there. We're going to simplify it by replacing the entire file with:

default: &default
  adapter: postgresql
  encoding: unicode
  username: <%= ENV['POSTGRES_USER'] %>
  password: <%= ENV['POSTGRES_PASSWORD'] %>
  pool: 5
  timeout: 5000
  host: <%= ENV['POSTGRES_HOST'] %>
development:
  <<: *default
  database: <%= ENV['POSTGRES_DB'] %>
test:
  <<: *default
  database: <%= ENV['POSTGRES_TEST_DB'] %>
production:
  <<: *default
  database: <%= ENV['POSTGRES_DB'] %>

This gives our app the credentials for a PostgreSQL role. It will use that role to access the Postgres Database (production and development) and the test database that is used for automated tests. Notice that we pull the credentials from an environment variable via <%= ENV['POSTGRES_USER'] %>. This is crucial when working with git. We don't want credentials uploaded to GitHub so we will have it in an .env. This file is simply a list of files to blacklist from being uploaded to a git repo. Github will not upload files listed in a .env, keeping our credentials safe.

Creating Environment Variables

In order to setup the .env, we need to install the dontenv-rails gem by adding gem 'dotenv-rails' to Gemfile. Once that is done, we can run $ bundle install to install it.

Once the command has been ran, lets create a .env in the root of the project. You can create it in your editor or by running $ touch .env in your terminal.

Open that file and add the following lines:

POSTGRES_USER=''
POSTGRES_PASSWORD=''
POSTGRES_HOST='localhost'
POSTGRES_DB=''
POSTGRES_TEST_DB=''

You can set the username, password, db name, and test db name to anything. But here is my sample .env:

POSTGRES_USER='social_media_blog'
POSTGRES_PASSWORD='password123'
POSTGRES_HOST='localhost'
POSTGRES_DB='social_media_blog'
POSTGRES_TEST_DB='social_media_blog_test'

Save the file and run $ rails console or $ rails c. Once the console starts, we can verify that the env variables were set by entering ENV['POSTGRES_USER']:

~/code/social-media-blog-api$ rails console
Loading development environment (Rails 6.0.2.1)
2.6.5 :001 > ENV['POSTGRES_USER']
 => "social_media_blog"

You can exit the console by typing exit or using the ctrl+z command.

Before we commit these changes, we need to tell git to ignore our .env file. Open .gitignore and append .env to the end so that it matches below:

...

# Ignore master key for decrypting credentials and more.
/config/master.key

.env

Run $ git status . You should not see .env listed in the output.

~/code/social-media-blog-api$ git status
On branch master
Your branch is up to date with 'origin/master'.

Changes not staged for commit:
  (use "git add <file>..." to update what will be committed)
  (use "git checkout -- <file>..." to discard changes in working directory)

	modified:   .gitignore
	modified:   Gemfile
	modified:   Gemfile.lock
	modified:   config/database.yml

no changes added to commit (use "git add" and/or "git commit -a")

This is a good point for us to commit our changes:

$ git add .
$ git commit -m 'setup postgresql config'
$ git push

Setting up PostgreSQL

We have one final step: Creating the postgresql role.

If you have not setup PostgreSQL on your machine yet, refer to the official postgresql site on how to install it

Note: I am using PostgreSQL 12.1

If you are using MacOSX, you can run $ brew search postgresql to see the available version names. For now, postgresql 12 falls under postgresql meaning you can just run brew install postgresql

In the future, you may need to run brew install postgresql@12

You should now be able to access the PostgreSQL console. If you installed it via Homebrew on Mac, you should be able to access it via $ psql postgres

In this console, we would need to create a new role, for now we will make it a superuser out of convience and simplicity. A role that has superuser privileges means that it has full control of everything.

We can create the role by running:

CREATE ROLE social_media_blog LOGIN SUPERUSER PASSWORD 'password123';

This will create a role named social_media_blog with password123 as the password. (Be sure this matches with your .env file.)

If successful, your console should look like this:

postgres=# CREATE ROLE social_media_blog LOGIN SUPERUSER PASSWORD 'password123';
CREATE ROLE

You can exit the console by typing exit or by using the ctrl+z command.

Creating The Databases

Now that Rails can login to a role, lets run a built-in rake task that creates the databases for us: $ rake db:create

You should see a similar response:

~/code/social-media-blog-api$ rake db:create
Created database 'social_media_blog'
Created database 'social_media_blog_test'

As you can see, it created two databases for us automatically: social_media_blog and social_media_blog_test

One last step is to verify that running rails server does not return any error. Go ahead and run the server and verify that nothing has been broken.

Viola! You have successfully setup a Rails API with a PostgreSQL database!