How to generate Rails models from database schemas

I’m evaluating Rails (vs Django) for a new project. A requirement of this project is that the Rails app(s) be able to work with a database (PostgreSQL, in case that matters) whose schemas (table definitions) have been created and, over time, will be modified independently of the Rails app(s).

Though I’m a newbie to Rails, it already seems to be that Rails’ standard mode of operation is: (1) Models are created and modified within Rails, and then (2) Rails Migration creates and modifies the database table definitions.

What I need is the opposite sequence: (1) The database table definitions are created and modified through tools external to Rails, and then (2) the Rails models are created and modified to fit the database table definitions.

So my questions are:

  1. First and most important, is there a Gem (or some other tool in the Ruby-Rails world) that can generate a model from a database table definition?
  2. If not, presumably one could use Rails on a pre-existing database by manually editing the Rails models. Right?
  3. If that’s right, what are the requirements for the table designs to enable them to work as viable Rails models? It seems that one requirement is that each table have a sequence-assigned primary key named “id”. Right? Any others?

~ Thanks in advance ~ Ken

You don’t always have to work with Rails functionality to utilize a database, not sure always true for PostgreSQL. The critical aspect is how you define your database connectivity in config/database.yml.

Perhaps helpful, check out brilliant Ryan Bates’ RailsCast: #342 Migrating to PostgreSQL - RailsCasts

Liz

I know how to connect a Rails app to a PG database. My question is about how to align the Rails contents (model definitions) with the database contents (table definitions), so that the app can read from and write to the tables.

It think perhaps you’re answering “yes” to my question 2 - that is, I can keep the app and database contents aligned without using “Rails functionality” by editing them manually. Right?

That still leaves question 1 and 3 unanswered.

Google for rails legacy database and you will get lots of useful hits

Colin

I'm evaluating Rails (vs Django) for a new project. A requirement of this project is that the Rails app(s) be able to work with a database (PostgreSQL, in case that matters) whose schemas (table definitions) have been created and, over time, will be modified independently of the Rails app(s).

Though I'm a newbie to Rails, it already seems to be that Rails' standard mode of operation is: (1) Models are created and modified within Rails, and then (2) Rails Migration creates and modifies the database table definitions.

What I need is the opposite sequence: (1) The database table definitions are created and modified through tools external to Rails, and then (2) the Rails models are created and modified to fit the database table definitions.

So my questions are:

1. First and most important, is there a Gem (or some other tool in the Ruby-Rails world) that can generate a model from a database table definition?

If you have a table named "things", all you need is a file named e.g. thing.rb that contains

class Thing < ActiveRecord::Base end

That will give you access to all the fields of that table.

2. If not, presumably one could use Rails on a pre-existing database by manually editing the Rails models. Right?

See above.

3. If that's right, what are the requirements for the table designs to enable them to work as viable Rails models? It seems that one requirement is that each table have a sequence-assigned primary key named "id". Right?

Not exactly. You do need a primary key, and it's *easiest* with an integer, and *easiest* if the name is "id", but not necessary; I've got an app using string ids based on a legacy db schema (from what was previously a Java app).

Any others?

Depends on what you're trying to do. I'd suggest just trying it :slight_smile:

The "schemas independently modified over time" part presages some future pain, I would think; make sure you have *really good* test coverage.

HTH, and good luck.

Largely, you just ignore migrations and mostly ignore Rails models.

Inherit from ActiveRecord::Base, list the attributes that can be bulk set, and you’re done. Rails connects to the db and reads the schema. Now of course you will wind up adding validations and calculations and relations and so forth, and you may have to specify the table name if it doesn’t follow RoR conventions. But really, the answer to your question is not just “yes”, it’s “yes and it’s so easy that you’re completely missing it by looking for something harder” :wink:

Thanks a million to Scott, Hassan, & Colin, for this rich feast of possibilities! It’s gonna take me some time to digest. Then I’ll be back with the solutions I’m finding and/or with more questions.

~ Ken

Yaay! I’m happy to report success with my first attempt to make a Rails app that works from an existing database. This first experiment used (a) the simplest “instructions” I could find (basically, Hassan’s); (b) a streamlined version of the “Getting Started Guide” process for setting up a Rails app (but - this is crucial - omitting the step of running the migrator); and (c) a pre-existing (“legacy”) database that conforms to the Rails naming conventions. For the benefit of other Rails newbies (or oldbies) that need to build apps on existing DBs, a play-by-play of what I did follows.

  1. Created new Rails app, x: [sunward@web324 rails_eval]$ rails new x

``

  1. Populated the “legacy” SQLite database with table toys, and added one row to that table: [sunward@web324 x]$ rails dbconsole SQLite version 3.6.20

CREATE TABLE toys (“id” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, “name” TEXT); INSERT INTO toys (name) VALUES (“Tinker”); SELECT * FROM toys; 1|Tinker|58

``

  1. Added the toys resource and root to routes.rb:
    Rails.application.routes.draw do resources :toys root ‘toys#index’ end

``

  1. Used rake to see the resulting routes: [sunward@web324 x]$ rake routes Prefix Verb URI Pattern Controller#Action toys GET /toys(.:format) toys#index POST /toys(.:format) toys#create new_toy GET /toys/new(.:format) toys#new edit_toy GET /toys/:id/edit(.:format) toys#edit toy GET /toys/:id(.:format) toys#show PATCH /toys/:id(.:format) toys#update PUT /toys/:id(.:format) toys#update DELETE /toys/:id(.:format) toys#destroy

``

  1. Generated toys controller: [sunward@web324 x]$ rails generate controller toys create app/controllers/toys_controller.rb invoke erb create app/views/toys invoke test_unit create test/controllers/toys_controller_test.rb invoke helper create app/helpers/toys_helper.rb invoke test_unit invoke assets invoke coffee create app/assets/javascripts/toys.coffee invoke scss create app/assets/stylesheets/toys.scss

``

  1. Added the necessary CRUD methods to the toys controller: class ToysController < ApplicationController def index @toys = Toy.all end

    def show @toy = Toy.find(params[:id]) end

    def new @toy = Toy.new end

    def create @toy = Toy.new(toy_params) if @toy.save redirect_to @toy else render ‘new’ end end

    def edit @toy = Toy.find(params[:id]) end

    def update @toy = Toy.find(params[:id]) if @toy.update(toy_params) redirect_to @toy else render ‘edit’ end end

    def destroy @toy = Toy.find(params[:id]) @toy.destroy

     redirect_to toys_path
    

    end

    private def toy_params params.require(:toy).permit(:name) end
    end

``

  1. Made index.html.erb, show.html.erb, new.html.erb, edit.html.erb, _form.html.erb, index.json.jbuilder, show.json.jbuilder, following the pattern in http://guides.rubyonrails.org/getting_started.html.
  2. Created the Toy model file, toy.rb: class Toy < ActiveRecord::Base end

``

  1. In my browser, tried http://x.sunward.webfactional.com/toys. Feel free to try it yourself. (However, this is a temporary site that may be gone by the time you read this.) It works!!! That is, all the CRUDs execute as they should.
  2. An odd note is that there still is no schema.rb file in x/db/. If this is what Rails uses as its “database catalog”, is the lack of it going to cause trouble down the road?

Thanks again to Scott, Hassan, & Colin for your key clues.

My next question is whether it is possible to sue the amazing Rails generators - in particular, scaffold - to more quickly create an app that works with an existing database - so it would not be necessary to go thru all those manual steps shown above. I tried it once, and it tried to run the migrator and croaked when it found that the DB table was already there. But that may have been pilot error on my part. Comments and advice would be welcome.

~ Ken

I think you just need to create Toy model and then use scaffold controller http://stackoverflow.com/questions/4333393/using-rails-generate-scaffold-when-model-already-exists

10. An odd note is that there still is no schema.rb file in x/db/. If this is what Rails uses as its "database catalog", is the lack of it going to cause trouble down the road?

Possibly, but you can use `rake db:schema:dump` to generate it from your existing DB.

My next question is whether it is possible to sue the amazing Rails generators - in particular, scaffold - to more quickly create an app that works with an existing database - so it would not be necessary to go thru all those manual steps shown above. I tried it once, and it tried to run the migrator

I've never seen a scaffold command automatically run migrations; can you be more specific about what led up to that?

You should be able to scaffold a model and then just delete the migration file.

Thank you, byakugie ~

Your link led me to the gem “schema-to-scaffold”, which enabled the following scenario:

The aim is to get all the components generated automatically, but based on the database schema. I did it my using the gem schema_to_scaffold, which simply generates a script (one command in this case) that runs the scaffold generator in a way that drives off of the database schema for the new table (users).

  1. Created a new database table, users:

CREATE TABLE “users” (“id” INTEGER PRIMARY KEY

AUTOINCREMENT NOT NULL, “name” text, “email” textuser);

INSERT INTO users (name) VALUES (‘Jo’);

SELECT * FROM users;

1|Jo|

  1. Installed schema_to_scaffold gem:

gem install schema_to_scaffold

  1. Ran schema_to_scaffold for users:

[sunward@web324 x]$ scaffold -p db

Looking for schema.rb in db

  1. db/schema.rb

Select a path to the target schema:

Loaded tables:

  1. toys

  2. users

Options are:

4 for table 4; (4…6) for table 4 to 6; [4,6] for tables 4 and 6; * for all Tables

Select a table: 1

Script for scaffold:

rails generate scaffold User name:text email:text --no-migration

  1. Ran the script produced by schema_to_scaffold:

[sunward@web324 x]$ rails generate scaffold User name:text email:text --no-migration

  invoke 

active_record

  create   

app/models/user.rb

  invoke   

test_unit

  create      test/models/user_test.rb

  create      test/fixtures/users.yml

  invoke 

resource_route

   route   

resources :users

  invoke 

scaffold_controller

  create   

app/controllers/users_controller.rb

  invoke   

erb

  create      app/views/users

  create      app/views/users/index.html.erb

  create      app/views/users/edit.html.erb

  create      app/views/users/show.html.erb

  create      app/views/users/new.html.erb

  create      app/views/users/_form.html.erb

  invoke   

test_unit

  create      test/controllers/users_controller_test.rb

  invoke   

helper

  create      app/helpers/users_helper.rb

  invoke      test_unit

  invoke   

jbuilder

  create      app/views/users/index.json.jbuilder

  create      app/views/users/show.json.jbuilder

  invoke 

assets

  invoke   

coffee

  create      app/assets/javascripts/users.coffee

  invoke   

scss

  create      app/assets/stylesheets/users.scss

  invoke 

scss

  create   

app/assets/stylesheets/scaffolds.scss

  1. It **work****s! ** See http://x.sunward.webfactional.com/users/.

Amazing!

Next step will be to make a change in the database schema and see if/how I can get Rails to automagically update its model, controller, and views to fit.

Any how-to hints would be welcome.

~ Ken

Thanks, Hassan ~ The rake command does the job of building (and updating) schema.rb. ~ Ken

Next step: I have succeeded in adding a column to a database table, then using the generator tools to update the Rails objects that implement that model. The process, which I share for what it’s worth to others, is:

Add attribute toys.color (text):

ALTER TABLE toys ADD COLUMN color TEXT;

INSERT INTO toys (name, color) VALUES (‘Tinker’, ‘Green’);

select * from toys;

1|Tinker|Green

Ran rake db:schema:dump to update schema.rb:

ActiveRecord::Schema.define(version: 0) do

create_table “toys”, force: :cascade do |t|

t.text "name"

**t.text

“color”**

end

Ran schema_to_scaffold for toys:

[sunward@web324 x]$ scaffold -p db

Loaded tables:

  1. toys

  2. users

Select a table: 0

Script for scaffold:

rails generate scaffold Toy name:text color:text --no-migration

Ran scaffoldusing that script:

[sunward@web324 x]$ rails generate scaffold Toy name:text color:text --no-migration

  invoke 

active_record

conflict   

app/models/toy.rb

Overwrite /home/sunward/webapps/rails_eval/x/app/models/toy.rb? (enter “h” for help) [Ynaqdh] Y

   force   

app/models/toy.rb

  invoke   

test_unit

  create      test/models/toy_test.rb

  create      test/fixtures/toys.yml

  invoke 

resource_route

   route   

resources :toys

  invoke 

scaffold_controller

conflict   

app/controllers/toys_controller.rb

Overwrite /home/sunward/webapps/rails_eval/x/app/controllers/toys_controller.rb? (enter “h” for help) [Ynaqdh] Y

   force   

app/controllers/toys_controller.rb

  invoke   

erb

   exist      app/views/toys

conflict      app/views/toys/index.html.erb

Overwrite

/home/sunward/webapps/rails_eval/x/app/views/toys/index.html.erb? (enter “h” for help) [Ynaqdh] Y

   force      app/views/toys/index.html.erb

conflict      app/views/toys/edit.html.erb

Overwrite

/home/sunward/webapps/rails_eval/x/app/views/toys/edit.html.erb? (enter “h” for help) [Ynaqdh] Y

   force      app/views/toys/edit.html.erb

conflict      app/views/toys/show.html.erb

Overwrite /home/sunward/webapps/rails_eval/x/app/views/toys/show.html.erb?

(enter “h” for help) [Ynaqdh] Y

   force      app/views/toys/show.html.erb

conflict      app/views/toys/new.html.erb

Overwrite

/home/sunward/webapps/rails_eval/x/app/views/toys/new.html.erb? (enter “h” for help) [Ynaqdh] Y

   force      app/views/toys/new.html.erb

conflict      app/views/toys/_form.html.erb

Overwrite

/home/sunward/webapps/rails_eval/x/app/views/toys/_form.html.erb? (enter “h” for help) [Ynaqdh] Y

   force      app/views/toys/_form.html.erb

  invoke   

test_unit

conflict      test/controllers/toys_controller_test.rb

Overwrite

/home/sunward/webapps/rails_eval/x/test/controllers/toys_controller_test.rb? (enter “h” for help) [Ynaqdh] Y

   force      test/controllers/toys_controller_test.rb

  invoke   

helper

identical app/helpers/toys_helper.rb

  invoke      test_unit

  invoke   

jbuilder

conflict      app/views/toys/index.json.jbuilder

Overwrite

/home/sunward/webapps/rails_eval/x/app/views/toys/index.json.jbuilder? (enter “h” for help) [Ynaqdh] Y

   force      app/views/toys/index.json.jbuilder

conflict      app/views/toys/show.json.jbuilder

Overwrite

/home/sunward/webapps/rails_eval/x/app/views/toys/show.json.jbuilder? (enter “h” for help) [Ynaqdh] Y

   force      app/views/toys/show.json.jbuilder

  invoke 

assets

  invoke   

coffee

identical app/assets/javascripts/toys.coffee

  invoke   

scss

conflict      app/assets/stylesheets/toys.scss

Overwrite

/home/sunward/webapps/rails_eval/x/app/assets/stylesheets/toys.scss? (enter “h” for help) [Ynaqdh] Y

   force      app/assets/stylesheets/toys.scss

  invoke 

scss

identical

It work****s! See http://x.sunward.webfactional.com/toys/.

However: This process has only a limited ability to update the Rails objects to handle the database change while preserving manually entered changes to them. Specifically: For each Rails file where the new (generated) version differs from the existing (possibly manually modified) version, you get to choose between keeping the old version (so it doesn’t match the database changes) or replacing it with the new version (so any manual updates to the old version are lost). In other words, I haven’t found a way to generate a new file that is a combination of the old and the new - that is, it is identical to the old file in all respects except that it implements the database schema changes. So… Next question: Are there any Rails tools that can generate a “combined” file in the sense just defined? Is there any way to generate a “combined” file in the sense just defined? - either with other Rails tools or with options to the tools used in the above scenarios? I know that meaningfully merging two versions of an object quickly gets us into the “hard problem” zone. But I notice that the prompts that scaffold puts out do offer to show you a “diff” of the old vs new versions of each file. It shouldn’t be too hard to give the user the opportunity to choose between the old and the new versions of each line that differs, and that would enable the user to produce the desired “combination” in most cases. Short of that, an option to tag the new file with a slightly different file name from the old file would enable the user to use an external “diff editor” to compare and manually merge the two versions fairly efficiently.

So, I’m wondering, does the Rails World contain any tools that can do this?

~ Thanks yet again ~ Ken

I do wonder if you really understand: steps 2 through 4 were unnecessary. You can manage your database schema using Rails & migrations, or you can manage it completely outside of Rails. Either option is pretty easy, while a mishmash combination of the two is not so easy.

The reason no work has been done on this is that the scaffold is intended merely as a quick and dirty method of getting some basic functionality up and running. By the time you have a real application going it is most unlikely that much of the original scaffolding code will remain.

Colin

You’re misreading my step 1: “Add attribute toys.color (text)”. That was added to the database schema, not to the Rails model definition. So steps 2-4 (or some equivalent process in Rails) absolutely were necessary. Otherwise the Rails app would have gone on oblivious to the change in the DB schema.

Yeah, I figured something like that was the case. Probably that’s the case with most/all Rails generators (like most of the generators in the world) - they’re good for a one-time kick-start, but not much good after that.

Still, a generator that could write on something other than a blank slate would be a very valuable addition to the toolkit. And doable.

No, I did not misread it. As I suspected, you still do not understand what we have been trying to tell you: Rails WILL READ THE SCHEMA FROM THE DATABASE. Your steps 2 through 4 were unnecessary, and, as I said before:

You can manage your database schema using Rails & migrations, or you can manage it completely outside of Rails. Either option is pretty easy, while a mishmash combination of the two is not so easy.

Well, it would be nice to do in one step what I thought required four! So let me try…

  1. Add another attribute toys.size to the schema (this looks different from the previous step 1 only because I have replaced my SQLite DB with a PostgreSQL one, named sunward_x: [sunward@web324 x]$ rails dbconsole … psql (9.1.15) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) sunward_x=> \d toys Table “public.toys” Column | Type | Modifiers

1. Exactly as you did, add the column to the table.

Now it's there. Use it, just like any other column. Add it to any view you wish. Do anything you wish with it in a controller.

Why on earth would you keep re-generating scaffolds over and over??? A scaffold is a starting point; it is not your app code. People have told you (over and over) that you do not maintain Rails apps by generating & re-generating scaffolds.