Adding references to migrations causes error on ecto.reset

dgreiss

Hi everyone,

I’m building out a complex database schema as part of a Phoenix app and I’m running into an error when i make updates to the migration. For example I have a :users table and :comments:

    create table(:comments) do
      add :title, :string

      timestamps()
    end

    create table(:users) do
      add :name, :string


      timestamps()
    end

I run ecto.migrate with no issue, but now I’d like to add a reference on the :comments table:

    create table(:comments) do
      add :title, :string
      add :users_id, references(:users, on_delete: :nothing)

      timestamps()
    end

Rather than rolling back the previous migration I prefer to ecto.reset, however I encounter this error.

The database for MigrationExample.Repo has been dropped
The database for MigrationExample.Repo has been created

20:42:28.605 [info]  == Running 20200126013640 MigrationExample.Repo.Migrations.CreateComments.change/0 forward

20:42:28.607 [info]  create table comments
** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "users" does not exist
    (ecto_sql) lib/ecto/adapters/sql.ex:629: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql) lib/ecto/adapters/sql.ex:716: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql) lib/ecto/migration/runner.ex:343: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (stdlib) timer.erl:166: :timer.tc/1
    (ecto_sql) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/7
    (ecto_sql) lib/ecto/migrator.ex:342: Ecto.Migrator.attempt/7
    (ecto_sql) lib/ecto/migrator.ex:243: anonymous fn/4 in Ecto.Migrator.do_up/4
    (ecto_sql) lib/ecto/migrator.ex:324: anonymous fn/3 in Ecto.Migrator.run_maybe_in_transaction/6
    (ecto_sql) lib/ecto/adapters/sql.ex:903: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection) lib/db_connection.ex:1427: DBConnection.run_transaction/4
    (ecto_sql) lib/ecto/migrator.ex:323: Ecto.Migrator.run_maybe_in_transaction/6
    (elixir) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
    (elixir) lib/task/supervised.ex:35: Task.Supervised.reply/5
    (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3

The issue is that the order of the migrations is important and the :comments table is being created before the :users table, and therefore throwing the error. Is there a way to resolve this without manually specifying the repo in the migration ecto.migrate -r or rolling back a migration. My preference is to always reset the database.

Thanks for the help

7 comments

/phoenix #ecto #ecto-migration

1 3125 7

2020-01-26 18:00:24 UTC

Showing Posts 1 to 7

Show Best Posts
Show All (oldest first)
Show All (newest first)

wolfiton

You need to create a new migration that will alter the comments table in your case and add the user_id refeference.

See here Ecto.Migration — Ecto SQL v3.14.0

You will have something like this

defmodule YourApp do
  alter table("comments") do
     add :users_id, references(:users, on_delete: :nothing)
  end
end

Then your migration will work as expected.

Post #1

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

You need to create the users table before the comments table, your migration shows comments as first.

Post #2

dgreiss

Is there a way to do specify the order of the migrations?

Post #3

wolfiton

From what i experience so far the migration each get a timestamp so the order is the one you define. But using my method to add the relationships after creating the actual migrations should help you automatize the process.

Post #4

dimitarvp

Yes, they are named with full timestamp (year, month, date, hour, minute, second) when created. If they are out of order then it means you created the comments migration first and then the users. You can simply rename your users migration to an earlier timestamp than that of the comments.

Check your priv/repo/migrations/ directory and you’ll see.

After you edit the filenames then just drop your DB (mix ecto.drop) and re-run migrations.

Post #5

LostKobrakai

They‘re applied in order of filenames. Generally the leading timestamps are used to link the alphabetical order to order of file creation.

Post #6

dgreiss

I’ve settled on altering the table as suggested by @wolfiton and reserve altering the table for creating any references and indices. I don’t like the idea of changing the timestamps on the file names, especially if the file has been committed.

Post #7

— All posts loaded —