Referencing Another Schema During Migration for Foreign-Key Relationship

Hi all,

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
 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
10      timestamps()
11    end
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.

1 Like

Maybe using the :prefix option on references function? Like this:

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

PS.: triplex creator here, thanks for using it, hopefully you find it useful, let me know if you have any other question :wink:


Thanks, @kelvinst! I actually worked it out with help from our friends on Discord. Read the documentation and smacked my head instantly. I thought I’d leave the question up on here just in case it helps someone in the future.

Re: Triplex, so far so good! We did, however, encounter a lot of deprecation warnings when running the tests on 1.11.4 OTP 23 and OTP 24 (just an FYI). All tests passed though.

(Also, now I know who to bug when I run into problems… :wink:)

1 Like