5
votes

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!

2

2 Answers

2
votes

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
0
votes

Postgrex has an example for a JSON extension in its Readme, which should do exactly what you want.