Using Elixir + Ecto when the table definition is considered 'user data'

Hi all,
I’m building a ‘database builder’ application, where users can create their own postgres tables and columns, and then edit the data. I’d really love to use Elixir for this, but I’m starting to think this use case might simply not be compatible with the thing I love most about working in Elixir: Ecto.

I currently have a Table schema and a Column schema, backed by database views on top of Postgres’ pg_catalog.

For getting and editing the data, my first thought was that I’d use the pg_catalog information (name/type/constraints) to dynamically generate an Ecto.Schema module (or at least a struct and a changeset function) for each table. But then I realised that would involve creating atoms for each column name. And since column names are effectively “user data” in this application, that would be a bad idea, from what I understand.

The trouble is, I’m not sure there’s a way to use Ecto without atom field names (apart from sending it raw sql):

A basic select statement expects field names to be atoms:

iex(31)> column_names = ["id", "email", "hashed_password", "confirmed_at", "inserted_at", "updated_at"]

iex(32)> query = from fragment("?", literal(^table_name)), select: ^column_names
** (ArgumentError) expected a list of fields in `select/2` inside `select`, got: `["id", "email", "hashed_password", "confirmed_at", "inserted_at", "updated_at"]`
    (ecto 3.10.3) lib/ecto/query/builder/select.ex:175: Ecto.Query.Builder.Select.fields!/2
    (ecto 3.10.3) lib/ecto/query/builder/select.ex:219:!/5
    iex:32: (file)

I was able to work around this using dynamic and fragment (thanks @Santi!):

iex(32)> select =, &{&1, dynamic(fragment("?", literal(^&1)))})

iex(33)> query = from fragment("?", literal(^table_name)), select: ^select

The bigger problem, though, is that the cast and validate_* functions all expect atom fields

iex(36)> target = %{"name" => nil, "email" => nil, "age" => nil}
%{"age" => nil, "email" => nil, "name" => nil}

iex(37)> types = %{"name" => :string, "email" => :string, "age" => :integer}
%{"age" => :integer, "email" => :string, "name" => :string}

iex(38)> params = %{"name" => "John", "email" => "", "age" => "30"}
%{"age" => "30", "email" => "", "name" => "John"}

iex(39)> cast({target, types}, params, ["name", "email", "age"])
** (ArgumentError) cast/3 expects a list of atom keys, got key: `"name"`

iex(39)> cast({target, types}, params, [:name, :email, :age])
** (ArgumentError) unknown field `:name` given to cast. Either the field does not exist or it is a :through association (which are read-only). The known fields are: "age", "email", "name"

Indeed cast explicitly rejects string keys (though I can’t quite make out why), and I’m not sure there’s a work around short of reimplementing Ecto.Changeset in a custom module.

Is there a way around this? If not, is there a safe way to allow user-specified column names to be converted to atoms? (e.g. limit the number of columns allowed)

That’s a known limitation of ecto. It could probably be changed, but would require a lot of effort to apply to ecto and related libraries.

I don’t think Ecto.Changeset is particularly well suited for the usecase of user defined types. I’d love if it would allow string keys as an option, but mixed keys are a footgun waiting to go off and I also think string keyed data doesn’t suite changesets design goals super well.

You can work around that by keeping a separate lookup table between your user defined fields and some known/predefined atom keys and manually mapping between those.

Edit: My elixir conf eu talk is not yet published, but that one might be interesting to you.

1 Like