Hey! I recorded two video courses!

My two video courses are called: Build an MVP with Elixir and Building Forms with Phoenix LiveView
If you like this article, you will also like the courses! Check them out here!

Hey there! Let me welcome you with the customary dad joke:

What do you call a knight
made entirely out of fine china?

Sir Ramic

Alright! Now, let’s dive into today’s topic: How to test an external, read-only Database connection in Phoenix.

🔗 The Problem

Sometimes, we have to connect our Elixir applications to read from an external database that we don’t control. Setting up the connection is easy. Just create another Repo, define the connection parameters in runtime.exs, and define some schemas. Done! Now, you can query the external database through your Repo.

The problem occurs when you want to write tests for that read-only database connection. How can you simulate the external database in your test environment? You could replace the connection with a mock module at runtime. Or you don’t test it at all 🤷‍♂️

🔗 The Solution

There’s a better approach: If you only rely on small, stable parts of the database, you can replicate it locally and for your tests only. That way, you can be sure that your tests test the actual functionality and not just a mock.

Let’s see how we can create and migrate the database and write an ExMachina factory for it too!

🔗 The Configuration

First, we need to create a new Ecto.Repo for the external database connection. We can simply copy&paste the autogenerated Repo module and add a read_only: true flag to block any write operations to the external database (if needed).

This is how the new Repo would look like

# lib/other_app/repo.ex
defmodule OtherApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres,
    read_only: Mix.env() != :test # <- Note this

Notice that we set the :read_only-flag to true in all environments that are not :test. In our tests, we need a read-and-write connection to create and migrate the database and populate it with our test data. But in all other environments, the external database will be created, migrated, and populated already. So, to prevent accidental changes to the database, we set read_only to true.

Now, let’s have a look at the Repo configuration in our tests.

# config/config.exs
config :my_app, ecto_repos: [MyApp.Repo]

# config/test.exs
config :my_app,
  ecto_repos: [MyApp.Repo, OtherApp.Repo] # <- Note this

config :my_app, OtherApp.Repo,
  priv: "test/other_app_repo", # <- Note this
  username: "postgres",
  password: "postgres",
  hostname: "localhost",
  database: "my_other_app_test#{System.get_env("MIX_TEST_PARTITION")}",
  pool: Ecto.Adapters.SQL.Sandbox,
  pool_size: 2

There are two important things in this configuration. First, we add the new Repo to the ecto_repos of your app. This means that whenever we run mix ecto.setup|create|migrate before our tests, Ecto will also create and migrate the new Repo. We don’t want to change the external database in any other environment, so we only add the new Repo in test.exs and leave it out in e.g. config.exs.

Now, if you run MIX_ENV=test mix ecto.create, Ecto will automatically create the my_other_app_test database in your local Postgres instance. Great!

The second important detail here is the priv: "test/other_app_repo" entry. When we create the test database, we also want to run migrations against it to create the tables that we want to test. We don’t want these test migrations inside our priv/repo/migrations folder, which belongs to the Repo of our app. Also, we only need these migrations in our tests, so we created a new folder inside ./test instead. We can tell Ecto where to find the migrations using the :priv-config, so that’s where we set it.

Now, the repo configuration is done! Next, let’s create some migrations for the test repo.

🔗 The Migrations

Before we can insert data into our external-but-now-local database, we need to create the tables that we test against. You can simply create migrations for the test repo using the following command:

MIX_ENV=test mix ecto.gen.migration create_or_add_something -r OtherApp.Repo

This command will create a new migration inside our test/other_app_repo/migrations folder. Great!

Now, add whatever migration you need to set up the external-but-now-local database. I’ll wait!

🔗 The Sandbox

Ready? Good. Now, one last step is to start the external database in sandbox mode. To do that, simply copy and paste any code that Phoenix autogenerated for your app Repo already. That would be:

# test/test_helper.exs
ExUnit.start()
Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, :manual)
Ecto.Adapters.SQL.Sandbox.mode(OtherApp.Repo, :manual) # <- Add this

# test/support/data_case.ex
def setup_sandbox(tags) do
  my_app_pid = Ecto.Adapters.SQL.Sandbox.start_owner!(MyApp.Repo, shared: not tags[:async])
  other_app_pid =
    Ecto.Adapters.SQL.Sandbox.start_owner!(OtherApp.Repo, shared: not tags[:async]) # <- Add this

  on_exit(fn ->
    Ecto.Adapters.SQL.Sandbox.stop_owner(my_app_pid)
    Ecto.Adapters.SQL.Sandbox.stop_owner(other_app_pid) # <- Add this
  end)
end

And that’s it! If you run your tests now, Ecto should create and migrate your test database and start it in sandbox mode.

Now, you might think that you can start writing your tests …

🔗 The Factory

… but when you start writing your tests, you realize that you have no way of populating the external test database yet! You might want to use the fabulous ExMachina library for that, but if you simply copy and paste your existing factory, you’ll run into problems.

ExMachina factories often receive the use ExMachina.Ecto, repo: MyApp.Repo configuration which means that they’ll insert test records for you automagically if you use their generated insert, insert_pair, or insert_list functions. You probably import these functions in your DataCase with import Support.Factory. The problem here is that with two databases and therefore two factories, both factories expose the same insert, insert_pair, and insert_list functions. So, you can’t simply import them both! One solution would be to call the factory of the external database by their full name, but code like the following just looks wrong:

test "foo" do
  insert(:my_record) # <- This uses the Factory for MyApp
  Support.OtherApp.Factory.insert(:external_record)
end

Now, what we want is code like the following:

test "foo" do
  insert(:my_record) # <- This uses the Factory for MyApp
  insert(:external_record) # <- This uses the Factory for OtherApp
end

So, how can we get there?

The answer is: By creating a Factory Delegator!

A Factory Delegator, or “Factogator” 🐊, delegates your insert calls to the correct factory. Here’s what it looks like:

defmodule Support.Factogator do
  alias Support.Factories.MyApp
  alias Support.Factories.OtherApp

  def insert(type, attrs \\ [], opts \\ []) do
    factory = get_factory(type)
    factory.insert(type, attrs, opts)
  end

  def insert_pair(type, attrs \\ []) do
    factory = get_factory(type)
    factory.insert_pair(type, attrs)
  end

  def insert_list(count, type, attrs \\ []) do
    factory = get_factory(type)
    factory.insert_list(count, type, attrs)
  end

  def params_for(type, attrs \\ []) do
    factory = get_factory(type)
    factory.params_for(type, attrs)
  end

  defp get_factory(type) do
    cond do
      type in [:own_record] -> MyApp
      type in [:external_record, :other_record] -> OtherApp
    end
  end
end

The Factogator exposes the same functions as an ExMachina Factory and delegates any calls to the correct factory.

Now, we can simply import Support.Factogator in our DataCase and write tests like this:

test "see you later, factogator" do
  insert(:own_record) # <- Delegated to the MyApp Factory
  insert(:external_record) # <- Delegated to the OtherApp Factory
end

🔗 Conclusion

And that’s it! I hope you enjoyed this article! If you want to support me, you can buy my book or video course. Follow me on Twitter or subscribe to my newsletter below if you want to get notified when I publish the next blog post. Until the next time! Cheerio 👋

Stay updated about my work