Change Postgres search_path with Ecto

Question

I'd like to use a specific Postgres schema for my Phoenix app.

I tried to achieve this with Ecto.Repo.after_connect/1 callback but it seems to recursively create new db connections about 10 times before timing out.

Here is my repo file :

defmodule MyApp.Repo do

  use Ecto.Repo, otp_app: :my_app
  use Scrivener, page_size: 20

  def after_connect(_pid) do
    IO.puts "after_connect"
    Ecto.Adapters.SQL.query(MyApp.Repo, "SET search_path TO 'my_app';", [])
  end

end

If you want to do that permanently, you can change the search path for the user using alter user set ... — user330315
– user330315, Commented Feb 1, 2016 at 13:07
I don't want to. My purpose is to share a single Heroku Postgres database between 3 different apps (each one has a schema) — Chris
– Chris, Commented Feb 1, 2016 at 13:22
Then why don't you create three different users, each one with the specific schema as their default search_path? — user330315
– user330315, Commented Feb 1, 2016 at 13:25
Because I can't. Heroku provides a single database with a single user. — Chris
– Chris, Commented Feb 1, 2016 at 13:28
I don't think your use case is a good one, but the question is still valid. — sevenseacat
– sevenseacat, Commented Feb 3, 2016 at 14:53

Kip · Accepted Answer · 2016-03-18 02:26:53Z

7

I think the timeout happens because after_connect is still too early in ecto's setup cycle. Using ecto 2 (still in beta) the following works, whether it works in ecto 1 would depend on whether you get the connection as a parameter in after_connection).

In your repo:

defmodule Et.Repo do
  use Ecto.Repo, otp_app: :et

  def set_search_path(conn, path) do
    {:ok, _result} = Postgrex.query(conn, "SET search_path=#{path}", [])
  end
end

In your config.exs file:

config :et, Et.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: "......",
  username: "......",
  hostname: "localhost",
  after_connect: {Et.Repo, :set_search_path, ["app,public,extensions"]}

Hope that helps, --Kip

edited Mar 18, 2016 at 2:26

answered Mar 18, 2016 at 2:21

Kip

7345 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fernando Gonçalves · Accepted Answer · 2016-11-28 10:25:27Z

4

+50

I had the same issue, and the first solution I have found was add the prefix (called schema, in postgres) in every access to my apps's Repo module. But in the recent versions of ecto and phoenix_ecto, you can modify the postgres's schema of your model just adding the attribute @schema_prefix "your_schema".

Example:

defmodule YourApp.YourModel do
  use YourApp.Web, :model

  @schema_prefix "your_schema"
  schema "your_model" do
    field :name, :string
  end

  # more stuff...
end

You can check the discussion about this feature, and another ways to solve this issue, at ecto's github: https://github.com/elixir-lang/ecto/issues/978

PS: This solve the database access for your model, but for other regular queries, you would specify prefix at your query:

%{query | prefix: "your_schema"}

I hope I've helped!

edited Nov 28, 2016 at 10:25

answered Feb 8, 2016 at 14:31

Fernando Gonçalves

936 bronze badges

2 Comments

Chris Over a year ago

Thanks Fernando. It's the best answer so far, but I was looking for a less obtrusive way to switch schema. I will go with this for now.

Fernando Gonçalves Over a year ago

It's the best solution I've found until now :)

Collectives™ on Stack Overflow

Change Postgres search_path with Ecto

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related