Ruby + SQL - Lab 1 – Solution

Just looking for the final code?  The lab file is here (which details the files generated/changed in this lab).

Migrations

Salesperson

Open up the labs/1-migrations.rb file.  Note that there are instructions in this file, but all of the code for this lab will be run from terminal or written in a different file.  There won't be any reason to run the lab file.

Step 1: in terminal, generate a model and table for Salesperson.

rails generate model Salesperson

This created 2 new files:

• app/models/salesperson.rb
• db/migrate/*_create_salespeople.rb (the * is a list of numbers representing the timestamp when this file was generated)

We don't need to worry about the app/models file just yet.  For now, let's open the migration file (db/migrate/*_create_salespeople.rb).

Step 2: add relevant columns matching our domain model.

When this file was generated, some initial code was added to the file:

class CreateSalespeople < ActiveRecord::Migration[7.0]
  def change
    create_table :salespeople do |t|

      t.timestamps
    end
  end
end

If you think back to the SQL unit, this may look similar:

CREATE TABLE salespeople (...)

The migration file is just ruby code that translates into the SQL code to create a table.  Note that the table doesn't yet exist - we just generated this code, it still needs to run.  But first, we need to add all the columns that we want this table to have.  So, in the space between create_table and t.timestamps, we're going to add some code.

class CreateSalespeople < ActiveRecord::Migration[7.0]
  def change
    create_table :salespeople do |t|
      t.string "first_name"
      t.string "last_name"
      t.string "email"

      t.timestamps
    end
  end
end

This file will now be able to create the salespeople table with a text column first_name, another text column last_name, and a 3rd text column email.  It will translate into the following SQL code (more or less):

CREATE TABLE salespeople (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT,
  last_name TEXT,
  email TEXT,
  created_at TEXT,
  updated_at TEXT
 )

Hold on...there are 6 columns, but we only added 3.  Where did the id, created_at, and updated_at columns come from?

When we run the migration code, by default it always adds a id primary key column - that's because it's best practice for every table to have a primary key so rails has it built-in.  Helpful, right?!  

But what about the created_at and updated_at columns?  Well, it's pretty common to keep track of when a row in a table was first inserted and when it was last updated - helpful information for auditing and debugging purposes, but also often useful as part of the domain model.  For example, we often order posts in a social media application by when they were "posted" (either in ascending or descending order).  We could use the created_at value for this.  We call these "timestamp" columns (we're "stamping" the time when something occurred).  And because it's common to want these columns, the generated code includes it as t.timestamps.  If we don't want those columns, we can remove that line of code.

Step 3: execute the migration file.

Ok, the migration file looks pretty good.  Reminder, if we check the database schema, the table does not yet exist.  We've written the code, now we want to run it, or execute it.  To do so, we run another command in terminal:

rails db:migrate

Hit enter and you should see some output:

== 20220427022730 CreateSalespeople: migrating ==========================
-- create_table(:salespeople)
   -> 0.0011s
== 20220427022730 CreateSalespeople: migrated (0.0012s) =================

The output is confirming that the migration code has been executed and if we check the database schema now, we'll see the table with its 6 columns.

Activity

Now that we have salespeople, and we have contacts from before, we can add the relationship model that joins salespeople and contacts: activities.

We'll follow the same steps as we did for salespeople.  The only difference is that this table also includes foreign key columns.

Step 1: in terminal, generate a model and table for Activity.

rails generate model Activity

This created 2 new files:

• app/models/activity.rb
• db/migrate/*_create_activities.rb

Step 2: add relevant columns matching our domain model.

class CreateSalespeople < ActiveRecord::Migration[7.0]
  def change
    create_table :salespeople do |t|
      t.integer "salesperson_id"
      t.integer "contact_id"
      t.string "note"

      t.timestamps
    end
  end
end

Step 3: execute the migration file.

rails db:migrate

Hit enter and you should see some output:

== 20220427030456 CreateActivities: migrating ===========================
-- create_table(:activities)
   -> 0.0018s
== 20220427030456 CreateActivities: migrated (0.0019s) ==================

That's it.  The salespeople and activities tables are now in the database.