How to connect a Rails 6+ App to PostgreSQL and deploy to Heroku in a development environment using WSL.

To use PostgreSQL, one must create an account and a user role. By default, PostgreSQL comes with a user account (Postgres) and a user role (postgres role). The user role is used to log into the Postgres command line to create user roles, add passwords or add multiple user roles.

After downloading PostgreSQL, log into the Postgres command line using the default account details to create a new user role and database. This database is what will be connected to the Rails App. Point to note, it is advisable to use the same database name, password, and username in your development, test, and production environments to prevent bugs from being introduced.

SQLite backs up stored data in files on disk but Heroku has an ephemeral filesystem that doesn't persist. This means it clears periodically, therefore you’d lose your entire DB at least once every 24hrs.

Ensure the following is installed before proceeding:

  1. Download PostgreSQL in your windows system
  2. Ensure you have a Windows subsystem for Linux installed.
  • log into PostgreSQL(using default account)-> sudo -u postgres psql
  • create a new role/user-> sudo -u postgres createuser (username)
  • create a DB whose name matches the name of your app-> sudo -u postgres createdb (DB name)
  • set password for created user-> alter user (username) with encrypted ‘(password)’
  • grant your created user all privileges -> grant all privileges on the database(DB name) to (username).

This method sets PostgreSQL as the default DB in your new Rails App. It also includes the pg gem in your gemfile automatically.

  1. Create a new app with postgres as default DB-> rails new appname --database=postgresql
  2. Change directory into new app ->cd appname
  3. Re-install dependencies ->bundle install

Creating a rails app with Postgres as the default database ensures that the config/database.yml is written correctly. All you have to add is the username and password you used to create the new DB in the postgres command line.

development:
adapter: postgresql
database: appname_development
pool: 5
username: myapp
password: password1
test:
adapter: postgresql
database: appname_test
pool: 5
username: myapp
password: password1


production:
adapter: postgresql
database: appname_production
pool: 5
username: myapp
password: password1

4. Create your DB -> rake db:create

5. Migrate pre-existing migrations ->rake db:migrate

This way assumes that you already have an existing rails app or didn't use the rails new -d postgresql command while creating a new rails app.

First, create your DB in PostgreSQL using the command line then do the following:

  1. Open your gemfile and replacegem 'sqlite3' withgem 'pg'
  2. Run bundle install
  1. Open the database.yml file located in config/database.yml
  2. Change the file to look like below:
development:
adapter: postgresql
database: appname_development
pool: 5
username: myapp
password: password1
test:
adapter: postgresql
database: appname_test
pool: 5
username: myapp
password: password1


production:
adapter: postgresql
database: appname_production
pool: 5
username: myapp
password: password1

3. Create your DB -> rake db:create

4. Migrate pre-existing migrations ->rake db:migrate

Run rails s command in your terminal. It should start on your localhost port 3000.

Congratulations you have successfully set up your rails application to run on PostgreSQL. Let’s deploy it to Heroku.

Deploy To Heroku

  1. Create a new Heroku account
  2. Download and install the Heroku CLI

Make sure you're in the main/master branch while deploying before proceeding with the steps below.

  1. heroku create
  2. git config --list --local | grep heroku
  3. git push heroku main
  4. heroku run rake db:migrate
  5. heroku run rake db:migrate
  6. heroku ps
  7. heroku open

The main point of this article is to highlight the two ways in which PostgreSQL can be added to a Rails app; Automatically using the rails new -d postgresql command or manually by adding the pg gem and overwriting the existing database.yml configuration.

The automatic method adds the pg gem and correctly downloads the database.yml configuration file. Only the username and password are added. The manual method requires automatically adding the pg gem and database.yml file.

Hope this article will be of help. Happy Coding.

Self-taught Software Developer.