Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to run updating in migration for Ecto?

I use Phoenix and Ecto in one of my project.

I want to add a column to one table, and I expect it to be a NOT NULL column. But I already have some existed data, so I decide to add the column, update all rows to some value and modify the column to NOT NULL.

I tried these two code:

  # solution 1
  def up do
    alter table(:channels) do
      add :type, :integer
      Exchat.Repo.update_all("channels", set: [type: 1])
      modify :type, :integer, null: false
    end
  end

  # solution 2
  def up do
    alter table(:channels) do
      add :type, :integer
    end
    Exchat.Repo.update_all("channels", set: [type: 1])
    alter table(:channels) do
      modify :type, :integer, null: false
    end
  end

Both of them didn't work and I got error like:

23::40::07.514 [info]  == Running Exchat.Repo.Migrations.AddTypeToChannels.up/0 forward

23::40::07.541 [debug] UPDATE "channels" AS c0 SET "type" = $1 [1] ERROR query=12.0ms
** (Postgrex.Error) ERROR (undefined_column): column "type" of relation "channels" does not exist
    (ecto) lib/ecto/adapters/sql.ex:383: Ecto.Adapters.SQL.execute_and_cache/7

I'm not sure if it's related to the Ecto version but my Ecto version is 2.0.0-rc.0.

Is there any way to achieve this? Or is there something wrong in my example code?

like image 559
Tony Han Avatar asked Apr 19 '16 15:04

Tony Han


People also ask

What does ecto migrate do?

Ecto migrations are used to do the following: Change the structure of the database, such as adding fields, tables, or indexes to improve lookups. Populating fields with modified data or new data.

How do I rollback ecto migration?

To roll back to a version number, supply --to version_number . To roll back a specific number of times, use --step n . To undo all applied migrations, provide --all .

What is ecto setup?

setup , Ecto tries to create the repository database, load the database structure present in the structure. sql file, run any pending migrations and finally run the seeds. However, unfortunately, it doesn't work in Ecto SQL versions before 3.1.


1 Answers

Solution #2 is the correct approach, but you need to add a call to Ecto.Migration.flush/0 after the first alter block to make sure all the changes are executed on the database instantly instead of being queued up to be executed later, which is the default.

def up do
  alter table(:channels) do
    add :type, :integer
  end
  flush()
  Exchat.Repo.update_all("channels", set: [type: 1])
  alter table(:channels) do
    modify :type, :integer, null: false
  end
end
like image 61
Dogbert Avatar answered Nov 02 '22 06:11

Dogbert