The World Before ORMs sql = "SELECT id, fname, lname, birth, likes_cats FROM people;" results = exec_query(sql) people = [] results.each do |result| p = Person.new p.id = result[0] p.fname = result[1] p.lname = result[2] p.birthdate = result[3] p.likes_cats = result[4] == 1 people << p end Now, do this same sort of thing for every database interaction in your application! …and be ready to make frequent changes when this extremely fragile code breaks! p.id = result[0] p.fname = result[1] p.lname = result[2] p.birthdate = result[3] p.likes_cats = result[4] == 1 results = [[1, "Jeff", "Lebowski", 1949-11-20, 0], [2, "Maude", "Lebowski", 1960-12-03, 1]]
ActiveRecord to the Rescue sql = "SELECT id, fname, lname, birth, likes_cats FROM people;" results = exec_query(sql) people = [] results.each do |result| p = Person.new p.id = result[0] p.fname = result[1] p.lname = result[2] p.birthdate = result[3] p.likes_cats = result[4] == 1 people << p end # with ActiveRecord... people = Person.all
Authors and Books create_table "authors" do |t| t.string "name" t.string "email" t.string "country" t.date "birth" end create_table "books" do |t| t.string "title" t.integer "pages" t.date "publish_date" t.string "topic" t.string "isbn" t.integer "author_id" end class Author < ApplicationRecord has_many :books end class Book < ApplicationRecord belongs_to :author end
Understanding Our Data SELECT count(id) FROM books; SELECT count(id) FROM authors; How many books do we have? How many Authors? # Book.count # Author.count
Understanding Our Data railsconf_sql_demo=# SELECT count(id) FROM books; How many books do we have? ┌───────┐ │ count │ ├───────┤ │ 4000 │ └───────┘ (1 row) Time: 1.692 ms railsconf_sql_demo=# $ select count(id) from books; psql -d railsconf_sql_demo
Understanding Our Data railsconf_sql_demo=# SELECT count(id) FROM authors; How many authors do we have? ┌───────┐ │ count │ ├───────┤ │ 550 │ └───────┘ (1 row) Time: 0.895 ms railsconf_sql_demo=# select count(id) from authors;
Security Concerns Preventing SQL Injection is beyond our scope today, but in general you must ensure that you are not including untrusted data in your SQL. http://guides.rubyonrails.org/security.html