Generate Rails migrations from a schema

Question

I am creating a new Rails application which will work with an existing schema. I have been given the schema SQL but I want to create Rails migrations to populate the database in development. The schema is not overly complicated, with around 20 tables, however I don't want to waste time and risk typos by manually creating the migrations.

Is there a way to generate Rails migrations given a schema's SQL?

have to admit, not easy to find duplicate of : http://stackoverflow.com/questions/2754771/can-i-generate-a-ruby-on-rails-database-migration-file-from-a-mysql-sql-file — Spyros, Apr 01 '11 at 00:33
Yes sorry, it is a duplicate. I searched but couldn't find a similar answer. — AaronThomson, Apr 03 '11 at 22:49

score 15 · Accepted Answer · answered Apr 01 '11 at 00:56

Sure, connect your application to your database, then run

rake db:schema:dump

This will give you a db/schema.rb ready with all of your definitions. Now that you have that db/schema.rb, simply copy the contents within the declaration into a new migration. I've done this before, and it works just great.

mike montagne · Answer 2 · 2012-09-29T21:41:14.117

I prefer to simply write the initial migration's up method with SQL execute calls:

class InitialDbStructure < ActiveRecord::Migration
    def up
        execute "CREATE TABLE abouts (
            id INTEGER UNSIGNED AUTO_INCREMENT,
            updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            created_at TIMESTAMP,

            title VARCHAR(125),
            body MEDIUMTEXT,

            CONSTRAINT PK_id PRIMARY KEY (id),
            INDEX ORDER_id (id ASC)
            ) ENGINE=InnoDB;"
        end

NOTES

You will find, particularly if you are often rebuilding and repopulating tables (rake db:drop db:create db:schema:load db:fixtures:load), that execute statements run far faster than interpreted Ruby syntax. For example, it takes over 55 seconds for our tables to rebuild from Rails migrations in Ruby syntax, whereas execute statements re-generate and re-populate our tables in 20 seconds. This of course is a substantial issue in projects where initial content is regularly revised, or table specifications are regularly revised.
Perhaps of equal importance, you can retain this rebuild and repopulate speed by maintaining a single original migration in executed SQL syntax and re-executing migrations (of that single file) by first gutting your schema.rb and then running rake db:reset before re-populating your tables. Make sure you set :version => 0, so that you will get a new schema, faithful to your migration:
```
ActiveRecord::Schema.define(:version => 0) do  
end
```

I don't like raw SQL in a migration because the ORM then cannot wrap different databases. It's slower, but you buy portability. Maybe that's not a cost for everyone's needs — New Alexandria, Apr 05 '13 at 13:33
No. In fact the idea interferes with the necessary exercise of utilizing the database you choose for its virtues. If you intend and need for example to depend on Postgres' BigSerial type, you have to take the bull by the horns. Ruby of course intends to hide all that "complexity" as if indifference and inaccessibility were virtues. So, we've had "help" (hired hands) hide our intentional utilization of data types as if they were doing us a favor! — mike montagne, Aug 22 '13 at 16:03

Generate Rails migrations from a schema

2 Answers2