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

Questions / Help
,
1

I need to include multiple legacy migrations (written in plain SQL) in an ecto migration.
The current legacy application relies heavily on custom ENUM types in postgresql.

When I try to run the existing migrations (concatenated as a string) via execute my_combined_sql I will get the error cannot insert multiple commands into a prepared statement.

The minimal example of a failing SQL file is:

CREATE TYPE UState AS ENUM ("state1", "state2");

CREATE TABLE IF NOT EXISTS Users
  (userEmail VARCHAR(320) NOT NULL PRIMARY KEY,
  state UState NOT NULL);

Is there a way to do this with ecto, or do I need another process to apply the legacy migrations to the database?

2

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…

1 Like
3

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.

4

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 :smile:

5

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
3 Likes
6

Awesome!

Thanks for the feedback. I will try that :slight_smile:

7

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
2 Likes
8

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
3 Likes
9

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 :man_shrugging:

Thanks for the input :+1: