How do I detect database connection issues from Elixir Ecto?

Question

I am writing an Elixir project that connects to a Postgres database via Ecto. The database server is on a different server from the application itself, and is more subject to outages that would not affect the Elixir project than if they were running on the same hardware.

When the application starts normally, the database connection seems to be made automatically, and everything works fine. But if there is a connection error, Ecto simply spews any errors into the log.

What I would like to do is detect the current connection status, and report that information via a simple Plug route to an external load balancer, such that traffic can be routed to a separate server with an active connection.

The trouble is, I'm unsure how to determine if Ecto has a viable connection to the database, apart from listening to the log, which doesn't then report that the database connection has been restored.

What can I call to determine if an Ecto connection is live and usable, preferably without making no-op queries against that connection?

Answer 1

Ecto simply spews any errors into the log.

This is the default strategy with enabled backoff for the connection. You can disable the backoff by setting backoff_type to :stop in repo options and deal with errors by yourself.

What can I call to determine if an Ecto connection is live and usable, preferably without making no-op queries against that connection?

I think you can check if the connection "usable" only by using it :)

try do
  Ecto.Adapters.SQL.query(MyApp.Repo, "SELECT 1")
rescue
  e in DBConnection.ConnectionError -> :down
end

BTW, the module mentioned above is the default pool module for the connection you can rely on its internals(don't) and do the checkout manually. Own pool can be implemented instead of this one :)

Answer 2

To detect whether a connection to the database is live and usable, you can try connecting to it directly outside of Ecto using :gen_tcp.connect/3. The code could look something like this:

case :gen_tcp.connect('127.0.0.1', 5432, []) do
  {:ok, _} ->
    # code for handling ok scenario
  {:error, error} ->
    # code for handling error scenario. Typically, you'll get the econnrefused error when the server is down
end

Note that Ecto gets the details of the server to connect to through the Mix config you set for it. If you want to change the server it connects to, you'll have to run Application.put_env/4 and then restart the Ecto repository for Ecto to recognize the new config. For example:

Application.put_env(:my_app, MyApp.Repo, [adapter: Ecto.Adapters.Postgres, username: "postgres", password: "postgres",database: "new_db", hostname: "different_host.com", pool_size: 20])
Supervisor.stop(MyApp.Repo)