How to make list in schema using MySQL as database?

While working with ecto schema connecting to Mysql as database I tried using list data type. I used {:array, :inner_type} for storing list into the database as per hex docs info .but it shows an error that MySQL doesn’t support array, so is there any solution?

Mysql doesn’t support list, that documentation is for postgresql I think. You can create an ecto custom type which will encode array as json and then save as string to mysql.

Some of ecto features are only supported by some adapters - MySQL in general does not support more advanced features, arrays are one of them (this is not a limitation of Ecto, but of MySQL). There are several options of emulating them - either serialising to JSON or simple comma separated string, or even having a separate table.


I had the same problem.
Decided to use an old plain one-to-many association instead. it’s a SQL database in the end.

1 Like

Thanks for your replies it is very helpful

As a supplement at 2021…

Ecto v3 with MySQL < 5.7.8 (don’t support JSON) :

  • use :text in migrations.
  • use :map or {:array, inner_type} at schema level, and Ecto will encode and decode it for you.

Ecto v3 with MySQL >= 5.7.8:

  • use :map or :json in migrations.
  • use :map or {:array, inner_type} at schema level.

I am using the :map in migration and in schema field :filed_name, {:array, :integer}
But when i query the table I am getting the below error
cannot load “[1,3,23]” as type {:array, :integer} for field :filed_name

is there anything i am doing wrong?

What is the version of MySQL that you are using?