sharing postgrex link between different handlers/keeping postgrex link as state using plug

Questions / Help
, ,
1

Hello,

I’m building a small REST API using Bandit and Plug, with Postgrex to talk to the database.

I currently have my code set up so each handler will call Postgrex.start_link, get a pid, and then call Postgrex.query! with that pid to interact with the database.

I have two questions:

Is it possible to have just one postgrex link shared between all handlers? Like some sort of shared state that the handlers can use to all use the same pid to interact with the database?

If so, is it better to have one shared pid for all handlers, or create a new link to get a new pid with each request?

2

:wave: @achenet

Postgrex.start_link starts a pool of connections that can be named and safely accessed from any process.

iex(1)> Mix.install [:postgrex]
:ok

iex(2)> Postgrex.start_link(pool_size: 5, name: MyPool, database: "example", username: "postgres", password: "postgres", hostname: "localhost", port: 5432)
{:ok, #PID<0.202.0>}

iex(3)> Postgrex.query!(MyPool, "select 1", [], [])
%Postgrex.Result{
  command: :select,
  columns: ["?column?"],
  rows: [[1]],
  num_rows: 1,
  connection_id: 55,
  messages: []
}

If so, is it better to have one shared pid for all handlers, or create a new link to get a new pid with each request?

Depends on your use case. But usually, for small requests that happen often, it’s more efficient to reuse an existing pool of connections.

3

hi @ruslandoga

Thank you very much for your response :slight_smile:

would you also know how to pass the pool to a plug router? Like is there some way to init the router so it creates the pool, and then have each of the handlers call the pool?

4

Typically the pool is started by the root supervisor and if it’s named, it can be called from any process processing a Plug pipeline. Check out the default structure of a mix phx.new project. It uses Ecto repos instead of bare Postgrex but the idea is the same: this child would be replaced with something like {Postgrex, config}, where config is what’s passed to Postgrex.start_link.

5

okay, thanks for the tip, I’ll check out the structure of a mix phx.new project and try to adapt that :slight_smile: