Mapping Terms and Concepts from SQL to MongoDB

#Why am I talking about Ron?

Ok, so why am I talking about Ron in the middle of this article about moving from SQL to MongoDB?

Ron saw the value of Yelp and was inspired by the new technology. However, he brought his old-school ways with him and did not realize the full value of the technology.

This is similar to what we commonly see as people move from a SQL database to a NoSQL database such as MongoDB. They love the idea of MongoDB, and they are inspired by the power of the flexible document data model. However, they frequently bring with them their SQL mindsets and don't realize the full value of MongoDB. In fact, when people don't change the way they think about modeling their data, they struggle and sometimes fail.

Don't be like Ron. (At least in this case, because, in most cases, Ron is amazing.) Don't be stuck in your SQL ways. Change your mindset and realize the full value of MongoDB.

Before we jump into how to change your mindset, let's begin by answering some common questions about non-relational databases and discussing the basics of how to store data in MongoDB.

#Storing Leslie's Information

Let's say we need to store information about a user named Leslie. We'll store her contact information including her first name, last name, cell phone number, and city. We'll also store some extra information about her including her location, hobbies, and job history.

1
{
2
   "_id": 1,
3
   "first_name": "Leslie",
4
   "last_name": "Yepp",
5
   "cell": "8125552344",
6
   "city": "Pawnee"
7
}

#Storing Latitude and Longitude

Now that we've stored Leslie's contact information, let's store the coordinates of her current location.

When using SQL, we'll need to split the latitude and longitude between two columns.

Users

ID	first_name	last_name	cell	city	latitude	longitude
1	Leslie	Yepp	8125552344	Pawnee	39.170344	-86.536632

MongoDB has an array data type, so we can store the latitude and longitude together in a single field.

Users

1
{
2
   "_id": 1,
3
   "first_name": "Leslie",
4
   "last_name": "Yepp",
5
   "cell": "8125552344",
6
   "city": "Pawnee",
7
   "location": [ -86.536632, 39.170344 ]
8
}

copy code

Bonus Tip: MongoDB has a few different built-in ways to visualize location data including the schema analyzer in MongoDB Compass and the Geospatial Charts in MongoDB Charts. I generated the map below with just a few clicks in MongoDB Charts.

1
{
2
   "_id": 1,
3
   "first_name": "Leslie",
4
   "last_name": "Yepp",
5
   "cell": "8125552344",
6
   "city": "Pawnee",
7
   "location": [ -86.536632, 39.170344 ],
8
   "hobbies": ["scrapbooking", "eating waffles", "working"]
9
}

1
{
2
   "_id": 1,
3
   "first_name": "Leslie",
4
   "last_name": "Yepp",
5
   "cell": "8125552344",
6
   "city": "Pawnee",
7
   "location": [ -86.536632, 39.170344 ],
8
   "hobbies": ["scrapbooking", "eating waffles", "working"],
9
   "jobHistory": [
10
      {
11
         "title": "Deputy Director",
12
         "yearStarted": 2004
13
      },
14
      {
15
         "title": "City Councillor",
16
         "yearStarted": 2012
17
      },
18
      {
19
         "title": "Director, National Parks Service, Midwest Branch",
20
         "yearStarted": 2014
21
      }
22
   ]
23
}

#Storing Ron's Information

Now that we've decided how we'll store information about our users in both tables and documents, let's store information about Ron. Ron will have almost all of the same information as Leslie. However, Ron does his best to stay off the grid, so he will not be storing his location in the system.

1
{
2
   "_id": 2,
3
   "first_name": "Ron",
4
   "last_name": "Swandaughter",
5
   "cell": "8125559347",
6
   "city": "Pawnee",
7
   "hobbies": ["woodworking", "fishing"],
8
   "jobHistory": [
9
      {
10
         "title": "Director",
11
         "yearStarted": 2002
12
      },
13
      {
14
         "title": "CEO, Kinda Good Building Company",
15
         "yearStarted": 2014
16
      },
17
      {
18
         "title": "Superintendent, Pawnee National Park",
19
         "yearStarted": 2018
20
      }
21
   ]
22
}

#Storing Lauren's Information

Let's say we are feeling pretty good about our data models and decide to launch our apps using them.

Then we discover we need to store information about a new user: Lauren Burhug. She's a fourth grade student who Ron teaches about government. We need to store a lot of the same information about Lauren as we did with Leslie and Ron: her first name, last name, city, and hobbies. However, Lauren doesn't have a cell phone, location data, or job history. We also discover that we need to store a new piece of information: her school.

#Storing New Information in MongoDB

Let's examine how we can store Lauren's information in MongoDB.

Users

1
{
2
   "_id": 3,
3
   "first_name": "Lauren",
4
   "last_name": "Burhug",
5
   "city": "Pawnee",
6
   "hobbies": ["soccer"],
7
   "school": "Pawnee Elementary"
8
}

copy code

As you can see above, we've added a new field named "school" to Lauren's document. We do not need to make any modifications to Leslie's document or Ron's document when we add the new "school" field to Lauren's document. MongoDB has a flexible schema, so every document in a collection does not need to have the same fields.

For those of you with years of experience using SQL databases, you might be starting to panic at the idea of a flexible schema. (I know I started to panic a little when I was introduced to the idea.)

Don't panic! This flexibility can be hugely valuable as your application's requirements evolve and change.

MongoDB provides schema validation so you can lock down your schema as much or as little as you'd like when you're ready.

#Row ⇒ Document

A row maps roughly to a document.

Depending on how you've normalized your data, rows across several tables could map to a single document. In our examples above, we saw that rows for Leslie in the Users, Hobbies, and JobHistory tables mapped to a single document.

#Column ⇒ Field

A column maps roughly to a field. For example, when we modeled Leslie's data, we had a first_name column in the Users table and a first_name field in a User document.

#Table ⇒ Collection

A table maps roughly to a collection. Recall that a collection is a group of documents. Continuing with our example above, our Users table maps to our Users collection.

#Database ⇒ Database

The term database is used fairly similarly in both SQL and MongoDB. Groups of tables are stored in SQL databases just as groups of collections are stored in MongoDB databases.

#Index ⇒ Index

Indexes provide fairly similar functionality in both SQL and MongoDB. Indexes are data structures that optimize queries. You can think of them like an index that you'd find in the back of a book; indexes tell the database where to look for specific pieces of information. Without an index, all information in a table or collection must be searched.

New MongoDB users often forget how much indexes can impact performance. If you have a query that is taking a long time to run, be sure you have an index to support it. For example, if we know we will be commonly searching for users by first or last name, we should add a text index on the first and last name fields.

Remember: indexes slow down write performance but speed up read performance. For more information on indexes including the types of indexes that MongoDB supports, see the MongoDB Manual.

#View ⇒ View

Views are fairly similar in both SQL and MongoDB. In MongoDB, a view is defined by an aggregation pipeline. The results of the view are not stored—they are generated every time the view is queried.

To learn more about views, see the MongoDB Manual.

MongoDB added support for On-Demand Materialized Views in version 4.2. To learn more, see the MongoDB Manual.

#Join ⇒ Embedding

When you use SQL databases, joins are fairly common. You normalize your data to prevent data duplication, and the result is that you commonly need to join information from multiple tables in order to perform a single operation in your application

In MongoDB, we encourage you to model your data differently. Our rule of thumb is Data that is accessed together should be stored together. If you'll be frequently creating, reading, updating, or deleting a chunk of data together, you should probably be storing it together in a document rather than breaking it apart across several documents.

You can use embedding to model data that you may have broken out into separate tables when using SQL. When we modeled Leslie's data for MongoDB earlier, we saw that we embedded her job history in her User document instead of creating a separate JobHistory document.

For more information, see the MongoDB Manual's pages on modeling one-to-one relationships with embedding and modeling one-to-many relationships with embedding.

#Join ⇒ Database References

As we discussed in the previous section, embedding is a common solution for modeling data in MongoDB that you may have split across one or more tables in a SQL database.

However, sometimes embedding does not make sense. Let's say we wanted to store information about our Users' employers like their names, addresses, and phone numbers. The number of Users that could be associated with an employer is unbounded. If we were to embed information about an employer in a User document, the employer data could be replicated hundreds or perhaps thousands of times. Instead, we can create a new Employers collection and create a database reference between User documents and Employer documents.

For more information on modeling one-to-many relationships with database references, see the MongoDB Manual.

#Left Outer Join ⇒ $lookup (Aggregation Pipeline)

When you need to pull all of the information from one table and join it with any matching information in a second table, you can use a left outer join in SQL.

MongoDB has a stage similar to a left outer join that you can use with the aggregation framework.

For those not familiar with the aggregation framework, it allows you to analyze your data in real-time. Using the framework, you can create an aggregation pipeline that consists of one or more stages. Each stage transforms the documents and passes the output to the next stage.

$lookup is an aggregation framework stage that allows you to perform a left outer join to an unsharded collection in the same database.

For more information, see the MongoDB Manual's pages on the aggregation framework and $lookup. MongoDB University has a fantastic free course on the aggregation pipeline that will walk you in detail through using $lookup: M121: The MongoDB Aggregation Framework.

#Recursive Common Table Expressions ⇒ $graphLookup (Aggregation Pipeline)

When you need to query hierarchical data like a company's organization chart in SQL, we can use recursive common table expressions.

MongoDB provides an aggregation framework stage that is similar to recursive common table expressions: $graphLookup. $graphLookup performs a recursive search on a collection.

For more information, see the MongoDB Manual's page on $graphLookup and MongoDB University's free course on the aggregation framework.

#Multi-Record ACID Transaction ⇒ Multi-Document ACID Transaction

Finally, let's talk about ACID transactions. Transactions group database operations together so they all succeed or none succeed. In SQL, we call these multi-record ACID transactions. In MongoDB, we call these multi-document ACID transactions.

For more information, see the MongoDB Manual.