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
As a visual, the database looks like this:
- app_database - public - users - organisations - organisations_users - tenant1 - products - (other tables...) - tenant2 - products - (other tables...)
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