ActiveRecord: The Sequel to SQL (featuring CRUD)

If you are familiar with database systems, you probably know that SQL (Structured Query Language) is a language that allows you to create and maintain relational database management systems. However, when using SQL in conjunction with another programming language, it is often a pain to join the two. To solve this issue when programming in Ruby/Rails, we have the famous ActiveRecord gem. ActiveRecord gives us a simplified set of instructions on how to create and update a database, and completes the SQL for us.

There are four main functions that can be performed on databases, create tables and table data, read data, update data, and delete tables and table data, as known as CRUD. Below, I highlighted some of the most common ActiveRecord CRUD commands and their corresponding SQL command.


Say we have a database for animals and want to create a table for our Dog class:

CREATE

a ‘dogs’ table:

#ActiveRecord
def change
create_table :dogs do |t|
t.string :name
t.string :breed
t.integer :age
end
end

#SQL
CREATE TABLE dogs (
in INTEGER PRIMARY KEY,
name STRING,
breed STRING,
age INTEGER);

a new instance of a dog:

#ActiveRecord
Dog.create(name: "Badger", breed: "Boxer", age: 3)
#SQL
INSERT INTO dogs (name, breed, age) VALUES ("Badger", "Boxer", 3);

READ

select all dogs:

#ActiveRecord
Dog.all
#SQL
SELECT * FROM dogs;

select the dog whose name is “Badger”

#ActiveRecord
Dog.find_by(name: "Badger")
#SQL
SELECT name FROM dogs WHERE name = "Badger";

UPDATE

add a ‘color’ column to the table

#ActiveRecord
def change
add_column :dogs, :color, :string
end
#SQL
ALTER TABLE dogs
ADD color STRING;

update data in the table

#ActiveRecord
Dog.update(name: "Milo", ...)
#SQL
UPDATE dogs SET name = "Milo" WHERE name = "Badger";

DELETE

delete a dog from the table

#ActiveRecord
Dog.find_by(name: :Milo").destroy
#SQL
DELETE FROM dogs WHERE name = "Milo";

delete all dogs form the table

#ActiveRecord
Dog.destroy_all
#SQL
TRUNCATE TABLE dogs;

delete the entire table

#ActiveRecord
def change
drop_table :dogs
end
#SQL
DROP TABLE dogs;

See more ActiveRecord commands here http://guides.rubyonrails.org/active_record_basics.html.