Best Ruby ORM for Wrapping around Legacy SQL Server Database?

Question

Hi. I found this answer and it sounds like almost exactly what I'm doing. I have heard mixed answers about whether or not datamapper can support SQL Server through dataobjects. Basically, we have an app that uses a consistently structured database, consistently named tables, etc in SQL Server. We're making all kinds of tools and stuff that have to interact with it, some of them remotely and so I decided that we need to create some common, simple access point to do read/write operations on the SQL Server app since it's API is all C# and other things I despise.

Now my question is if anyone has any examples or projects they know of where a ruby ORM can essentially create models for another application's legacy database by defining the conventions of each model's pkeys, fkeys, table names, etc. Sequel is the only ORM I've used with SQL Server but never to do anything quite like this. Any suggestions?

Answer 1

ActiveRecord is one option that ought to be able to do what you're describing.

So stuff like this ought to be possible:

class Order < ActiveRecord::Base
  establish_connection { #a hash describing your SQL Server connection attributes }
  set_table_name 'PRODUCT_ORDER' # or whatever
  set_primary_key :PROD_ORD_ID
  has_many :order_lines
end

class OrderLine < ActiveRecord::Base
  establish_connection { #as above }
  set_table_name 'ORDER_LINE'
  belongs_to :order
  has_many :products # etc...
end

order = Order.first
order.order_lines.each { |line| puts line }

Answer 2

I can't give you a fully worked example, since I've only started dabbling in using Ruby with MS SQL Server, but based on my experiences and what you've said, I would suggest using the model facility of Sequel.

Sequel definitely supports and is tested against MS SQL Server, but the situation seems a bit less clear cut with Active Record. I haven't used DataMapper, so can't comment on that. As far as I know, the only ORMs for Ruby are DataMapper, Sequel, Active Record 2 and Active Relation/Active Record 3 (the new Rails 3 data access stuff).

There is an Active Record adapter for MS SQL, but it didn't work for me at all with MRI 1.8.7. The IronRuby team at Microsoft are using a version of this adapter to run IronRuby on Rails with MS SQL Server, so you might consider trying an Active Record on IronRuby combination. IronRuby is currently at Release Candidate stage, so this should work, although there is definitely still a lot of bug fixing going on at the moment.

Answer 3

Sequel doesn't automatically create models, but you could write an extension that took the output of Sequel::Database#tables and Sequel::Database#schema (for each table), and did some analysis of the keys and table names to guess at associations. The reason Sequel doesn't do this by default is that it isn't possible to do correctly in all cases. But if your database always follows certain conventions, you could certainly get it to work.