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

What is ORM ?

Object Relational Mapping or simply ORM is a library or tool that helps us to interact with databases without writing raw database queries.

Instead of directly managing database tables and rows, we work with some objects in our code and the ORM translates this objects into database queries, and when the data is retrieved back from the database the ORM maps the data back into objects.

ORM creates a bridge between object oriented programs and relational databases.

Use Cases:

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

few ORMs used in different languages are:

1. JavaScript/TypeScript : TypeOrm, Sequelize


2. Python : Django ORM, SQLAlchemy


3. Java : Hibernate

Why should we use ORM's in our application ?

1. Improves security, ORM tools are built to eliminate the possibility of SQL injection attacks


2. you will write less code when using ORM tools than with sql.


3. With TypeORM, switching from one database to another (e.g., MySQL to PostgreSQL) is straightforward and requires minimal changes, making it highly adaptable for projects with evolving database requirements.

Now lets understand how we can add data from mongodb to Mysql database using TypeOrm in javaScript.

First and foremost thing that we need to do is to set the connection for MySql and MongoDb

First define the configuration using DataSource which will connect to MySql database using specified credentials and host information.It also maps entity classes to database table thus enabling orm functionality. Here the code for it.

Im going to take simple college model that i worked to make you understand it better.

fileName : connect.js

const { College } = require('./sqlmodels/College.model');   //taking model as a example here 

const sqlDataSource = new DataSource({

    type: "mysql",

    host: "XXXX", 

    port:  3306,

    username: 'myUserName',

    password: 'MyPassWord',

    database: 'MyDb',

    driver: require("mysql2"),  

    connectTimeout: 10000,

    entities: [

         College  // define all your entities here 

    ],

    synchronize: true,

    logging: false

}); 

Now connect to the MySQL database using the credentials and configurations defined in the sqlDataSource. Here is the code for it

module.exports.connectSQL = async function () {

    try {

        console.log("Trying to connect to SQL Database...");

        await sqlDataSource.initialize();

        console.log("Successfully connected to SQL Database");

    } catch (error) {

        console.error("Error occured while connecting SQL", error);



    }

}

always write your code within a try catch block with proper logs, which will helps you in debugging your code faster, incase you make mistakes.

Also make sure you have proper connection for mongoDb database before proceeding further.

First define the schemas for your table in a Separate folder, let me name it as sqlmodels and inside it create the pages for your models.

We need to import this file in the connect.js file in which we wrote code for connection (first code)

Here is the piece of code for college schema

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



        }

    }



})

Make Sure you have your mongoDb connection set and you also have the Schemas defined for it.

Now the final and important thing that we need to do is to write the function that will fetch the data from mongoDb and adds it to sql tables.

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

        }

    }

}

Here i took the data in chucks and then inserted them into tables, this will be helpful if you are dealing with large amount of data as fetching large amount of data at a time is not feasible in mongoDb.

As this is my first blog, i welcome your suggestion and feedback to improve myself.

Thank you for reading 🙏