How to connect to multiple databases for data migration in Laravel 10
During data migration, you might have to connect to multiple databases in Laravel to get data from source and put in the target database. Sometimes this could be straight forward, when you do not have to use any transformation logic, so you just use export and import in MySQL database. But, if you have to write transformation logic in Laravel controller, then a better way is to use both database connections to get data from source database, do transformation logic in your controller and finally insert data into the target database.
In this topic, we will develop a Laravel 10 application to migrate data from one MySQL database to another using Laravel multiple database connections. We will use database connections dynamically to select data from two different MySQL databases.
This will be useful if you are upgrading your current project and you need to migrate old data to the new application which is in Laravel. It may not be a one-to-one data migration always as some table structures might get changed in upgrading the project for performance or application enhancement.
Below setup changes are required for multiple database connections in Laravel.
Update app/config/database.php
In addition to 'mysql' which is default connection, add below code to define another connection 'mysql_2' just after 'mysql':
Update .env for DB_HOST_2, DB_PORT_2, DB_DATABASE_2, DB_USERNAME_2, DB_PASSWORD_2 which are used for 'mysql_2' connection.
Use 2nd connection in the controller to select data.
$customerData = DB::connection('mysql_2')->table('customers')->get();
I will develop an application to migrate data from one database to another, for example, customer data from "db_old" database to be migrated to "users" and "user_profiles" tables in "db_new" database in Laravel.
Step1 - Create a Laravel project
Let us create a project in laravel 10. Make sure you have composer and PHP installed in your system. I have given the project name as lara_multiple_db.
This will create a folder named "lara_multiple_db" under the folder where you run the above command.
Step 2 - MySQL Database connection setup
We will use two databases, "db_old" is the source database and "db_new" is the target database. So, basically we will select data from old database and migrate them to the new database.
Update config/database.php
We need to add another connection in config/database.php in addition to 'mysql' which is default connection. Add below code to define another connection 'mysql_2' just after 'mysql':
We have defined 'mysql_2' with new .env variables DB_HOST_2, DB_PORT_2, DB_DATABASE_2, DB_USERNAME_2, DB_PASSWORD_2 and these are defined in .env.
We are using two MySQL databases, so update .env file as below:
So, now our database connection setups are done.
We will create "customers" table in old databases ("db_old"), and "user_profiles" table in new database ("db_new"). Our old database may or may not be in Laravel. It could be used in any application that uses MySQL database. So, in our case we will just create "customers" table and insert some test data in this table in the old database.
"customers" Table in old database.
Create table script for this table is given below.
Create table script for customers in "db_old"
I have inserted 10 test rows in this table. We will migrate this data to our Laravel database (db_new) into two tables - "users" and "user_profiles". "users" table comes default with Laravel. We will create the "user_profiles" table in new database. So, let us create a Laravel migration file for "user_profiles" table in our Laravel project.
Above command will create the migration file name <yyyy_mm_dd_xxxxxx>_create_user_profiles_table.php under database/migrations folder and UserProfile model will be created in app/Models folder.
Update migration file
We will keep, name, email and password in "users" table and rest all columns from "customers" table will be in "user_profiles" table in new database. So, let us update the above migration file for user_profiles as below:
Let us now run the migration to create the default Laravel tables as well as our custom "user_profiles" table. Run below command from project folder to create the tables.
After running migration see the structure of 'user_profiles' table in "db_new" database. Note that name, email and password fields are not in this table, as those fields are already in users table.
Step 3 - Write Controller code
Let us now create the controller as below:
Controller will have below methods:
index() method to display the page to run data migrationmigrateCustomer() method to migrate Customer data from old database to new database.
migrateCustomer() method
This method connects to old database to get all customer data. Then in a loop, each row from customer data is processed to insert data in "users" and "user_profiles" tables in the new database. As mentioned earlier, name, email and password are inserted in "users" table and the remaining fields from customer data are inserted in "user_profiles" table in new database. Destination tables are emptied before inserting data in it. Let's see the code for this method:
Here, we are connecting to the old database by using 'mysql_2' connection and selecting all data from customer data. In our Laravel project, we have "users" and "user_profiles" tables. It has been decided that customer data from the old project need to be populated in the "users" and "user_profiles" tables in the new system. So we are just inserting the required fields in the respective tables. Note that for the new system, we do not need to mention the connection name to access "users" and "user_profiles" tables, because that is our default connection.
Since we will be calling this method from a page, we need to display a success or error message to the view.
Routes
Step 4 - Laravel Blade Views
We have one view to run the migrate method of the controller.
resources/views//index.blade.php
It just displays a button to call the migrateCustomer route. Success or error messages are printed once migration is done. See below when migration is completed:
Step 5 - Test the application
From the project root, run the php development server:
From browser start localhost:8000. Verify if application is working correctly. Check initially "users" and "user_profiles" tables are empty in the new database, but "customers" table has some data in it in the old database ./p>
Click on the Migrate Customer data button, data migration should complete successfully. Verify that "users" and "user_profiles" tables are populated with data from "customers" table in old database.
Conclusion
You can use this type of data migration when you have to do some transformation from the source database to the destination database using multiple database connections in Laravel. In this example, a single source table was mapped to two destination tables. It could be one-to-one or one-to-many. You can always use transformation in between and finally populate the destination tables. Hope this topic will help you work on the similar requirement.
Download code from github.
Development Steps