Postgres database inexplicably missing column

Phoenix
1

For some reason, my production DB is missing a column that’s explicitly defined in the migration that creates the table. I’m at a loss here. Never seen anything quite like this, which generally means it’s something hiding in plain sight and really stupid, but I can’t seem to see it.

From the migration:

create table(:roles, primary_key: false) do
      add :id, :uuid, primary_key: true, null: false
      add :name, :string, null: false
      add :description, :text
      add :permissions, {:array, :string}, default: []

      timestamps()
    end

From psql:

select * from roles;
 id | name | description | inserted_at | updated_at
----+------+-------------+-------------+------------
(0 rows)

From logs:

2024-09-23 19:04:16.980	
query: SELECT r0."id", r0."name", r0."description", r0."permissions", r0."inserted_at", r0."updated_at", u1."user_id"::uuid FROM "roles" AS r0 INNER JOIN "user_roles" AS u1 ON r0."id" = u1."role_id" WHERE (u1."user_id" = ANY($1)) ORDER BY u1."user_id"::uuid


2024-09-23 19:04:16.980	
02:04:16.979 request_id=F_gL0sT6ne3r7P0AAAeR [error] ** (Postgrex.Error) ERROR 42703 (undefined_column) column r0.permissions does not exist
2024-09-23 19:04:16.980	
02:04:16.979 request_id=F_gL0sT6ne3r7P0AAAeR [info] Sent 500 in 6ms
2

Solved! Today I learned what Ecto.Migration.flush/0 is :joy:

For anyone else who runs into this issue, typically I avoid writing migrations with steps that depend on one another which is why I’ve never encountered flush/0, but today I departed from that practice for no particular reason and wrote my migration like this:

def change do
    create table(:roles, primary_key: false) do
      add :id, :uuid, primary_key: true, null: false
      add :name, :string, null: false
      add :description, :text
      add :permissions, {:array, :string}, default: []

      timestamps()
    end

    create unique_index(:roles, [:name])

    # adding this fixed the issue
    flush()

    create table(:user_roles) do
      add :user_id, references(:users, on_delete: :delete_all, type: :binary_id)
      add :role_id, references(:roles, on_delete: :delete_all, type: :binary_id)
    end

    create index(:user_roles, [:user_id])
    create index(:user_roles, [:role_id])
    create unique_index(:user_roles, [:user_id, :role_id])
  end

As noted in the block above, adding a call to flush/0 resolved the issue. It’s strange that the only apparent symptom of the problem was the permissions column being missing from the db after running the migration, but hey here we are.

https://hexdocs.pm/ecto_sql/Ecto.Migration.html#flush/0