Connect to Multiple Databases for Data Migration in Laravel

Create a Laravel project

Create a project in Laravel 10. You must have Composer and PHP installed in your system. The project name is lara_multiple_db. Execute the below command to create the project:

composer create-project --prefer-dist laravel/laravel lara_multiple_db

A folder named "lara_multiple_db" will be created in the same directory from where you run the above command.

MySQL Database connection setup

We will use two databases, "db_old" is the source database and "db_new" is the target database. So, we will select data from the 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' the default connection. Add another connection 'mysql_2' just after 'mysql' as given earlier.

We are using two MySQL databases, so update .env file as below:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=db_new
DB_USERNAME=root
DB_PASSWORD=

# second connection to connect to old database for data migration
DB_CONNECTION_2=mysql
DB_HOST_2=127.0.0.1
DB_PORT_2=3306
DB_DATABASE_2=db_old
DB_USERNAME_2=root
DB_PASSWORD_2=

So, now our database connection setups are done.

We will create the "customers" table in the old databases ("db_old"), and a "user_profiles" table in the 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 create the "customers" table and insert some test data in this table in the old database.

"customers" table is in the old database.

CREATE TABLE `customers` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `zip` varchar(255) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` enum('Male','Female','Other','') DEFAULT NULL,
  `company_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `customers` (`id`, `name`, `email`, `phone`, `password`, `address`, `city`, `state`, `zip`, `dob`, `gender`, `company_name`) VALUES
(1, 'John Doe', 'john@test.com', '9999999999', '123', 'New ABC Boulevard', 'New york', 'New York', '99999', '1983-03-09', 'Male', 'ABC'),
(2, 'John Doe1', 'john1@test.com', '9999999991', '123', 'A street', 'New york', 'New York', '11111', '1990-03-01', 'Male', 'ABC'),
(3, 'Test user', 'test@test.com', '9999999992', '123', 'Toyon Ave', 'New york', 'New York', '11111', '1980-03-19', 'Female', 'ABC'),
(4, 'Test user1', 'test1@test.com', '9999999993', '123', 'Sunrise Boulevard', 'New york', 'New York', '11111', '1972-03-16', 'Female', 'ABC'),
(5, 'Test user2', 'test2@test.com', '9999999994', '123', 'Sunrise Boulevard', 'New york', 'New York', '11111', '1979-03-28', 'Female', 'ABC'),
(6, 'Test user3', 'test3@test.com', '9999999995', '123', 'B Street', 'Chicago', 'Illinois', '60001', '1982-03-10', 'Male', 'ABC'),
(7, 'Test user4', 'test4@test.com', '9999999996', '123', 'C Street', 'Chicago', 'Illinois', '60001', '1982-03-26', 'Female', 'ABC'),
(8, 'Test user5', 'test5@test.com', '9999999997', '123', 'D Street', 'Chicago', 'Illinois', '60001', '1982-03-26', 'Male', 'ABC'),
(9, 'Test user6', 'test6@test.com', '9999999998', '123', 'E Street', 'Chicago', 'Illinois', '60001', '1984-01-18', 'Male', 'ABC'),
(10, 'Test user7', 'test7@test.com', '9999999911', '123', 'E Street', 'Chicago', 'Illinois', '60001', '1987-05-15', 'Female', 'ABC');

ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `customers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

We have inserted 10 dummy records in this table. We will migrate this data to our Laravel database (db_new) into two tables - "users" and "user_profiles". The "users" table comes by default with Laravel. We will create the "user_profiles" table in the new database. So, let us create a Laravel migration file for the "user_profiles" table in our Laravel project.

php artisan make:model UserProfile -m

This command will create the migration file <yyyy_mm_dd_xxxxxx>_create_user_profiles_table.php under the database/migrations folder and the UserProfile model will be created in the app/Models folder.

Update migration file

We will keep the name, email and password in the "users" table and the rest of the columns will be in the "user_profiles" table. So, let us update the above migration file for user_profiles as below:

public function up(): void
    {
        Schema::create('user_profiles', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('user_id');
            $table->string('phone');
            $table->string('address')->nullable();
            $table->string('city')->nullable();
            $table->string('state')->nullable();
            $table->string('zip')->nullable();
            $table->string('dob')->nullable();
            $table->string('gender')->nullable();
            $table->string('company_name')->nullable();
            $table->timestamps();
        });

        Schema::table('user_profiles', function($table) {
            $table->foreign('user_id')
                ->references('id')->on('users')
                ->onDelete('cascade');
    
        });
      }

We will now run the migration to create the default Laravel tables and our custom "user_profiles" table. Run the below command from the project folder to create the tables.

php artisan migrate

After running the migration see the structure of the 'user_profiles' table in the "db_new" database. Note that name, email and password fields are not in this table, as those fields are already in the "users" table.

Write Controller code

Let us now create the controller as below:

php artisan make:controller DataMigrationController

The controller will have below methods:

1. index() method to display the page and run the data migration
2. migrateCustomer() method to migrate the customer data from the old database to the new database.

migrateCustomer() method

This method connects the old database to get all customer data. Then in a loop, each row from customer data is processed to insert data in the "users" and "user_profiles" tables in the new database. As mentioned earlier, name, email and password are inserted in the "users" table and the remaining fields from customer data are inserted in the "user_profiles" table. Destination tables are emptied before inserting data into them. Let's see the code for this method:

<?php
   public function migrateCustomer(){
        // get data from old database
        $customerData = DB::connection('mysql_2')->table('customers')->get();
        
        DB::beginTransaction();
        try {
        foreach ($customerData as $row){
           
            $userData = [
                'name'          => $row->name,
                'email'         => $row->email,
                'password'      => Hash::make(mt_rand(999, 99999)),
                'created_at'    => Carbon::now(),
                'updated_at'    => Carbon::now(),
            ];

            // insert name, email and password in users table
            $user = User::create($userData);

            $userProfileData = [
                'user_id'       => $user->id,
                'phone'        => $row->phone,
                'address'       => $row->address,
                'city'          => $row->city,
                'state'         => $row->state,
                'zip'           => $row->zip,
                'dob'           => $row->dob,
                'gender'        => $row->gender,
                'company_name'  => $row->company_name,
                'created_at'    => Carbon::now(),
                'updated_at'    => Carbon::now(),
            ];

            // remaining all data goes to user_profiles table
            UserProfile::create($userProfileData);
        }
        DB::commit();
        return back()->with('message', 'Customer Data migrated');
    }
        catch(\Throwable $th ){
            DB::rollBack();     
            return back()->with('message', $th->getMessage());  
        }
    }

We connect to the old database using 'mysql_2' connection and select all customer data. In our Laravel project, we have the "users" and "user_profiles" tables. Customer data from the old project will be stored in two tables - "users" and "user_profiles". So, we are just inserting the required fields in the respective tables. Note that for the new database, we do not need to mention the connection name to access the "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 in the view.

Routes

Route::get('/', [DataMigrationController::class, 'index'])->name('datamigration');
Route::get('/migrate-customer', [DataMigrationController::class, 'migrateCustomer'])->name('migrateCustomer');

Laravel Blade Views

We have one view to run the migrateCustomer() method of the controller.

<!doctype html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <title>Data Migration</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="csrf-token" content="{{ csrf_token() }}" />
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" />
    <style>
        h1 {
            text-align: center;
        }

        p {
            text-align: center;
        }
    </style>
</head>

<body>
    <div class="container">
        <h1 class="mt-5 mb-5">Data Migration using Laravel multiple database connections</h1>

        <p>
            
                    </p>
        <div class="row">
            <div class="col-md-12 mt-5 text-center">
                <a href="{{ route('migrateCustomer') }}" class="btn btn-primary">Migrate Customer Data</a>
            </div>
        </div>
    </div>
</body>
</html>

It just displays a button to call the migrateCustomer route. Success or error messages are displayed once the migration is done. See the below screenshot when migration is completed:

Test the application

From the project root, run the php development server:

php artisan serve

From the browser start localhost:8000. Verify if the application is working correctly. Check Initially "users" and "user_profiles" tables are empty in the new database, but the "customers" table has some data in it in the old database.

Click on the Migrate Customer data button, data migration should complete successfully. Verify that the "users" and "user_profiles" tables are populated with data from the "customers" table from the old database.

Conclusion

You can use this type of data migration when you need 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. You can always use transformation in between and finally populate the destination tables. I hope this topic will help you work on a similar requirement.