Context Multitenancy for sqlite

[firesidewing]

Was spinning up a project and was going to do a database per organization with sqlite, as that combination seems very appealing for my use case but I got

** (Spark.Error.DslError) multitenancy -> strategy:
  Data layer does not support multitenancy

Postgres just seems too heavy weight for what i want to do, and i don't really want to have to deal with setting up a cloud database instance, was hoping to just keep it simple with sqlite. Was wondering if this is on the roadmap at all?

[zachdaniel]

Per-database multi tenancy is something we'd like to support both in postgres & sqlite, but at the moment it's something you'd effectively need to roll yourself. You can set the repo in the context:

defmodule YourChange do
  use Ash.Resource.Change

  def change(changeset, _, _) do
    Ash.Changeset.set_context(changeset, %{data_layer: %{repo: compute_repo(changeset.tenant)}})
  end

  def atomic(changeset, opts, context) do
    {:ok, change(changeset, opts, context)}
  end
end

defmodule YourPreparation do
  use Ash.Resource.Preparation

  def prepare(query, _, _) do
    Ash.Query.set_context(query, %{data_layer: %{repo: compute_repo(query.tenant)}})
  end
end

You could then add this to each resource's global change & preparation blocks:

changes do
  change YourChange, on: [:create, :update, :destroy] # destroy is off by default
end

preparations do
  prepare YourPreparation
end

[C-Sinclair]

Opened #224, which implements this: strategy :context on AshSqlite, meaning one database file per tenant.

Worth flagging for anyone who finds this issue first — the workaround suggested above does not work. Setting %{data_layer: %{repo: ...}} in a change and a preparation selects between repo modules: both the read path and the write path invoke the result as a module, so passing a repo instance raises ArgumentError: Modules (the first argument of apply) must always be an atom. Database-per-tenant needs an instance, which is put_dynamic_repo/1 and is per-process.

#224 adds a tenant_binder instead, which the data layer calls once per statement — including the paths a caller cannot wrap from outside, like Ash.count/2 and atomic updates. It is stacked on #223 (transactions) and should merge second.