Rollback/delete ecto database

Here’s my situation:

~/phoenix_apps/dog$ mix ecto.migrations

Repo: Dog.Repo

  Status    Migration ID    Migration Name
--------------------------------------------------
  up        20190715054239  create_users
  up        20190716225030  create_members
  up        20190716225304  create_memberships

But, when I try:

~/phoenix_apps/dog$ mix ecto.rollback

17:32:13.213 [info]  == Running 20190716225304 Dog.Repo.Migrations.CreateMemberships.change/0 backward

17:32:13.216 [info]  drop table memberships
** (Postgrex.Error) ERROR 42P01 (undefined_table) table "memberships" does not exist
    (ecto_sql) lib/ecto/adapters/sql.ex:621: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir) lib/enum.ex:1327: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql) lib/ecto/adapters/sql.ex:708: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql) lib/ecto/migration/runner.ex:340: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql) lib/ecto/migration/runner.ex:119: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir) lib/enum.ex:1940: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql) lib/ecto/migration/runner.ex:118: Ecto.Migration.Runner.flush/0
    (stdlib) timer.erl:166: :timer.tc/1
    (ecto_sql) lib/ecto/migration/runner.ex:27: Ecto.Migration.Runner.run/7
    (ecto_sql) lib/ecto/migrator.ex:276: Ecto.Migrator.attempt/7
    (ecto_sql) lib/ecto/migrator.ex:211: anonymous fn/4 in Ecto.Migrator.do_down/4
    (ecto_sql) lib/ecto/migrator.ex:258: anonymous fn/3 in Ecto.Migrator.run_maybe_in_transaction/6
    (ecto_sql) lib/ecto/adapters/sql.ex:890: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection) lib/db_connection.ex:1415: DBConnection.run_transaction/4
    (ecto_sql) lib/ecto/migrator.ex:257: 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:247: :proc_lib.init_p_do_apply/3

And, if I try:

~/phoenix_apps/dog$ mix ecto.migrate
17:39:34.415 [info]  Already up

~/phoenix_apps/dog$ mix ecto.migrations

Repo: Dog.Repo

  Status    Migration ID    Migration Name
--------------------------------------------------
  up        20190715054239  create_users
  up        20190716225030  create_members
  up        20190716225304  create_memberships

It appears I am stuck in an infinite circle of hell.

1 Like

mix ecto.reset

Also this link may provide further help https://stackoverflow.com/questions/42162347/how-to-rollback-reset-or-drop-ecto-test-database

3 Likes

You can reset your db with

$ mix ecto.drop
$ mix ecto.create
$ mix ecto.migrate

Did You create migration cleanly?

3 Likes

mix ecto.reset is an alias set in a phoenix project. If You start a simple ecto application (without Phoenix) You won’t get it.

Here is an extract of mix file for a Phoenix project :slight_smile:

  # Aliases are shortcuts or tasks specific to the current project.
  # For example, to create, migrate and run the seeds file at once:
  #
  #     $ mix ecto.setup
  #
  # See the documentation for `Mix` for more info on aliases.
  defp aliases do
    [
      "ecto.setup": ["ecto.create", "ecto.migrate", "run priv/repo/seeds.exs"],
      "ecto.reset": ["ecto.drop", "ecto.setup"],
      test: ["ecto.create --quiet", "ecto.migrate", "test"]
    ]
  end

Isn’t @7stud application a phoenix one?

To me it seems that it is.

Yes it is… ecto.reset is available in that case :slight_smile:

@7stud Have you checked your SQL database to see if the tables exist?

Also what OS are you on Windows, Linux, Mac?

So before i sign out here is how you verify postgres tables:

psql -U "username" -W "password"
\l
\c database_you_want_to_use
\dt
also to quit use \q

Okay, I did that:

~/phoenix_apps/dog$ mix ecto.drop
The database for Dog.Repo has been dropped
~/phoenix_apps/dog$ mix ecto.create
The database for Dog.Repo has been created
~/phoenix_apps/dog$ mix ecto.migrate

17:42:49.198 [info]  == Running 20190715054239 Dog.Repo.Migrations.CreateUsers.change/0 forward

17:42:49.201 [info]  create table users

17:42:49.215 [info]  == Migrated 20190715054239 in 0.0s

17:42:49.252 [info]  == Running 20190716225030 Dog.Repo.Migrations.CreateMembers.change/0 forward

17:42:49.252 [info]  create table members

17:42:49.257 [info]  == Migrated 20190716225030 in 0.0s

17:42:49.263 [info]  == Running 20190716225304 Dog.Repo.Migrations.CreateMemberships.change/0 forward

17:42:49.263 [info]  create table memberships

17:42:49.271 [info]  == Migrated 20190716225304 in 0.0s

Then I was able to do this:

~/phoenix_apps/dog$ iex -S mix

iex(3)> Dog.MembershipManager.list_memberships()                                 
[debug] QUERY OK source="memberships" db=0.7ms queue=0.7ms
SELECT m0."id", m0."name", m0."start_date", m0."end_date", m0."member_id", m0."inserted_at", m0."updated_at" FROM "memberships" AS m0 []

Thanks!

@7stud Have you checked your SQL database to see if the tables exist?

No, sorry. I’m using OSX.

Handy to know:

defp aliases do
    [
      "ecto.setup": ["ecto.create", "ecto.migrate", "run priv/repo/seeds.exs"],
      "ecto.reset": ["ecto.drop", "ecto.setup"],
      test: ["ecto.create --quiet", "ecto.migrate", "test"]
    ]
  end

So, mix ecto.reset is equivalent to:

$ mix ecto.drop
$ mix ecto.create
$ mix ecto.migrate
$ mix run priv/repo/seeds.exs   #which in my case is empty

Thanks!

1 Like