Comparing SQL and NoSQL Databases

Introduction

A few years ago, The Economist published an article claiming that data was the "new oil". Now, the piece focuses more on the role of some of the bigger tech companies in our modern "oil" economy, but the point still stands: data is a powerful resource.

Thus, you could argue that it's very important that developers like us do everything we can to understand the safest, most scaleable, and most responsible ways to be good stewards of this data.

Good stewardship can mean a lot of things, but today we're going to focus on data storage. And while storage is admittedly not the most exciting of topics, we'll learn later how storing data could be one of the biggest decisions you make when building your application.

For storage, we typically utilize a database. Generally speaking, there are two types of databases: SQL (stands for "structured query language") and NoSQL ("not only SQL"). While their main responsibility is the same, there are some fundamental differences that are pretty important when thinking about building your app, so let's talk about the biggest one and then quickly touch on some of the most popular SQL and NoSQL systems.

SQL Databases are relational

So what does relational mean?

Well, over the past few weeks, we've been working on some group projects that require us to use a SQL database. Believe it or not, our longest group meetings have been entirely focused around designing the schema or the organization of our data. The reason why this can take quite long is because how you design your schema will directly impact not only how your data is stored, but also how you can access it.

In a way, you almost have to completely visualize your data flow to ensure you're accounting for all the data you will need and how everything will relate to another.

The image above is simple, but just complex enough to illustrate this.

Here we have a schema design that stores a player's scoring total. But in order to do that, we need to make sure we know what team the player is on, and to know the teams, we need to know which league (NBA, NFL, etc.), plus we probably have to keep track of the week that player scored these points.

Each block (leagues, teams, etc.) is what's called a table (more on that in a bit) and each entry into that table has a unique ID, which is in bold. In order to refer to the information stored in that table, we have to refer to that ID, which you can see in the lines.

Looking at that illustration gives you a pretty good idea of where the "relational" part comes from. It can certainly be a pain determining how each table will relate, but if done correctly, it gives you access to some pretty powerful queries within SQL.

NoSQL databases are not

NoSQL databases, however, are much less structured. Instead of pre-determined tables, NoSQL databases have a variety of options of storing data, but for this post we'll focus on document-oriented.

Generally, the NoSQL approach is pretty much throwing whatever you can into the pot and even changing the recipe on the fly too. Using our player score tracking example from above, forget needing the league, just create an entry (a document) with their score and throw it in!

One of the benefits to storing data is this way is the flexibility, especially when you're working with a large amount of data with varying amounts of relational components. This is why you'll generally see NoSQL databases preferred in projects focused on using Big Data or large sets of distributed data.

Using a SQL database

As I mentioned earlier, using a SQL database requires you to design your schema ahead of time, so let's build a simple table that stores Users.

For this example, we'll be using a system known as MySQL which is completely open-source and has great documentation online.

CREATE TABLE `User` (
  `Id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `Username` VARCHAR(50) NOT NULL,
  `Phone_Number` VARCHAR(12),
  `Email` VARCHAR(50) NOT NULL UNIQUE,
  `Image_Url` VARCHAR(160),
);

To make an entry to that database, we just make a query like so:

INSERT INTO User (Username, Phone_Number, Email, Image_Url) 
VALUES ('hulkamania', '555-555-5555', '[e-mail]', 'hulkSmiling.jpeg');

Once complete, we'll see a new row with all of the data we supplied lined up with the respective column. But you should notice that everything had to be in order to make sure it's implemented correctly.

Using a NoSQL database

MongoDB is one of the most widely used NoSQL databases, so let's follow the crowd. To interact with the database, we can use Mongoose which uses plain JavaScript to build our documents.

Even though MongoDB and other NoSQL databases don't require a schema, we can still use Mongoose to build one for us, but they're a lot more flexible than SQL queries.

Sticking with the wrestler theme, let's create a wrestlerSchema.

const wrestlerSchema = new mongoose.Schema({
  name: String
});

Next, we need to create a Model, which will then format all of our incoming documents.

const Wrestler = mongoose.model('Wrestler', wrestlerSchema);

// With our Model established, we can add a wrestler with the new keyword

const machoman = new Wrestler({name: 'Macho Man Randy Savage '});

Plus, we can always go back to our Schema within Mongoose and make changes as needed and there won't be any impact to the documents or Models we already have stored. Whereas in mySQL, we typically have to re-arrange an entire database.

Conclusion

It's pretty hard to understate our vital data is to our new digital world, making it extremely important that we know how to deal with it, and making it a valuable resource of information.

Choosing a SQL or NoSQL database is a big part of that exercise, so I hope this post helps you understand some of the fundamental differences. To keep things simple, I skimmed over a few details like implementation of these databases or utilizing queries in your functions, but feel to check out the links I've shared throughout the post to explore further.