Reading an html file in an ecto migration

Question

I'm trying to read an html file from my priv/repo/templates and insert it as a string value to a table:

def change do
  consultant_engagement_html = File.read!(Application.app_dir(:enterprise, "priv/repo/templates") <> "/consultant_engagement.html")
  execute "INSERT INTO rapid_contract_templates(name, html, type, inserted_at, updated_at) VALUES('Consultant Engagement', 'consultant_engagement', '#{consultant_engagement_html}', '#{DateTime.utc_now}', '#{DateTime.utc_now}')"
end

However, I am getting this error when running the migration:

(Postgrex.Error) ERROR 42601 (syntax_error): syntax error at or near "written"

"written" is a word found inside the html file.

Answer 1

If the HTML has any single quote in it, it will mess up the escaping in the query, leading to an invalid syntax. The correct thing to do would be to use the proper Repo API for this:

MyApp.Repo.query! "INSERT INTO rapid_contract_templates(name, html, type, inserted_at, updated_at) VALUES('Consultant Engagement', 'consultant_engagement', $1, $2, $3)",
                  [consultant_engagement_html, DateTime.utc_now, DateTime.utc_now]

But it is generally discouraged to perform data loading inside migrations, as those are separate concerns. Perhaps using a seed file (if you are using Phoenix, one is included) would be a better approach?