We have a few property based tests that hit the Database, and they are indeed slow. My general tips would be:

1. use async false and a shared sandbox mode so that you can insert common records ahead of all individual property interactions.

2. benchmark individual iterations. Maybe your code is actually just slower than it has to be and you can make it faster.

3. accept the slowness and set a time limit. Have this time limit be configurable and periodically test it with larger time limits if you are changing related code and want to be extra thorough.

4. consider setting up a Postgres database optimized for testing. This is a larger topic but a lot of postgreses data integrity features are unimportant for running tests.

PosgtreSQL and SQLite differ too much for those tests to be objective.

Depends on how you set it up. The sandbox is checked out and in where you or some code owned by you calls the APIs to do so. You can do both: Sandbox per generated value(s) or sandbox for the whole test.

I’d also add a completely different angle here. It sounds like you have a bunch of business logic, which at the end should result in an appropriate selection of records. You generally cannot really test Ecto.Query structs, as their content is mostly private API. So most often we default to executing the query as well to test the query.

Another option would be separating the “business logic to build up query details” from “turning query details into Ecto.Querys”. You can use a custom struct to aggregate all the information you need to build the final query. That struct can be fully (unit) testable with non-private fields. Also implement the Ecto.Queryable protocol and you can use that custom struct wherever Ecto.Queryable is accepted. The protocol implementation can then be a simpler (not as complex to test) implementation of turning the data in your struct into an Ecto.Query.