How to fix database that has many tables without matching migrations with Rails

Question

I'm helping out with a friend's rails app. They have a database that contains many tables which were added manually using SQL rather than with rails migrations. They've started adding migrations normally, but they need to get the two back in sync.

If we add the migrations now, they'll error because those tables/columns already exist. If we leave them out, people have to use db:schema:load to get up and running, and then run migrations one at a time by name to avoid errors. We'd like to avoid that and get back to a healthy state instead.

Does anyone know of a better way to get the migrations back in step with the database? We have to do this without losing any data.

Community · Accepted Answer · 2017-05-23 12:06:52Z

2

Here's the question with the same problem (didn't vote for closing yours as duplicate since the older one does not have an accepted answer):

The solution there is:

Start by referencing your existing MySQL database in database.yml.

run rake db:schema:dump to generate the schema.rb file

Paste the create_table methods from your schema.rb into a new migration, and Voila!

To avoid any inconsistencies you can always check whether the table is already exist, and, if yes, just skip the migration altogether:

class CreateTable < ActiveRecord::Migration
  def change
    unless ActiveRecord::Base.connection.table_exists?('table_name')
      create_table :table_names do |t|
        # some columns
      end
    end
  end
end

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Sep 27, 2016 at 16:26

Andrey Deineko

52.5k11 gold badges119 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

williamcodes Over a year ago

Seems like this would fail in prod because it would try to create those tables and they would already exist, no? I added a sentence to clarify that we have to do this without losing data.

Andrey Deineko Over a year ago

@williamcodes you can always use ActiveRecord::Base.connection.table_exists? 'table_name' to check and not run the migration

williamcodes Over a year ago

that makes sense. You mean use it as a conditional in the migration file to prevent it from being run twice, right? Can you add that to your answer?

williamcodes Over a year ago

so to clarify, db:schema:dump generates its schema.rb from the tables that are actually in the MySQL database, so we have to run this dump while connected to prod, correct?

Andrey Deineko Over a year ago

@williamcodes right, db:schema:dump creates the schema.rb based of database. But you do not have to be connected to production while running it as long as you have tables (even empty) in your local db - it only cares about table and columns, not about contents

|

José Pinto · Accepted Answer · 2016-09-27 16:25:50Z

1

After backing up the content of the tables which are not present in the migrations, you could:

DROP those tables;
Create migrations for each of the dropped tables;
Run each of the created migrations;
Manually repopulate the tables that were created with the migrations them.

That way you'll have no problem with the migrations. If you have no relevant data on them just DROP them right away.

answered Sep 27, 2016 at 16:25

José Pinto

6866 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to fix database that has many tables without matching migrations with Rails

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related