Sequelize: How to Migrate Data from One Database to Another (3 Ways)

This concise article will walk you through some different approaches to migrating a database with the help of Sequelize.js and Node.js.

Solution 1: Manual Data Export and Import

Description: A straightforward approach to migrate data by manually exporting from the source database as a file and then importing the file into the target database. This sometimes involves tools specific to the DBMS (Database Management System), such as mysqldump for MySQL.

• Export data from source database to a file using native database tools or Sequelize’s query interface.
• Import the data file into the target database using another set of native database tools or by reading the file content with Sequelize’s query interface.

Example:

// Using Sequelize's query() for both export and import

// Exporting Data
sequelize.query('SELECT * FROM source_table INTO OUTFILE \'/path/to/dump.csv\' FIELDS TERMINATED BY \\',\' LINES TERMINATED BY \'\n\'',
{ type: sequelize.QueryTypes.SELECT });

// Importing Data
fs.readFile('/path/to/dump.csv', 'utf8', (err, data) => {
  if (err) throw err;
  sequelize.query('LOAD DATA INFILE \'/path/to/dump.csv\' INTO TABLE target_table FIELDS TERMINATED BY \\',\' LINES TERMINATED BY \'\n\'');
});

Pros: Simple and does not require complex tools; Works well for a one-time migration.

Cons: Lack of automation; Not suitable for continuous synchronization; Potential compatibility issues with different database systems.

Solution 2: Sequelize Seeders

Description: Using Sequelize Seeders to reproducibly and programmatically transfer data from one database to another. This approach automates the data migration process through seed files, which are executed using Sequelize CLI.

• Create a seeder file that will obtain data from the source database.
• Modify the seeder file to insert the data into the target database.
• Use Sequelize CLI to run the migration on the target database.

Code example:

// Using Sequelize CLI seeders

// Generating Seeder
sequelize seed:generate --name data-migration

// Seeder logic (put as part of the generated seeder file)
module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Assuming source data is already fetched and placed into sourceData variable
    await queryInterface.bulkInsert('target_table', sourceData, {});
  },
  down: async (queryInterface, Sequelize) => {
    // Clean up if needed
  }
};

// Running Seeder
sequelize db:seed --seed data-migration

Pros: Scripted method provides repeatability; Can be integrated into more complex workflows.

Cons: Seeders are generally used for initial data load and not for continuous data synchronization; May require additional scripting to handle complex data relationships.

Solution 3: Custom Migration Script With Sequelize

Description: Developing a custom Node.js migration script using Sequelize to handle synchronization between databases. This approach can be used to achieve a more controlled and continuous data migration flow using JavaScript and Sequelize as an intermediary to manage transformations.

• Set up Sequelize instances for both source and target databases.
• Fetch the source data with Sequelize.
• Perform any necessary transformation or cleaning of data in the script.
• Insert or update the data in the target database with Sequelize.

Code example:

// Custom migration script using Sequelize
const { sequelizeSource, sequelizeTarget } = require('./sequelize-instances');

async function migrateData(modelName, whereClause) {
  const sourceData = await sequelizeSource.models[modelName].findAll(whereClause);
  await sequelizeTarget.models[modelName].bulkCreate(sourceData.map(data => data.get({ plain: true })), {
    updateOnDuplicate: Object.keys(sequelizeSource.models[modelName].rawAttributes)
  });
}

// Running migration
migrateData('User', { where: { active: true } });

Pros: Allows complex transformations and control; Ideal for ongoing synchronization.

Cons: Development time required; Potentially complex depending on data schemas.

That’s it. Happy coding & have a nice day!