Ecto Referencing Another Schema During Migration for Foreign-Key Relationship

Question

We are working with a schema-based multi-tenancy database. We are on an Elixir stack, using Postgres, Ecto, and Triplex within a Phoenix Framework project.

We are using the default schema public to store common data, such as users and organisations. In particular, we have within the Organisations table a tenant_prefix column which we use to map a user to their tenancy.

Within a tenancy, we tenant-specific tables. As an example, we have a Products table. When a new organisation is created, we use Triplex to create the schema and run tenant migrations, which create the tenant-specific tables such as Products.

As a visual, the database looks like this:

- app_database
  - public
    - users
    - organisations
    - organisations_users

  - tenant1
    - products
    - (other tables...)

  - tenant2
     - products
     - (other tables...)

The Products migration looks like this.

 1 defmodule App.Repo.Migrations.CreateProducts do
 2  use Ecto.Migration
 3
 4  def change do
 5    create table(:products) do
 6      add :title, :string
 7      add :description, :string
 8      add :organisation_id, references(:organisations, on_delete: :nothing), null: false
 9
10      timestamps()
11    end
12
13    create index(:products, [:organisation_id])
14  end
15 end

Right now, it is failing to run because of the Line 8. The erorr that is reported is: ERROR 42P01 (undefined_table) relation "59ef85c702d24d0fac5c7e425d0d3d44.organisations" does not exist

The tenant prefix is a UUID.

So to summarise, we are wondering how to reference the public.organisations table to define the foreign key relationship in tenant.products.

Answer 1

I am not sure if it helps but I think that you should be using prefix option with your references. You can checkout the docs for further references.

https://hexdocs.pm/ecto_sql/Ecto.Migration.html#references/2

As of my understanding line 8 should be something like this

add :organisation_id, references(:organisations, on_delete: :nothing, prefix: "public"), null: false

As for the syntax, use :public if "public" results in a syntax error.

Why you need this?

Normally when prefix defaults to nil and in that case ecto certainly prefixes your current schema name with the table name, which is happening in your case. By setting this prefix option you can set a custom value based on your requirements.

Please do comment if it works. Since I am also curious... 😅