Purge or recreate a Ruby on Rails database

Question

I have a dev Ruby on Rails database full of data. I want to delete everything and rebuild the database. I'm thinking of using something like:

rake db:recreate

Is this possible?

Solution 1

I know two ways to do this:

This will reset your database and reload your current schema with all:

rake db:reset db:migrate

This will destroy your db and then create it and then migrate your current schema:

rake db:drop db:create db:migrate

All data will be lost in both scenarios.

Solution 2

On Rails 4, all needed is

$ rake db:schema:load

That would delete the entire contents on your DB and recreate the schema from your schema.rb file, without having to apply all migrations one by one.

Solution 3

I use the following one liner in Terminal.

$ rake db:drop && rake db:create && rake db:migrate && rake db:schema:dump && rake db:test:prepare

I put this as a shell alias and named it remigrate

By now, you can easily "chain" Rails tasks:

$ rake db:drop db:create db:migrate db:schema:dump db:test:prepare # db:test:prepare no longer available since Rails 4.1.0.rc1+

Solution 4

Update: In Rails 5, this command will be accessible through this command:

rails db:purge db:create db:migrate RAILS_ENV=test

---

As of the newest rails 4.2 release you can now run:

rake db:purge 

Source: commit

# desc "Empty the database from DATABASE_URL or config/database.yml for the current RAILS_ENV (use db:drop:all to drop all databases in the config). Without RAILS_ENV it defaults to purging the development and test databases."
  task :purge => [:load_config] do
    ActiveRecord::Tasks::DatabaseTasks.purge_current
  end

It can be used together like mentioned above:

rake db:purge db:create db:migrate RAILS_ENV=test

Solution 5

Depending on what you're wanting, you can use…

rake db:create

…to build the database from scratch from config/database.yml, or…

rake db:schema:load

…to build the database from scratch from your schema.rb file.

View more solutions

Comments

[coreyward]

You've got to drop the database first…or you can just delete the tables if you prefer.

[coreyward]

That's going to run all of your migrations one after the other, which isn't scalable and is error-prone. Also, I'm pretty sure db:migrate updates your schema.rb, so your schema:dump isn't doing anything useful.

[AnApprentice]

so how does one empty the database? in development... clear it all out.

[plindberg]

It seems rake db:reset also runs all migrations (at least on Rails 3), so that should be all that is needed, right?

[plindberg]

Or, rather, it leaves the schema identical to what running all the migrations would have. But the migrations aren't run per se (so if you have migrations which insert data, that won't happen; for this, you should really use a db/seeds.rb file).

[Danny]

+1 for schema load. sometimes migrations get messed up, but the schema should be what is kept intact.

[labyrinth]

I know that for Tracks GTD app db:migrate didn't work. I had to do db:reset when moving from Sqlite3 to Postgres.

[Jason Swett]

I read in The Rails 3 Way that loading the schema is the way to go, as opposed to running all the migrations. I don't remember exactly what their reasoning was but it seems to make sense. If the end result is the same either way, it seems simpler and less error-prone just to load the database from the schema than to run a bunch of migrations.

[coreyward]

The reasoning is that migrations are meant to migrate data, and become increasingly brittle over time as your models change. You can (and should) bake in bare-minimum scoped models into your migrations whenever feasible to ensure they run, but this just doesn't scale well and is much less efficient than just building the database from what the application knows is the final point. Why rely on migrations to create a database that looks like your schema when you can just build from the blueprint itself?

[coreyward]

@AnApprentice You can run db:reset, which is just a Google (or check on the Guides) away. My comment wasn't to advise against using that, but to avoid using db:migrate when what you really want is db:schema:load.

[coreyward]

By the way, @TK, you really don't need to run all of these as separate processes dependent on the exit status of the last. Instead, just pass all desired tasks to rake, like so: rake db:drop db:create db:schema:load.

[s2t2]

You'll also need to run rake db:test:prepare for testing, or else you'll get an error like: Could not find table 'things' (ActiveRecord::StatementInvalid)

[laffuste]

Make sure there are no connections to db (web server, sql client...) otherwise drop won't work and shit will happen.

[Abdo]

I have found that drop, create then migrate is more consistent: I have some migrations that load constants in the db that were not loading correctly with the first solution.

[Arel]

You should run rake db:schema:load instead of rake db:migrate whenever you a recreating a database.

[nathanvda]

@Abdo: you should use seeds to load content, migrations only for structure (or maybe modifying structure and existing data). Then rake db:reset will just fill the database with the latest schema. Much quicker.

[bigpotato]

works for rails 3 as well. useful for when you just messed up your test database and want to reset it to a working version that matches your dev db

[Suan]

For some reason db:reset just doesn't do anything for me. I have to explicitly drop and create. This is on Rails 4.

[Grant Birchmeier]

Thanks for this. I didn't realize that db:drop and db:create were redundant.

[gotqn]

@Suan this is strange. For me db:reset did everything. I did not need to run db:migrate and db:seed, too.

[John Pankowicz]

@s2t2 rake db:test:prepare checks and warns about pending migrations. Since he is doing db:migrate in each example, this is not needed. Also db:test:prepare is deprecated in 4.1.0.

[Robbie Guilfoyle]

See the rails 4.2 way below.

[lbramos]

@nathandva In rails 3.2.13 rake db:reset also fills the db with the seed file

[ctc]

I like to add db:test:prepare to this, for good measure. This depends, of course, on whether or not you're testing.

[Darth Egregious]

@plindberg please remove your comment. It's terribly out of date and misinformative.

[Claudio Floreani]

Someone should make clear that rake db:reset and rake db:drop db:create db:migrate do two whole different things. The latter wipes out the whole app database, recreates it and then goes through every migration to update the schema (db/schema.rb or db/structure.sql), but does not fill it with seed data. The first instead is an alias for rake db:drop db:schema:load db:seed, so it wipes out the whole app database but it does not update the schema, and then populates with seed data. So, if you haven't changed anything in your migrations, the first is quicker, the latter is safer.

[Claudio Floreani]

This doesn't updates the schema, is not a safe way if you refactor your migrations.

[MCB]

As @bekicot says in plainer english db:purge "remove all the data but preserve all the table and columns"

[johncip]

It's anecdotal, but I've never had an issue running db:migrate... whereas db:schema:load is sensitive to someone forgetting to check schema.rb into version control alongside a new migration.

[Freddo]

Well... Just tried it, but it does not preserve tables and columns. You have to run a db:migrate after having run a db:purge. So this does not preserve tables and columns. It does however preserve the database itself so you do not have to db:create

[Yana Agun Siswanto]

@Cedric You are right, db:purge is not preserve the table. I updated the code.

[Yana Agun Siswanto]

@MCB I was wrong, sory about that, db:purge is not preserving the tables.

[Yoni]

Make sure that schema is up to date. Sometimes people commit migration files but skip committing the changes to the schema.rb file because they dont realize what it means.

[Michael Durrant]

Don't use migrate as shown in the answer. It will work on smaller repositories and systems but usually as they grow, as some point a data migration (as opposed to a schema migration) gets included (bad) and an issue arises with the data migration which breaks the migrations ability to be re-run. At that point people usually switch to use rake db:schema:load instead. Instead of raging against such migrations placed by the infamous 'former developer' and insisting they be fixed, I now just use the schema:load approach and carry on with the rest of my work.

[zzz]

@ClaudioFloreani refactoring migrations is asking for trouble. Once they're run, they should be left alone, permanently.

[Claudio Floreani]

@nrowegt Refactoring migration is encouraged indeed (especially during development) — if done with awarness of the facts. Read more about "AntiPattern: Messy Migrations"

[Dmitry]

Thanks, @ClaudioFloreani! I was confused, why my changes in migration files doesn't apply when I run db:reset.

[x-yuri]

@ClaudioFloreani Why is the latter safer? Can you elaborate?

[x-yuri]

db:reset == db:drop + db:schema:load + db:seed, db:migrate:reset == db:drop + db:create + db:migrate

[Salomanuel]

but this doesn't seed the database, so don't forget to run $ rails db:seed afterwards!

[shampoo]

this is the only way that makes the app to run all migrations again. Because each migration makes changes to schema.rb and if you only drop and create, migrate will do nothing (tested on rails 6)

[Jacob Miller]

I also want to point out that rake db:reset does not adhere to any PostgreSQL (I have not tested other SQL) schemas you have setup. For example, if you are seeding your database and have schema ABC with data in table XYZ (in addition to regular public schema), rake db:reset will repopulate public.XYZ NOT ABC.XYZ. However, rake db:drop, rake db:create, rake db:migrate will add the seed data to the correct schema.