Ecto migrations: is it possible to pass a variable value

Questions / Help
,
1

Hi,

I have a multi-tenant system and I use Postgres Schemas to isolate data for each tenant. When a tenant signs up I run a set of migrations using the Migrator. I would like to create a MATERIALIZED VIEW for each Tenant that joins a table from the tenant’s schema with a public schema table.

defmodule Migrations.AddUserView do
  use Ecto.Migration

  def up do
    execute """
    CREATE MATERIALIZED VIEW users 
    AS SELECT fullname, email, email_verified, mobile, mobile_verified, roles.display 
    FROM PUBLIC.users, TENANT.tenant_users, TENANT.roles WHERE PUBLIC. users.id = 
    TENANT.tenant_users.user_id 
    AND  PUBLIC.roles.id =TENANT.tenant_users.role_id;
    """

    
  end

  def down do
    execute "DROP MATERIALIZED VIEW users;"
  end
end

To create the VIEW I need to pass the schema name to the migration so it can access the correct tables. Does anyone know if this is possible?

Regards,

Andrew

2

Using the triple double-quotes syntax you already use makes it simple.

Just replace the hardcoded value with a string interpolation like so: #{users}, for example. Of course, the variable users must exist before that expression gets evaluated.

3

The prefix() function is probably what you’re looking for. Have a look at this forum thread.

3 Likes
4

Fantastic, exactly what I needed. Thanks

1 Like