SQL vs NoSQL: Pros and Cons for Building a Web App

[00:00 - 00:10] SQL databases use a defined schema for structured data. No SQL databases use a dynamic schema for unstructured or polymorphic data.

[00:11 - 00:16] But what does that actually mean? To find out, let's take a look at some live data.

[00:17 - 00:25] In my first browser tab, I have my MongoDB database. This database was created using the same source code from Lesson 4.9 from part one of the course.

[00:26 - 00:34] As you can see, each piece of data is stored as a JSON-like document. In my second browser tab, I have a Postgres database.

[00:35 - 00:43] I'll show you how to install and see your own Postgres database in the next few lessons. As you can see, data is stored in tables, much like a spreadsheet.

[00:44 - 00:52] Both databases contain the same listing data. However, in Postgres, there's a defined schema governing the shape of each listing.

[00:53 - 01:06] Let's head over to VS Code and start our node server. Currently, our server is still connected to our Mongo database.

[01:07 - 01:21] So if we execute a query for all of the listings, in our output, we'll see our entire listings collection. When we developed our server, we utilized TypeScript in our source code to define the shape of our listing data.

[01:22 - 01:30] However, it doesn't matter what technology we use in our source code, our Mongo database does not have a defined schema. So what does that mean?

[01:31 - 01:51] It means despite having a type definition in our source code, we can go over to our Mongo database and insert this into our listing. Now let's run the same GraphQL query and error.

[01:52 - 02:13] The key takeaway here is our Mongo database will accept any changes we make because it has no defined schema. If we delete that piece of garbage data, our query runs without any errors.

[02:14 - 02:25] Now try to do the same to a SQL database and we can't. That's the advantage of having a defined schema.

[02:26 - 02:34] By using a dynamic schema for unstructured or polymorphic data, our Mongo database embraces flexibility. Consider this.

[02:35 - 02:45] Imagine for some reason, going forward, instead of number of guests, I want to call this property "guests". And instead of a number, I want this property to be a string.

[02:46 - 03:01] To implement this in MongoDB, it's extremely easy because there is literally nothing we need to do. We can just start inserting data in the new format we want or update any of our existing data to the new format and MongoDB won't complain.

[03:02 - 03:09] Here in our Mongo collection, we have a mix of listing shapes. In other words, we now have polymorphic data.

[03:10 - 03:22] Remember, we are allowed to do this because of our dynamic schema. Now, we will still need to update our node server to process the new guests property, but there is nothing we need to do in our database.

[03:23 - 04:12] To achieve the same in a SQL database, we must perform a database migration. And here is an overview of the steps we will need to take.

[04:13 - 04:21] Number one, insert a new "guests" column in our table. Two, write a script to convert "num of guests" into "guests".

[04:22 - 04:31] Three, drop the old "num of guests" column from our table. And finally, four, update our server to use the new guests property.

[04:32 - 04:46] Depending on the size and complexity of a SQL database, a migration could take hours to complete. Therefore, to affect the least number of users, you will most likely want to perform this migration when your app usage is low.

[04:47 - 05:05] Flexibility is the reason why no SQL databases have become so popular in recent years, especially with startups, where they need to be flexible and agile. With that said, if you value structure modern flexibility, you just might prefer a SQL database over a no SQL database.