Transitioning from MySQL to MongoDB

Table of Contents

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.

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, 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.

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, to provide such capability.

Setting Up

To go through this article, we'll use the Mongoose npm package.

Installing



The file structure for this project will look something like this.

----/
    |
    |--app.js
    |--package.json
    |--models
        |--User.js




Run the following commands in this folder.

> npm init -y
> npm install mongoose nodemon

Inside package.json, under scripts, include the following

...
"start": "nodemon app.js"
...

Creating a Model

Inside User.js, we'll create a Mongoose Model with the following lines of code

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

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.

Connecting to the Database

Inside app.js, write the following lines of code

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

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

The MongoDB equivalent of this would be

...
(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

SELECT * FROM users

This corresponds to following line of code

(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.

const users = await User.find({name: 'John'});

This retrieves all users who have the name John while



To only retrieve some properties, supply a comma-separated string as the second parameter.

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

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

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

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

SELECT * FROM users WHERE name='John';

Updating Records

To update a record in a SQL database, we use the UPDATE query

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.

    const user = await User.find({name: 'John'});
user.password = 'johnsmith001';
await user.save();

  • Secondly, we could findAndUpdate, findOneAndUpdate or updateOne and the documents directly.

    
  // 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.

DELETE FROM users WHERE name='John';

The equivalent mongoose function would resemble any one of the following ways

// 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

28

Categories:

MongodbPhp

Tags:

MongodbNodePhpMysql