Laravel 4.2: Copying database records from one database to another

Question

I need to copy a subset of records from one database to another in Laravel 4.2

I've written an artisan task that loads the models that I need to copy from the "default" database connection, and now need to save them to the second database connection (which is defined in config/database.php). What I can't find is any way to use the model's save() method to save to a database other than the default connection.

Is this possible? Can I "push" my saves to the second database connection? Or do I need to change to a "pull" logic, reverse my database connection definitions, and load the data from the "second" connection before saving to the "default"?

score 1 · Answer 1 · answered May 10 '16 at 22:04

First of all you have to define the secoundary connection in app/conifg/database.php and then in connections for example:

'second_db_conn' => [
        'driver'    => 'mysql',
        'host'      => $_SERVER['MYSQL_HOST'],
        'database'  => $_SERVER['MYSQL_DATABASE'],
        'username'  => $_SERVER['MYSQL_USERNAME'],
        'password'  => $_SERVER['MYSQL_PASSWORD'],
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
    ],

Then set this connection on yout model - save it - and switch back to the default (in my example is a mysql):

$model->setConnection('second_db_conn')->save();
$model->setConnection('mysql');

Thanks, I'd finally worked out that I needed to set the connection in the model.... except I discovered that this tried executing an UPDATE transaction rather than an INSERT.... the solution was to explicitly set `$model->exists = false` before doing the `save()`, otherwise the `exists` property was always true from reading from the first database connection, so Laravel treated it as an UPDATE and UPDATing wouldn't work unless the record already existed on the second database — Mark Baker, May 10 '16 at 22:32

score 1 · Accepted Answer · answered May 10 '16 at 22:39

As @Filip has indicated, it's necessary to set the connection on the model to the second (destination) database before saving the model.

However, it's also necessary to set the id to null (unless you are certain that no record with that id exists on the second database, which I couldn't) to ensure that a new incrementing id value will be allocated.

And its also necessary to set the exists property of the model to false before saving. This value is true in the model after reading the record from the source database, because the record was successfully read from there (rather than being created as a new record). Failing to reset exists resulted in Laravel trying to execute an UPDATE WHERE id = $model->id, which wouldn't write anything to the database, because there was no matching record (with a null id) to update. Setting exists to false ensures that the save executes as an INSERT.

Laravel 4.2: Copying database records from one database to another

2 Answers2

Linked