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

Questions / Help
,
1

Hi all,

I’m a bit new to Ecto and Elixir. I got a question about connecting to a database.

I have a Phoenix project where I want to allow my user to connect to a database of their choosing. However, I’m not sure how to use Ecto to connect to the DB, unless it’s set in the configuration of the project and run when the server is started up.

When I have a second repo module

defmodule MyApp.SecondRepo do
  use Ecto.Repo, otp_app: :myapp
end

Elixir complains that it doesn’t have the right config to initialize this module. Can anyone point me to how to connect to a user-specified database after the Phoenix server has already been started up? Thanks.

Wil

2 Likes
2

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

2 Likes
3

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

4 Likes
4

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.

1 Like
5

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

1 Like
6

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?

7

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

8

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.

1 Like
9

I have created a simple app to handle this situation.

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.

10

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?

11

This is the purpose of the init callback in your repo.https://hexdocs.pm/ecto/Ecto.Repo.html#c:init/2 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
12

perfect, thanks