Published 15 Sep, 2018

Automatically export a database and import it to another using Node.js

When updating a development database with the latest changes it can be very time-consuming, so let me show you a quick service that will help speed up this process.

Let's initialize a new project by first creating a folder, and then creating a new npm package.

Bash

mkdir database-importer
npm init --yes

I will be using a MySQL database but the same principles can be applied to other SQL databases (with a bit of tweaking).

For this project, we will only need Node.js core dependencies so no need to install anything else. Add the file index.js in the folder we have just created and open it in your favorite code editor.

In order to export the data from the production database we will need to execute some shell commands. In order to do this we can use the child_processes dependency, specifically the exec function, so let's import it.

JavaScript

const { exec } = require('child_process');

Once we have got the dependencies, we can go ahead and set up the configuration variables that we can use later when connecting to the databases.

The dumpFile variable is the path + filename that we will export the database SQL file to. The other two object variables (exportFrom and importTo) contain the connection settings for both databases.

JavaScript

// Where would the file be located?
let dumpFile = 'dump.sql';	

// Database connection settings.
let exportFrom = {
	host: "localhost",
	user: "mysqluser",
	password: "mysqlpassword",
	database: "production_database"
}
let importTo = {
	host: "localhost",
	user: "mysqluser",
	password: "mysqlpassword",
	database: "development_database"
}

Now that we have the connection settings that we need, we can go ahead and use mysqldump. This specific command comes with the MySQL package and allows you to quickly export databases.

You can use it in the terminal if you want, but in order to automate the process of exporting databases we'll use it in our javascript file, since we can then access the variables we defined earlier.

You can find out more about this command here.

JavaScript

// Execute a MySQL Dump and redirect the output to the file in dumpFile variable.
exec(`mysqldump -u${exportFrom.user} -p${exportFrom.password} -h${exportFrom.host} --compact ${exportFrom.database} > ${dumpFile}`, (err, stdout, stderr) => {
	if (err) { console.error(`exec error: ${err}`); return; }
});

As mentioned previously, the exec() function allows you to execute commands in the shell. The first parameter that it requires is the actual command that you would write in the terminal.

In this case, we need to export the database so let's execute mysqldump and specify the connection details. We then can redirect all of that output into our SQL file, the --compact parameter is there to remove unnecessary comments that it adds by default. The second parameter of this function is the callback (which we can use later import data).

Since we now have the SQL data we will need a way to import it, so let's inside the callback write the following code.

JavaScript

// Import the database.
exec(`mysql -u${importTo.user} -p${importTo.password} -h${importTo.host} ${importTo.database} < ${dumpFile}`, (err, stdout, stderr) => {
    if (err) { console.error(`exec error: ${err}`); return; }
});

The code above is another exec() function and we are doing the exact opposite by first connecting to the database and running the commands inside the SQL file we exported.

This should be all we need! The final file would look something like this:

JavaScript

// Include the dependency we need.
const { exec } = require('child_process');


// Where would the file be located?
let dumpFile = 'dump.sql';	


// Database connection settings.
let exportFrom = {
	host: "localhost",
	user: "mysqluser",
	password: "mysqlpassword",
	database: "production_database"
}
let importTo = {
	host: "localhost",
	user: "mysqluser",
	password: "mysqlpassword",
	database: "development_database"
}

console.log(`Starting exporting data from the ${exportFrom.database} database`);

// Execute a MySQL Dump and redirect the output to the file in dumpFile variable.
exec(`mysqldump -u${exportFrom.user} -p${exportFrom.password} -h${exportFrom.host} --compact ${exportFrom.database} > ${dumpFile}`, (err, stdout, stderr) => {
	if (err) { console.error(`exec error: ${err}`); return; }
	
	console.log(`Now, importing data to the ${importTo.database} database`);
    
	// Import the database.
	exec(`mysql -u${importTo.user} -p${importTo.password} -h${importTo.host} ${importTo.database} < ${dumpFile}`, (err, stdout, stderr) => {
        if (err) { console.error(`exec error: ${err}`); return; }

        console.log(`The import has finished.`);
	});

});

You can find the final project in this public repo I have created.