Insert nested ecto record into two different postgres schemas

Valian

It’s my first post on this forum, so hi everyone! I’m coming from Python / Django background but been heavily looking into Elixir for past 2-3 years. I decided to use Phoenix in my newest project. I have a question I can’t resolve by myself in a clean way and I can’t find an answer.

I’m building a multi-tenant application. Some tables are in public schema, and some are in tenant-specific schemas. I’m using :prefix to work with it, pretty standard stuff. In tests I’m using factories pattern described here

The problem boils down to inserting nested records into multiple schemas. Example:

game = %Myapp.Game{name: "test"}  # Game has @schema_prefix "public"
session = %Myapp.GameSession{game: game}
Repo.insert!(session, prefix: "tenant")

Error:

** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "tenant.game" does not exist

I understand why it happens, prefix option to Repo.insert! has priority over @schema_prefix. I tried to use game = %Myapp.Game{name: "test"} |> Repo.put_meta(prefix: "public") but it changed nothing. The only workaround I found is to insert game separately and then pass game_id to the session. Still, this gets complex quickly since as I said I’m using factory pattern with helper, and it forces me to provide not only build(:type) but also insert(:type) each time.

  def insert!(factory_name, attributes) do
    factory_name |> build(attributes) |> Repo.insert!
  end

So ultimately my question: is there a way to set prefix for unsaved nested record, so it will be honored by Repo.insert!?

4 comments

#ecto

0 1373 4

2022-02-01 22:57:56 UTC

Showing Posts 1 to 4

Show Best Posts
Show All (oldest first)
Show All (newest first)

aziz

Hi and welcome!

Did you try this yet?

game = %Myapp.Game{name: "test"}  # Game has @schema_prefix "public"
session = %Myapp.GameSession{game: game} |> Repo.put_meta(prefix: "tenant")
Repo.insert!(session)

My guess is it won’t work because the docs say that the parent schema’s prefix will override all its childrens’ prefix.

:prefix - The prefix to run the query on (such as the schema path in Postgres or the database in MySQL). This overrides the prefix set in the query and any @schema_prefix set any schemas. Also, the @schema_prefix for the parent record will override all default @schema_prefixs set in any child schemas for associations.

Maybe it’s a limitation in Ecto that should be discussed given your case.

Post #1

Valian

It works!

So it looks like instead of using Repo.insert!(prefix: prefix) I need to use Repo.insert!() and set prefix in individual nested models.

It’s counter-intuitive - instead of setting a well-known “public” prefix for shared models, and specifying runtime-determined tenant prefix in insert, I have to avoid using insert! prefix entirely, otherwise it will override everything set in individual associations…

I think prefix in insert! shouldn’t change associated prefixes, if set (either through @schema_prefix or Ecto.put_meta). Do you think it’s worth discussing it? If yes, should I do it here or in a github issue?

Post #2

aziz

Ha! That’s great! Didn’t expect that really.

We should probably discuss it here first. I’m not sure but I think it’s more intuitive that Repo.insert!(x, prefix: prefix) would override the prefix for all structs. After all, if you don’t want that behaviour then you simply don’t pass the option. If it were the other way around and it didn’t override all the prefixes, then I guess we’d have to invent another setting like override_prefixes: true in order to have them overridden in case we actually meant to do that. I don’t know if that’s how @josevalim thinks and if that was his design decision but maybe he can comment shortly to help clarify the reasoning behind this.

PS.: Could you pls mark my post above as the solution?

Post #3

Valian

I’ve created an issue in ecto github Saving nested records with prefixed multitenancy · Issue #3827 · elixir-ecto/ecto · GitHub.

Jose replied almost immediately, explained why it’s working in the current way and updated docs to make it clear I truly love that community, amazing!

Post #4

— All posts loaded —