Transitioning from MySQL to MongoDB
Tags: mongodb, node, php, mysql
Published: 2021-10-25 09:10:17

## Table of Contents
- [Introduction](#introduction)
- [SQL Databases](#sql-databases)
- [NoSQL Databases](#nosql-databases)
- [MongoDB](#mongodb)
- [Setting Up](#setting-up)
  - [Installing](#installing)
  - [Creating a Model](#creating-a-model)
  - [Connecting to the Database](#connecting-to-the-database)
- [Creating/Inserting Data](#creatinginserting-data)
- [Retrieving Multiple Records](#retrieving-multiple-records)
- [Selecting One Record](#selecting-one-record)
  - [By ID](#by-id)
  - [By Attribute](#by-attribute)
- [Updating Records](#updating-records)
- [Deleting Records](#deleting-records)
- [Conclusion](#conclusion)
- [Read the Docs](#read-the-docs)

## Introduction
When going into software development, for most web applications, there is a need to store data, persistently, in some form or the other. This could include the need to store login/authentication information, financial details, an e-shopper's cart, training data for a machine learning model, etc. Amazingly, there are myriads of ways to store this data, examples include JSON (JavaScript Object Notation), XML, CSV (comma-separated values), or in a database, the most popular option, of course, being the most popular of them all.

This article will not discuss the different types of database systems, like RDBMS and so on. We will, however, focus on two concepts: SQL Databases and NoSQL databases.

## SQL Databases
SQL (Structured Query Language) is a standardized programming language that's used to manage relational databases and perform various operations on the data in them. [Read More](https://searchdatamanagement.techtarget.com/definition/SQL#:~:text=SQL%20(Structured%20Query%20Language)%20is,on%20the%20data%20in%20them.&text=SQL%20became%20the%20de%20facto,late%201970s%20and%20early%201980s.).

The data in relational databases is stored in rows and columns which are arranged in relevant tables and stored in the overall database.

## NoSQL Databases
According to the [MongoDB Webpage](https://www.mongodb.com/nosql-explained), NoSQL databases (aka "not only SQL") are non-tabular databases and store data differently than relational tables. NoSQL databases come in a variety of types based on their data model. The main types are document, key-value, wide-column, and graph. They provide flexible schemas and scale easily with large amounts of data and high user loads.

The NoSQL database of choice we would be looking at today is [MongoDB](https://www.mongodb.com/).

## MongoDB

MongoDB is the most popular solution for developers looking to benefit from features like scalability and speed. Unlike MySQL, MongoDB returns data as a well-organized, JSON document with helper methods that help to execute more complex queries on the data.

In place of tables, MongoDB uses a Schema, which defines the datatypes, constraints, and other parameters to construct Models that represent tables and are the ways we access, create and modify data. These models are then used to organize data into collections.

If you're used to viewing data in a graphical form such as PHPMyAdmin for MySQL, MongoDB also has the [MongoDB Compass](https://www.mongodb.com/products/compass), to provide such capability.

## Setting Up
To go through this article, we'll use the [Mongoose](https://www.npmjs.com/package/mongoose) npm package.

### Installing
<br>
The file structure for this project will look something like this.

```
----/
    |
    |--app.js
    |--package.json
    |--models
        |--User.js
```
<br>  
Run the following commands in this folder.

```sh
> npm init -y
> npm install mongoose nodemon
```

Inside package.json, under scripts, include the following
```json
...
"start": "nodemon app.js"
...
```

### Creating a Model
Inside `User.js`, we'll create a Mongoose Model with the following lines of code
```js
const mongoose = require('mongoose');
const {Schema} = mongoose;

const userSchema = new Schema({
  username: {
    type: String,
    required: true,
    unique: true,
  },
  password: {
    type: String,
    required: true,
  },
  name: {
    type: String,
    required: true
  }
});

module.exports = mongoose.model('User', userSchema);
```

The SQL equivalent of this would look something like this
```sql
CREATE DATABASE `transition-tutorial`;

USE `transition-tutorial`;

CREATE TABLE `users` (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  username VARCHAR(20) NOT NULL UNIQUE,
  password VARCHAR(20) NOT NULL
);
```

Here we can see how we use Schema to define the data structure and parameters, and then process it into, and export a Model object. Additionally, notice how we didn't define an `id` property in our Schema. That's because MongoDB automatically assigns a 16-bit unique ObjectId to all documents in the model. Amazing.

<br>

### Connecting to the Database
Inside `app.js`, write the following lines of code  

```js
const mongoose = require('mongoose');
const User = require('./models/User');

(async () => {
  try {
    await mongoose.connect(
        'mongodb://127.0.0.1:27017/transition-tutorial', 
        options);
  } catch (err) {
    console.log('Error connectiong to Database');
    console.log(err);
  }
});
```

And here, we include the constructed model object into our file.

Two things to note
 - We see from the connection that Mongoose is designed to work in an asynchronous environment. Mongoose supports both promises and callbacks.
  
 - The string `'mongodb://127.0.0.1:27017'` represents the connection URL for the mongoose instance. The URL in this tutorial is for those who have the MongoDB application installed on their local computer. Connecting to a cloud instance requires a connection string gotten from your MongoDB Cloud cluster homepage.

 - The `'transition-tutorial'` is the name of the database we will be querying. We did not create this database because MongoDB automatically creates it along with any defined Model when we connect and insert our first document using that Model Object. Another amazing reason to switch to MongoDB.

Now, run `npm start` in the terminal.


## Creating/Inserting Data
Typically, in an SQL database, the way to insert data into a table would be

```sql
INSERT INTO `users` (name,username,password) VALUES ('John', 'johnsmith', 'p@$$w0rd!');
```

The MongoDB equivalent of this would be
```js
...
(async () => {
  try {
    const user = new User({name: 'John', username: 'johnsmith', password: 'p@$$w0rd!'});
    await user.save();
    console.log(user);
  } catch (err) {
    console.log(err);
  }
})();

```

If you take a look at the output in the terminal or console, you'll notice that the new User Object has an `_id` property. This is the unique id that was generated by Mongoose;

## Retrieving Multiple Records
To retrieve data from a SQL database, for example users, you'd usually write a query like this

```sql
SELECT * FROM users
```
This corresponds to following line of code

```js
(async () => {
  const users = await User.find({});
  console.log(users);
});
```
This returns an array of MongoDB documents.

To perform a WHERE query, simply add the conditions as properties in the `find` method.

```js
const users = await User.find({name: 'John'});
```
This retrieves all users who have the name John while
<br><br>
To only retrieve some properties, supply a comma-separated string as the second parameter.

```js
const users = await User.find({}, 'name, username');
```
This will return only the `name` and `username` properties, as well as the unique `_id`. The SQL equivalent of this would look like

```sql
SELECT name, username FROM users;
```

## Selecting One Record
### By ID

This would find and return records based on the unique ID, provided you already have the id
```js
const user = await User.findById(id);
console.log(user);
```
### By Attribute

To return one document using one of the properties, for example, the name, write this
```js
const user = await User.findOne({name: 'John'});
```
This returns the first user in the collection with the name John.

The SQL equivalent of this would look like
```sql
SELECT * FROM users WHERE name='John';
```

## Updating Records
To update a record in a SQL database, we use the UPDATE query
```sql
UPDATE TABLE users SET password='johnsmith001' WHERE name='John';
```

To achieve this in MongoDB, we could go two ways
 - First, we could retrieve the data from the database, update the document and then save it again.
    ```js
    const user = await User.find({name: 'John'});
    user.password = 'johnsmith001';
    await user.save();
    ```
 - Secondly, we could `findAndUpdate`, `findOneAndUpdate` or `updateOne` and the documents directly.
    ```js

      // Update multiple documents
      const user = await User.findAndUpdate(
        {}, 
        {password: 'johnsmith001'},
        {new: true}
      );

      // Update multiple documents with condition
      const user = await User.findAndUpdate(
        {name: 'John'}, 
        {password: 'johnsmith001'},
        {new: true}
      );

      // Update single document
      const user = await User.findOneAndUpdate(
        {name: 'John'}, 
        {password: 'johnsmith001'},
        {new: true}
      );

      // Update single document
      const user = await User.updateOne(
        {name: 'John'}, 
        {password: 'johnsmith001'},
      );
    ```

    The first parameter to pass is an object of the conditions used to locate the required document, the second parameter is an object of the updates to be made to the document, and the `{new: true}` tells mongoose to return the updated record. If this is not included, the old, outdated document will be returned instead.


## Deleting Records
To delete records in a SQL database, the typical query would look like this.
```sql
DELETE FROM users WHERE name='John';
```

The equivalent mongoose function would resemble any one of the following ways
```js
// Deleting collection of documents
const users = await User.deleteMany({name: 'John'});
const users = await User.remove({name: 'John'});
// returns {deletedCount: x} where x is the number of documents deleted.

// Deleting Single Document
const user = User.findOneAndDelete({name: 'John'});
console.log(user); // returns the deleted document
```

Note:
- The `deleteMany` method is preferred over the `remove` unless you have a good reason otherwise.


## Conclusion
We've taken a look at the common operations that can be performed by querying a SQL database and the ways they can be implemented in MongoDB using mongoose. Don't stop there. There are many other things we could discuss such as implementing the LIKE query by passing a regex as the find condition and implementing JOIN queries with the `populate` method.

Create a MongoDB account for free today and take advantage of all the features MongoDB has to offer.

## Read the Docs
- [MongoDB](https://docs.mongodb.com/)
- [Mongoose](https://mongoosejs.com/docs/)