Working With Legacy Databases in Ecto - ElixirConf 2017

Often when starting an Elixir or Phoenix project that is data-backed, there is an existing database that you need to interact with. There are plenty of tutorials about getting started with Ecto in a green project, but how do you take a legacy database and get it working in your new application? We'll explore normal situations all the way to crazy ones like weird foreign keys and even dealing with multiple databases simultaneously!

Youtube link: https://youtu.be/3W2dMOQL2CM

Transcript

1. Geoffrey Lessel Working with legacy databases in Ecto
3. ❤
4. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel LEGACY?
5. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel LEGACY? Already existing
6. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel LEGACY? Has tables and data
7. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel LEGACY? Old database software
8. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel GOALS
9. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel GOALS Identify problems
10. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel GOALS Discover solutions
11. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel GOALS You should be able to seamlessly use an existing database
12. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE PROBLEM
13. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE PROBLEM Existing tables
14. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE PROBLEM Funky column name format
15. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE PROBLEM Non-integer primary keys
16. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel Non-integer primary keys d6286e95-47e9-48d3-ace9-a344d622d0ee THE PROBLEM
17. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE PROBLEM Custom field types
18. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE PROBLEM Schemas
19. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION
20. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION Code examples and slides can be found at geoffreylessel.com/elixirconf2017
21. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION Ergast Motor Racing Database http://ergast.com/mrd/
22. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION Use Ecto 2.2
23. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defp deps do [ {:phoenix, "!~> 1.3.0"}, {:phoenix_pubsub, "!~> 1.0"}, {:phoenix_ecto, "!~> 3.2"}, {:phoenix_html, "!~> 2.10"}, {:phoenix_live_reload, "!~> 1.0", only: :dev}, {:react_phoenix, "!~> 0.4.0"}, {:ecto, "!~> 2.2.0-rc.1", override: true}, {:gettext, "!~> 0.11"}, {:cowboy, "!~> 1.0"}, {:mariaex, !"> 0.0.0"} ] end The Solution Use Ecto 2.2 for Existing tables and data mix.exs
24. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defp deps do [ {:phoenix, "!~> 1.3.0"}, {:phoenix_pubsub, "!~> 1.0"}, {:phoenix_ecto, "!~> 3.2"}, {:phoenix_html, "!~> 2.10"}, {:phoenix_live_reload, "!~> 1.0", only: :dev}, {:react_phoenix, "!~> 0.4.0"}, {:ecto, "!~> 2.2.0-rc.1", override: true}, {:gettext, "!~> 0.11"}, {:cowboy, "!~> 1.0"}, {:mariaex, !"> 0.0.0"} ] end The Solution Use Ecto 2.2 for Existing tables and data mix.exs
25. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION for Existing tables and data
26. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION for Existing tables and data mix ecto.dump mix ecto.load
27. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION for Existing tables and data https://elixirforum.com/t/schema-file-for-phoenix-migrations/1590/2
28. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defp aliases do [ "ecto.setup": ["ecto.create", "ecto.migrate", "run priv/repo/seeds.exs"], "ecto.reset": ["ecto.drop", "ecto.setup"], "ecto.migrate": ["ecto.migrate", "ecto.dump"], "test": ["ecto.create !--quiet", "ecto.migrate", "test"] ] end The Solution mix ecto.dump mix ecto.load for Existing tables and data for Existing tables and data mix.exs
29. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defp aliases do [ "ecto.setup": ["ecto.create", "ecto.migrate", "run priv/repo/seeds.exs"], "ecto.reset": ["ecto.drop", "ecto.setup"], "ecto.migrate": ["ecto.migrate", "ecto.dump"], "test": ["ecto.create !--quiet", "ecto.migrate", "test"] ] end The Solution mix ecto.dump mix ecto.load for Existing tables and data for Existing tables and data mix.exs
30. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION for Funky column name formats
31. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION for Funky column name formats source: :columnName
32. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel +----------!!-+-------------!!-+-----!!-+-!!---!!-+-----------!!-+----------------+ | Field | Type | Null | Key | Default | Extra | +----------!!-+-------------!!-+-----!!-+-!!---!!-+-----------!!-+----------------+ | raceId | int(11) | NO | PRI | NULL | auto_increment | | year | int(11) | NO | | 0 | | | round | int(11) | NO | | 0 | | | circuitId | int(11) | NO | | 0 | | | name | varchar(255) | NO | | | | | date | date | NO | | 0000-00-00 | | | time | time | YES | | NULL | | | url | varchar(255) | YES | UNI | NULL | | +----------!!-+-------------!!-+-----!!-+-!!---!!-+-----------!!-+----------------+ 8 rows in set (0.01 sec) The Solution source: :columnName for Funky column name formats
33. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel +----------!!-+-------------!!-+-----!!-+-!!---!!-+-----------!!-+----------------+ | Field | Type | Null | Key | Default | Extra | +----------!!-+-------------!!-+-----!!-+-!!---!!-+-----------!!-+----------------+ | raceId | int(11) | NO | PRI | NULL | auto_increment | | year | int(11) | NO | | 0 | | | round | int(11) | NO | | 0 | | | circuitId | int(11) | NO | | 0 | | | name | varchar(255) | NO | | | | | date | date | NO | | 0000-00-00 | | | time | time | YES | | NULL | | | url | varchar(255) | YES | UNI | NULL | | +----------!!-+-------------!!-+-----!!-+-!!---!!-+-----------!!-+----------------+ 8 rows in set (0.01 sec) The Solution source: :columnName for Funky column name formats
34. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string end end The Solution source: :columnName for Funky column name formats +—————!!-+--------------+ | Field | Type | +----------!!-+--------------+ | raceId | int(11) | | year | int(11) | | round | int(11) | | circuitId | int(11) | | name | varchar(255) | | date | date | | time | time | | url | varchar(255) | +----------!!-+--------------+ 8 rows in set (0.01 sec)
35. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit end end The Solution source: :columnName for Funky column name formats
36. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit end end The Solution source: :columnName for Funky column name formats
37. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit end end The Solution source: :columnName for Funky column name formats circuit_id
38. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, source: :circuitId end end The Solution source: :columnName for Funky column name formats
39. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, source: :circuitId end end The Solution source: :columnName for Funky column name formats
40. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel +----------!!-+-------------!!-+-----!!-+-!!---!!-+-----------!!-+----------------+ | Field | Type | Null | Key | Default | Extra | +----------!!-+-------------!!-+-----!!-+-!!---!!-+-----------!!-+----------------+ | raceId | int(11) | NO | PRI | NULL | auto_increment | | year | int(11) | NO | | 0 | | | round | int(11) | NO | | 0 | | | circuitId | int(11) | NO | | 0 | | | name | varchar(255) | NO | | | | | date | date | NO | | 0000-00-00 | | | time | time | YES | | NULL | | | url | varchar(255) | YES | UNI | NULL | | +----------!!-+-------------!!-+-----!!-+-!!---!!-+-----------!!-+----------------+ 8 rows in set (0.01 sec) The Solution source: :columnName for Funky column name formats
41. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel +----------!!-+-------------!!-+-----!!-+-!!---!!-+-----------!!-+----------------+ | Field | Type | Null | Key | Default | Extra | +----------!!-+-------------!!-+-----!!-+-!!---!!-+-----------!!-+----------------+ | raceId | int(11) | NO | PRI | NULL | auto_increment | | year | int(11) | NO | | 0 | | | round | int(11) | NO | | 0 | | | circuitId | int(11) | NO | | 0 | | | name | varchar(255) | NO | | | | | date | date | NO | | 0000-00-00 | | | time | time | YES | | NULL | | | url | varchar(255) | YES | UNI | NULL | | +----------!!-+-------------!!-+-----!!-+-!!---!!-+-----------!!-+----------------+ 8 rows in set (0.01 sec) The Solution source: :columnName for Funky column name formats
42. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit @primary_key {:id, :id, source: :raceId} schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, source: :circuitId end end The Solution source: :columnName for Funky column name formats
43. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit @primary_key {:id, :id, source: :raceId} schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, source: :circuitId end end The Solution source: :columnName for Funky column name formats
44. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit @primary_key {:id, :id, source: :raceId} schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, source: :circuitId end end The Solution source: :columnName for Funky column name formats {fieldname, type, options}
45. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit @primary_key {:id, :id, source: :raceId} schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, source: :circuitId end end The Solution source: :columnName for Funky column name formats
46. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION for Non-integer primary keys
47. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION for Non-integer primary keys @primary_key
48. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit @primary_key {:id, :id, source: :raceId} schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, source: :circuitId end end The Solution @primary_key for Non-integer primary keys
49. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution @primary_key for Non-integer primary keys defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit @primary_key {:id, :id, source: :raceId} schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, source: :circuitId end end {fieldname, type, options}
50. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution @primary_key for Non-integer primary keys defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit @primary_key {:id, :binary_id, source: :raceId} schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, source: :circuitId end end {fieldname, type, options}
51. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution @foreign_key_type for Non-integer primary keys defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit @primary_key {:id, :binary_id, source: :raceId} @foreign_key_type :binary_id schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, source: :circuitId end end
52. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution @foreign_key_type for Non-integer primary keys defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit @primary_key {:id, :binary_id, source: :raceId} @foreign_key_type :binary_id schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, source: :circuitId end end
53. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION for no primary key
54. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel mysql> describe lapTimes; +-------------!!-+-------------!!-+-----!!-+-!!---!!-+--------!!-+-------+ | Field | Type | Null | Key | Default | Extra | +-------------!!-+-------------!!-+-----!!-+-!!---!!-+--------!!-+-------+ | raceId | int(11) | NO | PRI | NULL | | | driverId | int(11) | NO | PRI | NULL | | | lap | int(11) | NO | PRI | NULL | | | position | int(11) | YES | | NULL | | | time | varchar(255) | YES | | NULL | | | milliseconds | int(11) | YES | | NULL | | +-------------!!-+-------------!!-+-----!!-+-!!---!!-+--------!!-+-------+ 6 rows in set (0.01 sec) The Solution @primary_key for no primary key
55. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution @primary_key for no primary key defmodule F1Saver.API.Lap do use Ecto.Schema alias F1Saver.API.{Driver, Race} @primary_key false schema "lapTimes" do field :lap, :integer field :time, :string field :race_id, :integer, source: :raceId field :driver_id, :integer, source: :driverId belongs_to :race, Race, foreign_key: :raceId, define_field: false belongs_to :driver, Driver, foreign_key: :driverId, define_field: false end end
56. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution @primary_key for no primary key defmodule F1Saver.API.Lap do use Ecto.Schema alias F1Saver.API.{Driver, Race} @primary_key false schema "lapTimes" do field :lap, :integer field :time, :string field :race_id, :integer, source: :raceId field :driver_id, :integer, source: :driverId belongs_to :race, Race, foreign_key: :raceId, define_field: false belongs_to :driver, Driver, foreign_key: :driverId, define_field: false end end
57. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution @primary_key for no primary key defmodule F1Saver.API.Lap do use Ecto.Schema alias F1Saver.API.{Driver, Race} @primary_key false schema "lapTimes" do field :lap, :integer field :time, :string field :race_id, :integer, source: :raceId field :driver_id, :integer, source: :driverId belongs_to :race, Race, foreign_key: :raceId, define_field: false belongs_to :driver, Driver, foreign_key: :driverId, define_field: false end end
58. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution @primary_key for no primary key defmodule F1Saver.API.Lap do use Ecto.Schema alias F1Saver.API.{Driver, Race} @primary_key false schema "lapTimes" do field :lap, :integer field :time, :string field :race_id, :integer, source: :raceId field :driver_id, :integer, source: :driverId belongs_to :race, Race, foreign_key: :raceId, define_field: false belongs_to :driver, Driver, foreign_key: :driverId, define_field: false end end
59. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION for Custom field types
60. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION for Custom field types Ecto.Type @behaviour Ecto.UUID
61. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types If you need something other than… :id :binary_id :float :boolean :string :binary {:array, inner_type} :map {:map, inner_type} :decimal :date :time :naive_datetime :utc_datetime
62. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types defmodule EncryptedField do @behaviour Ecto.Type def type, do: :binary def cast(string) when is_binary(string) do {:ok, Cipher.encrypt(string)} end def load(string) do {:ok, Cipher.decrypt(string)} end def dump(string) do {:ok, Cipher.encrypt(string)} end end
63. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types defmodule EncryptedField do @behaviour Ecto.Type def type, do: :binary def cast(string) when is_binary(string) do {:ok, Cipher.encrypt(string)} end def load(string) do {:ok, Cipher.decrypt(string)} end def dump(string) do {:ok, Cipher.encrypt(string)} end end
64. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types defmodule EncryptedField do @behaviour Ecto.Type def type, do: :binary def cast(string) when is_binary(string) do {:ok, Cipher.encrypt(string)} end def load(string) do {:ok, Cipher.decrypt(string)} end def dump(string) do {:ok, Cipher.encrypt(string)} end end
65. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types defmodule EncryptedField do @behaviour Ecto.Type def type, do: :binary def cast(string) when is_binary(string) do {:ok, Cipher.encrypt(string)} end def load(string) do {:ok, Cipher.decrypt(string)} end def dump(string) do {:ok, Cipher.encrypt(string)} end end
66. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types defmodule EncryptedField do @behaviour Ecto.Type def type, do: :binary def cast(string) when is_binary(string) do {:ok, Cipher.encrypt(string)} end def load(string) do {:ok, Cipher.decrypt(string)} end def dump(string) do {:ok, Cipher.encrypt(string)} end end
67. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types defmodule EncryptedField do @behaviour Ecto.Type def type, do: :binary def cast(string) when is_binary(string) do {:ok, Cipher.encrypt(string)} end def load(string) do {:ok, Cipher.decrypt(string)} end def dump(string) do {:ok, Cipher.encrypt(string)} end end
68. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types defmodule EncryptedField do @behaviour Ecto.Type def type, do: :binary def cast(string) when is_binary(string) do {:ok, Cipher.encrypt(string)} end def load(string) do {:ok, Cipher.decrypt(string)} end def dump(string) do {:ok, Cipher.encrypt(string)} end end
69. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types defmodule F1Saver.API.Encrypt do use Ecto.Schema schema "encrypts" do field :text, EncryptedField end end
70. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types iex> %Encrypt{text: "this is a secret message"} !|> Repo.insert() [debug] QUERY OK db=1.6ms INSERT INTO `encrypts` (`text`) VALUES (?) ["FGqTIWvjsFF3ZD%2BFBeFn0T63vulTHq%2F9NAbpJ6nEJKc%3D"] {:ok, %F1Saver.API.Encrypt{__meta__: #Ecto.Schema.Metadata!<:loaded, "encrypts!">, id: 1, text: "this is a secret message"}}
71. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types iex> %Encrypt{text: "this is a secret message"} !|> Repo.insert() [debug] QUERY OK db=1.6ms INSERT INTO `encrypts` (`text`) VALUES (?) ["FGqTIWvjsFF3ZD%2BFBeFn0T63vulTHq%2F9NAbpJ6nEJKc%3D"] {:ok, %F1Saver.API.Encrypt{__meta__: #Ecto.Schema.Metadata!<:loaded, "encrypts!">, id: 1, text: "this is a secret message"}}
72. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types iex> %Encrypt{text: "this is a secret message"} !|> Repo.insert() [debug] QUERY OK db=1.6ms INSERT INTO `encrypts` (`text`) VALUES (?) ["FGqTIWvjsFF3ZD%2BFBeFn0T63vulTHq%2F9NAbpJ6nEJKc%3D"] {:ok, %F1Saver.API.Encrypt{__meta__: #Ecto.Schema.Metadata!<:loaded, "encrypts!">, id: 1, text: "this is a secret message"}}
73. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types mysql> select * from encrypts; +!!---!!-+----------------------------------------------------+ | id | text | +!!---!!-+----------------------------------------------------+ | 1 | FGqTIWvjsFF3ZD%2BFBeFn0T63vulTHq%2F9NAbpJ6nEJKc%3D | +!!---!!-+----------------------------------------------------+ 1 row in set (0.00 sec)
74. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types iex> Repo.all(from e in Encrypt, where: e.text !== "this is a secret message") [debug] QUERY OK source="encrypts" db=0.7ms decode=3.8ms SELECT e0.`id`, e0.`text` FROM `encrypts` AS e0 WHERE (e0.`text` = 'FGqTIWvjsFF3ZD%2BFBeFn0T63vulTHq%2F9NAbpJ6nEJKc%3D') [] [%F1Saver.API.Encrypt{__meta__: #Ecto.Schema.Metadata!<:loaded, "encrypts!">, id: 1, text: "this is a secret message"}]
75. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types iex> Repo.all(from e in Encrypt, where: e.text !== "this is a secret message") [debug] QUERY OK source="encrypts" db=0.7ms decode=3.8ms SELECT e0.`id`, e0.`text` FROM `encrypts` AS e0 WHERE (e0.`text` = 'FGqTIWvjsFF3ZD%2BFBeFn0T63vulTHq%2F9NAbpJ6nEJKc%3D') [] [%F1Saver.API.Encrypt{__meta__: #Ecto.Schema.Metadata!<:loaded, "encrypts!">, id: 1, text: "this is a secret message"}]
76. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types iex> Repo.all(from e in Encrypt, where: e.text !== "this is a secret message") [debug] QUERY OK source="encrypts" db=0.7ms decode=3.8ms SELECT e0.`id`, e0.`text` FROM `encrypts` AS e0 WHERE (e0.`text` = 'FGqTIWvjsFF3ZD%2BFBeFn0T63vulTHq%2F9NAbpJ6nEJKc%3D') [] [%F1Saver.API.Encrypt{__meta__: #Ecto.Schema.Metadata!<:loaded, "encrypts!">, id: 1, text: "this is a secret message"}]
77. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution Ecto.Type for Custom field types iex> Repo.all(from e in Encrypt, where: e.text !== "this is a secret message") [debug] QUERY OK source="encrypts" db=0.7ms decode=3.8ms SELECT e0.`id`, e0.`text` FROM `encrypts` AS e0 WHERE (e0.`text` = 'FGqTIWvjsFF3ZD%2BFBeFn0T63vulTHq%2F9NAbpJ6nEJKc%3D') [] [%F1Saver.API.Encrypt{__meta__: #Ecto.Schema.Metadata!<:loaded, "encrypts!">, id: 1, text: "this is a secret message"}]
78. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION for Schemas
79. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel THE SOLUTION for Schemas @schema_prefix
80. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution @schema_prefix for Schemas defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit @primary_key {:id, :id, source: :raceId} @schema_prefix "f1" schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, foreign_key: :circuitId end end
81. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution @schema_prefix for Schemas defmodule F1Saver.API.Race do use Ecto.Schema alias F1Saver.API.Circuit @primary_key {:id, :id, source: :raceId} @schema_prefix "f1" schema "races" do field :year, :integer field :round, :integer field :name, :string field :date, :date field :url, :string belongs_to :circuit, Circuit, foreign_key: :circuitId end end
82. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution @schema_prefix for Schemas @schema_prefix "f1" iex(1)> Repo.get(Race, 1) SELECT r0.`raceId`, r0.`year`, r0.`round`, r0.`circuitId`, r0.`name`, r0.`date`, r0.`url`, r0.`circuitId` FROM `f1`.`races` AS r0 WHERE (r0.`raceId` = ?) [1]
83. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel The Solution @schema_prefix for Schemas @schema_prefix “f1" iex(1)> Repo.get(Race, 1) SELECT r0.`raceId`, r0.`year`, r0.`round`, r0.`circuitId`, r0.`name`, r0.`date`, r0.`url`, r0.`circuitId` FROM `f1`.`races` AS r0 WHERE (r0.`raceId` = ?) [1]
84. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel Moving Forward
85. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel Moving Forward Multiple Databases
86. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel use Mix.Config config :f1_saver, F1Saver.LegacyRepo, adapter: Ecto.Adapters.MySQL, database: "f1_legacy", username: "root", password: "something_secret", hostname: "legacy.mysite.com" config :f1_saver, F1Saver.Repo, adapter: Ecto.Adapters.Postgres, database: "f1", username: "root", password: "something_secret", hostname: "new_hotness.mysite.com" config :f1_saver, ecto_repos: [F1Saver.LegacyRepo, F1Saver.Repo] Moving Forward Multiple Databases config/config.exs
87. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel use Mix.Config config :f1_saver, F1Saver.LegacyRepo, adapter: Ecto.Adapters.MySQL, database: "f1_legacy", username: "root", password: "something_secret", hostname: "legacy.mysite.com" config :f1_saver, F1Saver.Repo, adapter: Ecto.Adapters.Postgres, database: "f1", username: "root", password: "something_secret", hostname: "new_hotness.mysite.com" config :f1_saver, ecto_repos: [F1Saver.LegacyRepo, F1Saver.Repo] Moving Forward Multiple Databases config/config.exs
88. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.Application do use Application def start(_type, _args) do import Supervisor.Spec children = [ # Start the Ecto repository supervisor(F1Saver.Repo, []), supervisor(F1Saver.LegacyRepo, []), supervisor(F1SaverWeb.Endpoint, []), ] opts = [strategy: :one_for_one, name: F1Saver.Supervisor] Supervisor.start_link(children, opts) end def config_change(changed, _new, removed) do F1SaverWeb.Endpoint.config_change(changed, removed) :ok end end Moving Forward Multiple Databases lib/f1_saver/application.ex
89. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel defmodule F1Saver.Application do use Application def start(_type, _args) do import Supervisor.Spec children = [ # Start the Ecto repository supervisor(F1Saver.Repo, []), supervisor(F1Saver.LegacyRepo, []), supervisor(F1SaverWeb.Endpoint, []), ] opts = [strategy: :one_for_one, name: F1Saver.Supervisor] Supervisor.start_link(children, opts) end def config_change(changed, _new, removed) do F1SaverWeb.Endpoint.config_change(changed, removed) :ok end end Moving Forward Multiple Databases lib/f1_saver/application.ex
90. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel Moving Forward Multiple Databases
91. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel Images Jeff Cooper JP Valery Alex Holyoak
92. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel Geoffrey Lessel geoffreylessel.com @geolessel
93. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel Geoffrey Lessel Phoenix in Action
94. WORKING WITH LEGACY DATABASES IN ECTO Geoffrey Lessel Geoffrey Lessel geoffreylessel.com @geolessel