Tweet
Tweet

Slide 1

Slide 1 text

1 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 2

Slide 2 text

DATABASES 2 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 3

Slide 3 text

THE Database IS YOUR FRIEND 3 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 4

Slide 4 text

PostgreSQL 4 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 5

Slide 5 text

EMBRACE THE DATABASE WITH Ecto 5 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 6

Slide 6 text

WHO AM I? ▸ Josh Branchaud ▸ Software Developer at Hashrocket 6 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 7

Slide 7 text

7 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

OUR SAMPLE DATA SOURCE 9 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

TIL'S DATABASE SCHEMA ▸ Posts ▸ Developers ▸ Channels 11 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

ASKING QUESTIONS How do we ask questions of our data? 16 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

WHAT ABOUT Elixir AND Ecto? 20 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

QUERIES ARE JUST DATA 23 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

LAYING A SOLID FOUNDATION FOR OUR DATA 26 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 27

Slide 27 text

OUR DATA IS ONLY ANY GOOD IF IT IS correct 27 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 28

Slide 28 text

WE LIKE PUTTING DATA IN OUR database 28 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

VALIDATIONS, RIGHT? App → validations → DB Mobile → validations → DB Services → validations → DB 30 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 31

Slide 31 text

YOUR DATABASE IS THE ULTIMATE gatekeeper 31 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

SO, WHY SCHEMALESS? 41 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 42

Slide 42 text

QUERY TIME 42 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 43

Slide 43 text

HOW MANY DEVELOPERS ARE THERE? 43 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

HOW MANY POSTS BY CHANNEL? 46 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

HOW MANY POSTS ON AVERAGE PER DEVELOPER? 58 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

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

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

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

Slide 76

Slide 76 text

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

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

FRAGMENTS The Ecto.Query.API.fragment function 78 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

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

Slide 86

Slide 86 text

ONE STEP FURTHER Reusable fragments with custom functions 86 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 87

Slide 87 text

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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

ONE MORE QUESTION TO ASK 91 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 92

Slide 92 text

WHAT ARE THE HOTTEST POSTS? 92 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 93

Slide 93 text

WHAT ARE THE HOTTEST POSTS? 93 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

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

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

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

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

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

Slide 102

Slide 102 text

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

Slide 103

Slide 103 text

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

Slide 104

Slide 104 text

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

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

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

Slide 107

Slide 107 text

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

Slide 108

Slide 108 text

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

Slide 109

Slide 109 text

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

Slide 110

Slide 110 text

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

Slide 111

Slide 111 text

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

Slide 112

Slide 112 text

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

Slide 113

Slide 113 text

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

Slide 114

Slide 114 text

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

Slide 115

Slide 115 text

THAT'S IT 115 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 116

Slide 116 text

NOW THE database IS YOUR 116 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket

Slide 117

Slide 117 text

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

Slide 118

Slide 118 text

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

Slide 119

Slide 119 text

THANKS! ▸ Josh Branchaud ▸ Software Developer at Hashrocket ▸ Twitter: @jbrancha ▸ Github: @jbranchaud 119 — Embrace the Database with Ecto (4/7/2017) - Josh Branchaud - @jbrancha - Hashrocket