Nested forms, and associations across different database schemas

Phoenix Forum Questions / Help
,
1

I have a mutli-tenant application where each tenant gets their own schema, with a bunch of tables underneath it that I create during signup. In addition, I insert some records into tables underneath the public schema, so that I have a way of “mapping” unauthenticated/pre-authenticated requests from the web to the appropriate schema’s tables. One such public table is the “workspaces” table, which contains a subdomain column and a schema_name column. If a request is made to democompany.myapp.com, then I do a lookup and see that “democompany” subdomain’s schema name is “tenant_demo_company”, and use that as the prefix for subsequent lookups.

This works well, but I’m having issues with signup. Specifically, I want to allow the user to enter both their Organization info (e.g. company name) as well as their desired subdomain, on the same form, like the simplified one below:

controller:

def new(conn, _) do
  changeset = Organizations.Organization.create_organization_changeset(%Organization{workspace: %Workspace{}})
  render(conn, "new.html", changeset: changeset)
end

new.html.eex:

<%= form_for @changeset, signup_path(@conn, :create), fn f -> %>

  <%= text_input f, :name, class: "form-control", required: true, autofocus: true %>
  <%= label f, :organization_name %>
  <%= error_tag f, :name %>

  <%= inputs_for f, :workspace, fn w -> %>
    <%= text_input w, :subdomain, required: true %>
    <%= error_tag w, :subdomain %>
  <% end %>

<% end %>

Here’s where it gets hairy: I haven’t figured out a way to insert this data in a single transaction (to take advantage of automatic rollbacks) in a way that structures invalid changesets properly.

I first tried this:

def create_organization(attrs) do
  Ecto.Mutli.new()
  |> Ecto.Multi.run(:organization_with_workspace, fn(_repo, _result) ->
    %Organization{}
    |> Organization.create_organization_changeset(attrs)
    |> cast_assoc(:workspace, with: &Workspace.changeset/2)
    |> Repo.insert(prefix: TenantActions.build_prefix(tenant))
  end)
  ...
  |> Repo.transaction()
end

This failed completely, because Organization and Workspace tables exist under different db schemas, and Ecto tries to insert the Workspace into the tenant’s schema. This is despite the fact that I have @schema_prefix module attribute set for Workspace:

defmodule MyApp.Workspaces.Workspace do
  use Ecto.Schema

  @schema_prefix "public"

  schema "workspaces" do
    field :subdomain, :string
    field :name, :string
    belongs_to :organization, Organizations.Organization

    timestamps(type: :utc_datetime_usec)
  end
end

I then tried doing this:

Ecto.Multi.new()
|> Ecto.Multi.run(:organization, fn(_repo, _result) ->
  changeset = Organization.create_organization_changeset(%Organization{}, attrs)
  tenant = Ecto.Changeset.get_field(changeset, :slug)
  Repo.insert(changeset, prefix: TenantActions.build_prefix(tenant))
end)
|> Ecto.Multi.run(:workspace, fn(_repo, %{organization: organization}) ->
  attrs = %{subdomain: attrs["workspace"]["subdomain"], name: organization.name, tenant: organization.slug}

  %Workspace{}
  |> Workspace.changeset(attrs)
  |> put_assoc(:organization, organization)
  |> Repo.insert()
end)
 |> Repo.transaction()

This worked for the “happy path”, but failed for invalid changesets, because the resulting changeset had the workplace as the parent and organization as the child:

#Ecto.Changeset<
   action: :insert,
   changes: %{
     name: "Demo Company",
     organization: #Ecto.Changeset<action: :update, changes: %{}, errors: [],
      data: #MyApp.Organizations.Organization<>, valid?: true>,
     organization_id: 1,
     subdomain: "democompany",
     tenant: "demo_company_0"
   },
   errors: [
     unique_subdomains: {"That workspace URL is not available.",
      [constraint: :unique, constraint_name: "unique_subdomains"]}
   ],
   data: #MyApp.Workspaces.Workspace<>,
   valid?: false
 >,

How can I get these “inverted”, so that the following signup form renders the invalid changeset’s errors properly?

2

Hi,

I solved this by using an embedded schema for the sign up form and then passing through the validation errors.

I am not at a computer but will post some details when I get home (unless someone does).

Andrew

3

Yeah, I’d be interested in that solution. I’ve read that embedded schemas are mostly for “expanding” JSONB database columns.

4

Hi,

Sorry for the delay, a couple of busy days at work.

Before I show you what I did, I wanted to tell you that I used a similar model as you in my first iteration of the project but eventually removed most of it.

Originally I used subdomains for each Client with each subdomain mapping to a postgres schema. I handled Accounts, Credentials and Organisations under the public schema and linked the account to a user in the tenant schema to manage roles and permissions.

I wanted to have Users that potentially had access to two tenants and this introduced a similar problem to you (If I am understanding your issue correctly). Eventually I realised I didn’t need the subdomains at all (It was a nice visual cue but added little real value) as I was holding a list of tenants in the session and subsequently in the conn. I ended up removing the need to identify the Tenant from the login and registration process and instead if they only had access to one tenant sent them directly through, if more than one redirected to a page that listed them.

Because they were authenticated I gave them the option to add a tenant from the list page if they wanted to create another tenant. Alternatively, they could switch tenants from within the application from their account menu. Joining existing tenants is always done via invitation.

This added more screens for the User but significantly simplified my code, made it more testable and more robust. My screens for sign up and registration are very simple so low overhead for the User and it gives me the opportunity to choose the correct path.

However, originally for registration I used an embedded schema:

embedded_schema do
    field(:tenant_name, :string)
    field(:full_name, :string)
    field(:email, :string)
    field(:mobile, :string)
    field(:password, :string)
  end

  def changeset(registration, params \\ %{}) do
    registration
    |> cast(params, [:tenant_name, :full_name, :email, :mobile, :password])
    |> Tenant.validate()
    |> User.validate()
    |> Credential.validate()
  end

Each of the Tenant, User and Credential schemas work as normal. I then used a multi:

multi
    |> Ecto.Multi.run(:registration, __MODULE__, :validate, [params])
    |> Ecto.Multi.run(:user, User, :insert_user, [params])
    |> Ecto.Multi.run(:credential, Credential, :insert_credential, [params])
    |> Ecto.Multi.run(:organisation, Organisation, :insert_organisation, [params])
    |> Ecto.Multi.run(:association, __MODULE__, :associate_organisation_user, [params])
    |> Ecto.Multi.run(:tenant, __MODULE__, :create_tenancy_schema, [params])

If errors occur I capture them and put them into the registration changeset

{:error, _failed, changeset, %{registration: registration_changeset}} ->
        registration_changeset = %{
          registration_changeset
          | errors: changeset.errors,
            action: changeset.action
        }

        {:error, registration_changeset}

As I mentioned this is old code and is no longer a part of an active project, I have a feeling I also filtered the errors used pattern matching and inserted custom messages with a catch all for anything I hadn’t accounted for … but I can’t find the code.

I saw this talk last year at the Elixir Conf and it helped me start to think a little differently. Chris McCord has a talk online somewhere that goes through a similar breakdown.

I hope this helps, sorry if I have miss understood your issue.

Andrew