ActiveRecord: Object-Relational Mapping for Rails

Lecture Notes for CS 142
Fall 2010
John Ousterhout

Readings for this topic:
- Chapters 18-20 of Agile Web Development with Rails.

Object Relational Mapping (ORM): simplify the use of databases in applications.
- Use objects to hold database records
  - One class for each table in the database
  - Objects of the class correspond to rows in the table
  - Attributes of an object correspond to columns from the row
- Manage the movement of information between objects and the back-end database.
- Manage relationships between tables (joins), turn into linked data structures.

ActiveRecord Basics

Model: a Rails class corresponding to a database table
ActiveRecord:
- Base class for models in Rails
- Implements Object Relational Mapping

Example table:

SELECT * FROM students;
+----+-----------+------------+------+------+
| id | name      | birth      | gpa  | grad |
+----+-----------+------------+------+------+
|  1 | Anderson  | 1987-10-22 |  3.9 | 2009 |
|  2 | Jones     | 1990-04-16 |  2.4 | 2012 |
|  3 | Hernandez | 1989-08-12 |  3.1 | 2011 |
|  4 | Chen      | 1990-02-04 |  3.2 | 2011 |
+----+-----------+------------+------+------+

Create a class for this table (app/models/student.rb):
```
class Student < ActiveRecord::Base
end
```
Or, just use the script/generate program:
```
ruby script/generate model student
```
ActiveRecord examines the database schema for this table and makes appropriate attributes and methods available in the class automatically.

CRUD with ActiveRecord

Create a new record in the table:

student = Student.new
student.name = "Williams"
student.birth = "1989-11-16"
student.gpa = 2.8
student.grad = 2012
student.save()

Read:

student = Student.find(187)
student = Student.find_by_name("Hernandez")
smarties = Student.find(:all, :conditions => "gpa >= 3.0");
smarties = Student.find(:all, :limit => 10, :order => "gpa DESC");

Update:

student = Student.find(187)
student.gpa = 4.0
student.save()

Delete:
```
Student.find(187).destroy()
```
Many conventions here:
- Model class named Student
- Database table named students
- Variable student for instances of Student class
- Same names for database columns and attributes of Student objects
- Automatic pluralization and (de)capitalization: from Student class to students database table.

Relationships between tables

Many-to-one (e.g. students -> advisor):

Database tables:

SELECT * FROM students;
+----+-----------+------------+------+------+------------+
| id | name      | birth      | gpa  | grad | advisor_id |
+----+-----------+------------+------+------+------------+
|  1 | Anderson  | 1987-10-22 |  3.9 | 2009 |          2 |
|  2 | Jones     | 1990-04-16 |  2.4 | 2012 |          1 |
|  3 | Hernandez | 1989-08-12 |  3.1 | 2011 |          1 |
|  4 | Chen      | 1990-02-04 |  3.2 | 2011 |          1 |
+----+-----------+------------+------+------+------------+

SELECT * FROM advisors;
+----+----------+-----------+
| id | name     | title     |
+----+----------+-----------+
|  1 | Fujimura | assocprof |
|  2 | Bolosky  | prof      |
+----+----------+-----------+

Additional declarations in the models

class Student < ActiveRecord::Base
    belongs_to :advisor
end
class Advisor < ActiveRecord::Base
    has_many :students
end

Can now reference the tables as if their objects are connected:

advisor = Advisor.find_by_name("Fujimura")
for student in advisor.students() do
  ...
end

student = Student.find_by_name("Chen")
student.advisor = Advisor.find_by_name("Bolosky")
student.save

ActiveRecord automatically creates new methods in the various classes, (such as Advisor.students) which query information from the database as needed.

Many-to-many (e.g. students -> courses):

Database tables:

SELECT * FROM courses;
+----+--------+-----------------+-------------+
| id | number | name            | quarter     |
+----+--------+-----------------+-------------+
|  1 | CS142  | Web stuff       | Winter 2009 |
|  2 | ART101 | Finger painting | Fall 2008   |
|  3 | ART101 | Finger painting | Winter 2009 |
|  4 | PE204  | Mud wrestling   | Winter 2009 |
+----+--------+-----------------+-------------+

SELECT * FROM courses_students;
+-----------+------------+
| course_id | student_id |
+-----------+------------+
|         1 |          1 |
|         3 |          1 |
|         4 |          1 |
|         1 |          2 |
|         2 |          2 |
|         1 |          3 |
|         2 |          4 |
|         4 |          4 |
+-----------+------------+

You must define the database table courses_students (names of classes in alphabetic order).
No need to define a CourseStudent model.

Additional declarations in the Student and Course models:

class Student < ActiveRecord::Base
    has_and_belongs_to_many :courses
    ...
end
class Course < ActiveRecord::Base
    has_and_belongs_to_many :students
    ...
end

Again, ActiveRecord creates additional methods in the two classes so you can reference back and forth between Student and Course instances:
```
student = Student.find_by_name("Anderson")
courses = student.courses()
cs142 = Course.find_by_number("CS142")
courses << cs142
```

Migrations

Migrations in Rails provide a framework that makes schema evolution much easier to manage:
- Assume from the start that the schema will go through a series of versions.
- A migration provides code that will update the database schema from one version to the next, and back again.
- Rails keeps track of the database's current version and will apply appropriate migrations to switch it to any other version.
Don't create database schema with SQL: use migrations

Migration to create a new table:

class CreateStudents < ActiveRecord::Migration
    def self.up
        create_table :students do |t|
            t.column :name,        :string
            t.column :birth,       :date
            t.column :gpa,         :float
            t.column :grad,        :integer
        end
    end
    
    def self.down
        drop_table :students
    end
end

Primary key id created by default.

Migration to add a new column to the table:

class AddAdvisor < ActiveRecord::Migration
    def self.up
        add_column :students, :advisor_id, :integer
    end
    
    def self.down
        remove_column :students, :advisor_id
    end
end

Command to generate a new migration name:

ruby script/generate migration create_advisors

Command to run migrations (migrate to most recent version):
```
rake db:migrate
```
Command to run migrations (migrate to specific version):
```
rake db:migrate VERSION=20090130180755
```
Back out all migrations:
```
rake db:migrate VERSION=0
```
Destroy the database, create a new database, and run all migrations to bring the new database back up to date
```
rake db:migrate reset
```

CS 142: Web Applications (Fall 2010)

ActiveRecord: Object-Relational Mapping for Rails

ActiveRecord Basics

CRUD with ActiveRecord

Relationships between tables

Migrations