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: Ecto.Query.Builder.Select.select!/5
iex:32: (file)
I was able to work around this using dynamic and fragment (thanks @Santi!):
iex(32)> select = Map.new(column_names, &{&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" => "john@doe.com", "age" => "30"}
%{"age" => "30", "email" => "john@doe.com", "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)
