ORM library for automatically mapping foreign keys in Python or Ruby

Question

A frequent task I run into at work is writing scripts against pre-existing databases. Sometimes I'm connecting to Oracle, other times it might be MySql or even sql server.

What I would like is a tool which would reverse-engineer the database's tables and foreign keys and allow me to write OO-style scripts against the database. This could be in any language really, but python or ruby would be preferred.

For example - this is my ideal ruby script: (assuming the manager and employee tables already exist with foreign keys)

DB = Database.connect(connect_string)
DB.managers.each do |manager|
  puts manager.name
  manager.employees.each do |employee|
    puts employee.name
  end
end

Does this type of library exist? If so, it would save me so much time!

Edit - the main feature I would like is for it to automatically discover foreign key associations from the database metadata without explicitly mapping them - I have tried ActiveRecord, SQLAlchemy, Sequel, and DataMapper, and from what I can tell, none of them can do this.

Answer 1

In Ruby there are several ORMs like ActiveRecord, DataMapper or even Sequel.

For example using ActiveRecord you would do:

##################################
#mysql conection
##################################
begin
  ActiveRecord::Base.establish_connection(
    :adapter  => DBAdapter,
    :host     => DBHost,
    :username => DBUserName,
    :password => DBPass,
    :database => DBDatabase,
    :encoding=> DBEncoding,
    :socket=> DBSocket #drop this option for windows machines 
  )
rescue Exception => ex
  fout.puts "Error connecting to mysql : #{ex}"
  puts "Migration was terminated due to connection error , check out the log file"
  break
end

######################
# Define models
######################

class Employee < ActiveRecord::Base
 belongs_to :manager
end 

class Manager < ActiveRecord::Base
 has_many :employees
end

Answer 2

Serious Ruby ORMs don't do this for a reason: it's inflexible, not explicit enough and too much magic.

If you really want this in your project, try coding it yourself in Ruby. Here's what I've done in 5 minutes on top of ActiveRecord:

require 'active_record'

class ActiveRecord::Base
  def self.magic!
    connection.tables.map { |table|
      klass = Class.new(self)
      Object.send(:const_set, table.singularize.camelize, klass)
    }.each { |model|
      model.column_names.grep(/_id$/).each { |foreign_key|
        name = foreign_key.sub(/_id$/, '')
        model.belongs_to(name)
        name.camelize.constantize.has_many(model.name.tableize)
      }
    }
  end
end

Let's set up our database tables and have magic! analyze them:

ActiveRecord::Base.establish_connection(:adapter => 'sqlite3', :database => ':memory:')

ActiveRecord::Schema.define do
  create_table "managers" do |t|
    t.string "name"
  end
  create_table "employees" do |t|
    t.string "name"
    t.belongs_to "manager"
  end
end

# it happens!
ActiveRecord::Base.magic!

Create some test records and use it in the way you wanted:

mislav = Manager.create :name => "Mislav"
mislav.employees.create(:name => "Josh")
mislav.employees.create(:name => "Mike")

Manager.all.each do |manager|
  puts manager.name
  manager.employees.each do |employee|
    puts employee.name
  end
end

Full code available on this Gist.

This only works with belongs_to and has_many associations. If you need a real library that does this and much more, check out Dr Nic's Magic Models.

Answer 3

You should take a look at the SQLSoup extension in SQLAlchemy. It claims to do all this for you (including foreign keys). I haven't tested it myself.

Answer 4

Try the sqlautocode module for SQLAlchemy. It will generate the ORM classes by reflecting the database.