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

**URL:** <https://forum.elixirforum.com/t/executing-raw-sql-fails-with-cannot-insert-multiple-commands-into-a-prepared-statement/22909>\
**Category:** Questions\
**Tags:** ecto, databases\
**Created:** [June 4, 2019, 4:59am UTC](https://forum.elixirforum.com/t/executing-raw-sql-fails-with-cannot-insert-multiple-commands-into-a-prepared-statement/22909 "2019-06-04T04:59:09Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![mmmrrr](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/mmmrrr/32/12227_2.png) [@mmmrrr](https://forum.elixirforum.com/u/mmmrrr)\
**Post date:** [June 4, 2019, 4:59am UTC](https://forum.elixirforum.com/t/executing-raw-sql-fails-with-cannot-insert-multiple-commands-into-a-prepared-statement/22909/1 "2019-06-04T04:59:09Z")

</div>

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:

```elixir
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?

---

<div class="post-metadata">

**Author:** ![kip](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/kip/32/1440_2.png) [@kip](https://forum.elixirforum.com/u/kip)\
**Post date:** [June 4, 2019, 5:44am UTC](https://forum.elixirforum.com/t/executing-raw-sql-fails-with-cannot-insert-multiple-commands-into-a-prepared-statement/22909/2 "2019-06-04T05:44:32Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![kip](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/kip/32/1440_2.png) [@kip](https://forum.elixirforum.com/u/kip)\
**Post date:** [June 4, 2019, 5:50am UTC](https://forum.elixirforum.com/t/executing-raw-sql-fails-with-cannot-insert-multiple-commands-into-a-prepared-statement/22909/3 "2019-06-04T05:50:46Z")

</div>

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`.

---

<div class="post-metadata">

**Author:** ![mmmrrr](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/mmmrrr/32/12227_2.png) [@mmmrrr](https://forum.elixirforum.com/u/mmmrrr)\
**Post date:** [June 4, 2019, 5:58am UTC](https://forum.elixirforum.com/t/executing-raw-sql-fails-with-cannot-insert-multiple-commands-into-a-prepared-statement/22909/4 "2019-06-04T05:58:24Z")

</div>

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 😄

---

<div class="post-metadata">

**Author:** ![kip](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/kip/32/1440_2.png) [@kip](https://forum.elixirforum.com/u/kip)\
**Post date:** [June 4, 2019, 6:04am UTC](https://forum.elixirforum.com/t/executing-raw-sql-fails-with-cannot-insert-multiple-commands-into-a-prepared-statement/22909/5 "2019-06-04T06:04:06Z")

</div>

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:

```elixir
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

```

---

<div class="post-metadata">

**Author:** ![mmmrrr](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/mmmrrr/32/12227_2.png) [@mmmrrr](https://forum.elixirforum.com/u/mmmrrr)\
**Post date:** [June 4, 2019, 6:19am UTC](https://forum.elixirforum.com/t/executing-raw-sql-fails-with-cannot-insert-multiple-commands-into-a-prepared-statement/22909/6 "2019-06-04T06:19:22Z")

</div>

Awesome!

Thanks for the feedback. I will try that 🙂

---

<div class="post-metadata">

**Author:** ![mmmrrr](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/mmmrrr/32/12227_2.png) [@mmmrrr](https://forum.elixirforum.com/u/mmmrrr)\
**Post date:** [June 4, 2019, 11:18am UTC](https://forum.elixirforum.com/t/executing-raw-sql-fails-with-cannot-insert-multiple-commands-into-a-prepared-statement/22909/7 "2019-06-04T11:18:43Z")

</div>

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:

```elixir
  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:

```elixir
  def up do
    sql = file_reader()

    execute_sql_as_one_per_line(sql)
  end

```

---

<div class="post-metadata">

**Author:** ![NobbZ](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/nobbz/32/27235_2.png) [@NobbZ](https://forum.elixirforum.com/u/NobbZ)\
**Post date:** [June 4, 2019, 11:26am UTC](https://forum.elixirforum.com/t/executing-raw-sql-fails-with-cannot-insert-multiple-commands-into-a-prepared-statement/22909/8 "2019-06-04T11:26:38Z")

</div>

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

```elixir
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:

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

```

---

<div class="post-metadata">

**Author:** ![mmmrrr](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/mmmrrr/32/12227_2.png) [@mmmrrr](https://forum.elixirforum.com/u/mmmrrr)\
**Post date:** [June 5, 2019, 4:57am UTC](https://forum.elixirforum.com/t/executing-raw-sql-fails-with-cannot-insert-multiple-commands-into-a-prepared-statement/22909/9 "2019-06-05T04:57:47Z")

</div>

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 👍
