ORM and Migrating/Adding Data to MySql Database from MongoDb using TypeOrm in javaScript
December 22, 2024

ORM and Migrating/Adding Data to MySql Database from MongoDb using TypeOrm in javaScript

What is an ORM?
Object Relational Mapping or ORM for short is a library or tool that helps us interact with the database without writing original database queries.

Instead of managing database tables and rows directly, we use some objects in our code, the ORM converts these objects into database queries, and when data is retrieved from the database, the ORM maps the data back to the objects.

ORM provides a bridge between object-oriented programs and relational databases.

Use cases:
If we are building a javaScript/TypeScript application and when we need a database for backend services then we can use ORM to connect our application to the database.

Several ORMs used in different languages ​​are:

  1. JavaScript/TypeScript: TypeOrm, Sequelize
  2. Python: Django ORM, SQLAlchemy
  3. Java: hibernate

Why should we use ORM in our applications?

  1. Improved security, ORM tools are constructed to eliminate the possibility of SQL injection attacks
  2. Using ORM tools requires less code to write than using sql.
  3. Using TypeORM, switching from one database to another (for example, from MySQL to PostgreSQL) is very simple and requires only few changes, making it highly adaptable to projects with changing database needs.

Now let us understand how to add data from mongodb to Mysql database using TypeOrm in javaScript.

The first thing we need to do is set up the connection between MySql and MongoDb

First define the configuration using the DataSource, which will use the specified credentials and host information to connect to the MySql database. This is its code.

I’m going to use a simple college model of what I did to give you a better understanding of it.

File name: connect.js

const  '',
         = require('./sqlmodels/College.model');   //taking model as a example here 
const sqlDataSource = new DataSource(
            _id: record._id.toString(),
            college_name: record.college_name ); 
Enter full screen mode

Exit full screen mode

Now connect to the MySQL database using the credentials and settings defined in sqlDataSource. This is its code

module.exports.connectSQL = async function () {
    try 
            _id: record._id.toString(),
            college_name: record.college_name  catch (error) 
}
Enter full screen mode

Exit full screen mode

Always write your code in a try catch block with proper logging, this will help you debug your code faster to prevent errors.

Before proceeding, also make sure that you have the correct connection to the mongoDb database.

First define the schema of the table in a separate folder, let me name it sqlmodels, and create the model’s page in it.
We need to import this file into the connect.js file where we write the connection code (first code)

This is a code snippet for university mode

const { EntitySchema, Entity, Column } = require('typeorm');

module.exports.College = new EntitySchema({
    name: 'College',
    tableName: 'College',
    columns: {
        _id: {
            primary: true,     // true if _id is your primary key
            type: 'varchar',  // define the type 
            length: 255,

        },
        college_name: {
            // define name of the column and its properties
            name: 'college_name', 
            type: 'varchar',
            length: 255,
            default: 'NO COLLEGE', 
            nullable: false

        }
    }

})
Enter full screen mode

Exit full screen mode

Make sure you have set up the mongoDb connection and defined the schema for it.

Now the last important thing we need to do is write the function that gets the data from mongoDb and adds it to the sql table.

const CollegeSchema = path for your mongo schema
const { College } = path for your sql schema
const { sqlDataSource } = path for your DataSource that defined above

module.exports.migrateCollegeDataInChunks = async(chunkSize = 1000) =>{
    let skip = 0; // Initialize skip value
    let hasMoreData = true; // Flag to check if more data exists

    // Get repository for College
    const CollegeRepository = sqlDataSource.getRepository(College);

    while (hasMoreData) {
        // Fetch a chunk of College data from MongoDB
        const CollegeData = await CollegeSchema
            .find({})
            .skip(skip)
            .limit(chunkSize);

        // Prepare data for insertion into SQL for College
        const CollegeSqlData = CollegeData.map(record => ({
            _id: record._id.toString(),
            college_name: record.college_name || '',
        }));

        // Save the data into the College repository (SQL)
        await CollegeRepository.save(CollegeSqlData);

        // Update skip value for the next chunk
        skip += chunkSize;

        // Check if there are more records to fetch
        if (CollegeData.length < chunkSize) {
            hasMoreData = false; // Exit the loop if fewer records are returned
        }
    }
}
Enter full screen mode

Exit full screen mode

Here I am putting data into chuck and then inserting them into table, this will be helpful if you are dealing with large amount of data as fetching large amount of data at once is not feasible in mongoDb.

Since this is my first blog, I welcome your suggestions and feedback to improve myself.
Thank you for reading🙏

2024-12-22 17:41:54

Leave a Reply

Your email address will not be published. Required fields are marked *