Drop database for tests?

Questions / Help
,
#1

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?

#2

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ā€¦

#3

Iā€™m sorry I do not know what DDL DML or DCL means

#4

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?

#5

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.

#6

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.

#7

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.

#8

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.

3 Likes
#9

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

#10

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.