Run tests against a development database that is pre-filled with data

Disclaimer: I don’t actually want to runs tests against a prod database. I’m not that mad :slight_smile:

What I’m looking for is a way to run tests against a development database that is pre-filled with data.

Let’s assume the following scenario:

  1. It is not possible to generate the data each time the tests run
  2. We only want to test read-only operations
  3. We cannot use migrations on the database
  4. We want to implement a controller test with the help of conn_case

Is such a scenario possible?

Yes it is, I work in such a company. No technical hurdles.

1 Like

That sounds very scary if other people are allowed to update the db without warning.

That is a very common thing to do, and is well supported.

The key question is whether you want your tests to run with ephemeral data or seed data.

The standard thing is for tests to create the db, run migrations, and then run tests which e.g. create a record and then verify that it was created correctly. It is usually better to run those tests in a transaction so that they can run independently from other tests in parallel.

It’s common to use a seed file to populate the db with test data or reference tables: mix run priv/repo/seeds.exs.

If you just want to run your tests against static data in the database, then that’s easy enough. Just write queries in your tests and verify that you get the data back that you expect.

Here is a complete example project that runs tests against static data in a database. absinthe_federation_example/ci.yml at main · cogini/absinthe_federation_example · GitHub

It runs the normal unit tests, then, as a final step, initializes a database and runs external API tests using the production container.

If your database is large, you can save startup time by initializing the database using an SQL file: absinthe_federation_example/docker-compose.gha.yml at main · cogini/absinthe_federation_example · GitHub

It is :smiley:

How to actually do that? I tried using my dev env database settings but that won’t work as is. Do you use Ecto sandbox? How do you prepare your conn cases?

Thanks for the example! Maybe the sandbox mode is not working for me because I use a custom Ecto driver. But knowing that it should work in theory is helpful since now I can go to debug the thing :slight_smile:

We have a mix task that drops the test DB, creates it again, and seeds it (with Elixir code, not by importing an SQL dump). From then on its business as usual exactly as all other Elixir testing – you just have stuff in the test DB and it’s not empty.

You have all the objects in the DB and can modify them but all changes are rolled back after your test case is done. The only statefulness is actually having stuff in the test DB as the tests begin.


The sandbox is enabled in the Repo config in config/test.exs, test/test_helper.exs and test/support/data_case.ex. That’s the default for tests.
Sounds like you should be using DataCase instead of ConnCase.

ConnTest by default also includes the sandbox.

1 Like