Is there a way to transfer a file from a MySQL database to a MSSQL Server using Nodejs?

Say, I have two applications, Application A uses MySQL database and nodejs for the backend. Application B uses MSSQL Server database.

I uploaded an image/document from Application A. I want to transfer/synchronize that image/document to Application B from the source code of Application A. I've tried using mssql client for Node.js. It works as the rows from MySQL database is transferred to MSSQL Server database. The problem is when I download the image/document from Application B, the image/document is a plain text with a file size of 0 byte.

Any suggestions or solutions that can help? Thank you.

CodePudding user response：

Requirements

Before starting the database migration, you will need the following software on your Windows machine :

• A running MS SQL Server instance.

• A running MySQL Server instance (this depends on your environment, we work with the MySQL server available in Xampp ). The idea is basically to have a MySQL server instance accessible on port 3306.

• SQL Server Management Studio installed.

• MySQL Workbench . This tool will allow you to migrate the data at the end.

1. Identify the database you are trying to migrate.

As a first step, you must verify that the database you want to migrate is exposed in your SQL Server instance. The easiest way to do this is through the SSMS tool. SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor and manage SQL Server instances and databases. Use SSMS to deploy, monitor and update the data layer components used by your applications, as well as create queries and scripts.

Open SSMS and access the database engine with the default Windows authentication (or from the connection you want to access) :

Connect to the server and browse the databases in the object browser. In our case, we want to export the my_database database, which, as you can see, is available in the Databases directory :

Now that you know that the database is accessible on the server, we will start with the migration using MySQL Workbench.

2. Start with the migration in MySQL Workbench

MySQL Workbench is a unified visual tool for database architects, developers and administrators. MySQL Workbench provides data modeling, SQL development and comprehensive administration tools for server configuration, user management, backup and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.

Start MySQL Workbench and access the migration wizard from the toolbar (in this tutorial, we use MySQL Workbench 8.0) :

After opening the wizard, click on start migration :

This will open the source selection form. Here you will need to select SQL Server as the source relational database management system (RDBMS). A relational database management system (RDBMS) is a collection of programs and capabilities that allow IT teams and others to create, update, manage and otherwise interact with a relational database, most of which use Structured Query Language (SQL) to access the database. In our case, as mentioned in the article, we will migrate from a Microsoft SQL Server database to a MySQL database, so it will be the source. In our example, we have the MS SQL Server configured on the same computer and it automatically authenticates with Windows Authentication, if your server is hosted remotely, you will need to change the parameters according to your needs. You can test the connection :

And if you are successful, you can continue with the next step of configuring the target RDBMS, which means MySQL. As we mentioned at the beginning of the article, we assume that you already have a MySQL server configured and running in the background, in our case we used Xampp's MySQL server, which allows you to start/stop it with a control panel. The idea is basically to know the credentials to access the running MySQL instance, which in Xampp is accessible with a rootuser and an empty password :

If you are connecting to another MySQL server, you can configure it with SSH keys, etc. Now that both connections are established, you can proceed to the next step which fetches a list of schemas from the source RDBMS. This will create a list that can be toggled with all databases in the SQL Server instance, where you must select the database you want to migrate (as well as the schema name mapping method) :

In our case, we only want to work with the my_database database, so we will extract only that database. Now, if you continue and everything works as expected, you will now see the Source Objects window, which basically allows you to filter which tables you want to migrate or not, normally we would like to migrate them all :

Click next and you will see the migration page, which allows you to pre-check the MySQL script for each table and if there are warnings or errors that you need to correct manually, they will be highlighted in the list. For example, in our case, we have a warning (when importing it will be an error) that specifies a problem with the migration, if we read the code we will see a syntax error of incompatibility with the VISIBLEkeyword. In our example, simply removing that keyword from the lines will allow us to import the scripts without any problem :

After manually correcting the warnings and applying the changes, you can finally export the database structure to a file (.sql) or create the database in the target RDBMS (MySQL Server). In our case, it is easier to import it directly to the server, so we will choose Create schema in target RDBMS (you can export it to a file if you wish) :

Before MySQL workbench starts with the migration, it will check again for errors and warnings; if there are still any, you must manually correct them again :

Click on Recreate object, this will take you to the previous step to build the schematic once again, click next and if everything is successful you will see, everything should be marked as correct :

Finally, all that is left is the data. We already migrated the database structure, so now we need to transfer all the data:

Since we are working with both servers on the same computer, we can make an online copy of the data from the SQL Server database to the MySQL database. Click next and the data migration will start :

Once finished, you will now have the original SQL database in MySQL format on the server. You can now access your MySQL server, where you will find the database available.

CodePudding user response：

Introduction

To integrate any database with nodejs, you need a driver package or you can call it npm module which will provide you with a basic API to connect to the database and perform interactions. The same goes for mssql database, here we will integrate mssql with nodejs and perform some basic queries on SQL tables.

Remarks

We have assumed that we will have a local instance of the mssql database server running on the local machine. You can refer this document to do the same.

Also make sure that the appropriate user created with added privileges as well.

Connecting to SQL via. mssql npm module

We will start by creating a simple node application with a basic structure and then connecting to the local SQL server database and performing some queries on that database.

Step 1: Create a directory / folder with the name of the project you are trying to create. Initialize a node application with the npm init command which will create a package.json in the current directory.

mkdir mySqlApp
//folder created 
cd mwSqlApp
//change to newly created directory
npm init
//answer all the question ..
npm install
//This will complete quickly since we have not added any packages to our app.

Step 2: Now we will create an App.js file in this directory and install some packages that we will need to connect to sql db.

sudo gedit App.js
//This will create App.js file , you can use your fav. text editor :)
npm install --save mssql
//This will install the mssql package to you app

Step 3: Now we will add a basic configuration variable to our application that will be used by the mssql module to establish a connection.

console.log("Hello world, This is an app to connect to sql server.");
var config = {
        "user": "myusername", //default is sa
        "password": "yourStrong(!)Password",
        "server": "localhost", // for local machine
        "database": "staging", // name of database
        "options": {
            "encrypt": true
        }
      }

sql.connect(config, err => { 
    if(err){
        throw err ;
    }
    console.log("Connection Successful !");

    new sql.Request().query('select 1 as number', (err, result) => {
        //handle err
        console.dir(result)
        // This example uses callbacks strategy for getting results.
    })
        
});

sql.on('error', err => {
    // ... error handler 
    console.log("Sql database connection error " ,err);
})

Step 4: This is the easiest step, where we start the application and the application will connect to the SQL server and print some simple results.

node App.js
// Output : 
// Hello world, This is an app to connect to sql server.
// Connection Successful !
// 1