Now that we’ve explored some simple Ecto setups, let’s get a little bit exotic. Did you know that you can set up your application in such a way that you can connect to multiple databases with Ecto? Ecto actually makes it super simple to do so. Let’s take a look.

Setting everything up

I’m not going to go into much detail regarding the initial setup of your Elixir application. I’ve covered that in detail in other parts of this site. Instead, let’s just jump into an already set up project.

Let’s imagine that you have a legacy MySQL database that has User information that we need in order to have our application work properly. At the same time, we also would like to slowly migrate our site over to PostgreSQL and we store our new Favorite records there. Favorite what? Who knows. It doesn’t really matter in this post.

But let’s reiterate what we are going to be doing here. We’ll be connecting our application to two different databases. Not only that, those two databases will not even be the same type of database. Pretty cool, huh?

Like in a normal Elixir/Ecto setup, let’s take a look at our config/config.exs file:

config/config.exs

use Mix.Config

config :my_app, MyApp.LegacyRepo,
  adapter: Ecto.Adapters.MySQL,
  database: "legacy_db",
  username: "username",
  password: "somethingsecret",
  hostname: "legacy.mysite.com"

config :my_app, MyApp.NewRepo,
  adapter: Ecto.Adapters.Postgres,
  username: "username",
  password: "somethingsecret",
  database: "new_db",
  hostname: "newhotness.mysite.com"
  
config :my_app, ecto_repos: [MyApp.LegacyRepo, MyApp.NewRepo]

We also need to ensure that both Repos are supervised.

lib/my_app.ex

defmodule ExistingDb do
  use Application

  def start(_type, _args) do
    import Supervisor.Spec, warn: false

    children = [
      supervisor(MyApp.LegacyRepo, []), # <-- our addition
      supervisor(MyApp.NewRepo, [])     # <-- our addition
    ]

    opts = [strategy: :one_for_one, name: MyApp.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

Finally, we need to also create the Repo modules. For the sake of being concise, I’ll put them both in the same file:

lib/repos.ex

defmodule MyApp.LegacyRepo do
 use Ecto.Repo, otp_app: :my_app
end

defmodule MyApp.NewRepo do
 use Ecto.Repo, otp_app: :my_app
end

Wow, that was easy. For completeness, let’s define a couple of really simple schemas.

lib/user.ex

defmodule User do
  use Ecto.Schema

  schema "users" do
    field :username, :string
    has_many :favorites, Favorite
    timestamps
  end
end

lib/favorite.ex

defmodule Favorite do
  use Ecto.Schema
  
  schema "favorites" do
    field :value, :string
    belongs_to :user, User
    timestamps
  end
end

Using Our Repos

Now that we have our Repos and defined a couple sample schemas, let’s test out how to use them. In order to use them correctly, we’ll need to query each Repo separately depending on which record we’d like to retrieve.

We’re not going to get fancy here so let’s just open up iex with iex -S mix.

# a little setup
iex> import Ecto.Query

# let's grab a User from the legacy db
iex> user = MyApp.LegacyRepo.get_by(User, %{username: "Geo"})

# now that we have our user from the legacy database, let's find their
# favorites in the new database
iex> favorites = MyApp.NewRepo.all from fav in Favorite,
                 where: fav.user_id == ^user.id

Not A Lot of Work

One of the things that I’ve really been enjoying about Ecto is that although it is explicit in the way Elixir is explicit, it doesn’t take a lot of work to get it to do what you want it to do (most of the time). In this case, it was almost trivial to connect to two different databases of two different types within the same application.

There seems to be more and more people every day moving over to Elixir and Phoenix as their main development tools. I think this demonstration of connecting to both a legacy database and a fresh database could be helpful in a lot of transitions.

If you have any questions, please let me know. I’m @geolessel on Twitter and @geo in the Elixir slack group. Also, I’d be grateful if you signed up for my email list below. I’m brainstorming some neat things that I’d like to release in the near future and I’d love to let you be the first to know about it. Thanks!