A frequent task I encounter at the office is writing scripts against pre-existing databases. Sometimes I am hooking up to Oracle, in other cases it may be MySql as well as sql server.

What I'd like is really a tool which may reverse-engineer the database's tables and foreign secrets and let me write OO-style scripts from the database. This may be in almost any language really, but python or ruby could be preferred.

For instance - this really is my ideal ruby script: (presuming the manager and worker tables already exist with foreign secrets)

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 kind of library exist? If that's the case, it might save me a lot time!

Edit - the primary feature I'd like is for this to instantly uncover foreign key associations in the database metadata without clearly mapping them - I've attempted ActiveRecord, SQLAlchemy, Follow up, and DataMapper, and from what I will tell, not one of them can perform this.

Serious Ruby ORMs avoid this for any reason: it's inflexible, not explicit enough and an excessive amount of miracle.

Should you really want this inside your project, try coding it yourself in Ruby. This is what I have completed in a few minutes on the 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 us setup our database tables and also have magic! evaluate 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 employ it in the manner you desired:

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 is only for belongs_to and has_many associations. Should you prefer a real library that performs this plus much more, take a look at Dr Nic's Magic Models.

You need to have a look in the SQLSoup extension in SQLAlchemy. It states do all of this for you personally (including foreign secrets). I've not examined it myself.

Try the sqlautocode module for SQLAlchemy. It'll create the ORM classes by reflecting the database.

In Ruby you will find several ORMs like ActiveRecord, DataMapper as well as Sequel.

For instance using ActiveRecord you'd 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