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.
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
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.