How to connect to user-specified DB config using Ecto?

Ecto does not allow dynamic configuration of the DB at runtime. Your only option is to directly use the DB-Adapter to go through connection and query.

Thats at least what I understand when reading github issue #1183

There are some configuration values that are required at compile-time. This is primarily the adapter - ecto inlines some functions in the repo, based on the adapter, in order to speed-up dome hot paths.

Most of the other configuration values can be passed to the repo’s start_link function (or as options in the supervisor).

There’s a list of compile-time options and runtime options in the documentation: https://hexdocs.pm/ecto/Ecto.Adapters.Postgres.html

Can you tell us a bit more broadly what your end result should be? I mean, I suspect you may want to do multi-tenancy by connecting to multiple databases? If this is the case then Ecto has sort of different approach, by using namespaces/prefixes rather than connecting to multiple databases.

Do you know the list of DBs upfront ? then you can setup a repo per DB

I’m making an SQL query builder, so a user would have to be able to connect to a database at runtime.

Thanks for the tip about the adapter. Looking through the code for the Postgres adapter, I found that it used Postgrex. I was able to use Postgrex directly to connect to the database.

{ :ok, pid } = Postgrex.start_link(hostname: "localhost", username: "admin", password: "", database: "my_db")
    result = Postgrex.query!(pid, "select * from some_table limit 1", [])

For now, I’m just going to use it directly in a controller method. However, I imagine that one might need to create an application with a connection pool open if there are lots of queries?

Pretty sure all of that stuff could be passed into the Repo start link function as @micmus mentioned btw.

Sorry, I’m new to elixir and all this. I can see that start_link is used in the lib/myapp.ex. But I’m not sure exactly how to use Ecto.Adapters.Postgres, since there’s no function documentation in the link.

I’m inferring that the Ecto.Adapters.Postgres can be run as a child supervisor to the application. However:

• I don’t know how to change the runtime options outside of lib/myapp.ex.
• And if I inserted a Repo with bad connection login, it surfaces errors on startup.
• And if I run start_link after startup, how do I find the correct child supervisor to remove the link?

I think I just don’t have enough background to make sense of the answer right now. Are there resources you can point me to, or give some examples? Thanks.

I have created a simple app to handle this situation.

https://github.com/voxpoplabs/multi_postgrex

I find that it is useful for multi-tenancy where each organization can have their own database. There is a little dev ops to setup a new database, but it makes querying really simple as all data is isolated.

I’m an elixir newb. However, hard coding db connection credentials (password), in a file that I want to source control just seems wrong. If I want to get credentials from a local/trusted service at startup, how would I go about that?

This is the purpose of the init callback in your repo.Ecto.Repo — Ecto v3.14.0 You can have an init function that basically is

def init(_, config) do
  values = # code to go lookup values from service
  {:ok, Keyword.merge(values, config)}
end