1 month ago

Fetch data from remote database and add/update to local database

Posted 1 month ago by ThinkingMan


My app has a customers table that I want to "sync" with customer records from a remote database. The remote database contains customer records with core information like account number, name, address, etc... I want to add or update those records in my app so I can extend the functionality for things like call tasks and marketing or other CRM related functions.

I have to do this because the customer uses an on-premise solution which doesn't have API's. But I do have access to the MySQL data so I can read the records. I'll do an initial load then look for deltas based on the "last updated" field on the remote data.


Remote Customer Table Model

class RCustomers extends Model
    use RemoteConTrait;

    protected $primaryKey = 'cr_customer';

    protected $table= "CR";

    protected $connection = 'remote';

    protected static function boot()
        static::addGlobalScope('cr_customer', function (Builder $builder) {
            $builder->where('cr_customer', '<>' , 0);

        static::addGlobalScope('select_fields', function (Builder $builder) {
                    'cr_customer as cr_customer',
                    'cr_job_number as cr_job_number',
                    'cr_sort_name_type as cr_sort_type',

Local Customer Table Model

class Customers extends Model
    use Notifiable;

     * The attributes that are mass assignable.
     * @var array
    protected $fillable = [

I was going to have a job that ran daily to do this. This is where I keep getting hung up. I have tried a few approaches and I know I am wrong but this is where I am. What's the best approach?

 DB::connection('remote')->table('CR')->orderBy('cr_customer')->chunk(10, function ($remoteCRs) {
                $remoteCRs->map( function ($data){

                    return $data;
                foreach ($remoteCRs as $remoteCR) {

                    //customer record

                            'cr_customer' => $remoteCR->cr_customer

                    echo $remoteCR->order_number."\n";


Please sign in or create an account to participate in this conversation.