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.