Membuat script migrasi data dari mssql server ke postgresql dengan menggunakan nodejs

Pembuatan Script migrasi data dengan Node JS

Cara migrasi database Sql Server ke PostgreSQL bisa dilakukan melalui berbagai cara, salah satunya adalah melalui cara membuat aplikasi dengan Node JS, yakni melalui script batch yang berjalan di javascript, dalam hal ini adalah Node JS. 

Script ini ditulis menggunakan bahasa javascript yang dijalankan di node.js, yang digunakan untuk migrasi data antar database, yakni dari database SQL Server ke database PostgreSQL. Dengan catatan bahwa data yang dimigrasi adalah tabel yang memiliki primary key bertipe angka (integer, numeric) dan Auto Increment / Identity.

Install node module using NPM

Sebelum memulai membuat script Node JS, pertama kita install dulu dependensinya. Beberapa dependensi Node JS yang diperlukan adalah :

"mssql": "^6.2.1",
"pg": "^8.3.0",
"set-tz": "^0.2.0"

Berikut ini adalah scriptnya :

//ditulis oleh yusri irfani
'use strict'
const setTZ = require('set-tz')
setTZ('UTC')

var judulapp = "\x1b[0m" + "Auto insert "+"\x1b[34m"+"tabelSiswa "+"\x1b[0m"
var sql = require("mssql"); 

const pool = {
	user: 'postgres', host: 'localhost', database: 'dbSekolah', password: 'postgres', port: 5432,
}

const config = {
	user: 'sa',
	password: '',
	server: 'localhost', // You can use 'localhost\\instance' to connect to named instance
	database: 'dbSekolah',
	trustedConnection:true,
	requestTimeout:60000,
	options: {
		encrypt: false // Use this if you're on Windows Azure
	}
}

const tblSource = 'tabelSiswa';
var fieldssource = [ "idsiswa", "namasiswa", "aktif" ]

const tblDestination = 'public.tabelSiswa';
var fieldsDestination = ["id_siswa", "nama_siswa", "aktif"]
var seqName = 'public.tabelSiswaSeq';

//postgre
const { Pool } = require('pg')
const pool = new Pool(pool)

//function delay 
function delay(t, val) {
   return new Promise(function(resolve) {
	   setTimeout(function() {
		   resolve(val);
	   }, t);
   });
}	

async function insert_to_pg(fields){
	let valString = '';
	let index, len;
	for (index = 1, len = fieldsDestination.length; index <= len; ++index) {
		valString = valString + '$'+index+',';
	}
	let str1 = valString.replace(/.$/,"")
	const text = "INSERT INTO "+tblDestination+" ("+fieldsDestination+") VALUES("+str1+") RETURNING "+fieldsDestination[0]
	const values = fields
	const client = await pool.connect()
	try {
		const res = await client.query(text, values)
		//update sequence
		const res2 = await client.query("SELECT setval('"+seqName+"', "+fields[0]+")")
		console.log("\x1b[34m" + judulapp + "Insert into postgre " + "\x1b[32m" + res.rows[0][fieldsDestination[0]] + "\x1b[0m")
		return null
	} catch (err) {
		console.log(err)
		//return err
		throw new Error('Ran out of coffee')
		//return
	} finally {
		client.release()
	}
}

async function select_from_pg(){
	//where id_labpasien_tmp<=2020044539 
	const text = "select "+fieldsDestination[0]+" from "+tblDestination+" order by "+fieldsDestination[0]+" desc limit 1"
	const client = await pool.connect()
	try {
		const res = await client.query(text)
		return await res
	} catch (err) {
		console.log(err)
		//return null
	} finally {
		client.release()
	}
}

async function get_data_from_mssql(id_pkkey) {
	try {
		await sql.close();
		let pool = await sql.connect(config)
		let result1 = await pool.request()
		.input('ID_PKEY', sql.Numeric(18,0), id_pkkey)
		.query("select top 5000 "+fieldssource+" from "+tblSource+" where "+fieldssource[0]+" > @ID_PKEY order by "+fieldssource[0]+" asc")
		return await result1
	}catch (err) {
		console.log(err)
		return null
    }	
}

async function updtr () {
	
	try {
		//process.stdout.write('\x1Bc');
		let get_data = await select_from_pg();
			
		async function printFiles (rcrdset) {
			const files = await rcrdset;
			for (const file of files) {
				const d = await file;
				
				//bit
				let myBit = {
					aktif : d['aktif'],
				};
				
				for (var key in myBit) {
				  if(myBit[key] == true){myBit[key] = 1} else if (myBit[key] == false) {myBit[key] = 0}
				}
			
				let ins_mr = await insert_to_pg([
					d['idsiswa'] /*id_siswa*/, 
					d['namasiswa'] /*nama_siswa*/, 
					myBit['aktif'] /*aktif*/, 
				])
			}
		}
		
		if (get_data.rowCount>0){
			let dt_mssql = await get_data_from_mssql(get_data.rows[0][fieldsDestination[0]]);
			if(dt_mssql.rowsAffected>0){
				printFiles(await dt_mssql.recordset).then(async () => {
					await delay(100); 
					updtr(); 
				}).catch((e)=>{
					console.log(e)
					
					updtr(); 
				})
			}else{
				console.log('data tidak ada')
			}
		}else{
			let dt_mssql = await get_data_from_mssql(0);			
			 printFiles(await dt_mssql.recordset).then(async () => {
				await delay(100); 
				updtr(); 
			}).catch((e)=>{
				console.log(e)
				updtr(); 
			})
		}
		//await delay(100);
	}catch (err) {
        console.log(err);
		//await delay(100);
		updtr()
    }	
}

updtr().catch()
//ditulis oleh yusri irfani

Share this post