Executing raw SQL fails with `cannot insert multiple commands into a prepared statement`

By default Ecto uses prepared statements (this is like a precompiled form of the statement which is the cached so that reuse is materially faster).

There is an option prepare: :unnamed which I think goes on the Repo configuration (its a Postgrex adapter option) but I can’t recall and I’m in China this week so googling isn’t an option.

Sorry its an incomplete answer but hopefully thats enough to get you started…

On reflection, I suspect this won’t go either because the statements are still prepared and, as the message says, you can’t have more than one.

Indeed I recall having to split SQL statements at the ; in one use case and passing them one-by-one.

This is primarily a Postegrex issue, not Ecto.

Hey @kip
Thanks for the input and taking the time.

In principle this shouldn’t be a problem to execute the statements one by one. This specific migration does not need to execute fast.

Just to clearify: I could split my SQL statements into a list and then call execute SUBQUERY1, execute SUBQUERY2, etc. in the same up/down statement, right?

This sounds a lot like “macro” to me

Now I’ve paid a little more attention… I believe the issue is that you can’t create the Enum in the same transaction as using it.

Typically what I do in this case is actually prepare two migrations: one to create the Enum and the other to create the table.

However should be perfectly ok to:

def up do
  execute "CREATE TYPE UState AS ENUM (\"state1\", \"state2\");"
  execute """
    CREATE TABLE IF NOT EXISTS Users
      (userEmail VARCHAR(320) NOT NULL PRIMARY KEY,
      state UState NOT NULL);
  """
end

Awesome!

Thanks for the feedback. I will try that

Great! That worked. It needed a little cleanup in the sql files in order to work, but it does now.

For anyone interested: If you have a List/Array of sql statements, you can use a simple macro like:

  defmacro execute_sql_as_one_per_line(sql) do
    quote do
      Enum.each(unquote(sql), fn statement ->
        execute(statement)
      end)
    end
  end

in your up migration:

  def up do
    sql = file_reader()

    execute_sql_as_one_per_line(sql)
  end

Why a macro at all? If you had written it as a function, then there would be no noeed to quote and unquote…

def up do
  execute_sql_as_one_per_line(file_reader())
end

def execute_sql_as_one_per_line(sql_list) do
  Enum.each(sql_list, &execute/1)
end

Or even simpler, have it inline:

def up do
  Enum.each(file_reader(), &execute/1)
end

Hm. Good point.

I somehow assumed the execute statement would not allow to be called at runtime… now that I think of it, it’s probably stupid

Thanks for the input