Create table at runtime with Ecto

Question

According to this issue – Dynamic Models in Phoenix Framework – it is possible to dynamically create a model in Elixir using Module.create.

Is it also possible to create a table in the database for this model during runtime, i.e. without using Ecto migration files?

Every month I want to have a custom table to store data of a report. But I want the system to create this table when the user request the report (if it not exists). — Eduardo Manoel

Aleksei Matiushkin Aleksei Matiushkin · Accepted Answer · 2017-12-05T14:08:04

PostgreSQL and MySQL adapters for Ecto 2.0+ have an ability to execute raw SQL:

qry =
  """
  CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  """

res = Ecto.Adapters.SQL.query!(Repo, qry, [])

Ecto.Adapters.SQL.query/4.

Another (might be preferred) way would be to just delegate this to underlying mysql shell executable:

System.cmd("mysql", ["#{create_table_script}"])

Create table at runtime with Ecto

2 Answers