Upgrade to Pro — share decks privately, control downloads, hide ads and more …
Sign up for free

Working With Legacy Databases in Ecto - ElixirC...

Geoffrey Lessel
September 07, 2017
Programming
600
0

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

Geoffrey Lessel

September 07, 2017

Transcript

94 slides

  1. Geoffrey Lessel Working with legacy databases in Ecto

  2. None

  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

More Decks by Geoffrey Lessel

See All by Geoffrey Lessel
Bringing a 45 year old CPU to life with Elixir and Phoenix LiveView - ElixirConf 2021
geolessel
1
250

Other Decks in Programming

See All in Programming
Kawaiicon: Kubernetes, it's no longer cool, but let's secure it anyway!
barnbarn
0
130
AI時代のFlutter品質設計 — アプリ品質を守るQuality Layers
omatty198
0
380
SODA - FACT BOOK（EN）
sodainc
1
240
Crash Auto Pipelines
veronikapj
1
410
Apple Intelligenceとオンデバイス時代の到来
yahir0
0
240
クラウドエージェントは、 何がそんなに便利なのか / what makes cloud agents so useful
rkaga
5
860
Snowflake Openflow Connector利用の勘所 / 20261024developersio2026tokyo-snowflake-openflow
kasacchiful
PRO
0
340
FactoryBotをやめればRSpecは速くなるのか？
yuta_onishi_97
0
1.7k
AI時代のTerminal環境を UNIX哲学で設計・運用する
yasunori0418
1
350
動画プレイヤーはなぜカクつくのか 〜Flutterのレンダリングとネイティブの境界を解剖する〜
naruogram
0
340
React Native経験者が Flutterを30⽇間触って驚いたこと
3saito
0
180
あなたの await、OS まで届いてる？ strace で覗く JavaScript の非同期
hasutosasaki
1
2.4k

Featured

See All Featured
Test your architecture with Archunit
thirion
2
2.5k
4 Signs Your Business is Dying
shpigford
187
23k
First, design no harm
axbom
PRO
2
1.4k
We Have a Design System, Now What?
morganepeng
55
8.4k
GraphQLとの向き合い方2022年版
quramy
50
15k
Jess Joyce - The Pitfalls of Following Frameworks
techseoconnect
PRO
1
480
Abbi's Birthday
coloredviolet
5
11k
Future Trends and Review - Lecture 12 - Web Technologies (1019888BNR)
signer
PRO
0
3.8k
Lightning talk: Run Django tests with GitHub Actions
sabderemane
0
320
Groundhog Day: Seeking Process in Gaming for Health
codingconduct
0
480
Navigating Algorithm Shifts & AI Overviews - #SMXNext
aleyda
1
1.7k
Fashionably flexible responsive web design (full day workshop)
malarkey
409
67k