2 posts tagged with "nodejs"

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.

In this article I will talk about the reasons for why to choose either a SQL or a NoSQL or even having an hybrid of both or your NodeJS applications. I will talk about the nedb for NoSQL and say mysql for the SQL.

1. Installation -

   Both nedb and mysql installation is similar like npm i nedb or npm i mysql2. In terms of mysql you might have to follow a few additional steps as documented in the other article.

2. Disk size -

With nedb one part is evident is that data is stored, however there is a higher chance that you have more duplicates in it than the SQL one. The json object is more or less saved as is. With the SQL option the duplicates can be mitigated before loading the data into the table.

3. Performance -

Data retrieval times for both the SQL and the NoSQL seem to be similar but SQL could fair better in terms of how data is organized and adding indexes to the relevant column or columns.

One aspect here is working more with the SQL is always helpful. There could be situations where data retrieval is really slow and all that was needed was to add an index to required column.

In terms of data load the NoSQL could take like for about eight plus hours for about 1879691 rows of three columns ( int(11), int(11), time) types and SQL like mysql could do it in about six minutes. One situation I faced was say I had a table about six hundred unique fields and since it was taking forever to load it, I tried to separate the unique files into independent tables or databases. However, there were other issues like file wrtes as well when this was done and a higher chance of data loss or discrepencies.

4. Data retrieval -

Complex data retrieval is mainly possible through the RDBMS soltuion alone like the SQL JOINS or the INs . One might consider having additional columns instead of a JOIN could do for now, but it is all situational as well.

5. Skills -

Having used the SQL for quite some time ago, it was one part to revisit it and learn it. Honestly identifying the data types like CHAR(10), INT(11), TIME(), DATETIME() and implementing it in the app was well worth the effort I must agree.

Summary -

No doubt that NoSQL is the word in every one's mouth lately, however it mainly depends on the need fo the application. Say if it is a simple log store is what is needed, nedb might be the way. Even for situations like mine where the data needed on the website is fetched from another system, nedb was failry doing well.