With Ecto 2.0, there is support for Postgres schema or multiple databases for MySQL. What I am trying to do here is to use Postgres schema to achieve multi-tenancy, and of course using Ecto.

Disclaimer: This is mostly for my personal notes as I try to understand Ecto/Elixir better. There is also library called Apartmentex that you could use to do multi-tenancy.

This assumes the following model in web/models/guard.ex

defmodule Tenancy.Guard do  
  use Tenancy.Web, :model

  schema "guards" do
    field :name, :string
    field :position, :string

    timestamps()
  end

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:name, :position])
    |> validate_required([:name, :position])
  end
end  
Setup

You would also need to create your own schema by issuing CREATE SCHEMA "tenant_1"; from psql shell. I don't think Ecto has a built-in function to actually create a schema for you.

Then, run migration for your tenant by running mix ecto.migrate --prefix "tenant_1"

Fire up iex console by running iex -S mix phoenix.server.

First

These are the things that I do to actually insert/update records into a correct schema. Also, there are few things that are still confusing to me.

When defining a model, you could set module attribute @schema_prefix to tell Ecto that this table should be in postgres schema as defined in @schema_prefix. But for multi-tenancy purpose, @schema_prefix needs to be set dynamically - usually from subdomain i.e. tenant-1.example.com should use postgres schema for tenant-1. However, elixir module attribute is resolved at compile time, so you can't change that dynamically during run time.

Then, I found out about Ecto.put_meta/2 after looking through Ecto source code.

iex(13)> h Ecto.put_meta

def put_meta(struct, opts)                           

Returns a new struct with updated metadata.

It is possible to set:

  • :source - changes the struct query source
  • :prefix - changes the struct query prefix
  • :context - changes the struct meta context
  • :state - changes the struct state

I have no idea what :source is for.

Getting __meta__

Initially I thought I could update the changeset's __meta__, but Ecto.Changeset does not have __meta__ key. Ecto.Schema does!

iex> changeset = Guard.changeset(%Guard{name: "Abu", position: "Guard"}, %{})  
#Ecto.Changeset<action: nil, changes: %{}, errors: [], data: #Guard<>,
 valid?: true>

What we are interested in updating is in changset.data.

iex> guard_data = Ecto.put_meta(changeset.data, prefix: "tenant_1")  
%Tenancy.Guard{__meta__: #Ecto.Schema.Metadata<:built, "tenant_1", "guards">,
 id: nil, inserted_at: nil, name: "Abu", position: "Guard", updated_at: nil}

iex> changeset = %{changeset | data: guard_data}  
#Ecto.Changeset<action: nil, changes: %{}, errors: [], data: #Tenancy.Guard<>,
 valid?: true>

iex> changeset.data  
%Tenancy.Guard{__meta__: #Ecto.Schema.Metadata<:built, "tenant_1", "guards">,
 id: nil, inserted_at: nil, name: "Abu", position: "Guard", updated_at: nil}

iex> {:ok, guard} = Repo.insert(changeset)  
[debug] QUERY OK db=4.0ms
INSERT INTO "tenant_1"."guards" ("name","position","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" ["Abu", "Guard", {{2016, 11, 14}, {3, 26, 28, 0}}, {{2016, 11, 14}, {3, 26, 28, 0}}]  
{:ok,
 %Tenancy.Guard{__meta__: #Ecto.Schema.Metadata<:loaded, "tenant_1", "guards">,
  id: 1, inserted_at: #Ecto.DateTime<2016-11-14 03:26:28>, name: "Abu",
  position: "Guard", updated_at: #Ecto.DateTime<2016-11-14 03:26:28>}}

So, what I did was extract out the changeset.data because changeset.data is Ecto.Schema. Then update __meta__ to include :prefix option and create a new changeset. Then you could just insert the changeset and it will be added to the correct postgres schema.

You could also do it more succinctly in Elixir, something like this:

model = %Guard{}  
|> Ecto.put_meta(prefix: "tenant_1")

Guard.changeset(model, %{name: "Abu", position: "Guard"})  
|> Repo.insert

Future

From ecto's repo, this issue https://github.com/elixir-ecto/ecto/issues/1675 seems to standardize the Ecto.Repo API to take option for :prefix, which is nice.

Then you could actually just do something like,

iex> {:ok, guard} = Repo.insert(changeset_without_meta_prefix, prefix: "tenant_1")  
[debug] QUERY OK db=6.3ms
INSERT INTO "guards" ("name","position","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" ["Abu", "Guard", {{2016, 11, 14}, {3, 10, 54, 0}}, {{2016, 11, 14}, {3, 10, 54, 0}}]  
{:ok,
 %Guard{__meta__: #Ecto.Schema.Metadata<:loaded, "guards">, id: 1,
  inserted_at: #Ecto.DateTime<2016-11-14 03:10:54>, name: "Abu",
  position: "Guard", updated_at: #Ecto.DateTime<2016-11-14 03:10:54>}}

Then the record will be added to the correct table in tenant_1 schema. But of course, the above doesn't work because this feature is only in master branch at this point in time.

Part 2

What I want to do next is automatically issue query for creating postgres schema when new tenant is created. It also should delete the schema if tenant is deleted. Then, create a mix task to handle migrations.