Migrations

The CRM domain model will be helpful in this unit.

Back in the SQL unit, we learned how to write SQL CREATE TABLE statements to modify the database structure.  Here in our ruby application, we don't just need a table, we also need the corresponding model to interact with the table data.  For example, we want a table for the company data in our domain model and a Company model to interact with that data.

Generating Models and Migrations

If you haven't already, go to this lesson's repository and open it in Gitpod.  The file for this lesson is 1-migrations.rb in the code-along directory.  Note - we won't end up running this file; the code from this unit will be commands in terminal and code in other files.  The code-along file is only for informational purposes.

We need to do two things:

• We want to create a table that can hold data about companies
• We want to create a Company model and have it connect to this table

To accomplish the first step, we'll need a migration file.  A migration is just Ruby code that, when executed, will talk to the database and add a new table into it.  And for the second step, we just need to create a file for the Company class.

We could create these files manually, but Rails (the ruby application we've been working with) comes with several commands to help generate files that we might need.  From terminal, type the following command and hit enter:

rails generate model Company

The first 3 words in this command will always be the same.  The last word will be the name of the model (singular and capitalized) from the domain model that you want to add to the application.

The output you'll see indicates that 2 files were created by this command.

      create    db/migrate/*_create_companies.rb
      create    app/models/company.rb

The first is the migration file that will modify the database structure - there will be a long set of numbers where the asterisk (*) is in the file name above.  The second is the model class file.  Let's quickly look at the model since that's easier and we won't need to do anything to that file right now.  If you open company.rb in the app/models directory, you'll see this code:

class Company < ApplicationRecord
end

This is the model class and is responsible for connecting Company ruby code with the companies table.  At some point, we may choose to add custom code here, but that's later.  You can close this file.

Now open the migration file which has a bunch of numbers and then _create_companies.rb as its name.  It's in the db/migrate directory.

Creating Database Tables

Migration files modify the database structure, so you can imagine that we don't want to run them more than once - it wouldn't make sense to create a table and then try to create it again.  The order of the files also matters since it wouldn't make sense to add a column to a table unless the migration that created that table was already executed.  That's where the numbers in the file name are useful - they're the timestamp of when this file was generated.  The timestamp keeps the order of the files and also ensures file names are unique.

Check out the code.

class CreateCompanies < ActiveRecord::Migration[7.0]
  def change
    create_table :companies do |t|

      t.timestamps
    end
  end
end

We haven't seen this code before, but it's part of the ActiveRecord library and you can probably guess what it does - it writes and executes a SQL CREATE TABLE statement that will add a table named companies.  When we ran the generator, we used the singular model name, but this library is smart enough to know that the table name should be pluralized - cool, right?!

Inside this file, we need to add the columns that we want this table to have.  The format for adding a column is column_type :column_name.  Looking back at the domain model, the companies table has a name column, a city column, a state columns, and a url column.  It makes sense that those would all be strings, so let's add them to the code.

class CreateCompanies < ActiveRecord::Migration[7.0]
  def change
    create_table :companies do |t|
      t.string "name"
      t.string "city"
      t.string "state"
      t.string "url"

      t.timestamps
    end
  end
end

The t. is just part of the ruby code that tells this table object to add a column - another deep dive we won't get into here.

We don't have any numeric columns, but if we did, we could use the integer column type, for example t.integer "age".  There are several column types, but these are the 2 most common.

Running Migrations

Now that our migration file has the table columns, we're almost done, but nothing's actually happened yet!  Simply defining the migration file won't physically alter the database.  If you check the db/schema.rb file, you won't see the companies table there.  Same if you hop into sqlite and check .schema there.  We need to run the migration file in order to execute the code, make a live connection to the database, and insert the new table.  At the terminal prompt, run the following command:

rails db:migrate

You should see some output confirming that it was successful.  You can also check the schema again to verify the table now exists.

As explained above, this migration file only runs once - it wouldn't make sense (and in fact would raise an error) if we tried to create another companies table.  So, if you try rerunning the migrate command from above, nothing will happen because Rails is tracking which files have run and knows there aren't any new files.

That said, if you need to make a change, you can't just edit the migration file since you can't rerun it.  We'll learn some advance ways to modify the tables later, but for now, you'd need to actually delete the entire database and start again.  Fortunately (or unfortunately), doing so is very easy, but be aware, this will delete ALL of your data from all of your tables.  Since this is all development data so far, it might be ok, but it's still a process to recreate data.  If you want to proceed, find the sqlite file db/development.sqlite3 and delete it.  You can then modify the code in the migration file and migrate again with the command:

rails db:migrate

Contacts

With the companies table in place, we'll be able to add rows to the table - we'll do that in the next unit.  For now, let's repeat the steps to create a model and table for Contact.

Step 1: generate the model and table.

rails generate model Contact

Step 2: modify the migration file db/migrate/*_create_contacts.rb with columns matching the domain model.

class CreateContacts < ActiveRecord::Migration[7.0]
  def change
    create_table :contacts do |t|
      t.string "first_name"
      t.string "last_name"
      t.string "email"
      t.integer "company_id"

      t.timestamps
    end
  end
end

Note that this table has a foreign key column because each contact is associated to a company.  We designed that relationship back in our domain model unit.  The foreign key is an integer and will reference the id column in a row in the companies table that we just created.

Step 3: execute the file to actually create the table in our database.

rails db:migrate

That's it, we now have models and tables for our Company and Contact entities in our domain model.

Lab

Time for a lab to practice - in this lab we'll add the other tables in our core domain model. Instructions are in the file 1-migrations.rb in the labs directory.