Heather

Posted on Apr 5, 2020

mySQL vs MongoDB - What's the Difference?

#sql #database #beginners #codenewbie

In conversation, mySQL and MongoDB are often referred to as databases. While calling them a database is not wrong, the titles actually point to software that creates databases. A database management system, DBMS, is an interface that handles data from an end-user or application.

In this post, we’ll discuss the key differences between mySQL and MongoDB as well as point out how they are similar. We’ll also look at making a query in both systems during our comparison. Finally, we’ll go over some questions to ask when deciding which system is the best fit for your application or organization.

Checking Out the Differences

There are many differences between mySQL and MongoDB, but the overarching distinction is the use of SQL. SQL, or structured query language, is a declarative programming language. We call mySQL a relational database management system because it uses SQL. SQL maintains data in the form of tables with rows and columns. The row in a table will hold values that are organized by columns which tell the row what specific type of information it holds.

MongoDB, on the other hand, is a NoSQL database management system. NoSQL stands for not only SQL. This means that MongoDB doesn’t use tables to store data, but rather it is a document-oriented database. A MongoDB database that uses NoSQL will look more like an object that contains key-value pairs.

Choosing whether a SQL or NoSQL database is better can only be done subjectively based on the needs of your application. A SQL database management system like mySQL is structured specifically and organizes data rigidly. For an application storing bank transactions, this type of organization is critical. As a NoSQL database management system, MongoDB offers more flexibility and can be thought of as a folder that holds many files. These files can have a mixture of information that doesn’t need to be specifically organized.

Similarities Between Systems

When storing data, it makes sense that mySQL and MongoDB would do some of the same tasks. Each will need a way to add, remove, and change data as well as access data to perform an action.

Overall Organization

mySQL: Tables
MongoDB: Collection

Linking Information

mySQL: Primary key
MongoDB: _id

How to Store Data

mySQL: Row
MongoDB: BSON document

Combine Data

mySQL: Join
MongoDB: Embed/Link

Perform an Action on Stored Data

mySQL: Group by
MongoDB: Aggregation

Making a Query

Earlier we discussed a major difference between mySQL and MongoDB which is the use of the SQL language. When we look at the two styles of making a query, you will notice that mySQL looks like plain English. The use of means we don’t have to describe the control flow, it knows what to do. In contrast, MongoDB looks more like a JavaScript with the use of dot notation and methods.

Here are some basic query commands to demonstrate the difference between SQL and NoSQL:

MySQL

To start:

mysql -uroot

Create database:

CREATE DATABASE database name

Create table:

CREATE TABLE table name

Add:

INSERT INTO table name (data) VALUES

Show all tables:

SHOW TABLES

Describe how a table is organized:

DESCRIBE table name

List what’s inside a table:

SELECT * FROM table name

In MySQL we create a database and then we create tables to hold the data. When we insert data, the values need to match our column value type.

MongoDB

To start:

mongo

Create a database:

use Database_Name

Create a collection:

db.createCollection(collectionName)

Add a value:

db.collectionName.insertOne( { …. Data key-value pair })

Show a collection:

db.collectionName.find.pretty( )

In MongoDB we create a database and then we create a collection to store data. We create the key-value pair when we insert data, no need to have a specific type of information.

mySQL or MongoDB? Questions to Ask

Now that you have an idea of how mySQL and MongoDB function you can start to determine which database management system is the right choice for your application or organization. The differences between the two will cause you to ask yourself a few questions.

1. How big is your data and how is it structured?

The volume of your data will affect how quickly you are able to access information and your ability to maintain it. As a document-oriented database, MongoDB allows you to store data in one place without defining types of information. This is beneficial for large, unstructured data. If you have very specific data, organized in a certain way, mySQL will provide a structured database.

2. Is your data unchanging or are you making frequent updates?

If your data is consistent and unchanging a relational database like mySQL helps keep information clearly defined. Document-based databases like MongoDB allow more flexibility when it comes to altering data.

In the end, there is no best choice, there’s only a better choice when it comes to the specific needs of your database.

Resources Used

Comparing MongoDB with MySQL
SQL vs. NoSQL Databases: What’s the Difference?
MySQL vs MongoDB