README

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

Fork the repository
Create your feature branch (git checkout -b feature/amazing-feature)
Commit your changes (git commit -m 'Add amazing feature')
Push to the branch (git push origin feature/amazing-feature)
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

xslain / laravel-offline-sync

Maintainers

Details

README

Features

Requirements

Laravel Version Compatibility

Installation

1. Install the Package

2. Install Database Extensions (if needed)

For SQL Server Support

For PostgreSQL Support

3. Publish Configuration

4. Publish and Run Migrations

Configuration

Database Connections

MySQL Configuration

SQL Server Configuration

PostgreSQL Configuration

Environment Variables

Mixed Database Configuration

For MySQL

For SQL Server

For PostgreSQL

Model Configuration

Usage

Mixed Database Support

Making Models Syncable

Manual Synchronization

Sync All Models

Sync Specific Model

Full Synchronization

Programmatic Usage

Status and Monitoring

Check Sync Status

Detailed Status

View Conflicts

Queue Statistics

Test Configuration

Conflict Resolution Strategies

1. Latest Wins (Default)

2. Online Wins

3. Offline Wins

4. Manual Resolution

Queue Processing

Events and Hooks

Model Events

Custom Hooks

Advanced Configuration

Performance Optimization

SQL Server

PostgreSQL

MySQL

Security Settings

Data Validation

Troubleshooting

Common Issues

1. Connection Timeouts

2. Queue Not Processing

3. Sync Conflicts

4. SQL Server Connection Issues

5. PostgreSQL Connection Issues

Database-Specific Considerations

SQL Server

PostgreSQL

MySQL

Logging

Testing

Unit Tests

Testing with Different Laravel Versions

Integration Tests

Contributing

Continuous Integration

License

Support

Changelog

Credits