How To Build A Web App, part 8 of ?: first schema migration

Doing a Medium image search for “schema” turned up diddly squat, so I searched for “scheme” instead, and encountered various less dreary and excruciating images, some containing at least a spark of liveliness. Step in the right direction. So I did the same on Google image search. Bingo. This’ll do nicely.

This is the eighth in a series of articles taking you through all the actual steps in building a web app. If you’re an aspiring developer, if mucking around with teensy beginner tutorials frustrates you, if you’d love to build a properly substantial app that does fab things, these articles are for you.

Last time, we connected our fledgling Rails app to our PostgreSQL database. Today we’ll add some tables.

There’s a Thing called a database schema.

A “schema” is just a fancy word for a database’s structure: its tables, and each table’s columns. The time has come to design them.

The Gigs app’s central conceit is: we want to build something where users can search for various comedy acts’ gigs across the world. They search, map markers appear marking out venues where these gigs take place.

Acts, venues, gigs. Easy peasy. Right?

For starters, let’s do a really simple, basic, MVP (minimum viable product, not most valuable player) implementation of these three tables. Just the most basic info we need. We’ll add a ton more columns later, believe me, but these columns will do for now:

Acts: ID, name.

Venues: ID, name.

Gigs: ID, act ID, venue ID.

Let’s make this happen. New feature, so create a new branch.

$ git checkout -b 2-create-schema
Switched to a new branch '2-create-schema'
$ git branch
  1-connect-database
* 2-create-schema
  master

Rails (or rather, ActiveRecord) has these Things called migrations. They’re not unique to Rails, they’re fairly common across the web-dev world. Migration files are a series of Rails scripts used to keep track of changes to your app’s database. If you’re a new team member of an existing project with a long history, you can just run all the existing migration files, and they’ll create your database from scratch. It’ll then be identical to all your colleagues’ databases on their machines. Sweet.

Why bother with migrations? Why not just change your database directly, manually? PostgreSQL has its own command line, just like MacOS and Linux, and you could totally add and remove tables and their columns from there.

Problem is, it can be difficult to keep track of these changes across different coding team members, each of whom has their own local database on their own local machine. Or maybe your project’s manager has decided to swap out your DBMS from PostgreSQL to MySQL, or a totally different one.

Let’s make a Rails migration.

$ rails generate migration CreateActsGigsVenuesTables
Running via Spring preloader in process 9481
      invoke  active_record
      create    db/migrate/20190910020025_create_acts_gigs_venues_tables.rb

Quick explanation: Rails comes with its own command line program, which by the most astonishing coincidence, is called rails. Much like Git, it takes its own commands, one of which is generate, and we’re generating a migration. Migrations are like commits and branches in that their exact naming conventions are a bit loose, you can honestly call them whatever you like, but best practice is to keep them brief.

You’ll see it’s created a new file. Let’s open it.

The file name is in two parts: a date-time string of numbers (yyyymmddhhmmssmmm — years, months, days, hours, minutes, seconds, milliseconds), then the underscore-formatted name we’d given the migration.

Each migration file is a Thing called a class (for those of you who’ve studied object-oriented programming, yes, it’s that kind of class). All migration classes have one method, change. It’ll contain the database changes we desire.

We shall modify the change method’s contents thusly:

def change
  create_table :acts do |t|
    t.string :name
  end
  create_table :venues do |t|
    t.string :name
  end
  create_table :gigs do |t|
    t.references :acts
    t.references :venues
  end
end

(Star Trek EDIT: by the way, if you’re already a seasoned Rails dev, you may be thinking, wait, no t.timestamps? What gives? Oops! I’d genuinely forgotten to add them now, and didn’t catch my omission until article 18).

Each Rails-migration-based table includes an ID by default, so we don’t need to write that ourselves. But otherwise, it creates a name column for both Acts and Venues, and for Gigs, creates act_id and venue_id.

Now we run it.

$ rake db:migrate
== 20190910020025 CreateActsGigsVenuesTables: migrating =======================
-- create_table(:acts)
   -> 0.0572s
-- create_table(:venues)
   -> 0.0049s
-- create_table(:gigs)
   -> 0.0181s
== 20190910020025 CreateActsGigsVenuesTables: migrated (0.0806s) ==============
== 20190910020025 CreateActsGigsVenuesTables: migrated (0.0806s) ==============

We’ve now injected our desired changes into the database. PostgreSQL contains these new tables.

Now to commit our change, our new migration class file. First, a git status to review what we’re about to commit.

$ git status
On branch 2-create-schema
Untracked files:
  (use "git add <file>..." to include in what will be committed)
        db/migrate/
        db/schema.rb
nothing added to commit but untracked files present (use "git add" to track)

Whoa nelly. Another new file! What’s db/schema.rb? Big topic. I won’t duplicate the excellent Rails guides here, so if you’d like, do read their page on schema files.

Anyway. Onward!

$ git add .
$ git status 
On branch 2-create-schema
Changes to be committed:
  (use "git reset HEAD <file>..." to unstage)
        new file:   db/migrate/20190910020025_create_acts_gigs_venues_tables.rb
        new file:   db/schema.rb
$ git commit -m "Created and ran our first schema migration"
[2-create-schema 4ab6678] Created and ran our first schema migration
 2 files changed, 49 insertions(+)
 create mode 100644 db/migrate/20190910020025_create_acts_gigs_venues_tables.rb
 create mode 100644 db/schema.rb

Done. You can see the entire code base at this commit here.

Finally, merge our schema-creation branch back into master.

$ git checkout master
Switched to branch 'master'
Your branch is up to date with 'origin/master'.
$ git branch
  1-connect-database
  2-create-schema
* master
$ git merge 2-create-schema
Updating 163cd85..4ab6678
Fast-forward
 db/migrate/20190910020025_create_acts_gigs_venues_tables.rb | 16 ++++++++++++++++
 db/schema.rb                                                | 33 +++++++++++++++++++++++++++++++++
 2 files changed, 49 insertions(+)
 create mode 100644 db/migrate/20190910020025_create_acts_gigs_venues_tables.rb
 create mode 100644 db/schema.rb

Finished!

Great. We’ve created our boilerplate Rails app. We’ve got it connected to a database. We’ve created a few tables for it. Now let’s fill it with data.

Next time: we’ll hook our app up to the Ticketmaster API, and start siphoning off its sweet sweet gigs data.