SQL Databases in Node.js

Node.js is one of the best current platforms available for creating back-end applications that expose REST APIs. Often such applications are backed with a database. Nowadays, many NoSQL databases are available that integrate easily with the platform and operate using JSON data format and associated principles – so they’re natural to both a JavaScript platform such as Node.js and the modern REST APIs. These databases usually offer speed, conceptual simplicity, as well as massive horizontal scalability and parallelism – all great when the application, like many modern social media platforms and other services exposed to the end users on the Internet, works with large numbers of users. At the same time, the structural complexity of the data in the context of a single user is usually relatively simple and therefore can be handled perfectly by a NoSQL database.

Sometimes, for back-office use cases such as reporting, the data is extracted from the OLTP database into a separate (usually SQL) database where complex queries can be run against it without affecting the end users’ experience. The Node.js ecosystem offers good, popular, and stable tools for working with NoSQL databases, such as mongoose for MongoDB, that, coupled with a web application framework such as Express, makes writing modern back-end applications easy and fun.

However, there is also another type of a back-end application backed with a database. This type of application runs for a limited number of users, such as company employees or paying customers, and deals with substantially more complex data structures stored in the database. Amazingly enough, SQL databases or RDBMS, a technology introduced in the 1970s, is still the choice! Why do we still choose it? Yes, there are cases when an RDBMS is chosen simply because it is something that developers know, or otherwise inherited. Often in such cases the features characteristic to the RDBMS are either not used or used poorly, inevitably leading to a mess. If we choose to use an RDBMS today we better be able to justify it by using such features as complex transactions, foreign key, uniqueness and other data constraints, reasonably normalized data structure, carefully typed data fields, and the ability to use SQL to query the data. All of that to maintain a clean, well organized, valid set of data that makes sense even outside of the context of the application that uses it.

Unfortunately, the basic RDBMS concept of representing data in a two-dimensional tabular format, that worked so well for decades, has now became very limiting for modern REST API back-end applications as it does not match the hierarchical way of representing data in, say, JSON. Writing and later maintaining a Node.js back-end application that uses low level modules to access the SQL database directly, similarly to a Java application using naked JDBC, is a hellish experience as soon as we start dealing with more than a dozen related data object types and API endpoints. Sure, following the popularity of ORM frameworks in the Java world, Node.js offers some similar high level solutions. However, given a certain complexity of the application, these become impossible to use efficiently either. Usually, if working with such an ORM framework in your application does not frustrate the hell out of you, it means you could easily do with a NoSQL database and you don’t really need an RDBMS.

Some time ago I wrote a framework for Java, called Thymes2,  to solve this problem. Thymes2 uses advanced algorithms for the automatic building of sometimes very complex SQL queries and SQL query sequences. In some cases Thymes2 may even utilize transaction scope temporary tables on the database side and data merging on the client side to work with complex data structures represented in the application level with REST API friendly POJOs easily transformed in and out of the JSON format. I was very satisfied to use the new framework for the development of one of our applications, called Bishop, used to organize the operation of a manufacturing business in Pennsylvania. But Java is Java and no one who has been working with Node.js will ever go back to writing back-end applications in Java unless forced to by some circumstantial reasons. Having the exact requirements for a robust, high-level SQL database access tool crystallized during the real life project development that involved a few dozens of related persistent object types, I am working now on a similar tool for Node.js called X2Node framework (or Thymes2 for Node.js). I hope to publish it soon and offer it up for the developer community’s judgement. More soon…