How to approach property-based testing for features which heavily rely on the database?

Let’s say I’m writing a (rather typical) application using Ecto, which heavily relies on a database.
Most features, let’s say, read from or write to the database. Let’s say I have feature “foo” which reads from multiple tables, does some non-trivial transformations and splits out the result. Let’s also say this feature has a simple interface in form of a single public function foo/1.

How to approach property-testing this function?

My approach, so far, is as follows (I’m using PropCheck):

  • Write some generators
  • Setup test framework (ExUnit + Ecto.Adapters.SQL.Sandbox)
  • Spin forall in a single test
  • Seed generated data into the database
  • Perform the test
  • Repeat last three steps X number of times

In code:

property "bla bla bla" do
  forall dataset <- Gen.foo_dataset() do
    Repo.transaction(fn ->
      # perfom the test
      assert ... = foo(...)

(I’m skipping some non-essential bits)

The problems are:

  • a lot of data is generated, so tests are very expensive due to all the database inserts
  • Ecto sandbox is useless here because I’m inside a property (which basically is a loop) and transaction must be used to rollback data at the end of the test

I’m relatively new to Elixir, so I’m wondering, is this acceptable approach? Can this be done better conceptually?


Is there are any business logic happening the database itself? If not, I would decouple storage from the processing code and unit test the processing code with properties. Then have simple integration tests that interacts with the db.

If you’re concerned with making things public for testing purposes, this is where module hierarchy can come into play. Undocumented modules (@moduledoc false) are conventionally considered private. You can have public foo facade function and break stuff up undernearth into modules. If you want safety and explicitness around this, there is a brilliant library called Boundary.

If your db does do some business logic then I’m sorry I don’t have a good suggestion and am interested in this myself :slight_smile: I’m wondering if there is a way to combine mocks with properties so you could mock the database :thinking: This is something I haven’t looked into.


You can still use the sandbox for that. You need to manually tell it when to run though: Possibility to give forall a cleanup hook? · Issue #169 · alfert/propcheck · GitHub


You might want to pick a random subset of the data for local dev, and only in CI run all the inserts.

1 Like