Migrating your SQL Database to MongoDb

In this article you are going to learn how to migrate your Relational database(MySQL, MariaDb etc) to Non-relational database like MongoDb.

Requirements.

• I expect that any one reading this article has at least a minimal knowledge of databases, and if you are a complete newbie, there will be a brief introduction to database and a link will be attached for further reading.

Expectation.

• At the end of this article you should be able to write your own script and migrate your database from MySQL to MongoDB
• You should be able to differentiate between a Relational Database and a Non Relational Database is, how they work, their differences and their similarities.

Stack
Mongodb, MySQL(Maridb was used in this article), Sequelize, Sequelize-auto Nodejs
NOTE: The example code is similar to what you’ll be having when you follow the steps and do not expect the exact same thing since the structure of your database differs.

---

Database

A database is simply a collection of structured data. Think of taking a selfie: you push a button and capture an image of yourself. Your photo is data, and your phone’s gallery is the database. A database is a place in which data is stored and organized.

SQL

MySQL and SQL are not the same. Be aware that MySQL is one of the most popular Relational Database Management System (RDBMS) software’s brand names, which implements a client-server model. So, how do the client and server communicate in an RDBMS environment? They use a domain-specific language – Structured Query Language (SQL). If you ever encounter other names that have SQL in them, like PostgreSQL and Microsoft SQL server, they are most likely brands which also use Structured Query Language syntax. RDBMS software is often written in other programming languages, but always use SQL as their primary language to interact with the database. MySQL itself is written in C and C++. The word “relational” means that the data stored in the dataset is organized as tables. Every table relates in some ways. If the software doesn’t support the relational data model, just call it DBMS.

Introduction to MySQL

MySQL is an open source relational database management system (RDBMS) which uses the Structured Query Language (SQL) as a mechanism for dealing and interacting with the data. Although MySQL is one of the widely used and well known database management systems and is considered as reliable, scalable and efficient database management system, It is NOT well suited for handling big data, especially with HUGE insertion rates.

Intoduction to MongoDb

MongoDB is an open source, nonrelational database management system (DBMS) that uses flexible documents instead of tables and rows to process and store various forms of data. As a NoSQL solution, MongoDB does not require a relational database management system (RDBMS), so it provides an elastic data storage model that enables users to store and query multivariate data types with ease. This not only simplifies database management for developers but also creates a highly scalable environment for cross-platform applications and services.
MongoDB documents or collections of documents are the basic units of data. Formatted as Binary JSON (Java Script Object Notation), these documents can store various types of data and be distributed across multiple systems. Since MongoDB employs a dynamic schema design, users have unparalleled flexibility when creating data records, querying document collections through MongoDB aggregation and analyzing large amounts of information.

MongoDB vs. MySQL
MySQL uses a structured query language to access stored data. In this format, schemas are used to create database structures, utilizing tables as a way to standardize data types so that values are searchable and can be queried properly. A mature solution, MySQL is useful for a variety of situations including website databases, applications and commercial product management.
Because of its rigid nature, MySQL is preferable to MongoDB when data integrity and isolation are essential, such as when managing transactional data. But MongoDB’s less-restrictive format and higher performance make it a better choice, particularly when availability and speed are primary concerns

Why we needed to migrate
We built a digital Sytems for farmers and as we tried scaling and replicating our technology in other states and countries, we discovered our database structure was constantly changing. Because of the schemaless and unstructured design of MongoDb we could have documents with different datasets in MongoDb in contrast to relational database. I was going to migrate about 600k records from MariaDb to MongoDb.
I surfed the internet for tools that could be of help and I came across Studio3t and Mongify

Studio3T is owned by 3T Software Labs and they are a MongoDB technology partner.
3T has been making professional tools for MongoDB since 2014 and Studio 3T is now the number one 3rd party GUI / IDE for developers and data engineers who work with MongoDB. Data management features such as in-place editing and easy database connections are matched with polyglot query code generation, advanced shell with auto-completion, easy SQL import/export, as well as heavy-duty migrations between relational databases and MongoDB.

Problem Statement
Sincerely, Studio has great features. They have a free plan which allows you to test their features for 30 days. This was cool until I had finished writing my queries(filters and joins), clicked “export” and all saw was 1000 documents in MongoDb , but i have about 600k records in MariaDB there was no way my MongoDb database could be any less than that. So i decided to do some digging and I found out that the max i could do with the free tier was 1k. This nothing compared to 600k. Upgrading to their enterprise plan wasn’t a big deal the price was 700USD for a single user per year, but coming from a third -world country link Nigeria that’s a lot of money. Considering the fact that, i needed just a single tool from the entire software package. It was a no for me.

Mongify is a utility (or a ruby gem ) written in the ruby language and is used to migrate databases from SQL to mongodb. Further detailed information about ruby language and ruby gems can be found on their corresponding websites. Mongify utility migrates databases while not caring about primary keys and foreign keys as in the case of an RDBMS. It supports data migration from MySQL, SQLite and other relational databases. However, this article only focuses on migrating data from MySQL to MongoDB.

Problem Statement
The problem I had with mongify was ruby gems had compatibility issues. Installation on windows is not quite straight forward, it could be easier on Linux (give it a trial if you use Linux). Solving an issue appears to be a license for bugs, they keep springing up. After burring my head in their documentation and spending almost a full day trying to fix gem’s compatibility issues, I gave up.

My Solution

I figured it was high time i wrote my script.
For the migration i assume you should already have a SQL database somewhere either in the cloud or on your local machine.
To install mongodb on your server or local machine follow the instruction in the mongodb docs

You must have Node and MongoDB Compass installed on your local machine,
Your MongoDB itself could reside anywhere either on your local machine or on the cloud like MongoDB atlas. You can follow the instructions in the MongoDB docs to get you started.

MongoDB Compass is the official GUI for MongoDB, maintained by MongoDB itself. MongoDB Compass helps users make clever decisions about the data structure, querying, indexing, and many more actions you can perform on the database.
To get Node installed on your computer. Follow any of the links below:
for windows, for Mac or for linux
To check if you have Node.js installed, run this command in your terminal:
node -v
To confirm NPM is installed, type **npm** -v in Terminal. This should print the version number so you'll see something like this 6.4.

NPM is a package manager for the JavaScript programming language. npm, Inc. is a subsidiary of GitHub, that provides hosting for software development and version control with the usage of Git. npm is the default package manager for the JavaScript runtime environment Node.js.

To get started on a new project cd into your workspace folder make a new directory that will host your project open terminal and type:

npm init

you’ll be asked to fill in some details about your project. After saving a package.json file should be created for you.
Then you can now install the following npm dependencies with the following command.

npm install sequelize-auto sequelize mysql2

sequelize-auto was used to generate a model for my sequelize

sequelize is a promise-based Node. js ORM for Postgres, MySQL, MariaDB,
SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

mysql2 is a MySQL client for Node.js with focus on performance.
To generate your model with sequelize-auto you needed to run this command

npx sequelize-auto -h <host> -d <database> -u <user> -x [password] -p [port]  --dialect [dialect] -c [/path/to/config] -o [/path/to/models] -t [tableName]

the models are all saved into the file you set in “path/to/models”. It produces a file like this /model/user.js

Sequelize-auto also generates an initialization file, ./models/init-models.js, which contains the code to load each model definition into Sequelize:

This makes it easy to import all your models into Sequelize by calling initModels(sequelize).

To run our script create a file called index.js in the root folder this would be the starting point for your script.
Follow the **[getting started guide**](https://sequelize.org/master/manual/getting-started.html) in the sequelize documentation to get you started with sequelize.

We want to be able to write the result of our database into a json file which we will later import into MongoDB using MongoDB Compass.
We need two modules “fs” and “path” we don’t need to install them they already come with node. i just import them using ‘require’ in our code

The Path module provides a way of working with directories and file paths.
Node.js includes fs module to access physical file system. The fs module is responsible for all the asynchronous or synchronous file I/O operations. You can click on the links to learn more about their usecase.

to run the index.js file edit your package.json file to look something like this

then run the command npm run start in the terminal. Your imports should be saved into a file called users.json in your root folder.

Happy Coding.

For further questions you can reach out to me via email [e-mail]