Trying to use Adapters.SQL.Sandbox in non test environment

Hi everyone, I have a special case where some endpoints should decide to commit transactions or not to the database according to some boolean parameter that I pass along. These endpoints are already implemented so I’m looking for a way to do it without having to change a lot of things.

The obvious thing to do I guess is to wrap these functions into transactions (although I’m not sure this works correctly when using Multis) and then rollback the transaction conditionally when the boolean parameter is false.

The potential alternative that I wanted to ask about is that I created a replica of the Ecto.Repo that uses Ecto.Adapters.SQL.Sandbox pool instead and added a plug that explicitly checkout/checkin the sandbox database connection and now all that needs to be changed in these endpoints are just what Repo to actually use.

So my question is can Ecto.Adapters.SQL.Sandbox be relied on in non test environments, there is no example or anything mentioned about it in the docs. Would it create an obvious issue that I’m currently missing? Thank you!

You are asking several things and I am only going to address this one:

Can you give us a few examples and demonstrate your struggle with this approach?

I have a special case where some endpoints should decide to commit transactions or not to the database according to some boolean parameter that I pass along

Are you saying there is a boolean value that is decided before the transaction begins and that this value will control whether or not the transaction is committed? i.e. you might start a transaction knowing you want to rollback no matter what?

1 Like

This should work fine, the sandbox has no idea about “tests” specifically. However, as you note, you’ll need to switch which repo you’re using.

1 Like

I‘d actually suggest not using the sandbox for production. I don‘t see why you could start a plain old transaction in the place you‘d check out the sandbox connection. Or model things even more explicitly.

1 Like

Yes exactly, this service is a rewrite of another one and for testing purposes I want to make sure that the new endpoints are working correctly, basically succeeding a transaction that eventually get rolled back and returning success responses as if it actually got committed. (This is exactly what happens when I use Ecto.Adapters.SQL.Sandbox and thus my question)

Unless I’m missing something, the issue that I find with this approach is that a rollback will error out the transaction and thus the entire response.
I tried to have a custom transact method in my Repo this way:

  def transact(multi, commit_flag \\ true, opts \\ []) do
    transaction(
      fn repo ->
        repo.transaction(multi)
        |> case do
          {:ok, result} ->
            unless commit_flag, do: repo.rollback("Commit is off")
            {:ok, result}

          {:error, reason} ->
            repo.rollback(reason)
        end
      end,
      opts
    )
  end

The thing is once you call rollback, you can’t have the {:ok, result} returned anymore.

Instead of replaceing the transaction calls you already have leave them as is and wrap everything you want to work within another transaction that you roll back. Everything within should just work as before.

Wouldn’t that produce the same issue? If we take an example of a method in a context:

  def create_user(commit_flag) do
    Repo.transaction(fn repo ->
      Multi.new()
      |> Multi.insert(:user, %User{email: "example@example.com"})
      |> repo.transaction()
      |> case do
        {:ok, %{user: user}} ->
          unless commit_flag, do: repo.rollback("Commit is off")
          {:ok, user}

        {:error, _, error, _} ->
          {:error, error}
      end
    end)
  end

This will still return {:error, rollback_reason} instead of {:ok, user}.

At some point you need to deal with the fact that your rollback, but if you do it far enough up the stack (e.g. in the controller) it shouldn’t really be much work. You’d need to handle checkin out/check in the sandbox connection as well in some place.

1 Like

Yeah I would need to handle checkin/checkout and dynamically switching repos so I guess you are right, I have to handle the fact that I’m rolling back the transaction at some point. It’s just that this is going to be a very short lived solution and I don’t want it to have a big impact on how things are already structured.

As @LostKobrakai said, at one point you have to deal with it before sending a Phoenix response but that’s easier to do when you already have a wrapper. You would have to deal with it regardless of your approach so this is not a drawback to what has been offered as a solution in particular.

That definitely makes sense, thank you for the clarification.