How to clean PostgreSQL database created with Phoenix/Ecto

Question

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)?

Answer 1

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

Answer 2

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.