Designing a database interface for programmers
How do you connect to and converse with your database of choice (MySQL, in my case)? Chances are it’s largely formed by the programming language you choose. I’ve worked with a variety of programming languages. Anytime I begin learning a new one, I am reminded again that there’s more than one way to skin a cat.
Over the last few years, I’ve mostly programmed in Perl and shell. The shell interface to MySQL is simple: the mysql command-line tool. There is not much to think about. In Perl, however, the standard is to use DBI. I have grown accustomed to DBI over the years, but that doesn’t mean I like it. I think it’s one of the worst database APIs I’ve seen. The abstractions it uses (there are only two object abstractions: connections and “statement handles”) are awkward to the extreme.
It does get worse, though. PHP historically used libmysql’s C library for connecting to MySQL, and similarly used native drivers/libraries for every other database. You had no notion of abstraction: you called the library’s functions directly. Sure, there were PEAR libraries to wrap around this, but a) they were ugly too, and not really a PHP standard, and b) the language could have done us all a favor and defined a common interface that all of the drivers could be wrapped inside. That’s what every other language tends to do, love it or hate it. When I wrote a lot of PHP, and this was before the newer drivers were available, I wrote a set of classes to abstract away from the raw libmysql C function calls.
Guess what design I chose to emulate for that? Microsoft’s. Microsoft’s database interaction libraries make a lot of sense for me. There’s a notion of a Connection. You can create a Statement that’s tied to the connection, and when you execute the Statement, you get a Result, and so on. This is fairly consistent for VBScript (ASP), Visual Basic, the .NET class libraries, and so on. It is a clean and straightforward design. The functions you want to execute map to verbs you think about, and the nouns/objects are also obvious. You execute a Statement to get a Result.
Think about that in contrast to Perl, where you prepare a Statement against a Database (not a Connection?) and then execute a Statement ($sth) to get… nothing but an indication of whether it succeeded, and then you start retrieving rows from… the $sth? Huh? But rows don’t belong to a Statement, they belong to a Result! Oh, I get it, after I execute a Statement, it shape-shifts into a Result. And then I can re-execute it… bleh. All of the nouns are overloaded with several meanings because there aren’t enough nouns to represent the variety of concepts, and the verbs are attached to the wrong nouns, and it’s just ugly.
Funny enough, I never wrote a sane interface around DBI. (It’s already a wrapper anyway.) I just dealt with it. But I never liked it, and somehow I must have been like the frog in the teapot and never jumped out.
I’m learning a new language now — Go — and it’s time to learn how Go wants me to think about databases. Will it be sane or awkward? I don’t know yet. The documentation doesn’t take any time to introduce it, so I’ll just work through it and see how it’s done.
The “execute Statement to get a Result” sounds simple. But how does that work if the API support asynchronous access?
And please blog about your experiences with Go
Daniël van Eeden
23 Oct 12 at 3:28 am
When I started talking to databases, it was with Java. There weren’t any fancy frameworks back then (or none that I was aware of) – you programmed with plain JDBC.
So you had a DriverManager, a Driver, a Connection, a Statement (or a PreparedStatement), and a ResultSet (later on you also got DataSource. I date back to java 1.1).
It actually still works like that today, only you get so many abstraction frameworks like Hibernate and Mybatis (formerly Ibatis), Spring-JDBC and apache-commons (db).
My experience: due to some legacy code I still have to struggle with Connection/Statement/ResultSet etc. It is ugly programming. And it gets you tired and unwatchful. I mean, the only thing on your mind is: “I want to abstract this and get it over with”. At the very best, you may want to throw some _hint_ about what kind of connection you want to get (read/write, read-only, OK for lagging connection, etc.). Otherwise at all managing connections and statements and results sets is plain beurocracy.
Let me give an example: you can use the same statement to execute more than one query. Or you can choose to create a new query by creating a new statement within the same connection. Or, you could ask for a new connection, new statement, new query. How many programmers out there know the difference between the three? How many know the difference between just the first couple? How many want to know?
This hierarchy means more API to manage. You need to pass a special flag so as to get the AUTO_INCREMENT number. You must invoke another method to learn the names of column returned by the ResultSet. And, you only do that on first iteration, since they’re all the same. And this pattern emerges: the pattern of beurocracy programming.
It’s good that there’s a clear hierarchy on the low level; but as it is, there’s a good reason why everyone’s writing frameworks: they are letting you just write code!
Sometimes it goes too far, and complex ORMs can make a programmer loose hair; but the simple abstractions, e.g. Spring-JDBC, or PHP’s CondeIgniter ActiveRecord (not a real ActiveRecord implementation), can make SQLing and coding ever so simple.
The way the language wants you to talk to databases is irrelevant. Someone will write a good framework, that opens and releases connections for you, auto-detects column names on your result-set, auto-detects columns types and applies those types to your variables, pools your connections etc. Or you will write it. You will have your own interface to databases, and you will send queries and get results.
Shlomi Noach
23 Oct 12 at 5:09 am
I like DBI a lot, much more than the Python MySQL library. The design of DBI is influenced by the time it was created and the desire to keep the overhead of OO programming to a minimum. That’s less of a concern now, and most people use Rose::DB::Object or DBIx::Class as an ORM on top of DBI these days.
When discussing a next generation of DBI, Tim Bunce has said he intendeds to copy the JDBC interface, since it is widely known.
Perrin Harkins
23 Oct 12 at 11:11 am
I’ve explored The Go Way a bit now. It’s rather low-level and has some abstractions I think are arranged wrongly. I’ll write something about it later.
Xaprb
24 Oct 12 at 8:05 am