Drop database for tests?

vrod · January 20, 2021, 7:06pm

I encountered problems similar to Can't drop postgres db when in test env
where I cannot drop my database during test. As like other post, my config is using sandbox:

config :myapp, MyApp.Repo,
  url: "postgres://postgres:postgres@localhost/myapp_test",
  pool: Ecto.Adapters.SQL.Sandbox

and I use setup in my tests:

  setup do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(MyApp.Repo)
  end

test "something" do
    Mix.Task.rerun("ecto.drop")
    Mix.Task.rerun("ecto.create")
    Mix.Task.rerun("ecto.migrate")
   # ... 
end

Other tests work fine with this sandbox (insert, update, delete, etc), but I get errors when I try to drop/create:

** (Mix.Error) The database for MyApp.Repo couldn't be dropped: ERROR 55006 (object_in_use) database "tobby_test" is being accessed by other users

     There are 10 other sessions using the database.

I did reboot and there are no psql or GUI clients or iex connections. I think my app is connecting to the database somewhere but I do not understand how. Is there a better way to drop and recreate database?

NobbZ · January 20, 2021, 7:14pm

The sandbox transaction does not allow for DDL queries as far as I remember.

Only DML and DCL is allowed, though I am not sure about the later…

vrod · January 20, 2021, 7:16pm

I’m sorry I do not know what DDL DML or DCL means

al2o3cr · January 20, 2021, 7:35pm

MyApp.Repo has a connection pool that I believe defaults to 10 connections - that’s likely what you’re seeing.

ecto.migrate isn’t something you’d typically run inside a test, can you tell us more about your use case?

dimitarvp · January 20, 2021, 7:37pm

It could also mean you have a psql session open with the test DB somewhere. “There are 10 other sessions using the database” usually means another app with a pool of 10 connections is active.

vrod · January 20, 2021, 7:45pm

I need clean database for full test run. Although it is possible to run delete functions separately, re-creating the database with migrated tables is easier (I thought).
I opened no other apps or windows so I am very much confused by this.

NobbZ · January 20, 2021, 7:47pm

Data definition language, data control language and data modelling language.

DDL means basically creating and dropping tables and indices, control is for user and permission language, while modelling is what you do in your daily business to select and update data.

al2o3cr · January 20, 2021, 9:50pm

Database setup and similar tasks are usually performed before any tests run, either with mix tasks or in test_helper.ex.

For instance, the default mix.exs generated by Phoenix includes this in its aliases:

test: ["ecto.create --quiet", "ecto.migrate --quiet", "test"]

This ensures that the test database is always present and migrated to the most current version before running the tests.

Ecto.Adapters.SQL.Sandbox will automatically wrap each checkout in a transaction that rolls back, so your test database shouldn’t have any records in it at the end of the tests.

If you want to be 1000% sure the DB starts empty, you can add ecto.drop --quiet to the test alias.

vrod · January 20, 2021, 9:51pm

thank you, this I did not know! I added a drop there and everything works how I want

zorn · January 21, 2021, 2:47am

Apologies for jumping into a solved thread, but I have similar questions/issues around creating a postgres schema (tenant) during a test. I’ve never gotten it to work right and would love some suggestions if they are out there.

I have my own project needs and and want to get prefix/tenant stuff working in ex_machina.