The 'Ommm' of ORM

Object Relational Mapping, or ORM, provides a way for your Ruby program/app to manage database data by mapping database tables to classes and instances of classes to rows in those tables.

By developing your own ORM you can control how your application interacts with its data AND you can get Ruby (or any object oriented language) to do the heavy SQL lifting for you.

ORM's concept of a 'virtual database', or an object oriented language wrapped around a relational database, can be mind boggling at first--especially if you're a visual or tactile learner like me. This post is about how I stopped being afraid and discovered the inner zen (or 'om') of ORM.

Before we move on, take a deep breath and recite your mantra: "classes are mapped to tables and instances of classes are table rows." (I know, you're wondering how I knew your mantra, right?)

Let's say you've been out adventuring with Ash, Misty and Brock and you've captured all 150 Pokemon. 150 poke-balls are taking up a lot of space in your backback and you need a place to store them. But your Pokemon don't just need storage space, they also need to come alive and fight battles for you--they have behaviors and attributes. It sounds like we need a database to store them and a Pokemon class to instantiate real, live (sort of) Pokemon.

Enter ORM

In the executable file of our Pokedex program, we'll create our table with SQLite and open a new database file from within Ruby.

#Open an SQLite3 database file
db = SQLite3::Database.new('db/pokemon.db')

#create table 
db.execute << -SQL CREATE TABLE pokemon (
id INTEGER PRIMARY KEY AUTOINCREMENT, 
name TEXT, 
type TEXT);
SQL

Let's take a brief time-out to talke about the execute method used above. The Database.execute method is a method of the database object. It will return the results of an SQLite query in an array.

Okay, now that we've created our database and our pokemon table, we're ready to create our Pokemon class.

Let's start off with a question: what is the Pokemon class responsible for?

The Pokemon class needs to create individual pokemon with certain attributes (name and type should be enough for now), and it needs to store instances of itself in our database.

We'll initialize our pokemon with those qualities first:

class Pokemon

  attr_accessor :id, :name, :type, :db

  def initialize(id, name, type, db)
    @id = id
    @db = db
    @name = name
    @type = type
  end

Wait a second, we only need pokemon to have names and attributes, who are 'id' and 'db' and what are they doing in our initialize method?

Pokemon need an id so that we can easily add them to our database--id will correspond to the id primary key we added to our pokemon table earlier.

Pokemon also need to be instantiated with an argument of the database object so that the Pokemon class can add instancs of itself to that database.

Now we're ready to define our save method. This is the moment in time in which an individual pokemon is added to our database.

def save(name, type, db)
    sql = "INSERT INTO pokemon (id, name, type) VALUES (?, ?)" 
    
    db.execute(sql, [self.id, self.name, self.type])
  end 

Another brief time-out: you may be wondering abou those wonky question marks and brackets above. They're called bound parameters and they protect our program from getting confused by SQL injections and special characters. Instead of interpolating variables into a string of SQL, we are using the '?' characters as placeholders. The brackets contain the information associated with each ? in the statement.

Accessing rows in our pokemon table:

We've created a database, created the ability to seed it with pokemon via the save method available to instances of the Pokemon class, but how to retrieve pokemon rows from our table? Looks like we'll need another method. This should be a class method because it is the responsibility of the class to know about all of the different pokemon.

def self.all
	rows = db.execute("SELECT * FROM pokemon")
    #return the raw data from the database row.#
    #then, instantiate a Pokemon object#
	rows.collect do |row|
		p = Pokemon.new
		p.id = row[0]
		p.name = row[1]
		p.type = row[2]
		p
    end
end

because the above object has the same id, name and type as the raw data taken from the object that stored itself in the database orginally, it is actually the same object!

And that's it! We've created a database, created a new pokemon table within that database that maps to our Pokemon class and implemented a Pokemon class that not only instantiates individual pokemon, but is capable of saving those pokemon in our database and retrieving them from out database.