malvasia bianca

One of the things I need to do in Ruby is read and update data stored in an existing SQL database. Not wanting to reinvent the wheel, I thought I’d look at existing libraries that provide this functionality. The pickaxe book didn’t give anything useful, but I saved some posts in a newsgroup thread on the topic a couple of months ago, and looked through them.

The libraries that the thread referenced largely fell into two categories. Some of them, like Ruby MySQL, provide quite low-level access to a database. There are classes to wrap some basic constructs (result sets in particular), but you’re pretty much grubbing with the SQL code and data directly.

Other libraries were polar opposites. The most extreme there was Active Record, from Ruby on Rails. In that library, not only is everything abstracted away into Ruby objects, but the Ruby objects themselves actually drive the database structure.

Active Record sounds great to me: everything is defined in one place, and what could be nicer than having refactorings in or changes to your code propagate themselves to your database layer? It is famously known as “opinionated programming”, meaning that its author has ideas about how to structure your database, and if you don’t agree with those ideas, the library isn’t for you. I have no problem with that: I approve of prioritizing clean code over unneeded generality, and I have no reason to believe that I would disagree with the author’s opinions, were I better informed.

Having said that, I decided not to go with Active Record for now. For one thing, I have an existing database schema that I don’t want to modify right now. I don’t know for sure if Active Record would get along with it well, but I suspect that it wouldn’t, and I’m afraid that it would be easy to screw up my database (which has other users that I want to preserve for now) by going with Active Record. Also, I’m doing this for fun and for didactic reasons; reusing others’ code isn’t essential for either of those, and it wasn’t clear to me that going with Active Record would further those goals in this particular case. (I am keeping my ears out for new programming projects that would give me an excuse to use Rails, because I do want to learn about that at some point.)

I think that the level of abstraction that I really want is something more along the lines of Java’s JDBC. To be sure, there were many things about JDBC that annoyed me, but at least it was database-independent and had a few useful abstractions built on top of the bottom level. (Using updatable result sets to add new rows and modify existing rows, for example, which I would have to do by hand-crafted SQL code with the Ruby MySQL libraries, as far as I can tell.) I wouldn’t mind something a little more abstract than that, but I still have more to learn about SQL, and I’m in the mood to not have too much going on behind my back, so this level of abstraction seems appropriate.

Maybe there’s a Ruby library out there that can do what I want at that level of abstraction, but I didn’t see one. And, the more I thought about it, the more it seemed like fun to build a level of abstraction like that (narrowly tailored to my needs, of course) on top of a low level library. It seemed like the sort of project that might help me get my hands dirty with aspects of Ruby that I wouldn’t learn about by, say, implementing algorithms, and getting my hands dirty with SQL occasionally wouldn’t hurt, either. So I think I’m going with rolling my own.

One thing that bothers me, though, is being tied to MySQL by my choice of low-level library. That just seems wrong from a philosophical point of view; from a practical point of view, it could interfere with testing. One problem I’ve had when writing code interfacing with SQL is that it largely restricts me to acceptance tests instead of unit tests for certain kinds of code, because running tests takes so long. (Seconds instead of milliseconds.) The obvious solution to that would be to connect to a faster database (hopefully a purely in-memory one) for purposes of unit testing. So it would help a lot if there were some intermediate layer that gave me database-independence. I guess I should look into ODBC to provide that intermediary? And maybe SQLite can be the fast database that I need? I’ll do some research.

In the mean time, I’ve started writing some client code for my hypothetical SQL abstraction layer, to give me an idea about what sort of interface I’d like.