Lesson Weekend

In the last course section, we built full CRUD functionality for a used record store. Let's update that application to use a database instead of class variables. We'll create a database for our application. Then we'll use the tests we already have to update our methods so that they access and make changes to a database instead of class variables.

In this lesson, we'll cover all setup steps, including:

  • updating our Gemfile to include the pg gem;
  • creating two databases, one for development and one for testing;
  • use a spec_helper.rb file to DRY up our tests;
  • connecting our tests to the database;
  • clearing our test database between each test;
  • connecting app.rb to the database.

You can refactor your own Record Store project from the last course section, or use the following starter project with all of the code from the last course section. Note that the following link takes you to a branch called 2_completed_sinatra_app.

Example GitHub Repo for Record Store

Gemfile Update


First, our Gemfile needs to include the pg gem:

Gemfile
source('https://rubygems.org')

...
gem('pg')

Don't forget to bundle after adding the gem.

Database Creation


Next, we'll open psql and create a database named record_store. Make a table called albums with a primary key and a varchar column called name:

epicodus=# CREATE DATABASE record_store;
epicodus=# \c record_store;
record_store=# CREATE TABLE albums (id serial PRIMARY KEY, name varchar);

We should verify that our table has been set up correctly as well with \d albums.

Next, make a table called songs with a primary key, a varchar column for name, and an album_id that is an int:

record_store=# CREATE TABLE songs (id serial PRIMARY KEY, name varchar, album_id int);

Note that we'll need the album_id to make an association between an Album and a Song just as we did in the last section.

We also need a test database because we don't want to destroy our development database each time we run a test. We'll create a second database called record_store_test with the same schema as the original record_store database. As a shortcut, we can copy our schema like this:

record_store=# CREATE DATABASE record_store_test WITH TEMPLATE record_store;

Setting Up a spec_helper.rb File


We can clean up test suites with multiple test files by using a spec_helper.rb file. Currently, we have two test files that have similar gems as well as a similar before(:each) block. We don't need all this code to be repeated multiple times. We can move the repeated logic into a file called spec_helper.rb which will go in our spec folder with our tests.

spec/spec_helper.rb
require 'rspec'
require 'pg'
require 'album'
require 'song'
require 'pry'

# Shared code for clearing tests between runs and connecting to the DB will also go here.

Note that we're now including the pg gem as well.

Next, we need to add require('spec_helper') at the top of each spec file that needs to use it. In our case, this is spec/album_spec.rb and spec/song_spec.rb. In the process, we can also remove all the gems listed at the top of our spec files since they are now included in spec_helper.rb.

Setting Up and Clearing Tests


Let's get our tests set up. We'll start by commenting out all of our tests. The next step is to connect to the database and clear it between spec runs. We'll do this in our new spec_helper.rb.

spec/spec_helper.rb
# Gem requirements go here.

DB = PG.connect({:dbname => 'record_store_test'})

RSpec.configure do |config|
  config.after(:each) do
    DB.exec("DELETE FROM albums *;")
    DB.exec("DELETE FROM songs *;")
  end
end

The pg gem gives us access to the PG class and its connect method. Note that we pass in a hash with a key-value pair to specify the dbname. Windows users who need to supply a password to access Postgres will need to supply their password here as well:

DB = PG.connect({:dbname => 'record_store_test', :password => "epicodus"})

In the next lesson, we'll learn how to use the dotenv gem to save the database password in an environment variable, stored and accessed via a .env file.

We save our database connection in a capitalized variable DB. A variable capitalized like this is called a constant. A constant is available everywhere in your program. They should be used very rarely and generally only for application-wide configuration settings like the name of your database. By convention, constants have all upper-case names.

Next, we have a block that uses the RSpec class to call the exec method on our database, which is accessed via the DB variable. We use this to execute the simple SQL command "DELETE FROM albums *;". This has similar behavior to the before(:each) block we used in the last section. The main difference is that we're clearing out our test database instead a class variable.

The following code in your before(:each) block can now be deleted from both spec/song_spec.rb and spec/album_spec.rb now that our spec_helper will take care of clearing data between test runs:

Album.clear
Song.clear

This means that the before(:each) block in spec/album_spec.rb can be completely removed.

Note: The majority of tests will fail after the before(:each) block is removed from our classes. This is expected behavior. That is why we are commenting out our tests.

Connecting app.rb to the Database


We'll also use our DB constant in app.rb, too. However, our Sinatra application will connect to the non-test database:

app.rb
...
require("pg")

DB = PG.connect({:dbname => "record_store"})
...

We've made two changes here:

  • We add the pg gem so that Sinatra can access it;
  • We add a DB constant that holds a connection to the database.

Now that we've completed our basic setup, let's start updating our backend code to use our new database.

Overview


Use one database for running your program and one database for running your specs.

Use psql to create and change databases.

Copy your development database schema to your test database:

CREATE DATABASE record_store_test WITH TEMPLATE record_store;

Use a DB constant for your database connection:

DB = PG.connect({:dbname => 'record_store'})

Use a spec_helper.rb file to require gems and files, create the database connection, and clear the database between specs:

require 'rspec'
require 'pg'
require 'album'
require 'song'
require 'pry'

DB = PG.connect({:dbname => 'record_store_test'})

RSpec.configure do |config|
  config.after(:each) do
    DB.exec("DELETE FROM albums *;")
    DB.exec("DELETE FROM songs *;")
  end
end

Lesson 6 of 29
Last updated August 7, 2022