Multiple Databases with Ecto and Phoenix

$ cat "Multiple Databases with Ecto and Phoenix.html"

creation date:
--------

At work we have one db master and some slaves. It would alleviate the load of the master database if we could send some (or all!) of the reads to the slave(s) and the writes to the master.

Seeing that Ecto uses the repository pattern it seemed to me that adding another database connection to my phoenix project was a trivial thing.

That was my guess, so I asked in #elixir if adding another DB connection with Ecto was as trivial as it looked, and this is the response I got:

<jeregrine> edmz: it is exactly that trivial
<jeregrine> part of what makes ecto great is the query generation
  and the repo are decoupled

So I decided to give it a try. I created a new phoenix project:

mix phoenix.new dual_ecto --database mysql

I added the credentials to dev.exs for the other database:

config :dual_ecto, DualEcto.Repo,
  adapter: Ecto.Adapters.MySQL,
  username: "readwrite_user",
  password: "bleh",
  database: "data",
  hostname: "master_server",
  pool_size: 10

# notice the module name
config :dual_ecto, DualEcto.RepoReadOnly,
  adapter: Ecto.Adapters.MySQL,
  username: "readonly_user",
  password: "foo",
  database: "data",
  hostname: "slave_server",
  pool_size: 10

Now, notice that the second set of credentials refers to a repo called RepoReadOnly. We need to create that module and also ensure that it is started.

First, add the module to the supervision tree of your main module, which in my case is called DualEcto:

children = [
  # Start the endpoint when the application starts
  supervisor(DualEcto.Endpoint, []),
  # Start the Ecto repository
  supervisor(DualEcto.Repo, []),
  supervisor(DualEcto.RepoReadOnly, []), # <- new line!
  # Here you could define other workers and supervisors as children
  # worker(DualEcto.Worker, [arg1, arg2, arg3]),
]

And then create the actual module, which looks like this:

defmodule DualEcto.RepoReadOnly do
  use Ecto.Repo, otp_app: :dual_ecto
end

Now, it is only a matter of using it in your model (or ‘schema’, as they are called in Ecto 2.0).

defmodule DualEcto.User do
  use DualEcto.Web, :model
  alias __MODULE__
  alias DualEcto.Repo
  alias DualEcto.RepoReadOnly

  # obviously, this table exists in both dbs
  schema "users" do
    field :email, :string
  end

  # this one reads from the readonly db
  def all(:readonly) do
    RepoReadOnly.all(User)
  end

  # this one reads from master
  def all() do
    Repo.all(User)
  end
end

I created a very naive model that just does simple selects, but there is nothing stopping you for using one module when doing updates and another one when doing selects.

EOF