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.
Table of Contents
Various Methods to Migrate SQL to MongoDB
- Using Mongify Tool
- Write your own script.
- 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
Using Mongify Tool.
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.
“check” or ‘ck”
– Which will ensure thedatabase. config file is correct or not and whether all the connection mentioned in the file is working or not."translation" or "tr”
– This command will auto-generate the “database_translation.rb”. Like,mongify translation database.config > database_translation.rb
-
"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
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
- Make and Open connection with MongoDB
- Make a connection to MySQL
- Collect a list of tables from MySQL
- Collect data from the listed tables
- Insert the data into MongoDB
- 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:
- Clone the Project from https://github.com/dannysofftie/mysql-mongo-migrate.git. –
git clone https://github.com/dannysofftie/mysql-mongo-migrate.git
- Install Dependencies –
npm install
- Run Migrate –
npm run migrate
Method 2:
- Install package from NPM global repository –
npm i -g mysql-mongo-migrate
- Run the
t ool 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.
Certified Cloud Automation Architect and DevSecOps expert, skilled in optimizing IT workflows with Six Sigma and Value Stream Management. Proficient in both technical and leadership roles, I deliver robust solutions and lead teams to success.
Glad to see a tool that I authored being featured here.
Your work on this tool towards open-source community is really notable and deserved to be promoted as it is very useful for developers.
Sure and appreciated.