README

Note: This is a forked version of the original Laravel Database Sync package. This version has been enhanced with PostgreSQL support and optimized for real-world SSH and containerized workflows.

A powerful Laravel package that enables seamless synchronization of data from a remote database to your local development environment.

Requirements
What's New in This Fork
Installation
Configuration
- Environment Variables
Usage
Testing
- Test Structure
- Writing Tests
Security
Support
License

Requirements

PHP ^8.2
Laravel ^10.0|^11.0|^12.0

What's New in This Fork

This fork builds on the original PostgreSQL support with improvements focused on real-world SSH and containerized workflows:

Postgres schema bootstrapping: If the local database is empty, the sync now pulls schema objects first (tables, enums, types) so data imports succeed.
Custom pg_dump binary: You can point the sync to a Postgres 17 client binary on the remote host to avoid version mismatch errors.
Sail-friendly temp file location: The default local dump path uses Laravel's storage_path() so it works inside Docker containers.
SSH port parsing for scp: Custom SSH ports (e.g. -p 25610) are translated to scp's -P flag automatically.
Stricter validation and debug output: Dump, copy, and import steps now fail loudly when files are missing or empty to prevent silent no-op syncs.

Installation

You can install the package via composer:

composer require yukazakiri/laravel-database-sync --dev

Configuration

Publish the configuration file:

php artisan vendor:publish --tag="database-sync-config"

Environment Variables

Add these variables to your .env file:

# Database Driver (mysql or postgres)
DATABASE_SYNC_DATABASE_DRIVER=postgres

DATABASE_SYNC_REMOTE_USER_AND_HOST="forge@1.1.1.1"
DATABASE_SYNC_REMOTE_DATABASE_USERNAME=forge
DATABASE_SYNC_REMOTE_DATABASE_PASSWORD=

DATABASE_SYNC_TEMPORARY_FILE_LOCATION_REMOTE=~/new_data.sql
DATABASE_SYNC_TEMPORARY_FILE_LOCATION_LOCAL=/path/to/your/app/storage/new_data.sql

# Postgres-specific options
DATABASE_SYNC_POSTGRES_PG_DUMP_BINARY=/usr/lib/postgresql/17/bin/pg_dump
DATABASE_SYNC_POSTGRES_SCHEMA_DUMP_FLAGS="--schema-only --no-owner --no-privileges"
DATABASE_SYNC_POSTGRES_CREATE_MISSING_TABLES=true

Important: When connecting to a Forge-provisioned database server, you must use the main database user that was created during the initial server provisioning. Other database users created afterward may not have the necessary privileges to execute the required database commands for synchronization.

Usage

PostgreSQL Support

This package supports PostgreSQL in addition to MySQL. To use PostgreSQL:

Set DATABASE_SYNC_DATABASE_DRIVER=postgres in your .env.
Ensure PostgreSQL client tools (psql, pg_dump) are installed on both local and remote systems.
(Optional) Customize dump flags in config/database-sync.php:

'postgres' => [
    'dump_action_flags' => '--no-owner --no-privileges --data-only --column-inserts --on-conflict-do-nothing',
],

Migration from MySQL

To migrate an existing MySQL setup to PostgreSQL:

Update your database connections to PostgreSQL.
Set DATABASE_SYNC_DATABASE_DRIVER=postgres in your environment.
Ensure PostgreSQL client tools are installed.
Run the sync command as usual.

Basic Synchronization

To sync your remote database to local:

php artisan db-sync

By default, the package uses batch file transfers for optimal performance, transferring all table data in a single file to minimize network overhead.

Advanced Options

The sync command supports several options:

php artisan db-sync [options]

Available options:

--date: Sync records from a specific date
--suite: Use a predefined suite of tables
--table: Sync a specific table
--tenant: Specify tenant for multi-tenant applications
--skip-landlord: Skip landlord database in multi-tenant setup
--full-sync: Sync the full table without a date constraint
--status: View the sync history and status for all tables
--individual-transfers: Use individual file transfers for each table (legacy behavior)

Per-Table Sync Tracking

The package now tracks the last sync date for each individual table, preventing data loss when syncing single tables. This means:

Each table maintains its own sync history
When syncing a specific table with --table, only that table's sync date is considered
The package automatically falls back to the global sync date for backward compatibility
You can view the sync status of all tables using the --status option
Sync timestamps are captured at the start of the process to prevent missing data created during sync

Viewing Sync Status

To see the last sync date for all tables:

php artisan db-sync --status

This will display a table showing each table name and its last sync date, helping you track which tables have been synced recently and which might need attention.

How It Works

Sync Start Timestamp: The sync timestamp is captured when the sync process begins
Individual Table Tracking: Each table's sync date is stored separately in the cache file
Atomic Updates: Cache is only updated when the entire sync process completes successfully
Automatic Fallback: If no table-specific date exists, the global sync date is used
Backward Compatibility: Existing installations continue to work without any changes
Debug Information: When running with -vvv (debug mode), you'll see which sync date is being used for each table
Error Recovery: If sync fails, the cache remains unchanged with the previous sync dates

File Transfer Optimization

The package uses batch file transfers by default to minimize network overhead:

Batch Mode (Default): All tables are dumped to a single file and transferred once
Individual Mode: Each table is transferred separately (legacy behavior)

Configuration

Control the transfer mode in config/database-sync.php:

'file_transfer_mode' => 'batch', // or 'individual'

Or via environment variable:

DATABASE_SYNC_FILE_TRANSFER_MODE=batch

Benefits of Batch Transfer

Reduced network overhead: Single file transfer instead of multiple
Faster sync times: Especially noticeable with many tables
Better compression: SSH compression works more efficiently on larger files
Lower resource usage: Fewer process spawns and file operations

When Individual Transfers Are Used

Single table sync (--table=tablename)
Explicit override (--individual-transfers)
Config set to individual mode

Table Configuration

You can exclude specific tables from synchronization in the config/database-sync.php file:

'tables' => [
    'ignore' => [
        'action_events',
        'jobs',
        'telescope_entries',
        'password_resets',
    ],
],

Timeout Configuration

For large databases, you may need to adjust the process timeout to prevent operations from timing out:

// Set timeout in seconds (default: 300 seconds / 5 minutes)
'process_timeout' => 600, // 10 minutes

// Or set to null to disable timeout entirely for very large databases
'process_timeout' => null,

You can also set this via environment variable:

DATABASE_SYNC_PROCESS_TIMEOUT=600

This timeout applies to:

MySQL dump operations (mysqldump)
MySQL import operations (mysql)
File transfer operations (scp)

Synchronization Suites

Define custom synchronization suites in the configuration file to group tables for specific sync tasks:

'suites' => [
    'orders' => [
        'orders',
        'order_items',
    ],
],

Then use the suite option:

php artisan db-sync --suite=orders

Multi-Tenant Support

The package supports multi-tenant architectures. Enable it in the configuration:

'multi_tenant' => [
    'landlord' => [
        'database_name' => 'yukazakiri_landord',
        'tables' => [
            'ignore' => [
                'action_events',
            ],
        ],
    ],
    'tenants' => [
        'database_names' => [
            'yukazakiri_nl' => [
                'tables' => [
                    'ignore' => [
                        'users',
                    ],
                ],
            ],
            'yukazakiri_dev',
            'yukazakiri_io',
        ],
        'tables' => [
            'ignore' => [
                'logs',
            ],
        ],
    ],
],

Configure tenant-specific settings in your configuration file and use the --tenant option to sync specific tenant databases:

php artisan db-sync --tenant="yukazakiri_nl" --skip-landlord
php artisan db-sync --tenant="yukazakiri_nl" --skip-landlord --suite=orders

Testing

This package uses Pest PHP for testing. To run the tests:

composer test

To run tests with coverage report:

composer test-coverage

Test Structure

The test suite includes:

Unit Tests: Testing individual components
- Config class tests
- DatabaseSync class tests
- Other utility classes
Feature Tests: Testing the package functionality
- Command execution tests
- Multi-tenant functionality
- Suite configurations
- Table filtering

Writing Tests

To add new tests, create a new test file in either the tests/Unit or tests/Feature directory. The package uses Pest's expressive syntax:

test('your test description', function () {
    // Your test code
    expect($result)->toBe($expected);
});

Security

Never commit sensitive database credentials to version control
Always use environment variables for sensitive information
Ensure proper access controls on both remote and local databases

Support

For support, please email stef@yukazakiri.dev

License

The MIT License (MIT). Please see License File for more information.

yukazakiri / laravel-database-sync

Maintainers

Details

README

Table of Contents

Requirements

What's New in This Fork

Installation

Configuration

Environment Variables

Usage

PostgreSQL Support

Migration from MySQL

Basic Synchronization

Advanced Options

Per-Table Sync Tracking

Viewing Sync Status

How It Works

File Transfer Optimization

Configuration

Benefits of Batch Transfer

When Individual Transfers Are Used

Table Configuration

Timeout Configuration

Synchronization Suites

Multi-Tenant Support

Testing

Test Structure

Writing Tests

Security

Support

License