Ecto and Postgres schemas

Hello!

I have 2 applications in umbrella:

  1. Security (Postgres schema “security”)
  2. Health (Postgres schema “health”)

In the 1st application I have a model Security.Auth.User:

defmodule Security.Auth.User do
  use Ecto.Schema

  @schema_prefix "security"

  schema "users" do
    field :username,              :string
    field :password,              :string, virtual: true
    field :password_confirmation, :string, virtual: true
    field :password_hash,         :string

    timestamps
  end

In the 2nd application I have a model Health.Patient:

defmodule Health.Patient do
  use Health.Web, :model

  @schema_prefix "health"

  schema "patients" do
    field :surname, :string
    field :name, :string
    field :patronymic_name, :string
    field :birth_date, Ecto.Date

    belongs_to :user, Security.Auth.User

    timestamps()
  end

Also, I have FK from health.patients (patient_id) to security.users (id):

defmodule Health.Repo.Migrations.AddColumnToPatient do
  use Ecto.Migration

  @schema_prefix "health"
  @security_schema_prefix "security"

  def up do
    execute "ALTER TABLE #{@schema_prefix}.patients
              ADD CONSTRAINT patients_user_id_fkey
              FOREIGN KEY (user_id) REFERENCES #{@security_schema_prefix}.users (id)
              MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION"
  end

  def down do
    execute "ALTER TABLE #{@schema_prefix}.patients
              DROP CONSTRAINT patients_user_id_fkey"
  end
end

Now I am trying to join this models:

import Ecto.Query

query = from p in Health.Patient,
  join: u in Security.Auth.User,
  on: p.user_id == u.id

Repo.all query

iex(5)> query = from p in Health.Patient,
...(5)>   join: u in Security.Auth.User,
...(5)>   on: p.user_id == u.id
#Ecto.Query<from p in Health.Patient, join: u in Security.Auth.User,
 on: p.user_id == u.id>

And getting error:

iex(6)> Repo.all query
[debug] QUERY ERROR source="patients" db=13.1ms
SELECT p0."id", p0."surname", p0."name", p0."patronymic_name", p0."birth_date", p0."user_id", p0."inserted_at", p0."updated_at" FROM "health"."patients" AS p0 INNER JOIN "health"."users" AS u1 ON p0."user_id" = u1."id" []
** (Postgrex.Error) ERROR (undefined_table): relation "health.users" does not exist
    (ecto) lib/ecto/adapters/sql.ex:429: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:127: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:40: Ecto.Repo.Queryable.all/4

Seems that Ecto does not take @schema_prefix from model to build SQL query.

Trying to use schema.table in query:

query = from p in "health.patients",
  join: u in "security.users",
  on: p.user_id == u.id,
  select: p.id

But it does not work, too:

iex(8)> Repo.all query
** (Postgrex.Error) ERROR (undefined_table): relation "health.patients" does not exist
[debug] QUERY ERROR source="health.patients" db=3.0ms
SELECT h0."id" FROM "health.patients" AS h0 INNER JOIN "security.users" AS s1 ON h0."user_id" = s1."id" []
    (ecto) lib/ecto/adapters/sql.ex:429: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:127: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:40: Ecto.Repo.Queryable.all/4

Because now Postgres thinks that “health.patients” is a table name, but not schema_name.table_name (health.patients).

Preloading data is working if prefix was specified:

 Patient |> Repo.get(1) |> Repo.preload(:user, prefix: :security)

Am I doing something wrong?
Why doesn’t Ecto use @schema_prefix from model?
Are fragments the only possible way to do such queries?

I appreciate any help on this questions.

Currently ecto queries are limited to working within a single prefix. This is a limitation we’d like to lift eventually, but it’s not clear how the API should look like. Fragments and raw queries are probably your best option if you need cross-schema querying.

2 Likes

Michał, thank you very much for clarifying this.

Is there any way to do this now in 2018?

Looks like it is under development: https://github.com/elixir-ecto/ecto/pull/2416

2 Likes