Using ecto to store custom information

Question

I am re-implementing an application I originally wrote in Rails in Phoenix in which users can create custom fields using PostgreSQL's JSONB record type. As an example, we have the following (simplified representation) schema:

Client

ID (int)
Client Type ID (int)
Name (string)
Info (jsonb)

Client Type

ID (int)
Name (string)

Custom field definition

ID (int)
Client Type ID (int)
Key (string)
Label (string)

In Rails, ActiveRecord magically converts JSONB to and from a hash, which allows me to use JSONB to very easily to store a schemaless set of custom fields.

For example, each client type can define different custom field, and then when I display the information to the user, I loop through the definitions to get the keys, which I then use to get the data out of the JSONB document.

I was trying to figure out a way to accomplish this using Ecto, and it looks like I should be looking at an embedded schema (I saw some good info here), however I don't think from looking at it that I can define a custom amount of fields to this at run-time, can I?

I was just looking for some advice on this, as so far this is the only real road block I have come across that isn't solved almost immediately.

Thanks again, I appreciate it!

Mylan Connolly Mylan Connolly · Accepted Answer · 2015-09-22T20:51:23

I was able to resolve this by changing my data structure a bit. The custom fields are now a JSONB array, which allows me to do an embeds_many relationship which is pretty elegant. I actually have not read the documentation at the link (http://blog.plataformatec.com.br/2015/08/working-with-ecto-associations-and-embeds/) closely enough, and after I did I realized my data wasn't really structured enough.

So, instead of having a JSONB column that looks like

{
  "name":"Something",
  "address":"123 Fake St"
}

it looks like

[
  {
    "field":"name",
    "value":"Something"
  },
  {
    "field":"address",
    "value":"123 Fake St"
  },
]

This seems to be the best option for my use case, however it does take up a lot more space (since Ecto adds a required ID field to each object behind the scenes to ensure integrity).

After changing to this structure, I simply created a model and associated them per the link. In a nutshell, it looks like this:

defmodule UserManager.CustomField do
  use Ecto.Model

  embedded_schema do
    field :field
    field :value
  end

  @required_fields ~w(field value)
  @optional_fields ~w()

  @doc """
  Creates a changeset based on the `model` and `params`.

  If no params are provided, an invalid changeset is returned
  with no validation performed.
  """
  def changeset(model, params \\ :empty) do
    model |> cast(params, @required_fields, @optional_fields)
  end
end

defmodule UserManager.Client do
  # ...
  schema "clients" do
    # ...
    embeds_many :custom_fields, UserManager.CustomField
    # ...
  end
  # ...
end

Using ecto to store custom information

2 Answers