Managing to coordinate Ruby with a legacy DB

/ 编程, 教程, ruby, english

Recently I’ve been pushed to a request to take over another project, which project is written by PHP using MySQL, and the main DB data of this project is from API of another project, that project is using Microsoft’s SQL Server 2005.

Of course I’ll choose to use Ruby to refactor this project and do new feature developments. But this also means I’m facing a situation to develop a project that will hold three different DB connections at the same time.

Rails

First of all, Rails itself naturally support connecting to multiple DB in one project.

Let’s say I have two DB, the primary one is using PostgreSQL, the other one is using MySQL. Easily we just need to modify the config/database.yml file:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
development:
  primary:
    adapter: postgresql
    host: localhost
    database: apple_development
    username: yanying
    password:
  banana:
    adapter: mysql2
    host: localhost
    database: banana_development
    username: root
    password:
    migrations_paths: db/banana_migrate

And then we specify the target DB with connects_to in the model file such as app/models/users.rb:

1
2
3
 
class User < ApplicationRecord
  connects_to database: { writing: :banana, reading: :banana }
end

Rails with legacy DB

https://developer.oracle.com/dsl/haefel-oracle-ruby.html

Exactly like the content of link above from almost 10 years ago people says, Rails is a framework assumes lots of things, including the style of DB table names and column names. It’s very sure Rails is not friendly to a legacy DB, although we can modify lots of things such as using alias_attribute, ignored_columns and so on. But that is definitely not enough.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
class User < ApplicationRecord
  connects_to database: { writing: :banana, reading: :banana }

  self.table_name = "H_A_user"
  self.ignored_columns = [ :unused_column_name ]

  belongs_to :orders, optional: true, foreign_key: "order_code"
  has_many :payments, class_name: "Payment", foreign_key: "payment_code"

  alias_attribute :nick_name, :nickName

end

I’d even tried to refactor the source code of activerecord to let it support other missing customized features:

https://stackoverflow.com/questions/9535989/manually-set-column-name-in-rails-model

https://github.com/yanyingwang/active_columns_mapping

But my trying turns out Rails is such a strong framework with so many codes, both activerecord and activemodel as one part of it are following the basic rule of Rails, and as a whole they are not designed to be used for a foreign legacy DB.

Sequel

Luckily there is another ORM framework called Sequel in the Ruby world.

Sequel is a very agile framework, providing sufficiently abstract granularity, and those advantages make it very suitable for connecting to a legacy DB.

For example, Sequel is defaultly able to cast the DB table names and column names to Ruby snake case style words.

Beside, Sequel has a much better compatibility with the versions of DB. For instance, I’m trying to addin a MSSQL connection with version 2005. If I’m using activerecord I have to use Rails version 4.0, because the newtest adapter have given it up and only support SQL Server 2012 or higher. But the newest Sequel works very well with SQL Server 2005.

To initialize a DB connection with Sequel, We can just put code to config/initializers/sequel_conn.rb:

1
2
 
ENV['TDSVER'] = '7.0'
OrangeDB = Sequel.connect('tinytds://user:password@localhost:1433/dbname')

And then we can put all the Sequel model files to the lib dir, as we may have a file app/lib/orange/user.rb like:

1
2
3
4
 
class Orange::User < Sequel::Model(OrangeDB[:H_m_user])
  many_to_one :role, key: :role_code

end

another file app/lib/orange/role.rb like:

1
2
3
4
 
class Orange::Role < Sequel::Model(OrangeDB[:H_m__a_role])
  one_to_many :users, key: :role_code

end

Remember to add config.autoload_paths << Rails.root.join('lib') to config/application.rb file to enforce the code loading of lib dir.

And now open the rails console try it:

1
2
3
4
5
 
Sequel::DATABASE

u = Orange::User.last
r = u.role
r.users

Check more Sequel Docs at http://sequel.jeremyevans.net/rdoc/ and Plugins at https://sequel.jeremyevans.net/plugins.html.

Practical Sequel Usage

plugins and configurations

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
Sequel::Model.plugin :association_pks
Sequel::Model.plugin :pretty_print # https://github.com/umbrellio/sequel_pretty_print
Sequel::Model.plugin :many_through_many # https://sequel.jeremyevans.net/rdoc-plugins/classes/Sequel/Plugins/ManyThroughMany.html

YourDB = Sequel.connect("mysql://username:password@ip:port/dbname")
YourDB.extension(:pagination)

YourDB.loggers << Logger.new($stdout) if Rails.env.development? or Rails.env.staging?  # show sql query on the console for dev and staging env.
Sequel::Model.class_eval do # make the https://github.com/rails-api/active_model_serializers available for sequel model.
  include ActiveModel::SerializerSupport
end

other plugins

  1. LazyAtributes: Access instance’s attribute only if you’re really using it, for my instance, this is useful to avoid loading errors of some columns of model.
  2. StaticCache: I have a model which’s data is almost unchangable.
  3. Caching: I have a model which’s data only would be changed in a very rare time.

querying

1
2
3
4
5
6
7
8
 
# sql like:
User.where(Sequel.like(:name, 'Yanying%'))
# https://sequel.jeremyevans.net/rdoc/files/doc/core_extensions_rdoc.html#label-like

# sql join querying:
User.association_join(:orders).where(Sequel[:orders][:no] => "OR-2021-11-12-abcdeff")
# sql join querying with distinct:
User.eager_graph_with_options(:orders, limit_strategy: :distinct_on).where(Sequel[:orders][:no] => "OR-2021-11-12-abcdeff")