Active record is an awesome tool and helps with multiple parts of your backend. For example active record gives access to many useful methods for your models and is an ORM (Object-Relational Mapper). ORM is a technique for accessing a relational database with an object oriantated language. In this blog we are going to be focusing on the many amazing methods active record provides us. As well as recreate these methods to demostrate what active record is doing under the hood.

What Exactly is Active Record?

What is active record? to put it simply active record is a ruby gem that is an entire library of code. It allows us to focus on making exciting things instead of doing repetative tasks. Active record is simply installed by running gem install active record or including it in your gem file.

What is an ORM?

What is an ORM? Like mentioned before ORM is a technique for accessing a releational database with an object oriantated language. What does this mean for us? to put it simply we use ruby programs to manage database data by "mapping" database tables to ruby classes and instances of those classes to rows in tables. Without active record we will have to be creating our own custom ORM's.

Establishing Database Connection

First step we need to do is establish a connection for our database we can do this with the following code in our enviroment file.

require 'bundler'
Bundler.require

require_relative '../lib/dog'

DB = { conn: SQLite3::Database.new("db/dogs.db") }

With active record it would look like this.

ActiveRecord::Base.establish_connection(
  adapter: "sqlite3",
  database: "db/artists.sqlite"
)

Creating Tables

Now that our database connection is established next we need to create our tables first before we start mapping through them. Let's say we want to create a table for dogs and in the table we want coloumns of name and breed. Consider the following code.

class Dog

  def self.create_table
    sql =  <<-SQL
      CREATE TABLE IF NOT EXISTS dogs (
        id INTEGER PRIMARY KEY,
        name TEXT,
        breed TEXT
      )
      SQL
    DB[:conn].execute(sql)
  end
end

Let's break this down. Within our Dog class we are defining a class method called self.create_table and in that method we write the following SQL code for creating our table then use DB[:conn].execute(sql) to execute the SQL to create this table in our database.

In active record we would do the same thing just with a bit of a twist.

sql = <<-SQL
  CREATE TABLE IF NOT EXISTS dogs (
    id INTEGER PRIMARY KEY,
    name TEXT,
    breed TEXT
  )
SQL
ActiveRecord::Base.connection.execute(sql)

Instead of using DB[:conn].execute(sql) we use ActiveRecord::Base.connection.execute(sql).

Class Methods

As shown before we need to create classes and methods to create our tables. However, How will our classes know the names of the columns in our database? Sure it knows the databases name becuase of the class name but without it knowing it's columns name there isn't much we could do with the table. We fix this by making attr_accessors that match the column names and initializing it. Now our class would look like this.

class Dog

  attr_accessor :name, :breed, :id

  def initialize(name:, breed:, id: nil)
    @id = id
    @name = name
    @breed = breed
  end

  def self.create_table
    sql =  <<-SQL
      CREATE TABLE IF NOT EXISTS dogs (
        id INTEGER PRIMARY KEY,
        name TEXT,
        breed TEXT
      )
      SQL
    DB[:conn].execute(sql)
  end
end

Here we are "mapping" our class to a database table by having the class name match the table name and having it pluralized. We also have the column names match the attr_accessors of our class.

We then would have to continue to write out methods with SQL imbedded in them to manipulate our dog table. Let's start with our basic CRUD (Create, Read, Update, Delete) actions.

Create Method

While creating our initialize method did you notice something? let's take another look.

 def initialize(name:, breed:, id: nil)
    @id = id
    @name = name
    @breed = breed
  end

Why is the id instance initialized as nil? Well when you put new data into our database it will automatically give it an id. What this means is we want our database to give our class an id not the other way around becuase this will keep the id number same across the board.

While keeping that in mind we can move onto creating our method. We are going to need to accomplish two main things which is creating a new dog instance in our class and saving it into our database. We already have a way to save a dog instance by using the .new method so we have one step done we are just going to have to save this new instance to the database.

Save Method

So how do we start creating this save method? First we are going to have to define the method and create the SQL for inserting our new dog like so.

def save
   sql = <<-SQL
         INSERT INTO dogs (name, breed)
         VALUES (?, ?)
         SQL

Great! Here we are writting SQL to insert the name and breed of our new dog into our database. However, we arn't near done yet we still need to fill those dynamic parameters with the values we initialized and execute the SQL just like how we executed the SQL when creating our table

def save
   sql = <<-SQL
        INSERT INTO dogs (name, breed)
        VALUES (?, ?)
        SQL

    DB[:conn].execute(sql, self.name, self.breed)

Here we executed the SQL and us the self keyword to get our values into that SQL. We are almost there! All we need now is the new id value from our database.

We will do this by creating a new SQL query to grab our last inserted row and equal this to our id. When grabbing the last inserted row it will get us a heavly nested array which we will then do [0][0] to get just the id value. So just to recap our whole save method would look like this.

def save
   # creating SQL to insert values into our database
   sql = <<-SQL
        INSERT INTO dogs (name, breed)
        VALUES (?, ?)
        SQL
    # filling in those values in our SQL query and executing it
    DB[:conn].execute(sql, self.name, self.breed)

    # getting the id from the database and equating it to our Id
    self.id = DB[:conn].execute("SELECT last_insert_rowid() FROM 
    dogs")[0][0]

    # returning the ruby instance
    self
 end

We did it! we created our save method! With this method we can now persist new data to our database! Wheeeewwww that was alot of work for one method and sadly this does leave alot to be desired. For example everytime we create a new ruby instance we have to call the save method everytime.

Oreo = Dog.new(name: "Oreo", breed: "French Bulldog")
# => #<Dog:0x00007fdg33935128 @name="Oreo", @id=nil, @breed="French Bulldog">

Oreo.save
# => []

Hiedi = Dog.new(name: "Hiedi", breed: "Golden Retriever")
# => #<Dog:0x00007fdg135c6200 @name="Hiedi", @id=nil, @breed="Golden Retriever">

Hiedi.save
# => []

This can be very repetative and honestly exhausting so how can we do both create a new ruby instance and persist the changes to the database?

Create Method

The good news is we did most of the work already. Reason being is we are going to reuse the save method we made. So lets start by creating a method that creates a new dog instance and returns it to a variable

def self.create(name:, breed:)
   dog = Dog.new(name: name, breed: breed)
end

Now all we have to do is save that new instance reusing our save method.

 def self.create(name:, breed:)
    dog = Dog.new(name: name, breed: breed)
    dog.save
  end

With this method now we can do the folllowing.

Dog.create(name: "Fido", breed: "Husky")
# => #<Dog:0x00006b2f579ae6d8 @name="Fido", @id=1, @breed="Husky">

Ta-Da! With this method now we create a new instance and persist it to the database all with one line!

Read Method

There are many read methods that we can code out but for all those methods we are going to have to take the response from the database and make it into a ruby object.

New From Database Method

Depending on the database we can get different responses in this example we are using SQLite. With SQLite we will get an array of data similiar to a row in our database it would look something like this [1, "Fido", "Husky"]. Knowing that all we really have to do is create a method that takes this array and assigns it's values to a ruby instance. It would look something like this.

def self.new_from_db(row)
   self.new(id: row[0], name: row[1], breed: row[2])
end

Cool! Now all we need is to get that response from the database.

Find By Name Method

As the title suggests we are going to create a method that finds a dog by name. You probably already guessed by now where we need to start which is creating the SQL query.

def self.find_by_name(name)
  sql = <<-SQL
     SELECT * FROM dogs
     WHERE name = ? LIMIT 1
    SQL
end

That looks perfect! Now we need to tell the database to execute this SQL and map it into our new_from_db method.

def self.find_by_name(name)
  sql = <<-SQL
     SELECT * FROM dogs
     WHERE name = ? LIMIT 1
    SQL

  DB[:conn].execute(sql, name).map do |row|
     self.new_from_db(row)
  end.first
end

Nice! Here we are making the SQL query to search for a dog by name then executing that query and maping through the results. We then call our new_from_db method to create the new ruby instance and finally ending it after the first result with our #first method. BAM! we created our find_by_name method! Let's try using it.

Dog.find_by_name("Fido")
# => #<Dog:0x00007f7f632ae6c8 @name="Fido", @id=1, @breed="Husky">

Update Method

For our update method we are going to have to write SQL using the UPDATE keyword. The UPDATE keyword needs to be followed with the SET keyword which sets the new values and the WHERE keyword to make sure we have the right dog. The SQL would look like this.

def update
   sql = <<-SQL
   UPDATE dogs
   SET 
      name = ?,
      breed = ?
   WHERE id = ?
   SQL
end

Awesome! This SQL statment does most of the work for us now all we have to do is execute the SQL and fill in the parameters. We do this by using the self keyword.

def update 
        sql = <<-SQL
        UPDATE dogs
        SET 
            name = ?, 
            breed = ?
        WHERE id = ?
        SQL

        DB[:conn].execute(sql, self.name, self.breed, self.id)
    end

BOOOMMMM! We got our update method! So lets give this method a test drive.

buddy = Dog.create(name: "Buddy", breed: "BullDog")
# => #<Dog:0x00006b3f269ae8d3 @name="Buddy", @id=3, @breed="BullDog">

buddy.name = "Sir Buddy"
# => #<Dog:0x00006b2f439ae9d2 @name="Sir Buddy", @id=3, @breed="BullDog">

buddy.update
# => []

This works perfect!

Delete Method

So with this method we have to write in our SQL the keyword DELETE FROM followed by the table name. After that we use the WHERE keyword to make sure we delete the right dog from the database. So it would look something like this.

def delete
    sql = <<-SQL
       DELETE FROM dogs
       WHERE id = ?
    SQL
end

Now all we got to do is execute the sql and give it the id name

def delete
    sql = <<-SQL
       DELETE FROM dogs
       WHERE id = ?
    SQL

DB[:conn].execute(sql, self.id)
end

Nice with that we have our delete method all coded out!

Active Record

All the methods we have defined above is active record under the hood, granted active record has more methods than just CRUD methods but you get the gist. So the question comes to mind how would active record give our Dog class the methods it needs? Well like mentioned before active record is a library of code with already created methods so all we really have to do is the following in our class

class Dog < ActiveRecord::Base
end

Now we get access to all the methods we created earlier by using active record base class and having our Dog class inherit from it and that is it! no need for further coding! All that time we used for coding out all those methods are now done! Amazing right? Now we can focus less on remedial tasks and more on the exciting stuff.

Conclusion

Active record gives us the power to perform many useful methods without having to write any code for said methods. This gives us programmers our time back and allows us to focus on other things our applications are going to need.