TL;DR; Running mix ecto.rollback, edit migration, mix ecto.migrate will not rerun migrations in your test database!!! Use MIX_ENV=test mix ecto.reset or similar to freshen up that test database.
We have a standard elixir project using Ecto to interface with a postgresql instance. We use ExUnit and factories to produce data for unit tests.
One of our complex queries is being replaced by a VIEW in postgresql. Unfortunately, this VIEW returns an empty record set under test. In dev, I can exercise and use this view successfully, i.e. it returns the expected record sets.
I’ve attempted to wrap several factory calls and our application call which relies on the VIEW with asserts in a Repo.transaction block to no avail.
I would love to fix this cleaning, but if necessary I’m open to loading seeded test data post migration in the test setup, (if there is a clean way to do that… Ecto.load for instance).
Any insights? TIA, Ken
Solution, and thank you all for contributing…
Be careful to get your query nailed down before exploring VIEW and MATERIALIZED VIEW.
Don’t do three things at once.
And MIX_ENV=test mix ecto.reset will bring back sanity as you adjust the VIEW via migrating up and down.
It’s best if you have a test DB seeding script indeed. You can re-alias mix test to also call the test seed task and it should be completely transparent.
A view would be expected to just work. Plain views are at the db level replaced with their underlying queries, so they would function exactly the same as their queries.
I think I confused myself by trying first as a MATERIALIZED VIEW and not taking this step-by-step. I’m not sure how to REFRESH a materialized view during a regular test run. Sorry for the confusion (and thank you for the reply).
Have you checked that the correct migrations are in place? It can happen that you get wrong migrations in test env if you modify them between tests, you can reset the test migrations by running:
After being more careful… Confirming the complex query, and setting the test DB as you mentioned, I have a materialized view working with event-based calls to refresh it concurrently.
