How to use two different databases whilst setting export DATABASE_URL

I am able to correctly set one database in the environment variable, but how do I set two?
e.g.:
from Introduction to Deployment — Phoenix v1.6.2
export DATABASE_URL=ecto://USER:PASS@HOST/database ++ USER2:PASS2@HOST2/database2
etc…

I have two different databases with two different users on them.

You need two separate ENV variables. You’ll best off creating two repos:

config :my_app, MyApp.Repo,
  url: System.get_env("DATABASE_URL")

config :my_app, MyApp.SecondRepo,
  url: System.get_env("SECONDARY_DATABASE_URL")
1 Like

My runtime.exs file now has the following:

if config_env() == :prod do
  test_database_url =
    System.get_env("TEST_DATABASE_URL") ||
      raise """
      environment variable TEST_DATABASE_URL is missing.
      For example: ecto://USER:PASS@HOST/DATABASE
      """

live_database_url =
    System.get_env("LIVE_DATABASE_URL") ||
      raise """
      environment variable LIVE_DATABASE_URL is missing.
      For example: ecto://USER:PASS@HOST/DATABASE
      """

config :redacted1, redacted1.Repo.Test,
	  # ssl: true,
	  url: test_database_url,
	  pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10")

config :redacted1, redacted1.Repo.Live,
	  # ssl: true,
	  url: live_database_url,
	  pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10")

Is this what you meant?

Is this about configuring a database for prod vs test or two databases for prod?

To expand on the one above: if your app needs to connect to two databases in the prod environment, then yes, having two repos is the way to go.

If on the other hand you have one ENV var with the test database URL and another ENV var for the prod URL, then you should go with one repo and just pick the right ENV variable in the config.

3 Likes

I have the live db and a backup one that it will connect to if the live one goes down.
So I will need two databases for prod.
Thank you for your help

@EddTally Ecto has support for dynamic repo and multi tenancy Replicas and dynamic repositories — Ecto v3.7.1 , though i am not sure if the intended use-case for such feature is about Automatic Failover (Live & Backup DB), it seems the feature is mostly intended to spread out throughput of query to multiple DB at once.

As for Automatic Failover capability, i am also doesn’t have much experience with it, from my little understanding, usually what happen is there’s a proxy of connection (like pgBouncer) which will automatically change the connection to Backup DB in case Live DB failed.

1 Like

‘all write operations are sent to primary database and most of the read operations are performed against the replicas.’
Interesting, if my database gets too large I will implement this, thanks.