Selecting RDBMS for Node applications

In this article I will bring about some of the RDBMS to consider for Node applications. Having used SQLite for one of the .Net applications I thought to use the same for a Node application without doing any prior research into it. Besides it it one of those areas where it is not enough the benefits of each. Without any further delay let me bring about the comparisons of them.

1. Functionality -

Both the sqlite3 or sqlite and the mysql2 npm's do as stated and there is no issues with that. Sqlite and the mysql packages has implementation of single threaded execution of queries in cases where you like to do one transaction after the other like say CREATE table abc IF NOT exists; and INSERT INTO table abc VALUES ("one", 2, "three");

2. Performance -

I will bring about the comparison in terms of data retrieval through an api and data load as well. i. Data retrieval - Both sqlite and mysql did fairly similar in terms of fetching data and not considerable differeneces were found. ii. Data load - I had a table of about three columns ( int(11), int(11), time) types. The time to load about preciseley 1879691 rows in sqlite was about eleven hours. The same table was loaded in about six minutes.

3. Disk usage -

In terms of size it was not possible to do a one to one comparison. However, the size of the app's sqlite database was about 116 mb whereas the mysql export dump stood to about 56 mb with an additional medium sized table.

4. Data concurrency -

This is interesting as my app needed to load data periodically say every fifteen minutes or so. Unfortunately this happened to be one of the app's important requirement. The interesting fact found here was that I noticed the api calls needing the data from the databases were not able to do so as the database was locked during the process although the retrieval call was for an irrelevant table. This was one aspect which made to think about other options and some were posgres and mysql. Although I never tried posgres, mysql already catered to most of the requirements. This might necessarily be database specific I did not want to spend further time as I had options.

5. Installation or setup -

Adapters for both are an easy npm i sqlite3 or npm i sqlite or npm i mysql2. I tries the sqlite3 as it had higher downloads, however I saw that sqlite seemed to be newer which I didn't try.

Installing the mysql ot the sqlite client on a *nux should be pretty straight forward and there should be relevant pages for your distribution for the same. Mysql involves a bit of more time in setup as you may have to run security script, create users, add priveleges and all.

6. Database server -

I am afraid ony the mysql can run as a systemctl service and can be hosted on a remote server and might not be the same for sqlite which is a file database.

7. Other options -

If using large data, I mean like large large data, say for machine learning or data science or similar, might be posgres is a better option which I haven't tried yet. I am assuming it to be equivalent to Oracle and have complex features and lot of addons exist for it.

One other package I like to bring to light is the nedb nosql which I used initially for the app. The interesting facts that lead me to RDBMS I will document in another post in this blog.

Summary -

This article is a rough comparison on the avilable open source RDBMS for your NodeJS app. The decision can solely lie on the aspects of your situation. Say if you need a simple file only data store or a server running for it. Might be the features are better in a different ecosystem like dotnet. It all depends on the cases to case by situations.