Efficiently parsing SQL query result sets into the hierarchical data structures with which applications normally operate has been a problem for quite a long time. Numerous attempts have been made over what feels like the ages to solve the problem, the essence of which is that the strictly two-dimensional grid nature of what’s returned by a SQL SELECT query – those rows and columns – map very poorly to the tree. More generally speaking, they don’t suit the graph-like data structures utilized by modern applications to model the world.
It’s simple when one result set row maps to one object that represents the record and every column in the row maps to a property of that object. Things become more complicated when some properties are nested objects themselves. Things become severely more complicated when some of the object properties are collections with an unpredictable number of elements. Things become murderously complicated when the object has several such properties.
At Boyle Software we’ve been creating server-side applications that work with SQL databases for a very long time too. Over that time, I’ve settled on a certain approach and have been successfully using it in a wide range of applications. The goal is to load all the necessary data using as few queries as possible, which often helps with transactionality, minimizes the I/O, and lets the database engine do the part of the work that it does best.
I just finished formalizing the solution and published a Node.js module with the result set parser. The module is available at https://www.npmjs.com/package/x2node-rsparser. It is part of a larger framework, called X2 Framework (pronounced “times two”) for Node.js, which I will be gradually releasing, piece by piece, as time allows. The framework is a collection of tools and approaches that I have been using for server-side application development, made more generic, formalized, and documented. I will be announcing when other note-worthy modules are released – the next one planned is the one that automatically builds queries for the result set parser. More soon…