How to use Repo.transaction with raw sql queries with Ecto

ecto, elixir, phoenix-framework

Solution

I suggest you take a look at `Ecto.Multi`. It will allow you to name each query. It also stops the the pipeline if an earlier query fails.

defmodule UserServers do
  alias Ecto.Multi
  alias MyApp.Repo

  def insert(value1, value2, value3, value4) do
    raw_query = 
      ~s{INSERT INTO user_servers(user_id, server_id, server_type_code, position, active_flag, create_date, created_by)
      SELECT $1, $2, 'SERVER', (COALESCE(MAX(position), 0) + 1), 'Y', CURRENT_TIMESTAMP, $3
      FROM user_servers WHERE server_type_code = 'SERVER' and user_id = $4;}

    Multi.new
    |> Multi.run(:one, &query(&1, [value1, value2, value3, value4]))
    |> Multi.run(:two, &query(&1, [value1, value2, value3, value4]))
    |> Repo.transaction
  end

  def query(raw_query, values) do
    Ecto.Adapters.SQL.query(MyRepo, raw_query,values) 
  end
end

Problem

I am converting a Node.js app into Elixir and I am needing to use raw SQL queries within Ecto as I have some fairly complex SQL queries. I have about 10 that sit within a transaction. I am fairly new to Elixir as well and am trying to use Repo.transaction instead of trying to roll my own transactions. So I have about 10 of these: ``` raw_query = "INSERT INTO user_servers(user_id, server_id, server_type_code, position, active_flag, create_date, created_by) SELECT $1, $2, 'SERVER', (COALESCE(MAX(position), 0) + 1), 'Y', CURRENT_TIMESTAMP, $3 FROM user_servers WHERE server_type_code = 'SERVER' and user_id = $4;" Ecto.Adapters.SQL.query(MyRepo, raw_query, [value1, value2, value3, value4]) ``` If I do ``` Repo.transaction fn -> Ecto.Adapters.SQL.query(MyRepo, raw_query, [value1, value2, value3, value4]) Ecto.Adapters.SQL.query(MyRepo, raw_query, [value1, value2, value3, value4]) end ``` And one of them fails, will it auto-rollback any queries on a failure? Also is there a way to name those queries? For example, maybe I want my first query to be named user_servers so when the transaction comes back I can retrieve it with {:ok, :user_servers: results} like I can with Ecto.Multi?

Original source