I was curious if it was possible to have a dynamic database connection. For example, I’m wanting to create a database gui. I know the db config in config/dev.exs. Is it possible to dynamically change the connection config from a settings page inside the website to access different databases without manually changing the config/dev.exs and recompiling? I’m aware of config/runtime.exs but it seems it pulls the db info from config/dev.exs.
I’m not sure about Ecto, but it’s pretty straightforward to access Postgres directly using Postgrex:
opts = [hostname: "blah", database: "blah_db", username: "postgres" password: "secure_az"]
with {:ok, conn} <- Postgrex.start_link(opts),
{:ok, result} <- Postgrex.query(conn, sql, [])
do
IO.inspect "Got #{inspect(result.num_rows)} rows from #{inspect(sql)}"
IO.inspect result.columns
IO.inspect result.rows
GenServer.stop(conn)
rows
else
{:error, err} ->
IO.inspect err, label: ~s|Reading from db with sql #{inspect(sql)}|
[]
end
Of course, if you’re hitting up the database directly, you have to worry about escaping etc.
There’s a thread here that may help harness Ecto: Concurrent access to several Ecto Dynamic Repositories
Ecto.put_dynamic_repo/1
can take you there if you spawn a new process for each different DB.
In an application I’m working on I don’t actually set any database configuration at all in any of the config/*.exs scripts at all and use the dynamic repositories feature of Ecto. I’m using a “database-per-tenant” model and so different database connections are (relatively) many and can be independently started and stopped. Once you understand the basic concepts it’s pretty easy to code.
The documentation for the feature is at: Replicas and dynamic repositories — Ecto v3.12.5
You do have to call the appropriate start_link
function to configure and establish the database connection and then you may need to do some management of that (as needed).
You select the dynamic repo using the function that @dimitarvp pointed out.