Ecto SaaS Multi Tenant with Postgres - Create View

Questions / Help
1

I’m using Apartmentex to do basic tenant handle with Postgres Schemas.
Is it possible to dynamic append prefix inside an execute command to create a view for each tenant?
Is there any option with Ecto / Apartmentex to achieve this?

defmodule App.Repo.TenantMigrations.CreateSearchObjectsView do
  use Ecto.Migration

  def up do
    execute """
    CREATE VIEW search_objects_view AS
      select 'templates_account' as type, id, name from templates
      union all
      select 'templates_global' as type, id, name from public.templates
    ;
    """
  end

  def down do
    execute "DROP VIEW search_objects_view;"
  end
end
2

Not familiar with Apartmentex but you can just generate SQL that has CREATE VIEW for each schema. You can even wrapp all of them in a transaction since PG supports transactional DDL.

3

Just found a solution.
Add #{prefix()} to tenant objects.

defmodule App.Repo.TenantMigrations.CreateSearchObjectsView do
  use Ecto.Migration

  def up do
    execute """
    CREATE VIEW #{prefix()}.search_objects_view AS
      select 'templates_account' as type, id, name from #{prefix()}.templates
      union all
      select 'templates_global' as type, id, name from public.templates
    ;
    """
  end

  def down do
    execute "DROP VIEW #{prefix()}.search_objects_view;"
  end
end
1 Like