Request for Advice: migrating data from old database

I would probably do something like this

1. Make a copy of the database as is
2. Create new tables for everything according to the new schema
3. Transfer the old data to the new tables
4. Drop the old tables
5. Rename the new tables to the names in the schema
6. Export the database as SQL
7. Import the data into the new database.

I would do this using SQL and not involve Laravel if I can help it.

@nunodonato I've done this approach with one of my apps I had to migrate data from...

Step 1: Create a Database

Use mysql or whatever to create the database for the new app... CREATE DATABASE new_db

Step 2: Make an Import Bash Script

Create a script like pulldata.sh

# This script will clear the current database and update with a copy from the live database.

# Export old database to root folder.
echo "Exporting live old database to root folder."
ssh rds "mysqldump -uUSERNAME -hlocalhost -pPASSWORD old_database_name" > "./old.sql"

# Drop / re-create current db.
echo "Regenerating new db."
mysql -u root -e "DROP DATABASE new_db"
mysql -u root -e "CREATE DATABASE new_db"

# Import downloaded copy.
echo "Importing data into local database."
mysql -u root new_db < ./old.sql

# Remove SQL file.
echo "Cleaning up database SQL file."
rm ./old.sql

# Run Migrations
php artisan migrate

Just put that file in your root directory of your project and execute with sh pulldata.sh

So basically this handles copying the database over.

Step 3: Add Your Modification Migrations

Now before you run this... clear out your database\migrations folder. Either delete the existing migrations or modify them so they are simply making the necessary changes you want to the imported data. Changing column names, etc. You may need to import doctrine/dbal for certain schema changes.

WARNING! When doing these modifications, MAKE SURE YOU ARE NOT DELETING OR CORRUPTING DATA!!!

Step 4: Run the script

This approach has worked really well for me.

Once you've done this and are up and running... just delete your pulldata.sh script as you will no longer need it.

One thing to note is that you will get warnings about putting usernames and passwords into a mysql terminal command... I'm fine with doing this personally. Just be careful.

thanks guys. I'm afraid neither is a valid solution. The schemas are VERY different (there are fundamental changes to the way data is organized), so copying/importing sql from the old one is a BIG NO. And, like I said, some data is now split into different tables, so I need to do that manually for each of the old records

That is in step 3, you use SQL to store the data in the new tables.

For example this would extract all the authors from a books table and insert them into a new one

INSERT INTO authors (first_name, last_name)
SELECT DISTINCT first_name, 
                                last_name 
FROM books_old;

Then you can extract the rest of the book into the new books table.

INSERT INTO books (title, released, series, part)
SELECT title,
          released,
          series,
          part
FROM books_old;

And so on.

https://www.w3schools.com/sql/sql_insert_into_select.asp

What did you end up doing @nunodonato?

Personally I would create a command and a seeder for that

<?php

namespace App\Console\Commands;

use Database\Seeders\OldDbSeeder;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class SeedOldDatabase extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'seed:from-old-db database {--driver=} {--host=} {--username=} {--password=} {--charset=} {--collation=} {--prefix=}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Imports data from an old application with a different schema';

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $this->call('down');
        $default = DB::getDefaultConnection();
        config(['database.connections.old_db' => [
            'driver' => $this->option('driver') ?: config("database.connections.$default.driver"),
            'host' => $this->option('host') ?: config("database.connections.$default.host"),
            'database' => $this->argument('database') ?: config("database.connections.$default.database"),
            'username' => $this->option('username') ?: config("database.connections.$default.username"),
            'password' => $this->option('password') ?: config("database.connections.$default.password"),
            'charset' => $this->option('charset') ?: config("database.connections.$default.charset"),
            'collation' => $this->option('collation') ?: config("database.connections.$default.collation"),
            'prefix' => $this->option('prefix') ?: config("database.connections.$default.prefix"),
        ]]);

        $connection = DB::connection('old_db');

        (new OldDbSeeder())->run($connection);
        $this->call('up');
        return 0;
    }
}

<?php

namespace Database\Seeders;

use App\Models\Speaker;
use App\Structs\SocialLinks;
use Illuminate\Database\ConnectionInterface;
use Illuminate\Database\QueryException;
use Illuminate\Database\Seeder;

class OldDbSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run(ConnectionInterface $connection)
    {
        // Select from old db
        $connection
            ->table('app_speaker', 's')
            ->join('app_media as m', 's.image_id', '=', 'm.id')
            ->orderBy('s.created_at')
            ->whereNotIn('slug', Speaker::query()->select('slug')->get()->pluck('slug'))
            ->chunk(200, function ($speakers) {
                foreach ($speakers as $oldSpeaker) {
                    $oldSpeaker = (array)$oldSpeaker;
                    $speaker = new Speaker();
                    $speaker->slug = $oldSpeaker['slug'];
                    $speaker->first_name = $oldSpeaker['firstName'];
                    $speaker->last_name = $oldSpeaker['lastName'];
                    $speaker->setCreatedAt($oldSpeaker['created_at']);
                    $speaker->setUpdatedAt($oldSpeaker['updated_at']);
                    $speaker->job_title = $oldSpeaker['profession'];
                    $speaker->social_links = new SocialLinks([
                        'facebook' => $oldSpeaker['facebookLink'],
                        'twitter' => $oldSpeaker['twitterLink'],
                    ]);
                    $speaker->description = html_entity_decode($oldSpeaker['content']);
                    if (!$oldSpeaker['status']) {
                        $speaker->{$speaker->getDeletedAtColumn()} = $oldSpeaker['updated_at'];
                    }
                    try {
                        $speaker->save();
                    } catch (QueryException) {
                        echo "Speaker " . $speaker->slug . " could not be saved" . PHP_EOL;
                        // Ignore errors
                    }
                }
            });
    }
}

I don't really see the point of doing in batches in cli, you should have no limit of memory and time and your application probably needs this data before being deployed without too much downtime to the website, so might as well put the old site offline, with the new one in it's place in maintenance mode, then just run the command once, wait the 1h of import if it's really that big and then go out of maintenance

You can chunk the select statements for sure to improve the perfomance of the seeder as demonstrated (if you select 20 million rows at once, I don't think it will be very happy)