Weird sandbox problem with foreign keys in unit tests

I’m at my wits end, I can’t understand why I get foreign key errors in unit tests.

I’m building a small podcast server and it has a data model of Program → Episode. In the simplest Episode unit test:

   test "list_episodes/0 returns all episodes" do
      episode = episode_fixture()
      list = Media.list_episodes()
      first = Enum.at(list, 0)

      assert length(list) == 1
      assert episode.title == first.title
    end

This file uses the standard (non-async) Cast.Datacase test case generated by Phoenix.

episode_fixture() is in a separate file (media_fixtures.ex), and looks like this:

  def episode_fixture(attrs \\ %{}) do
    program = program_fixture()

    p2 = Media.get_program!(program.slug)
    IO.inspect(program)
    IO.inspect(p2)

    {:ok, episode} =
      attrs
      |> Enum.into(@episode_attrs)
      |> Map.put_new(:program_id, program.id)
      |> Media.create_episode()

    episode
  end

The two IO.inspects both show the same object:

%Cast.Media.Program{
  __meta__: #Ecto.Schema.Metadata<:loaded, "programs">,
  episodes: #Ecto.Association.NotLoaded<association :episodes is not loaded>,
  id: 234,
  inserted_at: ~U[2022-04-18 16:10:05Z],
  name: "The Elixir TV",
  slug: "elixir-tv",
  updated_at: nil,
  url: "https://elixir.tv/"
}

…meaning that the record must be present in the database (since the ID is generated by PostgreSQL).

But the line that tries to insert the episode always fails like this:

  1) test episodes list_episodes/0 returns all episodes (Cast.MediaTest)
     test/cast/media_test.exs:87
     ** (MatchError) no match of right hand side value: {:error, #Ecto.Changeset<action: :insert, changes: %{number: 1, program_id: 234, published_at: ~U[2020-10-14 01:00:00Z], title: "First show", url: "https://elixir.tv/show/1/"}, errors: [program_id: {"does not exist", [constraint: :foreign, constraint_name: "episodes_program_id_fkey"]}], data: #Cast.Media.Episode<>, valid?: false>}
     code: episode = episode_fixture()
     stacktrace:
       (cast 0.1.0) test/support/fixtures/media_fixtures.ex:41: Cast.MediaFixtures.episode_fixture/1
       test/cast/media_test.exs:88: (test)

So now it’s claiming that the Program with ID 234 doesn’t exist, but we just loaded it a few lines earlier?!?

The episode schema looks like this:

defmodule Cast.Media.Episode do
  use Ecto.Schema
  import Ecto.Changeset
  alias Cast.Media.{MediaFile, Program}

  schema "episodes" do
    belongs_to :program, Program
    field :number, :integer
    field :title, :string
    field :description, :string
    field :url, :string

    field :inserted_at, :utc_datetime
    field :updated_at, :utc_datetime
    field :published_at, :utc_datetime

    has_many :media_files, MediaFile
  end

  @doc false
  def changeset(episode, attrs) do
    episode
    |> cast(attrs, [:program_id, :number, :title, :description, :url, :published_at])
    |> validate_required([:program_id, :title, :published_at])
    |> foreign_key_constraint(:program_id)
  end
end

Is there something weird going on with sandboxing, or why is this not working?

Are you sure your schemas/migrations are correct? Is it working in e.g. dev?

Yeah, I have a bunch of forms hooked up to the entities, and I have no problems editing/creating/deleting them.

Hi @mikl.

I am stuck on what appears to be the same problem as you, although I only see it when running multiple test files. When I just run the test file in question it’s fine. It definitely seems like something strange going on with the Repo Sandbox pooling.

Did you ever figure out what was going on?

Scott

Hi Scott

No, since this is just a hobby project, I’ve just disabled the problematic test, so the mystery remains unsolved, sorry.