Go Back

Why I Use SQLite Instead of MongoDB for my Web Apps

Posted by Charlie Ontiveros on 2018-05-21

When I first started learning web development, I couldn't help but see mention of MongoDB everywhere. MongoDB is easy to use, MongoDB can scale to immense proportions, schemas bah! MongoDB is the next best thing since sliced bread and doesn't need them. In fact, the MEAN (MongoDB, Express.js, AngularJS, Node.js) stack is one of the most popular web development stacks out there.

Although I had previous experience with SQLite, I decided to check out MongoDB on route to creating my first web app. After all, the internet couldn't be wrong, could it?

First Impressions

After downloading and installing MongoDB, I ripped through the first part of the tutorial. Eagerly setting up a conf file and smashing a few commands into the Mongo shell. I come from the Windows world, and while I'm not afraid of the command line, I also dislike having to learn lengthy commands. Fortunately, setting up MongoDB wasn't too difficult. The biggest struggle I had was setting up the config file correctly with specific settings. So far so good.

The next step was to walk through a few database operations. Creating a collection, inserting data, updating data, deleting data. You know, the basic DB operations. That turned out to be a real joy. No need to use SELECT, FROM, WHERE, instead, I could go directly to the data of interest simply by drilling down the collection of interest.

Once I had become familiar with the Mongo shell, it was onto the next tutorial. After a bit of searching, I found one from Mozilla Developer Network (MDN), that went over using MongoDB with Express.js. I didn't get very far until I ran into something called Mongoose. I didn't know what it was, but man, what a cool name.

So it turns out that Mongoose is an object relational mapper (ORM), something I was familiar from my previous days when I tinkered with SQLAlchemy in Python. This immediately set off a flag in my head. Why are we using an ORM to create a schema, something that added structure to MongoDB? And wasn't the fact that MongoDB didn't need a schema one of its top selling points?

I continued my tutorial with more than a few questions in mind.

One Foot in the Bandwagon...

As I continued the MDN tutorial, everything looked more and more like the same stuff I would do with a SQL DB. Create data models and a schema to use with Mongoose. This all required thinking about... relationships! At this point, I was starting to become "woke" from all the MongoDB hype. Could it be possible that the internet lied to me?

Now, just to be clear, I understand that setting up an ORM is different from setting up a DB schema. However, these two are awfully related. While I completely understand the use of a schema to structure the data and create an object mapping, why not just use a SQL database to start with? Although SQL languages by themselves do not have this ORM like capability, there are plenty of third party ORM libraries to choose from when using SQL.

Despite this, I pressed on, still driven by my desire to learn. I also wanted to know more before making a final decision on whether to use MongoDB or SQLite.

Decision Time

Once I finished the tutorial, I started to data model my first web application, Project TT (Time Tracker). I came up with the following entities:

  • Projects
  • Users
  • Entries
  • Categories

A user may have many projects. A user may also have many categories. (Originally, I thought of assigning categories to a project to allow for categories specific to a project. But alas, as it was my first full stack application, I decided to simplify it.) A project can have many entries. Each entry is linked to a category.

I considered using the following JSON structure in MongoDB:

{
    users: {
        1: {
            projects: {
                proj1: {
                    entries:{
                        1: {
                            id: 1,
                            description: "",
                            category_id: 1,
                            start_time: "",
                            end_time: "",
                            total_time: ""
                        }
                    }
                },
                proj2: {
                    entries:{
                        2: {
                            id: 2,
                            description: "",
                            category_id: 1,
                            start_time: "",
                            end_time: "",
                            total_time: ""
                        }
                    }
            },
            project_categories: {
                1: "Planning",
                2: "implementation",
                3: "Design"
            },
            userInfo: {
                username: "",
                password: "",
                email: ""
            }
        }
    }
}

This would have worked fine for the initial stage of the application. But then I asked myself a few questions like, if I were to create a report to find out total times across all records, how would I do that? What if I wanted to create a teams feature to allow multiple people to work on one project?

After searching through Google and reading through the MongoDB documentation, it became clear that although this was possible, there was a steep learning curve to overcome. With SQLite, the answer was easy for me. Simply join together the entries' and the users' table and then filter it out.

While I'm sure my initial MongoDB schema probably wasn't that great and I could have improved it, at this point it was a really a question on whether to pile on another complex thing to learn on top of all the other things all new developers need to learn. I decided to stick with what I knew better, SQLite.

Further Analysis

Beyond hands on experience with both system, there are also other reasons why going with SQLite made more sense to me.

Like many other SQL databases, SQLite has been around for a very long time. It was first released in August 2000 (Wikipedia). It's very robust and has withstood the test of time as it is one of the top SQL systems used by developers today.

A lot of its popularity has to do with how easy it is to setup. It's actually easier to setup than MongoDB. Since SQLite is server-less, all one has to do is download it and install it. From there, it's just coming up with the data model and running a few queries to setup up the tables.

Because SQLite is, well a SQL database, the query language is very similar to other SQL applications, like MySQL and Microsoft's SQL Server. Meaning it's quite easy to transfer knowledge from one SQL application to another. I couldn't really say the same about MongoDB, at least not without spending a few hours checking out other NoSQL applications to do a comparison.

In regards to minimum requirements, SQLite wins hands down. Since SQLite writes to a file it's actually very easy to back it up and move it around. Its memory requirements are also very low compared to other DBs.

On the other hand, MongoDB created a large 100MB+ file on my hard drive. Upon further investigation, I found that it also needed a good amount of RAM to operate well. Although this was no problem on my personal computer, my web application was to live in a virtual machine somewhere in the cloud, ideally as cheaply as possible.

In conclusion, I chose SQLite because I personally found it easier to use and could not find a good enough reason to switch to MongoDB. Regardless of the choice, I still had to data model and come up with schemas, so no advantage to MongoDB there. Even if I didn't want to use schemas and just come up with simple storage for arbitrary JSON, SQLite can store JSON as blobs. SQLite and it's brethren share a lot more than MongoDB and its siblings, meaning the knowledge is more transferable to other SQL systems. Lastly, SQLite is actually easier to setup than MongoDB and has considerably lower operating requirements.