Embrace The Database With Ecto - REFL17

The database is our friend. Modern relational DBs, such as PostgreSQL, are not just data stores but powerful computation engines. Ecto’s schemaless query support and SQL-like syntax encourage us to understand our queries and utilize the DB’s full potential. With Ecto, we can embrace the database.

Transcript

1. 1 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
2. DATABASES 2 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
3. THE Database IS YOUR FRIEND 3 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
4. PostgreSQL 4 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
5. EMBRACE THE DATABASE WITH Ecto 5 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
6. WHO AM I? ▸ Josh Branchaud ▸ Software Developer at Hashrocket 6 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
7. 7 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
8. AGENDA ▸ Data Integrity ▸ Schemaless Queries ▸ Ecto's Escape Hatch ▸ Enhancing Ecto with Custom Functions ▸ Bonus: Tricky Query 8 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
9. OUR SAMPLE DATA SOURCE 9 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
10. WHAT IS TIL? TIL is an open-source project by the team at Hashrocket that catalogues the sharing & accumulation of knowledge as it happens day-to-day. 10 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
11. TIL'S DATABASE SCHEMA ▸ Posts ▸ Developers ▸ Channels 11 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
12. POSTS TABLE id | integer title | character varying body | text likes | integer developer_id | integer channel_id | integer Foreign-key constraints: "fk_rails_447dc2e0a3" FOREIGN KEY (channel_id) REFERENCES channels(id) "fk_rails_b3ec63b3ac" FOREIGN KEY (developer_id) REFERENCES developers(id) 12 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
13. DEVELOPERS TABLE id | integer email | character varying username | character varying Referenced by: TABLE "posts" CONSTRAINT "fk_rails_b3ec63b3ac" FOREIGN KEY (developer_id) REFERENCES developers(id) 13 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
14. CHANNELS TABLE id | integer name | text Referenced by: TABLE "posts" CONSTRAINT "fk_rails_447dc2e0a3" FOREIGN KEY (channel_id) REFERENCES channels(id) 14 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
15. DATABASE FULL OF DATA So many answers just waiting to be asked the right question 15 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
16. ASKING QUESTIONS How do we ask questions of our data? 16 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
17. WE NEED A MEDIATOR What is the best mediator between us and our data? 17 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
18. SQL SQL is the best way to talk to our SQL database 18 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
19. HOW MANY POSTS ARE THERE? sql> select count(id) from posts; count ------- 1066 (1 row) 19 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
20. WHAT ABOUT Elixir AND Ecto? 20 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
21. ECTO Ecto is a domain specific language for writing queries and interacting with databases in Elixir. 21 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
22. HOW MANY POSTS ARE THERE? iex> from(p in "posts", select: count(p.id)) |> Repo.one() 1066 17:16:36.573 [debug] QUERY OK source="posts" db=10.8ms queue=0.2ms SELECT count(p0."id") FROM "posts" AS p0 [] 22 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
23. QUERIES ARE JUST DATA 23 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
24. QUERIES AS DATA ▸ #Ecto.Query Struct ▸ You build them up as you go ▸ You can inspect them 24 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
25. DATA INTEGRITY SCHEMALESS QUERIES ECTO'S ESCAPE HATCH ENHANCING ECTO WITH CUSTOM FUNCTIONS 25 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
26. LAYING A SOLID FOUNDATION FOR OUR DATA 26 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
27. OUR DATA IS ONLY ANY GOOD IF IT IS correct 27 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
28. WE LIKE PUTTING DATA IN OUR database 28 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
29. WHO MAKES SURE WE DON'T PUT BAD DATA IN OUR DATABASE? 29 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
30. VALIDATIONS, RIGHT? App → validations → DB Mobile → validations → DB Services → validations → DB 30 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
31. YOUR DATABASE IS THE ULTIMATE gatekeeper 31 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
32. DATABASE AS THE Gatekeeper "A database actively seeks to maintain the correctness of all its data." — Joe Celko 32 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
33. DATA INTEGRITY - DATA TYPES Data types as constraints create table(:developers) do add :email, :varchar add :admin, :boolean add :created_at, :timestamp end 33 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
34. DATA INTEGRITY - DATA TYPES Using better, custom data types (e.g. uuid, bigint, and citext) execute("create extension if not exists citext;") create table(:developers, primary_key: false) do add :id, :uuid, primary_key: true add :email, :citext add :admin, :boolean add :created_at, :timestamp end 34 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
35. DATA INTEGRITY - NOT NULL Enforce Presence with Not Null Constraints create table(:developers, primary_key: false) do add :id, :uuid, primary_key: true add :email, :citext, null: false add :admin, :boolean, null: false add :created_at, :timestamp end 35 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
36. DATA INTEGRITY - NOT NULL The most important column constraint is the NOT NULL. Use this constraint automatically and then remove it only when you have good reason. This will help you avoid the complications of NULL values when you make queries against the data. — Joe Celko 36 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
37. DATA INTEGRITY - FOREIGN KEYS Enforce Relationships with Foreign Key Constraints create table(:posts) do add :title, :varchar, null: false add :body, :text, null: false add :developer_id, references(:developers, type: :uuid) end 37 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
38. DATA INTEGRITY - CHECK CONSTRAINTS Enforce More General Relationships with Check Constraints create table(:posts) do add :title, :varchar, null: false add :body, :text, null: false add :likes, :smallint, null: false, default: 0 add :developer_id, references(:developers, type: :uuid) end create constraint(:posts, "ensure_positive_likes", check: "likes >= 0") 38 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
39. DATA INTEGRITY SCHEMALESS QUERIES ECTO'S ESCAPE HATCH ENHANCING ECTO WITH CUSTOM FUNCTIONS 39 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
40. SCHEMALESS QUERIES iex> Repo.one(from p in "posts", select: count(p.id)) 1066 iex> Repo.one(from p in MyApp.Posts, select: count(p.id)) 1066 40 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
41. SO, WHY SCHEMALESS? 41 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
42. QUERY TIME 42 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
43. HOW MANY DEVELOPERS ARE THERE? 43 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
44. HOW MANY DEVELOPERS ARE THERE? iex> from(d in "developers", select: count(d.id)) |> Repo.one() 17:19:01.195 [debug] QUERY OK source="developers" db=1.0ms queue=2.9ms SELECT count(d0.id) FROM "developers" AS d0 [] 32 44 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
45. FROM CLAUSE The FROM specifies one or more source tables for the SELECT. SELECT CLAUSE SELECT retrieves rows from zero or more tables 45 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
46. HOW MANY POSTS BY CHANNEL? 46 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
47. HOW MANY POSTS BY CHANNEL? JOIN CLAUSE A JOIN clause combines two FROM items 47 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
48. HOW MANY POSTS BY CHANNEL? iex> posts_and_channels = from(p in "posts", join: c in "channels", on: p.channel_id == c.id) 48 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
49. HOW MANY POSTS BY CHANNEL? GROUP BY CLAUSE With group by, output is combined in groups of rows that match the grouping value 49 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
50. HOW MANY POSTS BY CHANNEL? iex> from([p,c] in posts_and_channels, group_by: c.name, select: c.name) 50 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
51. HOW MANY POSTS BY CHANNEL? iex> from([p,c] in posts_and_channels, group_by: c.name, select: c.name) |> Repo.all() ["clojure", "react", "rails", "vim", "workflow", "command-line", "sql", "elixir", "erlang", "design", "testing", "go", "mobile", "javascript", "devops", "ruby", "html-css", "git", "emberjs"] 51 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
52. HOW MANY POSTS BY CHANNEL? AGGREGATES Aggregate functions are computed across all rows making up each group, producing a separate value for each group. 52 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
53. HOW MANY POSTS BY CHANNEL? iex> from([p,c] in posts_and_channels, group_by: c.name, select: { count(p.id), c.name }) 53 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
54. HOW MANY POSTS BY CHANNEL? iex> from([p,c] in posts_and_channels, group_by: c.name, select: { count(p.id), c.name }) |> Repo.all() [{13, "clojure"}, {5, "react"}, {102, "rails"}, {201, "vim"}, {59, "workflow"}, {110, "command-line"}, {121, "sql"}, {73, "elixir"}, {1, "erlang"}, {6, "design"}, {28, "testing"}, {5, "go"}, {15, "mobile"}, {67, "javascript"}, {32, "devops"}, {125, "ruby"}, {17, "html-css"}, {63, "git"}, {23, "emberjs"}] 54 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
55. HOW MANY POSTS BY CHANNEL? ORDER BY CLAUSE If the ORDER BY clause is specified, the returned rows are sorted in the specified order. 55 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
56. HOW MANY POSTS BY CHANNEL? iex> from([p,c] in posts_and_channels, group_by: c.name, order_by: [desc: count(p.id)], select: { count(p.id), c.name }) 56 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
57. HOW MANY POSTS BY CHANNEL? iex> from([p,c] in posts_and_channels, group_by: c.name, order_by: [desc: count(p.id)], select: { count(p.id), c.name }) |> Repo.all() [{201, "vim"}, {125, "ruby"}, {121, "sql"}, {110, "command-line"}, {102, "rails"}, {73, "elixir"}, {67, "javascript"}, {63, "git"}, {59, "workflow"}, {32, "devops"}, {28, "testing"}, {23, "emberjs"}, {17, "html-css"}, {15, "mobile"}, {13, "clojure"}, {6, "design"}, {5, "go"}, {5, "react"}, {1, "erlang"}] 57 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
58. HOW MANY POSTS ON AVERAGE PER DEVELOPER? 58 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
59. HOW MANY POSTS ON AVERAGE PER DEVELOPER? iex> post_counts = from(p in "posts", group_by: p.developer_id, select: %{ post_count: count(p.id), developer_id: p.developer_id }) 59 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
60. HOW MANY POSTS ON AVERAGE PER DEVELOPER? iex> Repo.all(post_counts) [%{developer_id: 14, post_count: 6}, %{developer_id: 25, post_count: 43}, %{developer_id: 32, post_count: 1}, %{developer_id: 27, post_count: 2}, %{developer_id: 8, post_count: 332}, %{developer_id: 17, post_count: 1}, %{developer_id: 15, post_count: 23}, %{developer_id: 1, post_count: 1}, %{developer_id: 10, post_count: 18}, %{developer_id: 26, post_count: 78}, %{developer_id: 11, post_count: 15}, %{developer_id: 4, post_count: 130}, %{developer_id: 18, post_count: 14}, %{developer_id: 30, post_count: 10}, %{developer_id: 16, post_count: 3}, %{developer_id: 33, post_count: 1}, %{developer_id: 6, post_count: 3}, %{developer_id: 19, post_count: 9}, %{developer_id: 29, post_count: 82}, %{developer_id: 2, post_count: 236}, %{developer_id: 23, post_count: 10}, %{developer_id: 31, post_count: 5}, %{developer_id: 20, post_count: 8}, %{developer_id: 5, post_count: 3}, %{developer_id: 13, post_count: 3}, %{developer_id: 22, post_count: 12}, %{developer_id: 9, post_count: 10}, %{developer_id: 24, post_count: 4}, %{developer_id: 7, post_count: 3}] 60 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
61. HOW MANY POSTS ON AVERAGE PER DEVELOPER? iex> Repo.aggregate(subquery(post_counts), :avg, :post_count) #Decimal<36.7586206896551724> 61 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
62. SCHEMALESS QUERIES Let's try something a bit more complex What is the channel and title of each developer's most liked post in 2016? 62 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
63. COMPLEX QUERIES Writing complex queries is all about building the solution from the ground up PIECE BY PIECE 63 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
64. WHAT IS THE CHANNEL AND TITLE OF EACH DEVELOPER'S MOST LIKED POST IN 2016? 64 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
65. WHAT IS THE CHANNEL AND TITLE OF EACH DEVELOPER'S MOST LIKED POST IN 2016? iex> posts_devs_channels = from(p in "posts", join: d in "developers", on: d.id == p.developer_id, join: c in "channels", on: c.id == p.channel_id) 65 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
66. WHAT IS THE CHANNEL AND TITLE OF EACH DEVELOPER'S MOST LIKED POST IN 2016? iex> top_of_2016 = from([posts, devs, channels] in posts_devs_channels, order_by: [desc: posts.likes], select: %{ dev: devs.username, channel: channels.name, title: posts.title }) 66 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
67. WHAT IS THE CHANNEL AND TITLE OF EACH DEVELOPER'S MOST LIKED POST IN 2016? iex> top_of_2016 |> Repo.all() [%{channel: "javascript", dev: "Frank Rossitano", title: "Because JavaScript"}, %{channel: "vim", dev: "Kenneth Parcell", title: "Highlight #markdown fenced code syntax in #Vim"}, %{channel: "command-line", dev: "Kenneth Parcell", title: "Homebrew is eating up your harddrive"}, ...] 67 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
68. WHAT IS THE CHANNEL AND TITLE OF EACH DEVELOPER'S MOST LIKED POST IN 2016? DISTINCT CLAUSE If SELECT DISTINCT is specified, all duplicate rows are removed from the result set (one row is kept from each group of duplicates). 68 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
69. WHAT IS THE CHANNEL AND TITLE OF EACH DEVELOPER'S MOST LIKED POST IN 2016? iex> top_of_2016 = from([posts, devs, channels] in posts_devs_channels, distinct: devs.id, order_by: [desc: posts.likes], select: %{ dev: devs.username, channel: channels.name, title: posts.title }) 69 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
70. WHAT IS THE CHANNEL AND TITLE OF EACH DEVELOPER'S MOST LIKED POST IN 2016? posts.created_at > ^Ecto.DateTime.cast!({{2016,1,1},{0,0,0}}), and posts.created_at < ^Ecto.DateTime.cast!({{2017,1,1},{0,0,0}}), 70 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
71. WHAT IS THE CHANNEL AND TITLE OF EACH DEVELOPER'S MOST LIKED POST IN 2016? WHERE CLAUSE If the WHERE clause is specified, all rows that do not satisfy the condition are eliminated from the output. 71 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
72. WHAT IS THE CHANNEL AND TITLE OF EACH DEVELOPER'S MOST LIKED POST IN 2016? iex> top_of_2016 = from([posts, devs, channels] in posts_devs_channels(), distinct: devs.id, order_by: [desc: posts.likes], where: posts.created_at > ^Ecto.DateTime.cast!({{2016,1,1},{0,0,0}}), where: posts.created_at < ^Ecto.DateTime.cast!({{2017,1,1},{0,0,0}}), select: %{ dev: devs.username, channel: channels.name, title: posts.title }) 72 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
73. WHAT IS THE CHANNEL AND TITLE OF EACH DEVELOPER'S MOST LIKED POST IN 2016? iex> top_of_2016 |> Repo.all() [%{channel: "elixir", dev: "Liz Lemon", title: "Invoke Elixir Functions with Apply"}, %{channel: "workflow", dev: "Jack Donaghy", title: "Ternary shortcut in PHP"}, %{channel: "vim", dev: "Tracy Jordan", title: "Use colorcolumn to visualize maximum line length"}, %{channel: "ruby", dev: "Pete Hornberger", title: "Ruby optional arguments can come before required"}, %{channel: "ruby", dev: "Jenna Maroney", title: "Using pessimistic gem version to catch betas"}, ...] 73 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
74. SCHEMALESS QUERY FUNCTIONS IN ECTO 2.0 ▸ Ecto.Repo.update_all/3 ▸ Ecto.Repo.insert_all/3 ▸ Ecto.Repo.delete_all/3 74 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
75. DATA INTEGRITY SCHEMALESS QUERIES ECTO'S ESCAPE HATCH ENHANCING ECTO WITH CUSTOM FUNCTIONS 75 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
76. ESCAPE HATCH Ecto can't do it all, sometimes we need an Escape Hatch 76 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
77. ONE-OFF QUERIES Using Ecto.Repo.query iex> Repo.query("select * from generate_series(1,5);") 12:00:14.801 [debug] QUERY OK db=1.5ms select * from generate_series(1,5); [] {:ok, %Postgrex.Result{columns: ["generate_series"], command: :select, connection_id: 59379, num_rows: 5, rows: [[1], [2], [3], [4], [5]]}} 77 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
78. FRAGMENTS The Ecto.Query.API.fragment function 78 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
79. FRAGMENTS IN QUERIES iex> from(d in "developers", select: fragment("count(*)")) |> Repo.one() 17:19:01.195 [debug] QUERY OK source="developers" db=1.0ms queue=2.9ms SELECT count(*) FROM "developers" AS d0 [] 32 79 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
80. FRAGMENTS IN QUERIES iex> from(d in "developers", select: fragment("count(?)", d.id) |> Repo.one() 17:19:01.195 [debug] QUERY OK source="developers" db=1.0ms queue=2.9ms SELECT count(d0.id) FROM "developers" AS d0 [] 32 80 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
81. FRAGMENTS IN QUERIES iex> top_of_2016 = from([posts, devs, channels] in posts_devs_channels(), distinct: devs.id, order_by: [desc: posts.likes], where: posts.created_at > ^Ecto.DateTime.cast!({{2016,1,1},{0,0,0}}), where: posts.created_at < ^Ecto.DateTime.cast!({{2017,1,1},{0,0,0}}), select: %{ dev: devs.username, channel: channels.name, title: posts.title }) 81 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
82. FRAGMENTS IN QUERIES BETWEEN PREDICATE The BETWEEN predicate simplifies range tests a between x and y 82 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
83. FRAGMENTS IN QUERIES fragment("? between ? and ?", posts.created_at, ^Ecto.DateTime.cast!({{2016,1,1},{0,0,0}}), ^Ecto.DateTime.cast!({{2017,1,1},{0,0,0}}) ) 83 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
84. FRAGMENTS IN QUERIES iex> top_of_2016 = from([posts, devs, channels] in posts_devs_channels(), distinct: devs.id, order_by: [desc: posts.likes], where: fragment("? between ? and ?", posts.created_at, ^Ecto.DateTime.cast!({{2016,1,1},{0,0,0}}), ^Ecto.DateTime.cast!({{2017,1,1},{0,0,0}}) ), select: %{ dev: devs.username, channel: channels.name, title: posts.title }) 84 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
85. FRAGMENTS IN MIGRATIONS create table(:developers, primary_key: false) do add :id, :uuid, primary_key: true, default: fragment("gen_random_uuid()") add :email, :citext, null: false add :created_at, :timestamptz, null: false, default: fragment("now()") add :updated_at, :timestamptz, null: false, default: fragment("now()") end 85 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
86. ONE STEP FURTHER Reusable fragments with custom functions 86 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
87. DATA INTEGRITY SCHEMALESS QUERIES ECTO'S ESCAPE HATCH ENHANCING ECTO WITH CUSTOM FUNCTIONS 87 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
88. CUSTOM FUNCTIONS fragment("? between ? and ?", posts.created_at, ^Ecto.DateTime.cast!({{2016,1,1},{0,0,0}}), ^Ecto.DateTime.cast!({{2017,1,1},{0,0,0}}) ) 88 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
89. CUSTOM FUNCTIONS defmodule CustomFunctions do defmacro between(value, left_bound, right_bound) do quote do fragment("? between ? and ?", unquote(value), unquote(left_bound), unquote(right_bound)) end end end 89 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
90. CUSTOM FUNCTIONS iex> import CustomFunctions iex> from([posts, devs, channels] in posts_devs_channels(), distinct: devs.id, order_by: [desc: posts.likes], where: between(posts.created_at, ^Ecto.DateTime.cast!({{2016,1,1},{0,0,0}}), ^Ecto.DateTime.cast!({{2017,1,1},{0,0,0}}) ), select: %{ dev: devs.username, channel: channels.name, title: posts.title }) 90 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
91. ONE MORE QUESTION TO ASK 91 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
92. WHAT ARE THE HOTTEST POSTS? 92 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
93. WHAT ARE THE HOTTEST POSTS? 93 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
94. MEASURING HOTNESS with a HackerNews-esque Ranking Algorithm1 hotness_score = (likes / (age_in_hours ^ gravity)) 1 https://medium.com/hacking-and-gonzo/how-hacker-news-ranking-algorithm-works-1d9b0cf2c08d#.3sdij412h 94 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
95. MEASURING HOTNESS with a HackerNews-esque Ranking Algorithm1 hotness_score = (likes / (age_in_hours ^ 0.8)) 1 https://medium.com/hacking-and-gonzo/how-hacker-news-ranking-algorithm-works-1d9b0cf2c08d#.3sdij412h 95 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
96. WHAT ARE THE HOTTEST POSTS? age_in_hours = age_in_seconds / 3600 96 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
97. WHAT ARE THE HOTTEST POSTS? age_in_hours = age_in_seconds / 3600 age_in_seconds = (current_timestamp - published_at) 97 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
98. WHAT ARE THE HOTTEST POSTS? age_in_hours = age_in_seconds / 3600 age_in_seconds = extract(epoch from (current_timestamp - published_at)) 98 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
99. WHAT ARE THE HOTTEST POSTS? age_in_hours = extract(epoch from (current_timestamp - published_at) ) / 3600 99 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
100. WHAT ARE THE HOTTEST POSTS? age_in_hours = extract(epoch from (current_timestamp - published_at) ) / 3600 age_in_hours = greatest(age_in_hours, 0.1) 100 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
101. WHAT ARE THE HOTTEST POSTS? defmacro hours_since(timestamp) do quote do fragment( "extract(epoch from (current_timestamp - ?)) / 3600", unquote(timestamp) ) end end 101 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
102. WHAT ARE THE HOTTEST POSTS? iex> posts_with_age_in_hours = from(p in "posts", select: %{ id: p.id, hours_age: hours_since(p.published_at) }) 102 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
103. WHAT ARE THE HOTTEST POSTS? iex> posts_with_age_in_hours |> Repo.all() [%{hours_age: 16176.589612136388, id: 12}, %{hours_age: 8308.070006305556, id: 657}, %{hours_age: 7713.880550556667, id: 708}, %{hours_age: 6054.369684539444, id: 833}, %{hours_age: 6768.798842247777, id: 772}, %{hours_age: 8315.479890300556, id: 654}, %{hours_age: 5698.932204395278, id: 870}, ...] 103 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
104. WHAT ARE THE HOTTEST POSTS? iex> posts_with_age_in_hours = from(p in "posts", where: not is_nil(p.published_at), select: %{ id: p.id, hours_age: hours_since(p.published_at) }) 104 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
105. WHAT ARE THE HOTTEST POSTS? defmacro greatest(value1, value2) do quote do fragment("greatest(?, ?)", unquote(value1), unquote(value2)) end end 105 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
106. WHAT ARE THE HOTTEST POSTS? ecto_pg_extras # mix.exs defp deps do [..., {:ecto_pg_extras, "~> 0.1.1"}] end # hot_posts.ex import EctoPgExtras 106 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
107. WHAT ARE THE HOTTEST POSTS? iex> posts_with_age_in_hours = from(p in "posts", where: not is_nil(p.published_at), select: %{ id: p.id, hours_age: greatest(hours_since(p.published_at), 0.1) }) 107 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
108. WHAT ARE THE HOTTEST POSTS? iex> posts_with_age_in_hours = from(p in "posts", where: not is_nil(p.published_at), select: %{ id: p.id, likes: p.likes, hours_age: greatest(hours_since(p.published_at), 0.1) }) 108 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
109. WHAT ARE THE HOTTEST POSTS? iex> hot_posts = from(p in subquery(posts_with_age_in_hours), select: %{ id: p.id, hotness_score: fragment("? / (? ^ ?)", p.likes, p.hours_age, 0.8) }) 109 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
110. WHAT ARE THE HOTTEST POSTS? iex> hot_posts = from(p in subquery(posts_with_age_in_hours), order_by: [desc: 2], select: %{ id: p.id, hotness_score: fragment("? / (? ^ ?)", p.likes, p.hours_age, 0.8) }) 110 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
111. WHAT ARE THE HOTTEST POSTS? iex> hot_posts = from(p in subquery(posts_with_age_in_hours), order_by: [desc: 2], select: %{ id: p.id, hotness_score: fragment("? / (? ^ ?)", p.likes, p.hours_age, 0.8) }, limit: 5) 111 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
112. WHAT ARE THE HOTTEST POSTS? iex> hot_posts |> Repo.all() [%{hotness_score: 0.07338486607688295, id: 1134}, %{hotness_score: 0.0641696195616784, id: 1128}, %{hotness_score: 0.06255221703215852, id: 1131}, %{hotness_score: 0.05892805984356843, id: 1127}, %{hotness_score: 0.056850664015716326, id: 1125}] 112 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
113. WHAT ARE THE HOTTEST POSTS? iex> hot_posts_with_titles = from(p in subquery(posts_with_age_in_hours), join: posts in "posts", on: posts.id == p.id, order_by: [desc: 2], select: %{ title: posts.title, hotness_score: fragment("? / (? ^ ?)", p.likes, p.hours_age, 0.8) }, limit: 5) 113 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
114. WHAT ARE THE HOTTEST POSTS? iex> hot_posts_with_titles |> Repo.all() [%{hotness_score: 0.07335796393712307, title: "Custom loaders for webpack"}, %{hotness_score: 0.06415573399947418, title: "Rerun Only Failures With RSpec"}, %{hotness_score: 0.06253343464917116, title: "Rails on ruby 2.4: Silence Fixnum/Bignum warnings"}, %{hotness_score: 0.05891816565837998, title: "Polymorphic Path Helpers"}, %{hotness_score: 0.056841537315585514, title: "Clean untracked files in Git"}] 114 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
115. THAT'S IT 115 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
116. NOW THE database IS YOUR 116 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
117. BEYOND ECTO moebius - https://github.com/robconery/moebius defql - https://github.com/fazibear/defql 117 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
118. SOURCES AND LINKS ▸ github.com/hashrocket/ecto_pg_extras ▸ til.hashrocket.com ▸ Joe Celko's SQL for Smarties: Advanced SQL Programming, 5th Ed ▸ PostgreSQL 9.6 Documentation 118 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket
119. THANKS! ▸ Josh Branchaud ▸ Software Developer at Hashrocket ▸ Twitter: @jbrancha ▸ Github: @jbranchaud 119 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket