Laravel Offline Sync Package

A comprehensive Laravel package for synchronizing data between offline and online databases with automatic conflict resolution and bidirectional sync capabilities. Fully compatible with Laravel 10.x, 11.x, and 12.x.

Features

• 🔄 Bidirectional Synchronization: Sync data between offline (MySQL/SQL Server) and online (MySQL/PostgreSQL/SQL Server) databases
• 🚀 Automatic Mode Switching: Intelligently switches between offline and online modes based on connectivity
• ⚡ Queue-based Processing: Efficient background processing of sync operations
• 🔧 Conflict Resolution: Multiple strategies for handling data conflicts
• 🎯 Model Integration: Simple trait-based integration with your Eloquent models
• 📊 Status Monitoring: Comprehensive status and statistics tracking
• 🛡️ Error Handling: Robust error handling with retry mechanisms
• ⚙️ Configurable: Highly configurable to fit your specific needs

Requirements

• PHP 8.1+
• Laravel 10.0+, 11.0+, or 12.0+
• MySQL/SQL Server (for offline database)
• MySQL/PostgreSQL/SQL Server (for online database)

Laravel Version Compatibility

Laravel Version	Package Support	Status
Laravel 10.x	✅ Full Support	Stable
Laravel 11.x	✅ Full Support	Stable
Laravel 12.x	✅ Full Support	Latest

The package is designed to be forward-compatible and will work with future Laravel versions that maintain backward compatibility.

Installation

1. Install the Package

composer require xslain/laravel-offline-sync

2. Install Database Extensions (if needed)

For SQL Server Support

# On Windows with XAMPP/WAMP
# Download Microsoft Drivers for PHP for SQL Server
# https://docs.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server

# On Linux/Ubuntu
sudo apt-get update
sudo apt-get install php8.1-sqlsrv php8.1-pdo-sqlsrv

# On CentOS/RHEL
sudo yum install php-sqlsrv php-pdo_sqlsrv

For PostgreSQL Support

# On Ubuntu/Debian
sudo apt-get install php8.1-pgsql

# On CentOS/RHEL
sudo yum install php-pgsql

# On macOS with Homebrew
brew install php@8.1-pgsql

3. Publish Configuration

php artisan vendor:publish --provider="Xslain\OfflineSync\OfflineSyncServiceProvider" --tag="config"

4. Publish and Run Migrations

php artisan vendor:publish --provider="Xslain\OfflineSync\OfflineSyncServiceProvider" --tag="migrations"
php artisan migrate

Configuration

Database Connections

Add your offline and online database connections to config/database.php. The package supports MySQL, PostgreSQL, and SQL Server:

MySQL Configuration

'connections' => [
    // Your existing connections...
    
    'mysql_offline' => [
        'driver' => 'mysql',
        'host' => env('DB_OFFLINE_HOST', '127.0.0.1'),
        'port' => env('DB_OFFLINE_PORT', '3306'),
        'database' => env('DB_OFFLINE_DATABASE', 'offline_db'),
        'username' => env('DB_OFFLINE_USERNAME', 'root'),
        'password' => env('DB_OFFLINE_PASSWORD', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
    
    'mysql_online' => [
        'driver' => 'mysql',
        'host' => env('DB_ONLINE_HOST', '127.0.0.1'),
        'port' => env('DB_ONLINE_PORT', '3306'),
        'database' => env('DB_ONLINE_DATABASE', 'forge'),
        'username' => env('DB_ONLINE_USERNAME', 'forge'),
        'password' => env('DB_ONLINE_PASSWORD', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
],

SQL Server Configuration

'connections' => [
    // Your existing connections...
    
    'sqlsrv_offline' => [
        'driver' => 'sqlsrv',
        'host' => env('DB_OFFLINE_HOST', 'localhost'),
        'port' => env('DB_OFFLINE_PORT', '1433'),
        'database' => env('DB_OFFLINE_DATABASE', 'offline_db'),
        'username' => env('DB_OFFLINE_USERNAME', 'sa'),
        'password' => env('DB_OFFLINE_PASSWORD', ''),
        'charset' => 'utf8',
        'prefix' => '',
        'prefix_indexes' => true,
        // SQL Server specific options
        'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', true),
        'encrypt' => env('DB_ENCRYPT', true),
        'multiple_active_result_sets' => false,
    ],
    
    'sqlsrv_online' => [
        'driver' => 'sqlsrv',
        'host' => env('DB_ONLINE_HOST', 'localhost'),
        'port' => env('DB_ONLINE_PORT', '1433'),
        'database' => env('DB_ONLINE_DATABASE', 'online_db'),
        'username' => env('DB_ONLINE_USERNAME', 'sa'),
        'password' => env('DB_ONLINE_PASSWORD', ''),
        'charset' => 'utf8',
        'prefix' => '',
        'prefix_indexes' => true,
        'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', true),
        'encrypt' => env('DB_ENCRYPT', true),
        'multiple_active_result_sets' => false,
    ],
],

PostgreSQL Configuration

'connections' => [
    // Your existing connections...
    
    'pgsql_offline' => [
        'driver' => 'pgsql',
        'host' => env('DB_OFFLINE_HOST', '127.0.0.1'),
        'port' => env('DB_OFFLINE_PORT', '5432'),
        'database' => env('DB_OFFLINE_DATABASE', 'offline_db'),
        'username' => env('DB_OFFLINE_USERNAME', 'postgres'),
        'password' => env('DB_OFFLINE_PASSWORD', ''),
        'charset' => 'utf8',
        'prefix' => '',
        'prefix_indexes' => true,
        'schema' => 'public',
        'sslmode' => 'prefer',
    ],
    
    'pgsql_online' => [
        'driver' => 'pgsql',
        'host' => env('DB_ONLINE_HOST', '127.0.0.1'),
        'port' => env('DB_ONLINE_PORT', '5432'),
        'database' => env('DB_ONLINE_DATABASE', 'online_db'),
        'username' => env('DB_ONLINE_USERNAME', 'postgres'),
        'password' => env('DB_ONLINE_PASSWORD', ''),
        'charset' => 'utf8',
        'prefix' => '',
        'prefix_indexes' => true,
        'schema' => 'public',
        'sslmode' => 'prefer',
    ],
],

Environment Variables

Add these variables to your .env file based on your database choice:

Mixed Database Configuration

The package supports mixing different database types for offline and online connections. This allows you to use the best database for each scenario.

Example 1: MySQL Offline → SQL Server Online

# Offline Database (MySQL)
OFFLINE_SYNC_OFFLINE_CONNECTION=mysql_offline
DB_MYSQL_OFFLINE_HOST=localhost
DB_MYSQL_OFFLINE_PORT=3306
DB_MYSQL_OFFLINE_DATABASE=offline_db
DB_MYSQL_OFFLINE_USERNAME=root
DB_MYSQL_OFFLINE_PASSWORD=password

# Online Database (SQL Server)
OFFLINE_SYNC_ONLINE_CONNECTION=sqlsrv_online
DB_SQLSRV_ONLINE_HOST=server.example.com
DB_SQLSRV_ONLINE_PORT=1433
DB_SQLSRV_ONLINE_DATABASE=online_db
DB_SQLSRV_ONLINE_USERNAME=sa
DB_SQLSRV_ONLINE_PASSWORD=password

Example 2: SQL Server Offline → MySQL Online

# Offline Database (SQL Server)
OFFLINE_SYNC_OFFLINE_CONNECTION=sqlsrv_offline
DB_SQLSRV_OFFLINE_HOST=localhost
DB_SQLSRV_OFFLINE_PORT=1433
DB_SQLSRV_OFFLINE_DATABASE=offline_db
DB_SQLSRV_OFFLINE_USERNAME=sa
DB_SQLSRV_OFFLINE_PASSWORD=password

# Online Database (MySQL)
OFFLINE_SYNC_ONLINE_CONNECTION=mysql_online
DB_MYSQL_ONLINE_HOST=cloud.mysql.com
DB_MYSQL_ONLINE_PORT=3306
DB_MYSQL_ONLINE_DATABASE=online_db
DB_MYSQL_ONLINE_USERNAME=user
DB_MYSQL_ONLINE_PASSWORD=password

For MySQL

# Offline Sync Configuration
OFFLINE_SYNC_DEFAULT_MODE=auto
OFFLINE_SYNC_OFFLINE_CONNECTION=mysql_offline
OFFLINE_SYNC_ONLINE_CONNECTION=mysql_online

# Connectivity Settings
OFFLINE_SYNC_CHECK_INTERVAL=30
OFFLINE_SYNC_TIMEOUT=5
OFFLINE_SYNC_RETRY_ATTEMPTS=3

# Sync Settings
OFFLINE_SYNC_AUTO_SYNC=true
OFFLINE_SYNC_INTERVAL=60
OFFLINE_SYNC_BATCH_SIZE=100
OFFLINE_SYNC_MAX_RETRIES=3
OFFLINE_SYNC_CONFLICT_RESOLUTION=latest_wins

# Queue Settings
OFFLINE_SYNC_QUEUE_CONNECTION=database
OFFLINE_SYNC_QUEUE_NAME=offline-sync

# Logging
OFFLINE_SYNC_LOGGING_ENABLED=true
OFFLINE_SYNC_LOGGING_LEVEL=info
OFFLINE_SYNC_LOGGING_CHANNEL=offline-sync

# Offline Database Connection (MySQL)
DB_OFFLINE_HOST=127.0.0.1
DB_OFFLINE_PORT=3306
DB_OFFLINE_DATABASE=offline_db
DB_OFFLINE_USERNAME=root
DB_OFFLINE_PASSWORD=your-offline-password

# Online Database Connection (MySQL)
DB_ONLINE_HOST=your-online-host
DB_ONLINE_PORT=3306
DB_ONLINE_DATABASE=your-online-database
DB_ONLINE_USERNAME=your-online-username
DB_ONLINE_PASSWORD=your-online-password

For SQL Server

# Offline Sync Configuration
OFFLINE_SYNC_DEFAULT_MODE=auto
OFFLINE_SYNC_OFFLINE_CONNECTION=sqlsrv_offline
OFFLINE_SYNC_ONLINE_CONNECTION=sqlsrv_online

# Connectivity Settings
OFFLINE_SYNC_CHECK_INTERVAL=30
OFFLINE_SYNC_TIMEOUT=5
OFFLINE_SYNC_RETRY_ATTEMPTS=3

# Sync Settings
OFFLINE_SYNC_AUTO_SYNC=true
OFFLINE_SYNC_INTERVAL=60
OFFLINE_SYNC_BATCH_SIZE=100
OFFLINE_SYNC_MAX_RETRIES=3
OFFLINE_SYNC_CONFLICT_RESOLUTION=latest_wins

# Queue Settings
OFFLINE_SYNC_QUEUE_CONNECTION=database
OFFLINE_SYNC_QUEUE_NAME=offline-sync

# Logging
OFFLINE_SYNC_LOGGING_ENABLED=true
OFFLINE_SYNC_LOGGING_LEVEL=info
OFFLINE_SYNC_LOGGING_CHANNEL=offline-sync

# Offline Database Connection (SQL Server)
DB_OFFLINE_HOST=localhost
DB_OFFLINE_PORT=1433
DB_OFFLINE_DATABASE=offline_db
DB_OFFLINE_USERNAME=sa
DB_OFFLINE_PASSWORD=your-offline-password
DB_TRUST_SERVER_CERTIFICATE=true
DB_ENCRYPT=true

# Online Database Connection (SQL Server)
DB_ONLINE_HOST=your-online-host
DB_ONLINE_PORT=1433
DB_ONLINE_DATABASE=your-online-database
DB_ONLINE_USERNAME=your-online-username
DB_ONLINE_PASSWORD=your-online-password

For PostgreSQL

# Offline Sync Configuration
OFFLINE_SYNC_DEFAULT_MODE=auto
OFFLINE_SYNC_OFFLINE_CONNECTION=pgsql_offline
OFFLINE_SYNC_ONLINE_CONNECTION=pgsql_online

# Connectivity Settings
OFFLINE_SYNC_CHECK_INTERVAL=30
OFFLINE_SYNC_TIMEOUT=5
OFFLINE_SYNC_RETRY_ATTEMPTS=3

# Sync Settings
OFFLINE_SYNC_AUTO_SYNC=true
OFFLINE_SYNC_INTERVAL=60
OFFLINE_SYNC_BATCH_SIZE=100
OFFLINE_SYNC_MAX_RETRIES=3
OFFLINE_SYNC_CONFLICT_RESOLUTION=latest_wins

# Queue Settings
OFFLINE_SYNC_QUEUE_CONNECTION=database
OFFLINE_SYNC_QUEUE_NAME=offline-sync

# Logging
OFFLINE_SYNC_LOGGING_ENABLED=true
OFFLINE_SYNC_LOGGING_LEVEL=info
OFFLINE_SYNC_LOGGING_CHANNEL=offline-sync

# Offline Database Connection (PostgreSQL)
DB_OFFLINE_HOST=127.0.0.1
DB_OFFLINE_PORT=5432
DB_OFFLINE_DATABASE=offline_db
DB_OFFLINE_USERNAME=postgres
DB_OFFLINE_PASSWORD=your-offline-password

# Online Database Connection (PostgreSQL)
DB_ONLINE_HOST=your-online-host
DB_ONLINE_PORT=5432
DB_ONLINE_DATABASE=your-online-database
DB_ONLINE_USERNAME=your-online-username
DB_ONLINE_PASSWORD=your-online-password

Model Configuration

Configure which models should be synchronized in config/offline-sync.php:

'models' => [
    App\Models\User::class => [
        'sync_fields' => ['name', 'email', 'updated_at'],
        'exclude_fields' => ['password', 'remember_token'],
        'sync_deletes' => true,
        'priority' => 1,
    ],
    App\Models\Post::class => [
        'sync_fields' => ['title', 'content', 'published_at', 'updated_at'],
        'exclude_fields' => [],
        'sync_deletes' => true,
        'priority' => 2,
    ],
],

Usage

Mixed Database Support

The package automatically handles data type conversions when synchronizing between different database systems (MySQL ↔ SQL Server ↔ PostgreSQL).

Automatic Conversions Include:

• Boolean Values: Converted to appropriate format for target database
• DateTime Formats: Adjusted for database-specific precision and format
• JSON Data: Handled natively where supported, converted as needed
• Binary Data: Properly encoded for target database (hex format, etc.)
• Character Encoding: UTF-8 compatibility across all systems

Supported Mixed Configurations:

• MySQL ↔ SQL Server
• MySQL ↔ PostgreSQL
• SQL Server ↔ PostgreSQL
• Any combination of the above

Making Models Syncable

Add the Syncable trait to your models:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Xslain\OfflineSync\Traits\Syncable;
use Xslain\OfflineSync\Contracts\SyncableModelInterface;

class User extends Model implements SyncableModelInterface
{
    use Syncable;

    protected $fillable = ['name', 'email', 'password'];
    
    // The trait automatically implements the required interface methods
    // You can override them if needed:
    
    public function getSyncableFields(): array
    {
        return ['name', 'email', 'updated_at'];
    }
    
    public function getExcludedFields(): array
    {
        return ['password', 'remember_token'];
    }
    
    public function getSyncPriority(): int
    {
        return 1; // Higher numbers sync first
    }
}

Manual Synchronization

Sync All Models

php artisan offline-sync:sync

Sync Specific Model

php artisan offline-sync:sync --model="App\Models\User"

Full Synchronization

# Sync everything from online to offline
php artisan offline-sync:sync --full --direction=to-offline

# Sync everything from offline to online
php artisan offline-sync:sync --full --direction=to-online

# Bidirectional full sync
php artisan offline-sync:sync --full --direction=both

Programmatic Usage

// Get sync services
$connectionManager = app('offline-sync.connection');
$syncEngine = app('offline-sync.engine');
$queueManager = app('offline-sync.queue');

// Check connection status
if ($connectionManager->isOnline()) {
    echo "Connected to online database";
} else {
    echo "Working in offline mode";
}

// Manually sync a model instance
$user = User::find(1);
$user->sync();

// Get sync status for a model
$status = $user->getSyncStatus();

// Sync all pending changes
$results = $syncEngine->syncAll();

// Check for conflicts
$conflicts = $syncEngine->checkForConflicts();

// Resolve conflicts
$resolved = $syncEngine->resolveConflicts('App\Models\User', 'latest_wins');

Status and Monitoring

Check Sync Status

php artisan offline-sync:status

Detailed Status

php artisan offline-sync:status --detailed

View Conflicts

php artisan offline-sync:status --conflicts

Queue Statistics

php artisan offline-sync:status --queue

Test Configuration

php artisan offline-sync:test-config

Conflict Resolution Strategies

The package supports several conflict resolution strategies:

1. Latest Wins (Default)

'conflict_resolution' => 'latest_wins'

The record with the most recent updated_at timestamp wins.

2. Online Wins

'conflict_resolution' => 'online_wins'

The online database record always takes precedence.

3. Offline Wins

'conflict_resolution' => 'offline_wins'

The offline database record always takes precedence.

4. Manual Resolution

'conflict_resolution' => 'manual'

Conflicts are flagged for manual resolution.

Queue Processing

The package uses Laravel's queue system for background processing. Make sure to run queue workers:

php artisan queue:work --queue=offline-sync

Events and Hooks

Model Events

The Syncable trait automatically hooks into Eloquent model events:

// Automatically queued for sync when models are created, updated, or deleted
$user = User::create(['name' => 'John', 'email' => 'john@example.com']);
$user->update(['name' => 'John Doe']);
$user->delete(); // Only if sync_deletes is true

Custom Hooks

Override trait methods in your models for custom behavior:

class User extends Model implements SyncableModelInterface
{
    use Syncable;
    
    public function afterSync(string $direction): void
    {
        // Custom logic after sync
        if ($direction === 'to-online') {
            // Handle online sync completion
        }
    }
    
    public function isReadyForSync(): bool
    {
        // Custom sync readiness check
        return $this->status === 'active';
    }
    
    public function prepareSyncData(): array
    {
        $data = parent::prepareSyncData();
        
        // Custom data preparation
        $data['sync_timestamp'] = now();
        
        return $data;
    }
}

Advanced Configuration

Performance Optimization

'performance' => [
    'use_transactions' => true,
    'chunk_size' => 1000, // Adjust based on database type
    'memory_limit' => '256M',
    'optimize_queries' => true,
],

Database-Specific Performance Tips:

SQL Server

• Use 'chunk_size' => 500 for better performance with large datasets
• Enable connection pooling in production
• Consider using read-only connections for sync operations
• Use SET NOCOUNT ON for better performance

PostgreSQL

• Use 'chunk_size' => 1000 (default works well)
• Enable shared_preload_libraries = 'pg_stat_statements' for query optimization
• Consider using connection pooling with PgBouncer

MySQL

• Use 'chunk_size' => 1000 (default works well)
• Enable query cache for read-heavy operations
• Consider using read replicas for sync operations

Security Settings

'security' => [
    'encrypt_sync_data' => true,
    'encryption_key' => env('OFFLINE_SYNC_ENCRYPTION_KEY'),
    'verify_ssl' => true,
],

Data Validation

'validation' => [
    'verify_checksums' => true,
    'validate_foreign_keys' => true,
    'skip_validation_errors' => false,
],

Troubleshooting

Common Issues

1. Connection Timeouts

# Test your connections
php artisan offline-sync:test-config

2. Queue Not Processing

# Check queue status
php artisan offline-sync:status --queue

# Start queue worker
php artisan queue:work --queue=offline-sync

3. Sync Conflicts

# View conflicts
php artisan offline-sync:status --conflicts

# Resolve automatically
php artisan offline-sync:sync --model="App\Models\User"

4. SQL Server Connection Issues

# Check if SQL Server extensions are installed
php -m | grep sqlsrv

# Test SQL Server connection
php artisan tinker
DB::connection('sqlsrv_offline')->select('SELECT 1 as test');

Common SQL Server Errors:

• "could not find driver": Install php-sqlsrv and php-pdo_sqlsrv extensions
• "SSL connection is required": Set 'encrypt' => false in your connection config for local development
• "Login failed": Check username, password, and SQL Server authentication mode
• "Certificate verify failed": Set 'trust_server_certificate' => true for self-signed certificates

5. PostgreSQL Connection Issues

# Check if PostgreSQL extensions are installed
php -m | grep pgsql

# Test PostgreSQL connection
php artisan tinker
DB::connection('pgsql_offline')->select('SELECT 1 as test');

Database-Specific Considerations

SQL Server

• Ensure SQL Server is configured to accept connections (SQL Server Configuration Manager)
• Check that TCP/IP protocol is enabled for SQL Server
• Verify firewall settings allow connections on port 1433
• For Windows Authentication, use 'username' => null, 'password' => null

PostgreSQL

• Default port is 5432
• Check pg_hba.conf for connection permissions
• Ensure PostgreSQL service is running

MySQL

• Default port is 3306
• Check that MySQL is configured to accept external connections if needed
• Verify user permissions for database access

Logging

Check the logs for detailed information:

tail -f storage/logs/laravel.log

Enable debug logging in config/offline-sync.php:

'logging' => [
    'enabled' => true,
    'level' => 'debug',
    'channel' => 'offline-sync',
],

Testing

Unit Tests

Run the package tests:

./vendor/bin/phpunit

Testing with Different Laravel Versions

The package supports multiple Laravel versions. You can test compatibility by specifying the Laravel version in your test environment:

# Test with Laravel 10.x
composer require "laravel/framework:^10.0" --dev
./vendor/bin/phpunit

# Test with Laravel 11.x  
composer require "laravel/framework:^11.0" --dev
./vendor/bin/phpunit

# Test with Laravel 12.x
composer require "laravel/framework:^12.0" --dev
./vendor/bin/phpunit

Integration Tests

Test with your actual database connections:

php artisan offline-sync:test-config
php artisan offline-sync:sync --model="App\Models\User"
php artisan offline-sync:status --detailed

Contributing

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

Continuous Integration

The package includes GitHub Actions workflows that automatically test compatibility across:

• Laravel versions: 10.x, 11.x, 12.x
• PHP versions: 8.1, 8.2, 8.3
• Database systems: MySQL, PostgreSQL, SQL Server
• Operating systems: Ubuntu, Windows

All pull requests are automatically tested to ensure compatibility.

License

This package is open-sourced software licensed under the MIT license.

Support

For support, please open an issue on GitHub or contact us at info@xslain.com.

Changelog

See CHANGELOG.md for a list of changes.

Credits

• Xslain Team
• Laravel Community
• All Contributors