Migrate data from sql database to mongodb
npm install @emiliosel/sql-to-mongoA simple node.js module that Migrate data from sql database to mongo
npm install @emiliosel/sql-to-mongo
const Migration = require('@emiliosel/sql-to-mongo');
Config for mysql connection:
const knexConfig = {
client: 'mysql',
connection: {
host: '0.0.0.0',
port: '3306',
user: 'root',
password: 'secret',
database: 'world'
}
}
Config for mysql connection:
const monooseConfig = {
host: 'localhost',
port: '27017',
database: 'myWorld'
}
Options for running migration of data:
const options = {
fromTable: 'city',
toCollection: 'mongoCity',
paginate: 1000, // paginate the query by 1000 items
columns: { // are the fields that will be created to collection
title: { // the name that will give to the field of collection
type: String, // accepts all the types of mongoose.Schema
from: 'Name' // the column name from sql table
},
name: {
type: String,
from: 'Name'
},
countryCode: { // 'beforeSave' callback is called at mongoose
type: String, // middleware post('validate')
from: 'CountryCode', // 'doc' is the current document that passed mongoose
// validation and is about to save
// we can change or format the properties of doc
beforeSave: async (doc, mongoose, knex) => {
doc.countryCode = doc.countryCode.toLowerCase()
}
},
Population: { // here is not provided 'from' so the name of the
type: Number // collection field 'Population' is the same with
}, // sql column
customColumn: {
type: { // custom field added to mongo collection that
type: String, // does not exist to sql table
default: 'test' // with default value 'test'
},
custom: true
},
anotherCustomField: { // custom field that we populate with a custom
type: Array, // query using knex. We could also use mongoose
custom: true,
beforeSave: async (doc, mongoose, knex) => {
let someValues = await knex.select().from('someTable')
doc['anotherCustomField'] = someValues
}
}
}
}
Instantiate the Migration with the databases config:
let migration = new Migration({
sql: knexConfig,
mongo: mongooseConfig
})
Run migration passing the options object as param:
migration.up(options).then(() => {
console.log('succesfully')
})
.catch((er) => {
console.log('error')
})
Run delete created collection:
migration.down().then(() => {
console.log('ok')
})
.catch((er) => {
console.log('error')
})
Run custom migration:
migration.up(async (knex, mongoose) => {
let data = await knex.select().from('tableName')
let schema = new mongoose.Schema({
title: String,
date: Date
})
let model = mongoose.model('ModelName', schema)
await model.insertMany(data)
})
docker-compose up
npm test