Lesson Weekend

Now that we've completed the basic set up for our record store application, we're ready to start updating our code. At this point, all tests should be commented out. We'll start by making a few updates to our Album class including removing our class variables and updating our initialize method. Since we'll be using a database, we no longer need class variables.

Updating the Constructor


lib/album.rb
class Album
  attr_reader :id
  attr_accessor :name

  # Class variables have been removed.

  def initialize(attributes)
    @name = attributes.fetch(:name)
    @id = attributes.fetch(:id) # Note that this line has been changed.
  end
...
end

Next, we can uncomment our test for the == method in spec/album_spec.rb. This test is already passing and it will help us with future tests.

Updating the .all() Method


Now we're ready to update our .all() method. Uncomment out that test next.

album.rb
def self.all
  returned_albums = DB.exec("SELECT * FROM albums;")
  albums = []
  returned_albums.each() do |album|
    name = album.fetch("name")
    id = album.fetch("id").to_i
    albums.push(Album.new({:name => name, :id => id}))
  end
  albums
end

First, we use the pg gem's exec() method to select all albums from the database and store that in a variable. Our query returns a <PG::Result>, which is a collection of hashes. We loop through this collection, fetch each Album's name and id, and then push the Albums into a new albums array, which the method returns. Note that we need to make sure that the id is an integer using to_i.

It may seem inefficient to iterate through the entire database, but our application needs to translate SQL into Ruby. In a very large real world database, we'd only return a page of results. After all, it wouldn't be helpful if a user loaded a page that literally had a million results on it — nor would it work correctly.

Updating the #save() Method


Let's uncomment the test for an Album's save() method next. We need to add a SQL insert statement with a new wrinkle included.

album.rb
  def save
    result = DB.exec("INSERT INTO albums (name) VALUES ('#{@name}') RETURNING id;")
    @id = result.first().fetch("id").to_i
  end

Most of the SQL insert statement should look familiar. However, we've added a few details:

  • We use string interpolation to pass in the value of the instance variable @name.
  • We've added a new piece of code at the end of our insert statement: RETURNING id.

We need RETURNING id so our application knows what id value our database has assigned to an Album. Remember that our application shouldn't assign ids. That's a job for the database. So Ruby creates an Album object with a nil id and passes only the album name to the database. As far as our application is concerned, the id is still nil even after the database assigns a numerical value to it. For that reason, we need our insert statement to return the Albums new id so our application knows about it and can correctly update the @id instance variable for a specific Album. Otherwise, the value of an Album's id will be inconsistent between our application and our database, which would lead to problems with our code down the road.

Updating the .clear() Method


We don't really need a self.clear() method anymore since we were just using it in our before(:each) block to clear tests before. However, it's an easy one to update. Let's uncomment out that test next. We simply need to delete all the results in our database:

album.rb
def self.clear
  DB.exec("DELETE FROM albums *;")
end

Updating the .find() Method


Next, we're ready to uncomment out the test for self.find(). Here's our updated method:

album.rb
def self.find(id)
  album = DB.exec("SELECT * FROM albums WHERE id = #{id};").first
  name = album.fetch("name")
  id = album.fetch("id").to_i
  Album.new({:name => name, :id => id})
end

We start by making a SQL query along with Ruby string interpolation to find an album by its id. A SQL query always returns a collection even if there is only one result. Because we want a specific album, not a collection, we use Ruby's first method to return the first (and only) result of this query.

Then we can fetch its attributes and instantiate a new Album, which the method returns. Just as with our Album.all method, we need to convert a SQL result to a Ruby object so our application can use it.

Updating the #update() Method


Now we're ready to make changes to our update() method. Go ahead and uncomment the test first.

album.rb
def update(name)
  @name = name
  DB.exec("UPDATE albums SET name = '#{@name}' WHERE id = #{@id};")
end

First we update the value of the Album's @name — otherwise, we'd be updating the Album in our database but not in our application. Once again, we should always make sure that any objects in our application are consistent with their counterparts in the database. Otherwise, we'll run into bugs and unintended consequences.

Other than that, it's a simple SQL update statement that uses string interpolation to set a new name value based on the Album's id.

Updating the #delete() Method


Our delete() method simply uses a SQL delete statement with a where clause. Once again we'll uncomment it and get it to pass with the following method:

album.rb
def delete
  DB.exec("DELETE FROM albums WHERE id = #{@id};")
end

Our application now has full CRUD functionality for Albums. Our Album class has one more instance method: songs(). However, we won't be able to update this method until we update our Song class.

We didn't have to update a single test in order to get our methods working with a PostgreSQL database. Ultimately, our application can have the exact same user interface and tests as it did before. The key difference is that we are now using a database instead of storing information in class variables.

Note that if your tests are failing, it may be because they were written prior to when we updated our classes to use the attributes hash. If you haven't incorporated hashes, you'll need to update your Record Store classes to use them before continuing.

In the next lesson, we'll update the Song class and also update the final method in our Album class.

Example of an .all Method


def self.all
  returned_albums = DB.exec("SELECT * FROM albums;")
  albums = []
  returned_albums.each() do |album|
    name = album.fetch("name")
    id = album.fetch("id").to_i
    albums.push(Album.new({:name => name, :id => id}))
  end
  albums
end

Example of #save() Method


  def save
    result = DB.exec("INSERT INTO albums (name) VALUES ('#{@name}') RETURNING id;")
    @id = result.first().fetch("id").to_i
  end

Example of .clear Method

def self.clear
  DB.exec("DELETE FROM albums *;")
end

Example of .find() Method

def self.find(id)
  album = DB.exec("SELECT * FROM albums WHERE id = #{id};").first
  name = album.fetch("name")
  id = album.fetch("id").to_i
  Album.new({:name => name, :id => id})
end

Example of #update() Method

def update(name)
  @name = name
  DB.exec("UPDATE albums SET name = '#{@name}' WHERE id = #{@id};")
end

Example of #delete() Method

def delete
  DB.exec("DELETE FROM albums WHERE id = #{@id};")
end

Lesson 8 of 29
Last updated August 7, 2022