Using Ecto with multiple, distinctive connections

Questions / Help
1

I want to use Ecto in my application, but with distinctive connections to the database, since every user of the application has a corresponding database user.

Now there seems to be Ecto.Repo.put_dynamic_repo(), but I do seem to do something wrong and furthermore this functionality is marked “experimental”. I currently have a solution which does that, but I’m missing out on a lot of stuff, since I’m always reinventing the wheel (e.g. CRUD stuff in Phoenix, and everyhting else there is when using “raw” Postgrex).

I’m trying the following:

iex(1)> DBRepo.start_link(name: :admin, username: "admin", database: "db", hostname: "localhost")
{:ok, #PID<0.247.0>}
iex(2)> DBRepo.put_dynamic_repo(:admin)
DBRepo
iex(3)> DBRepo.get_dynamic_repo        
:admin
iex(4)> DBRepo.query("select * from users")
** (RuntimeError) could not lookup DBRepo because it was not started or it does not exist
    (ecto) lib/ecto/repo/registry.ex:18: Ecto.Repo.Registry.lookup/1
    (ecto) lib/ecto/adapter.ex:127: Ecto.Adapter.lookup_meta/1
    (ecto_sql) lib/ecto/adapters/sql.ex:334: Ecto.Adapters.SQL.query/4

What am I doing wrong here and is Ecto.Repo.put_dynamic_repo() really the only way? Especially as it is marked “experimental”?

Thanks in advance…

2

@Jansemon please file a bug. I think that query is not respecting the dynamic_repo choice. :slight_smile:

1 Like
3

Excellent question. I ran into a similar situation in Oban yesterday where I need a process to use the same connection for all of its calls.

Essentially a process is using shared advisory locks for a distributed lock. The lock needs to be released by the same connection that started it, and the pool doesn’t work for that.

My solution has been to start a dedicated postgrex connection and use that, but I’m missing the convenience of Ecto. What I would really like is either:

  1. A way to give a process affinity for one of the pool’s connections (which seems prone to contention)
  2. A way to provide a specific connection when calling Repo.query/4
4

@josevalim Thanks for the fast reply. query seems to be the culprit, as it works with “normal” queries.

But can I really rely on put_dynamic_repo, as it is marked experimental? Or is there a chance that this functionality will be removed or changed?

5

It won’t be removed. It will stay in some sort. But the API may still change.