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
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.
