Say Goodbye to RDBMS, Embrace MongoDB

Relational databases have been the gold standard for many decades. RDBMS have been the tried and true data systems utilized by developers to meet application requirements. However, over time, exponentially growing volumes of data, the explosion of big data and cloud technologies have required alternative ways to access data. This has led to the uprise of NoSQL databases as a more scalable, flexible, and cost-effective alternative. In this article we will explore the differences between relational (or SQL) and NoSQL databases, key considerations for migrating to MongoDB and examine several conversion examples.

RDBMS vs. MongoDB: What’s the difference?

Relational databases use structured tables, in which data should adhere to predetermined formats. It implies that tables consist of rows and columns where each row represents a single record of data, and each column is a single field of data. Such databases rely on the powerful SQL language to work with data, while NoSQL alternatives have no set data structures and allow you to access input and search functions with the parameters you set. The key features of MongoDB are the following:

• MongoDB is a document-oriented database. Compared to relational databases, it does not use tables, nor does it require defining the data structure beforehand. In contrast, the data is stored in documents that can be nested and have dynamic schemas.
• MongoDB allows you to store and process data in JSON format.
• Documents are stored in collections that play the role of tables in RDBMS.
• MongoDB uses the MongoDB Query Language (MQL) to operate with data.

Why migrate to MongoDB?

Migration from RDBMS to MongoDB may be essential for many reasons. If your model and data structure are constantly evolving, MongoDB with its agility is a great choice. MongoDB allows you to change the structure of documents easily without significantly updating queries and references to data in the application. Alternatively, a relational database may be sufficient for your needs in case you do not need to incorporate frequent changes. Also, if your application relies heavily on SQL-based clients and platforms, it makes sense to postpone the migration and consider redesigning the application as a long-term solution.

Another important factor in choosing MongoDB is scalability. It is perfectly suited for quickly executing queries and scaling with ease. MongoDB is easy to scale both vertically and horizontally. Scaling vertically involves improving the database performance by increasing the server capacity (processor, memory, etc.). Such scaling is typical for both relational and NoSQL databases, however, it imposes limitations and may cost a fortune in some instances. Horizontal scaling involves adding new nodes to the database cluster to split the workload and often serves as a life-saving alternative. But here comes the catch, though. Due to the complexity of distributing related data across nodes, horizontal scaling in relational databases may be extremely difficult.

Yet, due to the self-contained collections, horizontal scaling with MongoDB is considerably simplified. Scaling can even be reconfigured online without downtime with the help of an online update.

Benefits of MongoDB

• Easy to scale. With the addition of extra nodes to the cluster, MongoDB can easily scale horizontally. This can be beneficial if your database is growing and needs enhanced performance.
• Flexible storage of information. Mongo makes it much easier to introduce changes to the data structure, for example, to add new fields. Documents contain different types of information, which is important when working with big data that have a different structure and come from different sources.
• Accelerated data access. In MongoDB, you can index any field in any document, which significantly speeds up a search through a collection. It can be especially useful if you are working with large amounts of data.
• Versatility. Most programming languages come in a package with special tools for working with MongoDB.

Limitations & Disadvantages of Mongo

• Lack of stored procedures, functions, and triggers
• Few opportunities to use transactions
• Difficulties when working with multi-related data structures
• Incompliance with ACID principles

When is MongoDB a perfect solution?

MongoDB can be used in web, analytics and BIG DATA, where you have to work with a large amount of information. It is also often used in startups where a data storage structure is not yet defined and constant changes may be required. MongoDB is definitely worth considering when flexibility and scalability are the top requirements for a database. For example:

• Real-time analytics
• Storage of sensor and device data
• Working with Big Data for machine learning
• Location-based data maintenance
• Social network

Some examples when MongoDB is not the best option

MongoDB is not a perfect choice in the following cases:

• In transactional systems or applications that require transactions at the database level, for example in banking applications
• Projects where the data model is strictly defined
• Storage of multi-related data structures

Steps to follow when migrating from RDBMS to MongoDB

Step 1. Analyze the existing database

The first step when migrating to MongoDB is to analyze your current database. You need to put special attention to the types of data you store, tables you use and how they are related to each other. It will help you understand how to rebuild your database in MongoDB in the best way.

Step 2. Plan the documents structure

MongoDB does not use tables, instead the data are stored in documents. Therefore, you need to determine what data you are going to store in each document. This includes defining the document fields and types of relationships between them. It is also important to determine the update frequency of each field in order to set up the indexes correctly. This is the most important stage that needs to be paid close attention to.

Step 3. Migration of tables and data

Taking into account the change in the database structure, it is necessary to load all tables from the source correctly and prepare scripts for correct data conversion before uploading to the new structure in MongoDB.

Step 4. Migration of business logic objects

Converting business logic objects from a relational database to MongoDB can be an uphill battle. MongoDB doesn’t support the ability to create most of the objects common to a relational database - views, functions, procedures, triggers. You can use JavaScript in a Mongo database, which functionally will give you a kind of equivalent of stored procedures, but you run the risk of splitting the logic between your database and the application (usually it would cause issues). In relational databases, stored procedures were used to consolidate logic, but the main reason for using them these days is to increase efficiency in terms of execution speed and server resource usage. With a Mongo database, you won't benefit from using server side business logic.

If a database contains a large number of procedures, functions, triggers in the source, all this functionality will have to be written at the application level. Thus, transferring the logic from a database level to an application level requires not just a code conversion, but a significant change in its structure and logic. That is why this process can become a nightmare in terms of complexity and required time.

Step 5. Migration of queries & application transformation

Once the collections and documents have been defined in MongoDB, it is necessary to start changing the application. It includes converting the connection to the database, accessing it, converting the Embedded SQL in the application, as well as the rest of the queries. Below are examples of basic statements in SQL and their equivalents in MongoDB (MQL).

Insert:

 
	INSERT INTO tab1(c1, c2)
	VALUES (1,"A")
 
 

 
 db.tab1.insertOne({ c1: 1, c2: "A" })	
 

Update:

 
	UPDATE tab1
	SET c1 = c1 + 3
	WHERE c2 = "A"
 

 
	db.tab1.updateMany(
   		{ c2: "A" } ,
   		{ $inc: { c1: 3 } }
	)
 

Delete:

 
DELETE FROM tab1
WHERE c2 = "A"
 

 
db.tab1.deleteMany( 
{ c2: "A" } )
 

Simple select queries

Migration of the simple queries from SQL to MQL doesn’t cause any difficulties. In addition, most standard functions and operators are supported in MongoDB, so the conversion of the simplest queries should not be difficult. Below are examples of converting such queries from SQL to MongoDB syntax:

 
SELECT * FROM tab1;
SELECT * FROM tab1 where c2 = "A";
SELECT * FROM tab1 WHERE c2 = "A" OR c1 > 5;
SELECT * FROM tab1 WHERE c2 = "A" ORDER BY c1 DESC;
SELECT COUNT(*) FROM tab1 WHERE c1 > 10;
SELECT * FROM tab1 LIMIT 3;
 
 

 
db.tab1.find()
db.tab1.find({ c2: "A" })
db.tab1.find({ $or: [ { c2: "A" } , { c1: { $gt: 5 } ] })
db.tab1.find( { c2: "A" } ).sort( { c1: -1 } )
db.tab1.count( { c1: { $gt: 10 } } )
db.tab1.find().limit(3)
 

Complex SQL queries

One of the biggest differences between SQL and NoSQL databases is JOIN operations. In relational databases, JOIN allows you to join rows from two or more tables using common columns. Document-oriented databases such as MongoDB are designed to store denormalized data. So, in general, there should be no connections between collections. If two or more documents require the same data, they just need to be repeated in both collections.

However, in recent versions of MongoDB, there is a $lookup operator for linking collections, which can perform a LEFT-OUTER-JOIN-like operation on two or more collections. But it can only be resolved in aggregation operations. Below is an example of how to reproduce JOIN in MongoDB.

 
Select * from Books, Authors where Books.author_id = Authors.auth_id;
 

 
db.Books.aggregate([
   {
      $lookup:
         {
           from: "Authors",
           localField: "author_id",
           foreignField: "auth_id",
           as: "Detail"
         }
   }
])
 

To conclude, the usage of $lookup makes it possible to rewrite complex queries, however, we recommend changing the data structure to resolve such issues in MongoDB.

Conclusion

Undoubtedly, MongoDB is a promising and rapidly developing technology, which can be much more useful in comparison with relational databases for specific purposes and business tasks. It has its strengths and weaknesses and areas of application, just the way any other database does. Before making a decision to migrate your relational database to MongoDB, you need to carefully consider all the pros and cons that come along with the transition from your database.

In case the decision is already made, it is necessary to set up a system that will make the process of migration optimized to carry out all the necessary transitions as efficiently as possible. It is important to design the structure of collections and documents in such a way as to reduce the cost of rewriting queries. Also, all the business logic will have to be taken to the application level and changed significantly. An equally important step is the proper planning of all activities related to data migration.

Ispirer provides an expert service of converting SQL scripts from any RDBMS to MongoDB. In addition, Ispirer solutions make it possible to automate the conversion of the Embedded SQL in the application to the MongoDB language. It significantly reduces the migration time for all embedded SQL queries and DML commands when migrating to MongoDB.

Ispirer’s team of seasoned developers can assist with every aspect of migration to MongoDB - from the migration plan and changing of the data storage structure to the migration of business logic to the application level and data migration.