Migrating from SQL(MySQL) to NoSQL(MongoDB) with the help of Parse – [Part 1]

Migrating from SQL(MySQL) to NoSQL(MongoDB) with the help of Parse – [Part 1]

When faced with the challenge to migrate from RDBMS to NoSQL, specifically MySQL to MongoDB, many programmers and/or people who don’t have deep experience with Databases end up cornered between expensive commercial ETL tools or free community tools.

Usually the latter is the option of choice for its obvious pricing advantage and, while MongoDB has a great import tool (a.k.a. mongoimport) which can successfully import CSV and JSON files, that can be easily generated with MySQL, a few times the complexity of the Database structure makes this process quite challenging, as conversion of values might not always match for both databases.

This article is intended to give you another option to help developers with migrating such data.

Just as the other options, it will not cover all possible cases, but will give you an extra option that might help you if it fits your case and has, indeed, helped us here at Back4app to migrate data successfully for a few clients who fitted this use case.

 

Pre-requisites

For this tutorial, we will use Parse as the gateway between MySQL and MongoDB.

Parse has a very flexible way for writing properties (known as Columns on the MySQL world) to objects (a.k.a Tables for MySQL’ers) and fewer, but more flexible data types compared to MySQL. That makes conversions quite easy when we figure out exactly what to convert.

For those of you who don’t have any experience with Parse, I recommend to take a look at our Tutorials at our guides and also check our Tutorial videos at our Youtube channel 

Also, I am going to present code here written in NodeJS which is the programming language of choice here at Back4app. Parse works with most major programming languages and has a similar syntax for all of them, so please adapt the code shown as needed to run in your programming language of choice.

Last but not least, I assume you have some experience with MySQL and is getting started with MongoDB.

So here is a checklist before proceeding:

 

  1. Create an account in Back4app
  2. Create your first App
  3. Test all the connections and privileges of your MySQL database so you can read data from it (a.k.a. all necessary tables and rows)
  4. Let’s go…

 

MySQL Database Structure

The database we are using for this tutorial was shameless ripped from Phillip Pace, which you can get from here

Phillip provides a very easy to understand database called “dogs” which has most kinds of relations and will do a great job for us to understand a simple migration.

That kind of database, being so simple, could possibly be migrated by exporting it as JSON or CSV and imported using mongoimport, but again, the main goal of this article is to give you yet another option to accomplish the same result.

You can follow Phillip’s article in order to create a similar MySQL database, but if you are in a hurry, you can download the full MySQL script for creating it from here.

Phillip’s database structure is as follows:

screen-shot-2019-07-05-at-16-07-39

 

If you notice, there are a few data types that are not directly converted to MongoDB, such as the ENUM type.
Also, Phillip has a table called “breed_breeder” which is a relation table between, of course, breed and breeder.

In MongoDB, that table would not be necessary as we can use type Parse type Relation to make that relation between Perse.Objects. Don’t call it Table in the Parse world.

As we understand that many people migrating from MySQL to MongoDB will prefer to keep the structures as similar as possible for familiarity, we will present two different structures we can use: one very close to the MySQL and one as I would do it, eliminating joining tables and using Relations and Pointers, features unique to MongoDB.

 

MongoDB Database Structure – The MySQL familiar structure

As mentioned earlier, the MongoDB database structure that will be created in this article using Parse can be more similar to the MySQL structure, so MySQL familiar users can better understand, or it can use the full features of MongoDB and leave joining tables out of the equation.

Don’t worry. We will show you how to do both approaches and how to query on both as well. Just pick the one you like the most, but remember using the MongoDB structure will end up in a cleaner, more readable structure when you get more experience with MongoDB.

The traditional MySQL structure in MongoDB would look to something like the image below:

screen-shot-2019-07-05-at-16-07-59

 

A few Columns (MySQL) will not be needed anymore (painted in red on the diagram) as Properties (Parse), specifically the “date” column, as Parse already creates a few Properties for convenience (painted in green on the diagram) and one of those is the “createdAt” which we will use for the same purpose.

If you took a look at Phillip’s scripts for creating the database, the date column holds a Timestamp which is populated with Now(), a value retrieved at the moment of insertion:

INSERT INTO breeder (name,address,state,city,phone,email,date) VALUES (‘Tom Smith’, ’11 Tucker St’, ‘QLD’, ‘Brisbane’, ’07 023 2343′, ‘[email protected]‘, NOW());

 

That is the reason we are using the createdAt property of Parse to hold this value, which is also retrieved at the moment of insertion. If that value was a set value, for instance, a value entered by a user, then I would have to migrate the date column as well.

 

Notice the relations in dashed-red lines are there only to show you what they would be and how they would connect in a MySQL reflecting structure. On this approach, there would not be any actual relation among those Parse.Objects but you could query for them as there was.

 

The only actual relation in the diagram above is from the Dog.size to the Size Parse.Object, as I created a new Size Parse.Object.

As the Enum data type is not supported and future data might exist (more about this in a section forward in this tutorial), I created that Parse.Object to store it and, since it will not be actually migrated, but created from scratch, a Pointer seems to be the logical way to connect both.

 

A few things to notice in the diagram above:

 

  • Dashed red lines are NOT real relations
  • As a coding convention, all the Parse.Objects are created with a capitalized first letter, camel case string without underscores, so the table rfid_dog will become RfidDog
  • Properties are also renamed for coding convention to a lowercase first letter, camel case string without underscores, so the property bar_code from the table rfid_dog will now be called only barCode
  • A few other properties were created for convenience: updatedAt and ACL, which holds the timestamp of when the object was last changed and the access permissions of the object, respectively.

 

Want to hire a Dev Team to your next project?

Talk to a Software Development Company Partner.

 

MongoDB Database Structure – The MongoDB optimized structure

Now that you saw how a MySQL familiar structure would be, let’s take a look at an actual MongoDB structure, with Pointers and Relations connecting the Parse.Objects:

screen-shot-2019-07-05-at-16-08-13

 

On this diagram, the joining table was removed and true Relations and Pointers connect the objects. This is a much stronger and faster way to work with MongoDB data, as it was made to work with this kind of structure by design.

Another advantage of this approach is the ability to retrieve data from distinct Parse.Objects with just one query. On the  “unrelated” example, we would have to make multiple queries in order to achieve the same results.

 

A few things to notice in the diagram above:

 

  • We do not have a joining table anymore (BreedBreeder)
  • All Parse.Objects are connected by true relations
  • I don’t have the id property anymore for the Parse.Objects. ObjectId will do everything we need
  • Parse.Objects name convention remains
  • Parse.Objects Properties name convention also remains
  • We still don’t need the date property as createdAt will do

 

 

 

Data Type Conversions

As you probably noticed in the diagram above, a few data types were changed when converting.

Choosing the data type to which you will convert to can be key when migrating and you should choose wisely so you can keep your application working and ready for possible future changes.

MySQL text types such as varchar, text, char can easily be converted to the type Text of Parse/MongoDB and MySQL numeric types such as int, float, double and decimal can easily be converted for the Parse/MongoDB type Number.

The magic relies on choosing an equivalent data type for non-directly convertible types, such as the MySQL type Enum.

An Enum can be held in many different ways, and you have to consider a few things before choosing.

For instance, on the column size in the table dog is an enum of 3 values (small, medium and large):

image1

 

But at the end of the day, that is just for limiting the options we can have set to that column, as the final value is just a regular string that must be one of those 3 options:

image6

 

As the data type Enum is not directly converted to Parse/MongoDB, we have a few options:

  1. Store a string and validate it with Cloud Code to match one of those 3 values
  2. Create a new Parse.Object called Size and store the 3 values, and have a Pointer set to the correct value of each dog
  3. Other options that you might think of

In my case, I decided to follow the second option mainly for two reasons:

 

  1. This article is not about Cloud Code and I wanted to focus on the migration itself
  2. This table can have new values over time, such as “extra-large” and “extra-small”

 

Also, on the table rfid_dog, Phillip chose to use an Enum of Y and N to the column iso_compliant:

image8

which again will be only used to specify the allowed values which will be saved as a String:

image4

 

Which I found easier to convert to a type Boolean in Parse/Back4app, as it will probably not get any new values over time (it’s either compliant or not compliant):

 

image3

Database Conversion Suggestions

Many people ask me to suggest data types for converting to Parse/MongoDB but that is not so simple as it depends on the case.

An Enum could be a String or another Parse.Object. A Year could be a Number or Date. A Date could be a Date or a String.

It all depends on the operations that you have to do with the information. If you store a Date as a String, it might be difficult to make date calculations with that data. On the other hand if you store a Date as a Date, you will have to parse it to String in order to print it on the correct format on a button or label.

As a rule of thumb, we can always try the easy approach first, which is to convert:

 

  • MySQL String types to String
    • Such as char, varchar, text, mediumtext, blob, etc.
  • MySQL Numeric types to Number
    • Such as smallint, int, integer, float, bigint, double, decimal, etc.
  • MySQL Date types to Date
    • Such as date, datetime, timestamp, year, etc.
  • MySQL Bool and Boolean to Boolean
  • Evaluate other cases one by one, depending on the case
    • A Bit can be a Boolean if only 1 or a 0, or a Number
    • A Binary can be a Base64 encoded String, or a File 
    • A Time can be a String such as 00:00 or a Date
    • List goes on…

 

To help picking a data type to convert, I created the chart below, but please notice this is for main guidance and probably won’t work for all cases, so use it with caution and test your migrations to ensure these types will work for you.

 

image7

Conclusion

This first part of the tutorial covered how you can evaluate the data types and database structures in order to migrate from MySQL to MongoDB with the help of Parse.

We learned that there are ways to trick MongoDB to work just like an RDBMS, which is far from ideal but can be done if familiarity with your already present knowledge is your goal.

We also learned that joining tables are not really necessary when we can create direct relations using the Pointer and Relation types, and that different data structures can handle the same data type depending on how the information will be used, and saw a few suggestions of which data types to use for our migration (remember, always stressfully test the types before doing the actual migration).

On future articles, we will cover the creation of the Parse.Objects to handle the migrated data and also the MongoDB generated structure for both database structures we created. We will make it both graphically trough the Back4app Parse Dashboard)and by code using the NodeJS Parse Framework.

Stay tuned!

Part 2 is out here.

 

Want to hire a Dev Team to your next project?

Talk to a Software Development Company Partner.


Leave a reply

Your email address will not be published.