Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Level-up Your ActiveRecord Skills: Learn SQL!

Level-up Your ActiveRecord Skills: Learn SQL!

Slides from my Junior Developer Track talk on the value of learning SQL. Originally presented at RailsConf 2016 in Kansas City.

Ryan Dlugosz

May 04, 2016
Tweet

Other Decks in Programming

Transcript

  1. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Level-up Your ActiveRecord Skills: Learn SQL! Ryan Dlugosz – Railsconf 2016
  2. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Behind almost every Rails app lies a powerful language called SQL Learning its strengths will make you a better developer!
  3. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Our time together • SQL Backstory • Wonderful Examples • Where to go from here
  4. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    About Me Ryan Dlugosz (duh-LOO-gus)
 @lbwski
 Consultant Ruby Instructor with Girl Develop It
  5. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    The World Before ORMs (Object-Relational Mappers, e.g., ActiveRecord)
  6. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    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]]
  7. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Hilariously Dangerous, Naïve Solution! <form action="/save_data.php" method="post"> <input type="text" name="fname"> <input type="text" name=“lname"> <button type="submit"></button> </form> <input type="hidden" name="table_name" value="people"> <button type="submit">Pls Don't Hack Me!</button>
  8. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    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
  9. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    –Chris Nelson, @superchris Apps: they come and go Frameworks, databases too SQL will live on And now, a poem…
  10. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Learn the Native Language Tourists can point at things and get around, but could never start a business. –Richard Hipp, SQLite creator
  11. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    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
  12. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    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
  13. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    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
  14. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    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;
  15. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Understanding Our Data How many books do we have in each topic? SELECT topic, count(topic) FROM books GROUP BY topic; ┌────────────┬───────┐ │ topic │ count │ ├────────────┼───────┤ │ sql │ 480 │ │ java │ 495 │ │ html │ 479 │ │ javascript │ 567 │ │ elixir │ 494 │ │ rails │ 473 │ │ css │ 507 │ │ ruby │ 505 │ └────────────┴───────┘ (8 rows) Time: 1.806 ms railsconf_sql_demo=# # ActiveRecord equivalent # Book.group(:topic).count(:topic)
  16. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Understanding Our Data How many books do we have in each topic? ┌────────────┬─────────────┬────────┬────────┬───────────┐ │ topic │ topic_count │ min_pg │ max_pg │ avg_pages │ ├────────────┼─────────────┼────────┼────────┼───────────┤ │ sql │ 480 │ 6 │ 504 │ 254.57 │ │ java │ 495 │ 7 │ 504 │ 262.81 │ │ html │ 479 │ 6 │ 504 │ 265.11 │ │ javascript │ 567 │ 5 │ 504 │ 253.56 │ │ elixir │ 494 │ 6 │ 503 │ 261.36 │ │ rails │ 473 │ 6 │ 503 │ 250.96 │ │ css │ 507 │ 6 │ 504 │ 255.95 │ │ ruby │ 505 │ 5 │ 503 │ 253.68 │ └────────────┴─────────────┴────────┴────────┴───────────┘ Min, max and mean Page Count by topic? SELECT topic, count(topic) as topic_count, min(pages) as min_pg, max(pages) as max_pg, round(avg(pages), 2) as avg_pages FROM books GROUP BY topic;
  17. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Joining Data Which authors have written 5 or more books in a topic? authors books author.id == book.author_id
  18. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Joining Data Which authors have written 5 or more books on a topic? ┌────────────────────┬────────────┬───────┐ │ name │ topic │ count │ ├────────────────────┼────────────┼───────┤ │ Nadia McLaughlin │ sql │ 5 │ │ Domenick Von │ javascript │ 5 │ │ Miss Rick Lowe │ css │ 5 │ │ Darlene Feeney II │ elixir │ 5 │ │ John Lubowitz │ javascript │ 5 │ │ Chadrick Howe DVM │ css │ 6 │ │ Curt Dietrich │ html │ 6 │ │ Shyanne Krajcik MD │ java │ 5 │ │ Hadley Zulauf │ html │ 5 │ └────────────────────┴────────────┴───────┘ (9 rows) Time: 4.545 ms SELECT authors.name, topic, count(books.id) FROM authors JOIN books ON books.author_id = authors.id GROUP BY authors.name, topic HAVING count(books.id) > 4
  19. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Joining Data authors books author.id == book.author_id
  20. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    EXPLAIN a.k.a. – why is this slow? ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Limit (cost=0.00..0.20 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1) │ │ -> Seq Scan on books (cost=0.00..100.00 rows=505 width=4) (actual time=0.005..0.005 rows=1 loops=1) │ │ Filter: ((topic)::text = 'ruby'::text) │ │ Rows Removed by Filter: 3 │ │ Planning time: 0.132 ms │ │ Execution time: 0.018 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (6 rows) Time: 0.368 ms railsconf_sql_demo=# railsconf_sql_demo=# EXPLAIN ANALYZE select id from books where topic = 'ruby' limit 1; ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Limit (cost=0.00..0.20 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1) │ │ -> Seq Scan on books (cost=0.00..100.00 rows=505 width=4) (actual time=0.005..0.005 rows=1 loops=1) │ │ Filter: ((topic)::text = 'ruby'::text) │ │ Rows Removed by Filter: 3 │ │ Planning time: 0.132 ms │ │ Execution time: 0.018 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (6 rows) Time: 0.368 ms railsconf_sql_demo=# class AddIndexToBookTopic < ActiveRecord::Migration[5.0] def change add_index :books, :topic end end
  21. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    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
  22. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Remember Learn the Native Language Understand what ActiveRecord is doing Pick the Right Tool for the job!