Writing Rails app with SQL first

Question

Say for example I've got an SQL schema that is ready to go. How would I import it into my Rails app so that I use my prepared database instead of all those funny migrations.

EDIT: You have all misunderstood my question so far. I'm asking if I had a working database application in say PostgresQL. How would I use this as the basis of my Rails application?

Answer 1

Providing your database tables follow the Rails' ActiveRecord naming conventions then you should be good to go. Run the SQL to create the database objects. Then you can generate your Rails models in the normal fashion, but skipping the creation of a migration file.

For example:

script/generate --skip-migration User name:string, age:integer

Answer 2

I would dump the current database into a rails schema, and then use that to generate the migration files. This way you can have more control over your database, in a rails fashion. For this, you should:

1. setup your database configuration file (config/database.yml)
   
2. run the rake db:schema:dump which will create the schema file db/schema.rb
   
3. create your migration file/files using the content of your schema.rb
   

-

 #db/migrate/001_create_database.rb
 class CreateDatabase < ActiveRecord::Migration
   def self.up
     # the content of schema.rb
   end

   def self.down
     # drop all the tables
   end
 end

This way you can take advantage of migrations later on, when developing your app.

Answer 3

This should do it

  def self.up
    execute <<EOF
begin;
  SQL HERE
commit;
EOF
  end

Answer 4

There's no requirement that you use migrations, "funny" or otherwise. Just start creating models from your tables. The Rails authors are smart enough to recognise the need to support "legacy schemas".

Note that if your primary keys aren't called id then you'll need to define primary keys (see p316 of "Agile Web Development With Rails 3rd edition"):

class LegacyBook < ActiveRecord::Base
  self.primary_key = "isbn"
end

Similarly, if your foreign key names do not follow the AR conventional default style, you'll need to explicitly define them in your relationship definitions.

Out of the box, ActiveRecord doesn't support composite primary keys yet: it kind of assumes something more like 5th Normal Form (PK just a sort of arbitrary number with no meaning in the business domain). There is at least one gem, appropriately named composite_primary_keys (gem install in the usual way) but it may not support AR 2.3 yet (I see v2.2.2 when I gem list --remote composite). There's some discussion on Google Groups.

Answer 5

edit your config/database.yml file like so:

development:
  database: [your legacy database name]
  host: localhost
  adapter: postgres

you may have to include username and password keys as well, I usually don't for postgres. You'll want to set up a test database in postgres as well.

Once you've configured the development database in this way you'll be able to:

> rake db:schema:dump

and create your test database using

> rake db:schema:load RAILS_ENV=test

with the resulting file.