Beyond Good and ORMs: Uncovering The Hidden Features of Your Database

ORMs such as ActiveRecord are fabulous tools that have improved the speed at which we're able to develop working, shippable products. As DSLs for working with our persistence layers, they've proven their worth time and time again, but at the cost of stunting our collective knowledge about the built-in, powerful features that different databases have to offer. Let's explore some of those features, rediscover what we've left behind by accepting abstraction, and recover some tools that can help ensure the long-term health of our applications.

Transcript

1. BEYOND GOOD & ORMS Uncovering The Hidden Features of Your Database @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
2. Friday, March 27, 15
3. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
4. @kerrizor Ancient City Ruby 2015 Globe Theater David Welch CC BY-NC-SA 2.0 https://www.flickr.com/photos/dlnwelch/6000888348 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
5. @kerrizor Ancient City Ruby 2015 Unattributed @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
6. @kerrizor Ancient City Ruby 2015 Romeo + Juliet Postcard Michelle B. CC BY-ND 2.0 https://www.flickr.com/photos/42176496@N05/3903316620 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
7. @kerrizor Ancient City Ruby 2015 Romeo & Juliet Anna Magal CC BY-NC 2.0 https://www.flickr.com/photos/annamagal/6282984898 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
8. Lead Software Development Engineer @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
9. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
10. @kerrizor Ancient City Ruby 2015 <?php $query = sprintf("SELECT firstname, email, birthday FROM users WHERE first_name='%s'", mysql_real_escape_string("william")); $result = mysql_query($query); if (!$result) { $message = 'Invalid Query! \n'; $message .= 'Query: ' . $query; $message .= 'Error: ' . mysql_error() . '\n'; die($message); } while ($row = mysql_fetch_assoc($result)) { echo $row['first_name']; echo $row['email']; echo $row['birthday']; } mysql_free_result($result); ?> @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
11. @kerrizor Ancient City Ruby 2015 User.all do |user| puts user.first_name puts user.email puts user.birthday end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
12. @kerrizor Ancient City Ruby 2015 Hammer and nails Anders Illum CC BY-NC-ND 2.0 https://www.flickr.com/photos/aai/6936657289 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
13. @kerrizor Ancient City Ruby 2015 falling down barn Becca CC BY-ND 2.0 https://www.flickr.com/photos/51845556@N00/136735885 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
14. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
15. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
16. @kerrizor Ancient City Ruby 2015 Kuroshio Sea - 2nd largest aquarium tank in the world Jon Rawlinson CC BY 2.0 https://www.flickr.com/photos/london/3745603114 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
17. @kerrizor Ancient City Ruby 2015 Views “A view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions.” Kuroshio Sea - 2nd largest aquarium tank in the world Jon Rawlinson CC BY 2.0 https://www.flickr.com/photos/london/3745603114 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
18. @kerrizor Ancient City Ruby 2015 id name 1 East Coast 2 Midwest 3 Alaska 4 Hawaii restaurant_i d region_id 1 1 1 2 1 3 2 1 2 4 3 3 3 4 id name annual_sales 1 Thai Tanic 120,000 2 Lard Have Mercy 130,000 3 Grillenium Falcon 135,000 4 La Fishcotheque 87,000 restaurants restaurants_regions regions @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
19. @kerrizor Ancient City Ruby 2015 Restaurant.all.each do |r| puts r.name puts r.annual_sales puts r.regions.first.name end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
20. @kerrizor Ancient City Ruby 2015 Restaurant.all.include(:regions).each do |r| puts r.name puts r.annual_sales puts r.regions.first.name end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
21. @kerrizor Ancient City Ruby 2015 Restaurant.all.include(:regions, :managers).each do |r| puts r.name puts r.annual_sales puts r.regions.first.name puts "Managers:" puts "---------" r.managers.each do |manager| puts " " + manager.name end end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
22. @kerrizor Ancient City Ruby 2015 SELECT "restaurants".* FROM "restaurants" SELECT "restaurants_regions".* FROM "restaurants_regions" WHERE "restaurants_regions"."restaurant_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50) SELECT "regions".* FROM "regions" WHERE "regions"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) SELECT "restaurants_managers".* FROM "restaurants_managers" WHERE "restaurants_managers"."restaurant_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50) SELECT "managers".* FROM "managers" WHERE "managers"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100) @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
23. @kerrizor Ancient City Ruby 2015 SELECT "restaurants"."id" AS t0_r0, "restaurants"."name" AS t0_r1, "restaurants"."annual_sales" AS t0_r2, "restaurants"."created_at" AS t0_r3, "restaurants"."updated_at" AS t0_r4, "regions"."id" AS t1_r0, "regions"."name" AS t1_r1, "regions"."created_at" AS t1_r2, "regions"."updated_at" AS t1_r3, "managers"."id" AS t2_r0, "managers"."name" AS t2_r1, "managers"."assistant" AS t2_r2, "managers"."created_at" AS t2_r3, "managers"."updated_at" AS t2_r4 FROM "restaurants" LEFT OUTER JOIN "restaurants_regions" ON "restaurants_regions"."restaurant_id" = "restaurants"."id" LEFT OUTER JOIN "regions" ON "regions"."id" = "restaurants_regions"."region_id" LEFT OUTER JOIN "restaurants_managers" ON "restaurants_managers"."restaurant_id" = "restaurants"."id" LEFT OUTER JOIN "managers" ON "managers"."id" = "restaurants_managers"."manager_id" @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
24. @kerrizor Ancient City Ruby 2015 + + @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
25. @kerrizor Ancient City Ruby 2015 class CreateRestaurantRegionManagerReports < ActiveRecord::Migration def up ActiveRecord::Base.connection.execute('CREATE VIEW restaurant_region_manager_reports AS SELECT r.name AS restaurant_name, re.name AS region_name, m.name AS manager_name, annual_sales FROM restaurants r JOIN restaurants_regions rd ON r.id = rd.restaurant_id JOIN regions re ON rd.region_id = re.id JOIN restaurants_managers rm ON r.id = rm.restaurant_id JOIN managers m ON rm.manager_id = m.id;') end def down ActiveRecord::Base.connection.execute('DROP VIEW restaurant_region_manager_reports') end end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
26. @kerrizor Ancient City Ruby 2015 RestaurantRegionManagerReport.each do |record| puts record.restaurant_name puts record.annual_sales puts record.region_name puts record.manager_name end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
27. @kerrizor Ancient City Ruby 2015 Broxigar's Aquarium Scott Akerman CC BY 2.0 https://www.flickr.com/photos/sterlic/3193335016 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
28. @kerrizor Ancient City Ruby 2015 Materialized Views Broxigar's Aquarium Scott Akerman CC BY 2.0 https://www.flickr.com/photos/sterlic/3193335016 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
29. @kerrizor Ancient City Ruby 2015 "Duck-Rabbit illusion". Licensed under Public Domain via Wikimedia Commons http://commons.wikimedia.org/wiki/File:Duck-Rabbit_illusion.jpg#/media/File:Duck-Rabbit_illusion.jpg @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
30. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
31. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
32. @kerrizor Ancient City Ruby 2015 Today's Specials Jon Cockley CC BY-NC 2.0 https://www.flickr.com/photos/jonnycocker/3422187692 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
33. @kerrizor Ancient City Ruby 2015 Stored Procedures Today's Specials Jon Cockley CC BY-NC 2.0 https://www.flickr.com/photos/jonnycocker/3422187692 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
34. @kerrizor Ancient City Ruby 2015 Stored Procedures <rossfu> SPs are like onions. They can really improve a dish, but I wouldn't want a dish that was 90% onions. Also, why are there onions in my ice cream? Today's Specials Jon Cockley CC BY-NC 2.0 https://www.flickr.com/photos/jonnycocker/3422187692 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
35. @kerrizor Ancient City Ruby 2015 “Kerri, it can execute javascript? Does that mean I can load my node.js application into Postgres and have it run there?!” Today's Specials Jon Cockley CC BY-NC 2.0 https://www.flickr.com/photos/jonnycocker/3422187692 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
36. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
37. @kerrizor Ancient City Ruby 2015 > CREATE OR REPLACE FUNCTION plv8_test(keys text[], vals text[]) RETURNS text AS $$ var o = {}; for(var i=0; i<keys.length; i++){ o[keys[i]] = vals[i]; } return JSON.stringify(o); $$ LANGUAGE plv8 IMMUTABLE STRICT; @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
38. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
39. @kerrizor Ancient City Ruby 2015 CREATE OR REPLACE FUNCTION log_purchase() RETURNS trigger AS $$ DECLARE BEGIN INSERT INTO logs (purchase_id, user_id, total_amount) VALUES (NEW.purchase_id, NEW.user_id, NEW.total_amount); RETURN NEW; END; $$ LANGUAGE plpgsql; @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
40. @kerrizor Ancient City Ruby 2015 CREATE TRIGGER log_purchases AFTER INSERT ON purchases FOR EACH ROW EXECUTE PROCEDURE log_purchase(); @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
41. @kerrizor Ancient City Ruby 2015 AR Default Data Types • :primary_key • :string • :text • :integer • :float • :decimal • :datetime • :time • :date • :binary • :boolean @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
42. @kerrizor Ancient City Ruby 2015 Solar Array Pattern Amaresh Sundaram Kuppuswamy CC BY-NC-SA 2.0 https://www.flickr.com/photos/zaldoe/3066243656 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
43. @kerrizor Ancient City Ruby 2015 Solar Array Pattern Amaresh Sundaram Kuppuswamy CC BY-NC-SA 2.0 https://www.flickr.com/photos/zaldoe/3066243656 Arrays @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
44. @kerrizor Ancient City Ruby 2015 title price inventory description :string :decimal :integer :text products @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
45. @kerrizor Ancient City Ruby 2015 title price inventory description colors Shirt 24.95 64 “Sweet swag!” Red, Blue, Green products @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
46. @kerrizor Ancient City Ruby 2015 # shirt.colors = "Red, Blue, Green" shirt_colors = shirt.colors.split(",").map{ |color| color.strip } @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
47. @kerrizor Ancient City Ruby 2015 $ rails c 2.1.5 :001 > product = Product.first Product Load (0.6ms) SELECT "products".* FROM "products" ORDER BY "products"."id" ASC LIMIT 1 => #<Product id: 1, title: "Shirt", price: #<BigDecimal: 7f87f4928190,'0.2495E2',18(18)>, inventory: 64, description: "Sweet swag!", colors: ["Red", "Blue", "Green"]> 2.1.5 :002 > product.colors.class => Array @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
48. @kerrizor Ancient City Ruby 2015 $ rails c 2.1.5 :010 > Product.where('colors @> ARRAY[?]', ["Red"]) Product Load (0.4ms) SELECT "products".* FROM "products" WHERE (colors @> ARRAY['Red']) => #<ActiveRecord::Relation [#<Product id: 1, title: "Shirt", price: #<BigDecimal:7f87eb5b3bf8,'0.2495E2',18(18)>, inventory: 64, description: "Sweet swag!", colors: ["Red", "Blue", "Green"]>]> @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
49. @kerrizor Ancient City Ruby 2015 2.1.5 :010 > Product.where('colors @> ARRAY[?]', ["Blue", "Red"]) Product Load (0.4ms) SELECT "products".* FROM "products" WHERE (colors @> ARRAY['Blue','Red']) => #<ActiveRecord::Relation [#<Product id: 1, title: "Shirt", price: #<BigDecimal:7f87eb498a70,'0.2495E2',18(18)>, inventory: 64, description: "Sweet swag!", colors: ["Red", "Blue", "Green"]>]> @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
50. @kerrizor Ancient City Ruby 2015 2.1.5 :010 > Product.where('colors @> ARRAY[?]', ["Blue", "Red"]) Product Load (0.4ms) SELECT "products".* FROM "products" WHERE (colors @> ARRAY['Blue','Red']) => #<ActiveRecord::Relation [#<Product id: 1, title: "Shirt", price: #<BigDecimal:7f87eb498a70,'0.2495E2',18(18)>, inventory: 64, description: "Sweet swag!", colors: ["Red", "Blue", "Green"]>]> @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
51. @kerrizor Ancient City Ruby 2015 class Product < ActiveRecord::Base scope :any_of, -> (colors){where('colors && ARRAY[?]', colors)} scope :all_of -> (colors){where('colors @> ARRAY[?]', colors)} end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
52. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
53. @kerrizor Ancient City Ruby 2015 class CreateProducts < ActiveRecord::Migration def change create_table :products do |t| t.string :title t.decimal :price t.integer :inventory t.text :description t.text :colors, array: true, default: [] end add_index(:products, :colors, :using => 'gin') end end Friday, March 27, 15
54. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
55. @kerrizor Ancient City Ruby 2015 class AddHstore < ActiveRecord::Migration def up execute 'CREATE EXTENSION hstore' end def down execute 'DROP EXTENSION hstore' end end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
56. @kerrizor Ancient City Ruby 2015 class AddHstoreColumnToProducts < ActiveRecord::Migration def change add_column :products, :color_inventory, :hstore end end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
57. @kerrizor Ancient City Ruby 2015 2.1.5 :010 > Product.where("color_inventory -> 'Blue' > '0'") @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
58. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
59. @kerrizor Ancient City Ruby 2015 class AddJsonColumnToProducts < ActiveRecord::Migration def change add_column :products, :inventory, :json end end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
60. @kerrizor Ancient City Ruby 2015 2.1.5 :010 > Product.where("inventory -> 'Blue' > '0'") 2.1.5 :010 > Product.where("inventory ->> 'Blue' > '0'") @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
61. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
62. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
63. @kerrizor Ancient City Ruby 2015 • Range • Money • Composites • ENUMs • IP Addresses (INET/CIDR) • UUID • Geospatial records and calculations @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
64. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
65. @kerrizor Ancient City Ruby 2015 “Now is the winter of our discontent Made glorious summer by this sun of York;” @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
66. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
67. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 “They made the store disappear. AMAZING!” Ernie Miller - Used Without Permission https://twitter.com/erniemiller/status/580806706306904065 Friday, March 27, 15
68. @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
69. math (and premise): http://abstrusegoose.com/474 1 + 1 = 2 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
70. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
71. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
72. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
73. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15
74. @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15