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.

4ad03d38dbd63f3d4ab1ae41d7522a6b?s=128

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!

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

    Es-Que-El or Sequel?
  5. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Our time together • SQL Backstory • Wonderful Examples • Where to go from here
  6. 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
  7. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    The World Before ORMs (Object-Relational Mappers, e.g., ActiveRecord)
  8. 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]]
  9. 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>
  10. 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
  11. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

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

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

    Wonderful Examples
  16. 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
  17. 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
  18. 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
  19. 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;
  20. 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)
  21. 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;
  22. 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
  23. 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
  24. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Joining Data authors books author.id == book.author_id
  25. 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
  26. 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
  27. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Little Bobby Tables https://xkcd.com/327/
  28. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Where to go from here
  29. 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!
  30. Ryan Dlugosz — @lbwski Level-up Your ActiveRecord Skills: Learn SQL!

    Thank You! Questions?