Modern SQL Server & Rails

Our latest guest post is from community contributor Ken Collins, aka MetaSkills. Ken is a software engineer at Decisiv Inc, author of the SQL Server Adapter for ActiveRecord, and organizer for the Hampton Roads Ruby Users Group 757.rb. He blogs at metaskills.net about all things Ruby, JavaScript and iOS related.

Last month, I released an updated version of the SQL Server Adapter that supports the latest version of ActiveRecord and the second generation of ARel, the relational algebra gem. As with all SQL Server Adapter releases over the past 2 years, this means we are passing 100% of the ActiveRecord test suite as well as a battery of our own tests specifically designed for SQL Server. As good as that news is, I want to share something even more exciting.

Unlike other adapters, the SQL Server Adapter supports alternate connection modes and our latest is going to make your life a whole lot easier. The connection mode is DBLIB and the library is called TinyTDS, a small Ruby C extension that wraps FreeTDS. FreeTDS is the open source implementation of the Tabular Data Stream used by clients communicating to Sybase and SQL Server databases. To appreciate this announcement, it is necessary to examine the history of our connection layer – specifically ODBC.

ODBC Connection Mode

The longest supported and most often used connection mode for the SQL Server Adapter is ODBC. Here is a diagram of the components that were needed for the adapter’s raw ODBC connection on a UNIX-based platform.

DBI => DBD::ODBC => RubyODBC => unixODBC/iODBC => FreeTDS

Besides looking like a train wreck, this stack was hard to install by the average developer. It was also difficult to maintain by our small community and coordinate patches with others. Lastly, it was very slow. Thankfully Erik Bryn, one of our core contributors, took up the work and removed the unnecessary DBI/DBD gems. His optimizations yielded a 15% performance boost.

So the ODBC stack was shorter. We no longer needed DBI and thanks to others like Klaus Gundermann, we were finally getting patches upstream to Christian Werner, the author of ruby-odbc. So what was wrong? The answer is plenty. There are still two very huge libraries in between our native TDS protocol.

Our Problems With ODBC

For the SQL Server Adapter, it is not the right tool for the job. There is simply no need to compile FreeTDS with an ODBC data manager and put two big hurdles in the way of your database. On a more practical note, here are some technical problems with ruby-odbc as a connection mode for the SQL Server Adapter.

• No client encoding options.
• Does not convert all data types.
• Large code base. Not tested. No visible repository or community.
• Does not handle query timeouts.

The ruby-odbc gem is not a good steward of character encodings. It does come with a UTF-8 variant, but that is a separate require and is buggy. Its all or nothing configuration leaves out many possible encoding options to be addressed by the end user. In the past, the adapter tried to account for the shortcomings of proper encoding support by our raw connections and we just had to give up. If encodings are not handled properly by the client connection, it is up to that library to fix it.

Some data types from ruby-odbc are returned as strings, most notably dates and times. This is a big performance hit under ActiveRecord since it will have to perform column reflection before casting these strings to ruby objects followed by a final conversion to the correct time zone.

Many things are changing with ActiveRecord and in general the Ruby community moves pretty fast. The ruby-odbc project is not keeping up with the times nor does it have a vested interest in performance optimizations that are driven by the needs of the SQL Server Adapter and/or the Rails community. I decided to write my first Ruby C extension to solve this problem.

TinyTDS - Modern, Simple, Tested, Fast!

TinyTDS allows us to talk directly to SQL Server databases using FreeTDS’s DB-Library API. It has a simple Ruby interface to execute queries and iterate over the results. Full details can be found on our documentation page, but I would like to highlight a few features.

Converting all SQL Server data types to native Ruby primitives is supported. Hooks for ActiveRecord’s :local or :utc timezone conversion are also provided. Result sets are Ruby objects that implement the Enumerable mixin which lazily loads the row from the wire as it is yielded to a block. The client connection allows almost any ICONV encoding to be used and FreeTDS automatically handles conversion to and from SQL Servers internal UCS-2 encoding.

TinyTDS is tested with SQL Server 2000, 2005 & 2008 using Ruby 1.8.6 to 1.9.2 and even JRuby’s latest 1.6.0 release candidate which has experimental support for C extensions. By avoiding the ODBC layers, leveraging FreeTDS’s built-in encoding support and using a few simple optimization techniques, TinyTDS is able to parse query results up to 5x faster than ruby-odbc. Those interested in switching from ODBC to TinyTDS can consult our wiki page that covers all the related details.

What About JRuby

Though I never use Java or JRuby, I have heard that many people have opted to do so specifically for connecting to SQL Server. I think Nick Sieger says it best:

It’s awesome that we can have better SQL Server support on MRI. The previous approach using ODBC was untenable, and for a long time the easiest option was JRuby + JDBC + JTDS. Though there’s no direct relationship to JDBC here, this should make for many happy Windows Rubyists who have no specific need for JRuby or Java.

So if you are on JRuby and do not mind using compiled C extensions, give us a try. The official SQL Server Adapter is highly tested and works very well.

Other SQL Server Adapter Details

The adapter project has two active branches. Our 2-3-stable branch continually accepts bug fixes and also supports the DBLIB connection mode using TinyTDS. Both of our 2.3/3.x versions also support an ADONET connection mode for IronRuby, though we are actively looking for someone in that community to maintain and test it. So if you are such a person, please contact us, we would greatly appreciate the help!

I invite anyone that uses the SQL Server Adapter to join the Google Group or stop by the #rails-sqlserver room on irc.freenode.net and help us make it better. Thanks and I hope to see you there!