Ecto.Migration table references to table in another schema

Questions / Help
1

Hello!

I am using Ecto 2.0 with Postgres and I need to have two tables in different schemas:

  • security.roles
  • public.users

Table public.users should referenced security.roles.

I am able to create migration with custom schema:

def change do
  create table(:roles, prefix: :security) do
    add :name, :string, null: false 

    timestamps
  end 

  create unique_index(:roles, [:name], prefix: :security)
end

There is information about MySQL in Ecto.Migration:

Note: if using MySQL with a prefixed table, you must use the same prefix for the references since cross database references are not supported.

But I can’t find about Postgres. I have tried:

def change do
  create table(:users) do
    add :name, :string
    add :role_id, references(:roles, prefix: :security) 

    timestamps()
  end
  create unique_index(:users, [:name])  

end

And this returns:

** (Postgrex.Error) ERROR (undefined_table): relation "roles" does not exist

And now I am stuck.

2 Likes
2

I can do this:

def change do
  create table(:users) do
    add :name, :string
    add :role_id, :integer  

    timestamps()
  end
  execute "alter table users
            add constraint users_role_id_fkey foreign key (role_id)
            references security.roles (id)
            match simple on update no action on delete no action"
  create unique_index(:users, [:name])
  create index(:users, [:role_id])
end

But it is ugly…

3 Likes
3

And you would need to put the execute in up/down functions instead.

3 Likes
4

Couldn’t you try to do it manually in Postgres so you could check how it chooses the prefixes?

2 Likes
5

Of course, I am putting:

def change do
end

to migration file :slight_smile:

Or you mean I should do it in up/down to make it work with migrate/rollback?

2 Likes
6

I am able to do it manually:

alter table users
  add constraint users_role_id_fkey foreign key (role_id)
  references security.roles (id)
  match simple on update no action on delete no action
3 Likes
7

execute is not a ‘changeable’ command and will fail if rollback is run. Have to put an execute in up and an execute that reverses it in down (or nothing in down if it does not need to be reversed, have to only have it in up though).

2 Likes
8

@OvermindDL1, thanks for mentioning!

I made it for database schema migration:

defmodule App.Repo.Migrations.CreateSchema do
  use Ecto.Migration    

  @schema_prefix Application.fetch_env!(:app, :schema_prefix)   

  def up do
    execute "create schema #{@schema_prefix}"
  end   

  def down do
    execute "drop schema #{@schema_prefix}"
  end
end

With FK, I guess it will be dropped with table during rollback.

3 Likes
9

You cannot do this in any Ecto public API.
I found that José Valim commented:

References no longer support prefix because we cannot make it work across different database vendors. They always always point to something in the same prefix by default.

in github discussion.
So you have two ways:

  1. Find or create and include as dependency an PostgreSQL Ecto extension that supports missing PostgreSQL only features.
  2. Use execute command like you pointed already.
2 Likes