Phoenix how to disabled Ecto sanbox in test- Test without sandbox

In previous Phoenix version I have been running test in my database without any sandbox and everyting run well.
Because already we have a specific database for development,configured in: config/test.exs
Now upgrade Phoenix and a new feature appear Sandbox

So I want to make all changes insert, update, delete, persistent.

I read this post but it is not was usefull for me:

I don’t want to use Shared mode:

I just only want to remove the sanbox an work in the database

Please can somebody tell me wich is the right way.

This is a part from my test.exs:

import Config

config :gis, Gis.Repo,
  username: "gisuser",
  password: "gisuser",
  hostname: "localhost",
  database: "gisapp" , #{System.get_env("MIX_TEST_PARTITION")}",
  pool: Ecto.Adapters.SQL.Sandbox,
  pool_size: 10

How to configure pool argument to use NO sandbox ?
What must to write to directly use my Repo
Or may be need to change : connn_case.ex file

I try removing the next line from test_helpers.exs

# Ecto.Adapters.SQL.Sandbox.mode(Gis.Repo, :manual)

and remove:

#pool: Ecto.Adapters.SQL.Sandbox,
from config/test.exs

But nothing happen

Also the documentation does not say anything of how to disable it.

please can anybody tell me how to proceed ?


@LionelMarco Simply replace the config :gis, Gis.Repo contents with the same stuff you have in dev, but change the database to whatever you want to use for your test db.

However there are some things that are confusing me here:

The Ecto Sandbox is not a Phoenix feature, it’s an ecto feature. It’s also opt in so simply upgrading ecto will not add it to your project. If you were not using it before, how did it end up in your project at all?

Are you really really sure you want that? You can just as well just seed / populate your test DB to have baseline data that you need – that’s a legitimate need, a good amount of applications cannot run adequate tests on an empty DB – and then proceed to run tests in the normal sandboxed manner.


can’t delete this

Saw your edit – no need to delete.

If you need different seeds then just make different seed scripts? Easy enough, just put them in priv/repo/seeds/ and then fill up the test DB with MIX_ENV=test mix run priv/repo/seeds/team_one_seeds.exs for example.

Yes SandBox is a Ecto feature was a misundertanding error.

Copy the code from dev.exs is exactly what I tried:
my test.exs:

config :gis, Gis.Repo,
  username: "gisuser",
  password: "gisuser",
  hostname: "localhost",
  database: "gistest"

If I run: mix test

I get a lot of errors like that:

      1) test renders 404.html (GisWeb.ErrorHTMLTest)
     ** (MatchError) no match of right hand side value: {:error, {%RuntimeError{message: "cannot invoke sandbox operation with pool DBConnection.ConnectionPool.\nTo use the SQL Sandbox, configure your repository pool as:\n\n    pool: Ecto.Adapters.SQL.Sandbox\n"}, [{Ecto.Adapters.SQL.Sandbox, :lookup_meta!, 1, [file: ~c"lib/ecto/adapters/sql/sandbox.ex", line: 575, error_info: %{module: Exception}]}, {Ecto.Adapters.SQL.Sandbox, :checkout, 2, [file: ~c"lib/ecto/adapters/sql/sandbox.ex", line: 485]}, {Ecto.Adapters.SQL.Sandbox, :"-start_owner!/2-fun-0-", 3, [file: ~c"lib/ecto/adapters/sql/sandbox.ex", line: 407]}, {Agent.Server, :init, 1, [file: ~c"lib/agent/server.ex", line: 8]}, {:gen_server, :init_it, 2, [file: ~c"gen_server.erl", line: 962]}, {:gen_server, :init_it, 6, [file: ~c"gen_server.erl", line: 917]}, {:proc_lib, :init_p_do_apply, 3, [file: ~c"proc_lib.erl", line: 241]}]}}

And if I run : mix test ./test/data_layer_test.exs
I get:

** (RuntimeError) cannot invoke sandbox operation with pool DBConnection.ConnectionPool.
To use the SQL Sandbox, configure your repository pool as:

    pool: Ecto.Adapters.SQL.Sandbox

    (ecto_sql 3.10.2) lib/ecto/adapters/sql/sandbox.ex:575: Ecto.Adapters.SQL.Sandbox.lookup_meta!/1
    (ecto_sql 3.10.2) lib/ecto/adapters/sql/sandbox.ex:458: Ecto.Adapters.SQL.Sandbox.mode/2
    test/test_helper.exs:2: (file)

Also there is a file named “test_helpers.exs”

Ecto.Adapters.SQL.Sandbox.mode(Gis.Repo, :manual)

I tried commenting the second line but nothing happen.

What should I do with him?

What cant be wrong ??

Search for all calls on Ecto.Adapters.SQL.Sandbox functions. The one in test_helpers.exs is not the only one. Though like others I’d suggest validating if that’s indeed the best way forward. Manually maintaining db state across tests can easily become a problem.

Again I want to ask how your project got in this state? Upgrading Ecto does not magically introduce the sandbox into your project.

Ben, my main goal was to update Phoenix to version 1.7. in order to use Core components, it is the only reason, so:

  1. Install it:

mix archive.install hex phx_new 1.7.7

  1. Then run

mix gisapp

  1. And then this is the mix.exs created:

  defp deps do
      {:phoenix, "~> 1.7.7"},
      {:phoenix_ecto, "~> 4.4"},
      {:ecto_sql, "~> 3.10"},
      {:postgrex, ">= 0.0.0"},
      {:phoenix_html, "~> 3.3"},
      {:phoenix_live_reload, "~> 1.2", only: :dev},
      {:phoenix_live_view, "~> 0.19.0"},
      {:floki, ">= 0.30.0", only: :test},
      {:phoenix_live_dashboard, "~> 0.8.0"},
      {:esbuild, "~> 0.7", runtime: Mix.env() == :dev},
      {:tailwind, "~> 0.2.0", runtime: Mix.env() == :dev},
      {:swoosh, "~> 1.3"},
      {:finch, "~> 0.13"},
      {:telemetry_metrics, "~> 0.6"},
      {:telemetry_poller, "~> 1.0"},
      {:gettext, "~> 0.20"},
      {:jason, "~> 1.2"},
      {:plug_cowboy, "~> 2.5"}

Can you tell me if I can downgrade to a version without sandbox ?

Also there is a file : test/support/data_case.ex
where the sand box is started or configured, is there any way to turn off it,

Why something that should be so easy like:

with_sand_box: false

Is not implemented ?

I am used to work just with Cowboy, and it does not have sandbox in test.


There is no such version. Also that’s a feature of Ecto, not a feature of Phoenix.

Because having a persistent test DB is a bad practice.

You never answered the questions why do you need this. I’m willing to bet you’ll be better off with the sandbox.

I do work for a company that distributes gas to homes with more than 2,000,000 homes, there are many areas, measurement, billing, payments, accounting, etc. There is a specific database for development, totally isolated from production. To test each area needs the data from the previous one. And each area tests in due time. In times unrelated to those of the others.
Each test involves thousands of items that are created and stored procedures that must be executed. This takes a lot of time.
This is the working methodology, it may not be the correct one, but it is the one we use.

when configured the database in test.exs Is there any way to set another kind of pool ? that work without transaction ?

Just out of curiosity, have you simply tried searching all the text files that make up your Elixir codebase for SQL.Sandbox or even just Sandbox?

In my current application I don’t use the Ecto Sandbox either and everything in regards to testing and the database works the way I suspect you desire. However, if I or someone else had added code making use of the Ecto Sandbox to the project (a necessary step for it to be used: it’s fully opt in) and I wanted to get rid of it, but couldn’t: I’d do that simple brute force search to see what remnants might have been missed.

Hi, can you share your:
and a simple test


I strongly suggest you try my original suggestion of doing search for other sandbox references before diving into my code. Also, others here may have something closer to what you’re looking for in terms of standard Elixir development practice. But, if to do nothing more than to prove the old adage “be careful what you ask for”, please find the best I can do with your requests.

My code is source available (though not open source) and I will provide links to the closest analogues to a simple Phoenix project from my code, but know that my code is not going to help you, especially if you’re trying to come to terms with standard Elixir development practices.

My project has a fair number of non-standard/unconventional practices for Elixir development (reasons™, some good, some bad). A few of these to be aware of which pertain to you:

  • For Phoenix:

    • It’s an umbrella project (due to the possibility of different release profiles; this is not an umbrella for logical code organization).

    • The Phoenix part of my application pretty strictly only provides Web and external API interfaces. There is no business logic or “contexts” beyond the minimum to make the external interfaces work nor is there direct database access from the Phoenix related code. Business logic, and data access, comes through dependencies.

    • The only Phoenix provided templates I use are those generated when calling mix --umbrella --no-ecto. I don’t use the generators and there is no Ecto at all configured in Phoenix.

    • The Phoenix parts of my application are the least developed at this stage.

  • For database related code and testing:

    • All business logic, including logic requiring the database, are kept in standard (not Phoenix) Elixir projects which act in a library-like way and are very narrowly scoped to specific features. There are Elixir projects where these lower level libraries are orchestrated into full fledged applications, but again, these are not Phoenix projects, just normal old Elixir projects.

    • I use a custom database library which wraps Ecto to better meet my requirements. This library includes a bespoke database migrator and Repo connection management system using Ecto Dynamic Repositories.

    • Because of the custom connection management stuff, I don’t use any of the config/*.exs configuration files for Ecto configuration.

    • All testing of the database is done in these library-like Elixir projects and uses the custom database stuff just mentioned. The test themselves look like typical Ecto code because I don’t futz with the Ecto Query DSL.

So, again, if you are still fairly new to Elixir, Ecto, and/or Phoenix Framework my code will not help you and may well confuse you and lead you astray. But if you really want to continue on that path… your requests as best as I can fill them:

To really understand what’s going in this application and to navigate the application code any better you’ll need to read the docs at: Technical Documentation | Muse Systems Business Management System (what of them actually exists).


I am probably not understanding you well here but what exactly is stopping you from having several DB dumps that you can restore on top of each other? And then when you want to test a certain scenario that needs database dump 1, 4 and 6, you just make a mix task alias to clear up the test DB and then load those dumps in order?

I’ve done that too, several times in my career. There’s really no point in running expensive seeding scripts if the required test data barely changes; so just make one functional and good copy of the DB and pg_dump it and then pg_restore it before tests.

1 Like

Thanks Dimitarvp, I made a mix between seed and test with sandbox and every thing work flawlesslly.

When in Rome, do as the Romans do