How leverage the features your database provides. CUSTOM DATA TYPES IN ECTO CHICAGO ELIXIR MEETUP - MAY/2017 

INTRODUCTION ➤ Pedro Assumpcao ➤ Senior Software Engineer at CityBase pedroassumpcao.ghost.io @pedroassumpcao |> github.com @pedroassumpcao |> twitter.com CHICAGO ELIXIR MEETUP - MAY/2017 

AGENDA ➤ What problem we are trying to solve ➤ Context/Scenario ➤ How Ecto handles custom data types ➤ Creating a custom data type ➤ Querying a custom data type CHICAGO ELIXIR MEETUP - MAY/2017 

WHAT PROBLEM WE ARE TRYING TO SOLVE ➤ Let's say we have two models: Product and Discount ➤ Product has price ➤ Discount has a percentage off based on a price range CHICAGO ELIXIR MEETUP - MAY/2017 

WHAT PROBLEM WE ARE TRYING TO SOLVE ➤ Let's say we have two models: Product and Discount ➤ Product has price ➤ Discount has a percentage off based on a price range ➤ Our first attempt is in the Discount model: ➤ minimum_price ➤ maximum_price CHICAGO ELIXIR MEETUP - MAY/2017 

No content

WHAT PROBLEM WE ARE TRYING TO SOLVE ➤ We should avoid queries like this: ➤ List percentage_off for a product price of $65: SELECT percentage_off FROM discounts WHERE mininum_price <= 65 and maximum_price >= 65; CHICAGO ELIXIR MEETUP - MAY/2017 

WHAT PROBLEM WE ARE TRYING TO SOLVE ➤ List all records based on a price range of $100 and $250: SELECT * FROM discounts WHERE (minimum_price < 100 AND maximum_price > 250) OR (minimum_price between 100 and 250) OR (maximum_price between 100 and 250); CHICAGO ELIXIR MEETUP - MAY/2017 

HOW CAN WE DO THIS IN A BETTER WAY? CHICAGO ELIXIR MEETUP - MAY/2017 

CONTEXT/SCENARIO ➤ Postgres 9.2 introduced Range Types: ➤ int4range — Range of integer ➤ int8range — Range of bigint ➤ numrange — Range of numeric ➤ tsrange — Range of timestamp without time zone ➤ tstzrange — Range of timestamp with time zone ➤ daterange — Range of date CHICAGO ELIXIR MEETUP - MAY/2017 

CONTEXT/SCENARIO ➤ Some range representation examples in Postgres: ➤ [2,14] => 2 ≤ x ≤ 14 ➤ [2, 14) => 2 ≤ x < 14 ➤ (2,14] => 2 < x ≤ 14 ➤ (2,14) => 2 < x < 14 ➤ [2,) => 2 ≤ x < ∞ ➤ (,2] => -∞ < x ≤ 2 CHICAGO ELIXIR MEETUP - MAY/2017 

CONTEXT/SCENARIO ➤ Some examples of querying range types: ➤ List percentage_off for a product price of $65: SELECT percentage_off FROM discounts WHERE 65::numeric <@ price_range; ➤ List all records based on a price range of $100 and $250: SELECT * FROM discounts WHERE discounts.price_range && int4range(100, 250, '[]'); CHICAGO ELIXIR MEETUP - MAY/2017 

IN ELIXIR WORLD... CHICAGO ELIXIR MEETUP - MAY/2017 

CONTEXT/SCENARIO ➤ Ecto is the database wrapper and DSL for queries ➤ Ecto works with database adapters: ➤ postgrex (Postgres) ➤ mariex (MySql) ➤ msql_ecto (MSSql) ➤ and others ➤ Each adapter knows better the database features and specifics CHICAGO ELIXIR MEETUP - MAY/2017 

CONTEXT/SCENARIO ➤ Ecto only provides the most common data types: ➤ id, binary_id, uuid ➤ integer, float, decimal ➤ string, binary ➤ boolean ➤ date, time, utc_datetime ➤ list, map CHICAGO ELIXIR MEETUP - MAY/2017 

No content

ECTO.TYPE BEHAVIOUR CHICAGO ELIXIR MEETUP - MAY/2017 

WHAT IS A BEHAVIOUR? ➤ Separates the generic part (the behaviour module) of a component from the specific part (the callback module) ➤ Perfect when modules share the same public API ➤ A behaviour module defines a set of functions ➤ The callback module implements them CHICAGO ELIXIR MEETUP - MAY/2017 

CREATING A CUSTOM TYPE ➤ Ecto provides a behaviour called Ecto.Type: CHICAGO ELIXIR MEETUP - MAY/2017 

CREATING A CUSTOM TYPE type/0 ➤ outputs the name of the database type CHICAGO ELIXIR MEETUP - MAY/2017 

CREATING A CUSTOM TYPE cast/1 ➤ receives any term and validates CHICAGO ELIXIR MEETUP - MAY/2017 

CREATING A CUSTOM TYPE load/1 ➤ loads the data from the adapter and outputs the custom type CHICAGO ELIXIR MEETUP - MAY/2017 

CREATING A CUSTOM TYPE dump/1 ➤ receives a validated data and outputs an adapter type CHICAGO ELIXIR MEETUP - MAY/2017 

USING A CUSTOM TYPE ➤ Migrations: CHICAGO ELIXIR MEETUP - MAY/2017 

USING A CUSTOM TYPE ➤ Schemas: CHICAGO ELIXIR MEETUP - MAY/2017 

QUERYING A CUSTOM TYPE ➤ Using fragment/1 you can perform any range specific operation that Postgres offers: CHICAGO ELIXIR MEETUP - MAY/2017 

SUMMARY ➤ Postgres has data types that help us model properly ➤ Database adapters are close to the database features ➤ Ecto gives us flexibility to create our own types if we need ➤ Querying customized data types is easy CHICAGO ELIXIR MEETUP - MAY/2017 

FURTHER READING ➤ https://www.postgresql.org/docs/9.6/static/rangetypes.html ➤ http://pedroassumpcao.ghost.io/using-postgres-range-data-type-in-ecto/ ➤ https://hexdocs.pm/ecto/Ecto.Type.html ➤ https://github.com/elixir-ecto/postgrex#data-representation CHICAGO ELIXIR MEETUP - MAY/2017 

QUESTIONS? CHICAGO ELIXIR MEETUP - MAY/2017 

THANKS! CHICAGO ELIXIR MEETUP - MAY/2017