How to clean PostgreSQL database created with Phoenix/Ecto

15
votes

I'm starting to play with Ecto trying to understand it. As expected I messed up (with the user model) and I get an error while running a migration:

(Postgrex.Error) ERROR (duplicate_table): relation "users" already exists

Now, I want to clean the database using the shell/PgAdmin III so that I can then fix my model and run migrations again. I've set up PgAdmin but I'm not able to see any "user" table... What's the best way of doing this (either with Ecto, PostgreSQL shell or PgAdmin)?

2
postgresqlphoenix-frameworkecto
If all tables (views, sequences, ...) are owned by the same user, then drop owned by foobar is the quickest method (where foobar is the name of the Postgres user owning everything) – a_horse_with_no_name
Done! Don't you want to make this an answer so that I can accept it? BTW do you have any Postgres admin guide you would recommend? – Paulo Janeiro

2 Answers

20
votes

With ecto you get some new mix tasks to your project to deal with the database. They might help you:

  • mix ecto.create - create the database which is used by your repository as backend
  • mix ecto.migrate - runs the pending migrations for your repository
  • mix ecto.drop - drops the database

There are some more new tasks, but these three will fix your problem. Try mix --help for the other new tasks. In your case: first run mix ecto.drop to drop the database, mix ecto.create to re-create it again and finally mix ecto.migrate to migrate the tables, and you are back at start.

I've setup some keyboard shortcuts to type these commands quicker:

  • mec is a alias for mix ecto.create
  • mem is a alias for mix ecto.migrate
  • med is a alias for mix ecto.drop
2
votes

If all tables (views, sequences, ...) are owned by the same user, then 

drop owned by foobar;

is the quickest method (where foobar is the name of the Postgres user owning everything). This will really drop everything owned by that user regardless on how this was created. You also can't use this if for some reason you created everything with the superuser (typically postgres) - but you shouldn't use that for "regular" things anyway.