LinkedIn
The Bookstore: A Rails 2.0 Tutorial…continued…who wants to play with relational databases? Here’s the section I’m sure you’ve all by dying for; sure, the scaffold was pretty bad ass, but now we’re going to play with something you ALL will enjoy. This segment is going to be a little bit longer than the others, we’ll be covering a lot of database information! Before you continue, I will assume that you have an understanding of the previous articles:
- Ruby on Rails Tutorial, now with more 2.0.2!
- Basic Rail’s routing and a journey into Views, and Controllers
- RESTful design and the HTTP request
If you’ve been tinkering with the old bookstore application, which I hope all of you have, there is a chance that it looks nothing how we left it. Do yourself and grab a fresh copy, which I will be using as the foundation for this segment.
With our last iteration of the bookstore we left with a simple, functional application that allowed us to build and maintain a database of books using the Rails’ scaffolding. Currently, our bookstore stores the title and description of a book; that’s not enough…I demand more! Don’t books have authors? Indeed they do. There are a few options, and it’s time for you to choose your own adventure…you want to add an author to your books, but there’s a fork in the road…which path do you take?
- Add a column to the existing Books database (create a database migration to add an “author” column to your existing database)
- Create a new Authors database (create a relational database)
Both are viable options, and it is likely you may not have any idea what the hell I am talking about. Let’s choose option two, by the time we work through this method, you will be able to use either method. Before we start coding, as with any project, it’s good to understand what we are trying to accomplish. Here’s the goal: create another database that contains authors, then relate this database with the existing book database.
Two Rails Databases
The first thing we’re going to do is create a separate database; which really, is just a table in the existing database. Using your friend the scaffold generator with one column for the authors “name”. Do you remember the syntax? Remember: models are singular…author, not authors!
ng:bookstore admin$ script/generate scaffold Author name:string
exists app/models/
exists app/controllers/
exists app/helpers/
create app/views/authors
exists app/views/layouts/
exists test/functional/
exists test/unit/
create app/views/authors/index.html.erb
create app/views/authors/show.html.erb
create app/views/authors/new.html.erb
create app/views/authors/edit.html.erb
create app/views/layouts/authors.html.erb
identical public/stylesheets/scaffold.css
dependency model
exists app/models/
exists test/unit/
exists test/fixtures/
create app/models/author.rb
create test/unit/author_test.rb
create test/fixtures/authors.yml
exists db/migrate
create db/migrate/002_create_authors.rb
create app/controllers/authors_controller.rb
create test/functional/authors_controller_test.rb
create app/helpers/authors_helper.rb
route map.resources :authors
Short and sweet. If you jumped the gun and tried to access: “http://localhost:3000/authors” you likely got an error. Did you run “rake db:migrate”? If so, do it now.
ng:bookstore admin$ rake db:migrate (in /Users/admin/Desktop/bookstore) == 2 CreateAuthors: migrating ================================================= -- create_table(:authors) -> 0.0040s == 2 CreateAuthors: migrated (0.0043s) ========================================
Visit your new Authors section at “http://localhost:3000/authors” and add two authors. So, now we have a database of books and a database of authors, how do we tie these together? Allow me to introduce the “foreign key”, while this sounds super complex, I’ll simplify it for our purposes. Every record in our database has a unique ID, you may notice in our URLs we access our books and authors by “http://localhost:3000/books/1″ and “http://localhost:3000/authors/1″; these ID numbers correspond with our databases. Remember, these are unique! Take a look at the data from our the authors and books tables, take note that I ran out of room on my blog post to show the created_at and updated_at columns for the books table, trust me that it’s in the database.
By looking at the tables, you should be able to see how the relationship between the ID in authors table and the respective entry. To beat a dead horse, ID 1 from authors contains the following:
Barack Obama’s Record from Authors
- ID = 1
- name = Barack Obama
- created_at = 2008-05-01 19:59:17
- updated_at = 2008-05-01 19:59:17
Just as ID 2 from authors contains this set of data:
Bill Clinton’s Record from Authors
- ID = 2
- name = Bill Clinton
- created_at = 2008-05-01 19:59:32
- updated_at = 2008-05-01 19:59:32
Freakonomics Record from Books
If we look at the books table (keep in mind I didn’t show created_at or updated_at above), and look at the book with an ID of 1, we see:
- ID = 1
- title = freakonomics
- description = Which is more dang…(truncated)
- created_at = 2008-05-01 19:59:32
- updated_at = 2008-05-01 19:59:32
If this looks repetitive and trivial, GREAT, that means it makes sense to you. I am showing three, simple examples because you MUST understand how these records are accessed. Still look foreign? Look over the examples a few more times, once it sinks in, we’ll take this a step further.
If you remember the task at hand, you’ll recall that we want to associate an author with a book. We’ll do this with a foreign key. What is that, well the reason I disassembled the database table structure for this reason exactly! Simply put, a foreign key is the ID of your record, in a table isn’t its own. If you place ID 1 from authors in the books table, you now have a foreign key. Not that cool….yet.
We don’t arbitrarily place the foreign key in the books table; foreign keys, by default are placed in their own column. Since we want to place the authors ID in the books table, we need a new column for this foreign key. It is convention to name these columns based on the origin of the foreign key; in this particular case, we would create a column named “author_id” to the table books. Okay…a little confusing still? Hang with me. I didn’t ask you to create any columns, so don’t worry…but let’s pretend we created author_id in books and the author_id column for our Freakonomics entry was assigned a value of “2″…it would looks like this:
- ID = 1
- title = freakonomics
- description = Which is more dang…(truncated)
- author_id = 2
- created_at = 2008-05-01 19:59:32
- updated_at = 2008-05-01 19:59:32
What does this author_id with a value of “2″ actually mean? Well, this foreign key tells us that if we look at the author table, and find record with an ID of “2″, all that data applies to our Freakonomics book! Currently, the only data in our authors table is a name, but what if it contained a lot more? Perhaps the author’s homepage, biography, age, etc!
By using a foreign key, we can keep data separate. This may not seem like a big deal right now, but imagine this scenario:
We have a million books all authored by the same guy, and instead of setting of a separate database for the book’s authors, we decided to put the author’s name, age, hair color, and publisher in out book table. Say the author changed their hair color, name, and found a new publisher. This sucks…not only are we repeating all this data in our books table, but we need to go through EACH record and change all of those fields.
Now image this…instead of just adding columns to our books table, we create a separate table called authors and in this table we add the columns: name, hair color, and publisher. In our books table, instead of having name, age, hair color, and publisher we have one foreign key column called author_id. Now that we use a foreign key to reference the secondary, related table we can easily update the required data fields!
Why did I take the time and teach you this? Even though Rails will handle most associations, you will require this basic understanding in order to create the foreign keys so Rails can perform its magic.
Rails Migrations
You may recall we run “rake db:migrate” after we generate a scaffold; we do this to create, or “migrate” or database to the most current version. You haven’t had to manually create any “migrations”, because Rails has automagically done it for you. Before we play with migrations, you should know what they are.
Back in the day, when developers worked on databases they would manually add tables (books, authors) and columns (name, description, title). No qualms here. The problem was, somewhere down the line if you wanted to remove a column, modify a table, change a field type, etc. You would again, manually modify the database. The end result is a database with the structure that you want, but let’s say you were trying to figure out the differences between your first and third version of your database. Not really possible…what migrations allow you to do is version control your database.
Here’s a bigger picture: As your development skills progress, you will likely move to a version control system for your code (git, subversion). Version control will allow you to save your code at different stages, and instantly revert to any of those saved stages (think saved game on the xbox). With version control in place, one version of your code might be looking for the title of your book; but down the road, you decided that the book title should really be called the this_is_the_book_title. You make the change to the database, renaming your title field to this_is_the_book_title. Let’s say you’ve done some more code, and realize that you made a mistake, and want to revert back to your saved version where the book title was actually called title.
Using your version control system, you quickly revert back to your saved version where your code references your Book.title (verses Book.this_is_the_book_title). The problem here is your database has a field called this_is_the_book_title. Of course, you can manually change this back; and with one or two fields this would be fine. Imagine if you changed hundreds of fields over hundreds of thousands of tables? Not so simple anymore!
Migrations, as you will see, allow for you make sure your code and your databases are in sync. Examples are golden, so open up /db/migrate/001_create_books.rb!
class CreateBooks < ActiveRecord::Migration def self.up create_table :books do |t| t.string :title t.text :description t.timestamps end end def self.down drop_table :books end end
Off the bat, take note of the file name, “001_create_book.rb”. We know that this is the migration with number “001″ and the name “create_book”, we can see that this migration is the first, and it will create something called “book”. If you look in the db/migrate directory you will see the sequential list of files 002, 003, etc. Looking at the migration file I listed above, you’ll see a self.up and self.down. The code defined in self.up is what Rails runs when we migrate forward, the code in self.down is what is run when we migrate backwards.
When we run rake db:migrate, we ask Rails to bring our database up to the most current version, from 001 onwards; though we haven’t used it yet, we can just as easily bring the database back to the first day we created the application, we’d do this with:
rake db:migrate VERSION=1
Now, open up “/db/migrate/002_create_authors.rb” you should see this:
class CreateAuthors < ActiveRecord::Migration
def self.up
create_table :authors do |t|
t.string :name
t.timestamps
end
end
def self.down
drop_table :authors
end
end
If we were to run the command "rake db:migrate VERSION=1" the first thing Rails would do, is figure out which version of the database it is currently at (Rails does this through a table called schema". Once it finds the version, in this case, we are at version 2, it will proceed to run all the self.down code until it reaches the version we specified, in this case version 1. In this particular instance, Rails will first drop the table "authors".
This may all be confusing to you, it was to me. As long as you understand the basic foundation, through repeated use, it will all make more sense. Alright, enough of that...time to play with Rails!
Rails Associations
Our original goal was to add author data to a particular book. Now that we've created a separate author database, let's tie them together. You're going to see why I spent so much time explaining foreign keys and migrations.
Under the hood, Rails is more than willing to simplify relational databases. The important thing about associations is to have a high level understanding of what belongs to what. Let me preface this by saying, I am aware this example can go multiple ways, so don't nitpick 
Anyways, with our Book example, let's look at our books and authors. A book belongs to an author, and an author has many books? It just happens Rails has a has_many and belongs_to method. These are basic Rails associations, and we declare them in the model. Open up your model/book.rb and model/author.rb and add the following lines of code:
# in model/book.rb class Book < ActiveRecord::Base belongs_to :author end # in model/author.rb class Author < ActiveRecord::Base has_many :books end
What we've done is set up the associations between the two models, now Rails knows to look for an additional piece of data, the foreign key, and will handle all the database queries for you! One thing of particular importance: you will notice that author is singular with belongs_to declaration, while the has_many is plural. I point this out because many of you will overlook this and wonder why your application is not working.
Adding a Foreign Key in Rails
Now that our associations are in place, let's create a migration for adding the foreign_key. This migration will be called, "add author ID to books", and to do this we'll run this command:
script/generate migration add_author_id_to_books
ng:bookstore admin$ script/generate migration add_author_id_to_books
exists db/migrate
create db/migrate/003_add_author_id_to_books.rb
Open the file that was just generated. Remember everything I told you about foreign keys? We're going to create a column called "author_id" in our Book table. Rails will see the relationship we declared in our model, and by using our foreign key, will be able to determine Author record matches up with our Book record.
class AddAuthorIdToBooks < ActiveRecord::Migration
def self.up
add_column :books, :author_id, :int
end
def self.down
remove_column :books, :author_id
end
end
Save the file, and run "rake db:migrate". Another important tidbit: the rule of thumb is the foreign key should be created in the database whose model contains the "belongs_to" declaration, in this case, the Book table.
ng:bookstore admin$ rake db:migrate (in /Users/admin/Desktop/bookstore) == 3 AddAuthorIdToBooks: migrating ============================================ -- add_column(:books, :author_id, :int) -> 0.0200s == 3 AddAuthorIdToBooks: migrated (0.0202s) =================================== ng:bookstore admin$
Modifying the View
Before you get took excited, there are a few things we need to do. Our backend is complete, now we need to modify some views so we can pair the page our users see to our backend database. Let's start by adding few lines of code to the Books view. Open: app/views/books/new.html.erb:
<h1>New book</h1> <%= error_messages_for :book %> <% form_for(@book) do |f| %> <b>Title</b> <%= f.text_field :title %> <b>Description</b> <%= f.text_area :description %> <b>Author:</b> <%= collection_select(:book, :author_id, Author.find(:all, :order => "name") , :id, :name) %> <%= f.submit "Create" %> <% end %> <%= link_to 'Back', books_path %>
We just modified the view when the "new" method is called in our Books controller. We called on method from the Rails FormHelper library, here's quick breakdown of what the collection select does:
<%= collection_select(:book, :author_id, Author.find(:all, :order => "name") , :id, :name) %> # This returns the following HTML <select id="book_author_id" name="book[author_id]"> <option value="THE AUTHOR ID">THE AUTHOR TITLE</option> </select>
Accessing Associated Data Fields
Up to this point, I feel like I've been doing everything for you I'm going to give you a small piece of information, then ask you to complete a task. When we set up the associations we specified the following:
- An author has_many :books
- A book belongs_to :author
Visualizing and setting up associations is really the most difficult part, Rails really takes on the rest of relational tasks. Recall, the author table contains the name field, since we told Rails that an author has many books, and a book belongs to an author; we can access the associated field using the following:
# returns the book title <%= @book.title %> # returns the authors name <%= @author.name %> # returns the associated author's name <%= @book.author.name %>
Now spend a few minutes, and alter the rest of the views in app/views/books; leave the index view alone for now. If you get stuck, this is what they should look like:
# app/views/books/edit.html.erb <h1>Editing book</h1> <%= error_messages_for :book %> <% form_for(@book) do |f| %> <b>Title</b> <%= f.text_field :title %> <b>Description</b> <%= f.text_area :description %> <b>Author:</b> <%= collection_select(:book, :author_id, Author.find(:all, :order => "name") , :id, :name) %> <%= f.submit "Update" %> <% end %> <%= link_to 'Show', @book %> | <%= link_to 'Back', books_path %>
# app/views/books/show.html.erb <b>Title:</b> <%=h @book.title %> <b>Description:</b> <%=h @book.description %> <b>Author:</b> <%=h @book.author.name %> <%= link_to 'Edit', edit_book_path(@book) %> | <%= link_to 'Back', books_path %>
Alright! Since we added a foreign key column, our old data will not have a value for its foreign key. We could simply delete the old record or modify it so it is up to date, but I want to introduce a handy little feature: resetting the database! With the "rake db:reset" command, Rails drops and recreates the current database from db/schema.rb for the current environment. Run it!
ng:bookstore admin$ rake db:reset
(in /Users/admin/Desktop/bookstore)
"db/development.sqlite3 already exists"
-- create_table("authors", {:force=>true})
-> 0.0043s
-- create_table("books", {:force=>true})
-> 0.0042s
-- initialize_schema_information()
-> 0.0439s
-- columns("schema_info")
-> 0.0007s
ng:bookstore admin$
All Systems Go
FIRE UP YOUR SERVER! Proceed to add a few authors via /authors...
...then add a few books via /books; notice the drop down menu? Success!
Finally, click on a book and get a more detailed picture...see the associated author? Ahhh! The fruits of your labor!
Under The Hood
If you're curious what is happening when you're creating or viewing records, take a look at your console. Behold, the power of Rails!
Accessing /books/new:
Processing BooksController#new (for 127.0.0.1 at 2008-05-04 23:26:02) [GET] Session ID: BAh7ByIKZmxhc2hJQzonQWN0aW9uQ29udHJvbGxlcjo6Rmxhc2g6OkZsYXNo%0ASGFzaHsABjoKQHVzZWR7ADoMY3NyZl9pZCIlZGEyMjY4MzgzZDQ1MGU4NjE1%0ANDEwMDlmY2VlYjJhZGI%3D--fb8c96ae999682b51105e633e7e6da0c3d008cf1 Parameters: {"action"=>"new", "controller"=>"books"} Rendering template within layouts/books Rendering books/new Author Load (0.000754) SELECT * FROM authors ORDER BY name Completed in 0.01405 (71 reqs/sec) | Rendering: 0.00755 (53%) | DB: 0.00075 (5%) | 200 OK [http://localhost/books/new]
On the creation of a book:
Processing BooksController#create (for 127.0.0.1 at 2008-05-04 23:26:48) [POST] Session ID: BAh7ByIKZmxhc2hJQzonQWN0aW9uQ29udHJvbGxlcjo6Rmxhc2g6OkZsYXNo%0ASGFzaHsABjoKQHVzZWR7ADoMY3NyZl9pZCIlZGEyMjY4MzgzZDQ1MGU4NjE1%0ANDEwMDlmY2VlYjJhZGI%3D--fb8c96ae999682b51105e633e7e6da0c3d008cf1 Parameters: {"commit"=>"Create", "authenticity_token"=>"82b54355dede97ccb50e1bfa90ab3c5ffcf081ae", "action"=>"create", "controller"=>"books", "book"=>{"title"=>"Don't Make Me Think", "description"=>"Usability design is one of the most important--yet often least attractive--tasks for a Web developer. In Don't Make Me Think, author Steve Krug lightens up the subject with good humor and excellent, to-the-point examples.", "author_id"=>"2"}} Book Create (0.000378) INSERT INTO books ("updated_at", "title", "description", "author_id", "created_at") VALUES('2008-05-04 23:26:48', 'Don''t Make Me Think', 'Usability design is one of the most important--yet often least attractive--tasks for a Web developer. In Don''t Make Me Think, author Steve Krug lightens up the subject with good humor and excellent, to-the-point examples.', 2, '2008-05-04 23:26:48') Redirected to http://localhost:3000/books/3 Completed in 0.01351 (74 reqs/sec) | DB: 0.00038 (2%) | 302 Found [http://localhost/books]
Viewing a single book's details:
Processing BooksController#show (for 127.0.0.1 at 2008-05-04 23:27:37) [GET] Session ID: BAh7ByIKZmxhc2hJQzonQWN0aW9uQ29udHJvbGxlcjo6Rmxhc2g6OkZsYXNo%0ASGFzaHsABjoKQHVzZWR7ADoMY3NyZl9pZCIlZGEyMjY4MzgzZDQ1MGU4NjE1%0ANDEwMDlmY2VlYjJhZGI%3D--fb8c96ae999682b51105e633e7e6da0c3d008cf1 Parameters: {"action"=>"show", "id"=>"2", "controller"=>"books"} Book Load (0.000247) SELECT * FROM books WHERE (books."id" = 2) Rendering template within layouts/books Rendering books/show Author Load (0.000246) SELECT * FROM authors WHERE (authors."id" = 3) Completed in 0.01162 (86 reqs/sec) | Rendering: 0.00550 (47%) | DB: 0.00049 (4%) | 200 OK [http://localhost/books/2]
Until next time
Four tutorials in, you guys should know the drill by now 
Next time I'll be covering validations, routing, and maybe even some Rails SEO. Useful? Donate to my beer fund!
The next post has been posted! It covers partials, the before filter, and basic HTTP authentication.
For further reading, take a look at the Rails API, it is an invaluable reference:
Module: ActiveRecord::Associations::ClassMethods
