How to Migrate SQL to MongoDB

MongoDB is a popular Opensource NoSQL and document-based database. MongoDB’s documents are structures in JSON with the proper schema. One of the challenging process of Translating or Migrating your SQL data to MongoDB (NoSQL). There are many options are available to Migrate SQL to MongoDB. But in this article, we will see the various methods that help us to Migrate SQL to MongoDB.

Various Methods to Migrate SQL to MongoDB

1. Using Mongify Tool
2. Write your own script.
3. MySQL to MongoDB data migration tool (https://github.com/DannySofftie/mysql-mongo-migrate)

This is not the only a list of available migration tool but acouple of best available. Let’s see the above-mentioned methods one by one.

Using Mongify Tool.

Mongify is one of the best available migration tool for SQL to MongoDB. It supports any SQL database tools which has built-in ActiveRecord like MySQL, PostgreSQL, SQLite, Oracle, SQLServer, and DB2. Mongify can be installed as gem as this tool is built on Ruby.

To install Mongify, run following command

gem install mongify

Once Installed, Tool can be used as

mongify command database.config [database_translation.rb]

So, “mongify” is the tool. “command” is the process. “database.config” is the file which has connection details of both SQL and NoSQL database. “database_translation.rb” is the file which is written manually or generated using translation process.

In this “command” can be replaced with three different steps.

1. “check” or ‘ck” – Which will ensure the database.config file is correct or not and whether all the connection mentioned in the file is working or not.
2. "translation" or "tr” – This command will auto-generate the “database_translation.rb”. Like, mongify translation database.config > database_translation.rb
3. "process" or "pr" – With this command, SQL data will be converted into MongoDB documents as mentioned in “database_translation.rb” file and “database.config” file

Write Your Own Script

Below is the sample script which will enable export data from SQL to MongoDB. In this Example code snippet written in NodeJS. In this, we are using MySQL as SQL database.

var mysql = require('mysql');
var MongoClient = require('mongodb').MongoClient;

function getTABLESfromSQL(Mysql_Connectionnection, callback) {
    Mysql_Connectionnection.query("show full tables where Table_Type = 'BASE TABLE';", function(error, results, fields) {
        if (error) {
            callback(error);
        } else {
            var tables = [];
            results.forEach(function (row) {
                for (var key in row) {
                    if (row.hasOwnProperty(key)) {
                        if(key.startsWith('Tables_in')) {
                            tables.push(row[key]);
                        }
                    }
                }
            });
            callback(null, tables);
        }
    });
}

function CollectionTable(Mysql_Connectionnection, tableName, mongoCollection, callback) {
    var sql = 'SELECT * FROM ' + tableName + ';';
    Mysql_Connectionnection.query(sql, function (error, results, fields) {
        if (error) {
            callback(error);
        } else {
            if (results.length > 0) {
                mongoCollection.insertMany(results, {}, function (error) {
                    if (error) {
                        callback(error);
                    } else {
                        callback(null);
                    }
                });
            } else {
                callback(null);
            }
        }
    });
}

MongoClient.connect("mongodb://localhost:27017/importedDb", function (error, db) {
    if (error) throw error;
    var Mysql_Connection = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'root',
        port: 8889,
        database: 'my_database'
    });
    Mysql_Connection.connect();
    var jobs = 0;
    getTABLESfromSQL(Mysql_Connection, function(error, tables) {
        tables.forEach(function(table) {
            var collection = db.collection(table);
            ++jobs;
            CollectionTable(Mysql_Connection, table, collection, function(error) {
                if (error) throw error;
                --jobs;
            });
        })
    });
    var interval = setInterval(function() {
        if(jobs<=0) {
            clearInterval(interval);
            db.close();
            Mysql_Connection.end();
        }
    }, 300);
});

This snippet will do the following steps

1. Make and Open connection with MongoDB
2. Make a connection to MySQL
3. Collect a list of tables from MySQL
4. Collect data from the listed tables
5. Insert the data into MongoDB
6. Close MongoDB connection

This is Just snippet to get an idea on migrating SQL data to MongoDB. This can be improved at any level or changed according to the need.

MySQL to MongoDB data migration tool

MySQL to MongoDB data migration tool is the GitHub project available for cloning. This tool is written in NodeJS application. To install this tool, We have two methods to install and configure.

Method 1:

1. Clone the Project from https://github.com/dannysofftie/mysql-mongo-migrate.git. – git clone https://github.com/dannysofftie/mysql-mongo-migrate.git
2. Install Dependencies – npm install
3. Run Migrate – npm run migrate

Method 2:

1. Install package from NPM global repository – npm i -g mysql-mongo-migrate
2. Run the tool directly from the command line – $> mysql-mongo-migrate

For more instructions and details, visit Danny Sofftie’s GitHub Project here https://github.com/DannySofftie/mysql-mongo-migrate.

On top of these, We have exclusive White Paper which explains the best practice and guidelines to Migrate MySQL database to MongoDB in Enterprise way.

Conclusion

In this article, We have discussed the various methods that help us to Migrate SQL to MongoDB. Apart from the above methods, there are many ways to Migrate RDBMS to MongoDB. The above-mentioned ways are popular suggested ways of migration or ETL. But we always have our own way to migrate RDBMS to MongoDB. Stay tuned and subscribe DigitalVarys for more articles and study materials on DevOps, Agile, DevSecOps and App Development.