Download the PHP package artemyurov/laravel-incremental-db-sync without Composer

Laravel Incremental DB Sync

Laravel package for incremental PostgreSQL database synchronization from remote servers via SSH tunnel.

Supports incremental sync, full refresh (DROP + CREATE), automatic backups, foreign key dependency resolution, and self-referencing table handling.

Requirements

• PHP 8.2+
• Laravel 10, 11, 12, or 13
• PostgreSQL
• pg_dump and psql CLI tools available on the local machine
• artemyurov/laravel-autossh-tunnel for SSH tunnel management

Installation

Publish the configuration file:

Configuration

The configuration file config/db-sync.php defines sync connections:

Each connection defines:

You can define multiple connections (e.g. production, staging) and switch between them using the --sync-connection option.

Commands

db-sync:pull — Incremental Synchronization

Analyzes differences between remote and local databases, then applies only the changes (DELETE + UPSERT).

Features:

• Compares record counts and updated_at timestamps to detect changes
• Automatically rebuilds tables with changed structure
• Reconciles indexes and constraints to match remote (see Index/constraint reconciliation below)
• Resolves foreign key dependencies for correct sync order
• CASCADE RECHECK: re-syncs child tables after parent deletions
• Syncs views and resets sequences

Index/constraint reconciliation. pull's structure detection compares columns only, so index/constraint changes (including renames) would otherwise be invisible. Each run performs a dedicated reconciliation pass: it compares indexes and constraints of every table (by name) between remote and local and brings local in line — constraint-aware (PRIMARY KEY / UNIQUE / EXCLUSION via ALTER TABLE, plain indexes via CREATE/DROP INDEX), applying all drops before all creates so renamed objects don't collide. Excluded tables are skipped. Removed objects are reported afterwards. Note: pull never drops local-only tables (it preserves work in progress), but it does drop indexes/constraints that exist locally and not on remote — including a locally-added index from a not-yet-pushed migration. DDL is owned by migrations; this pass only keeps index/constraint metadata in sync.

Options:

db-sync:clone — Full Clone

Drops all tables and recreates them from the remote server. Use this for a clean start.

Local-only tables. clone mirrors the remote schema, so tables that exist locally but not on remote (e.g. leftovers from an interrupted run, or a renamed table whose old index name lingers) must be removed first — otherwise their index and constraint names collide with recreated tables (SQLSTATE[42P07] ... already exists). Such local-only tables are listed in the plan (with row counts) alongside the tables to refresh, and are dropped as part of the single confirmation prompt (no separate question). Pass --keep-local-tables to keep them (only safe when their names don't clash with remote objects).

Options:

db-sync:restore — Restore from Backup

Restore local database from a previously created backup.

Options:

How It Works

Incremental Sync (db-sync:pull)

1. Opens SSH tunnel to the remote server
2. Creates a local backup
3. Analyzes each table: compares row counts, max IDs, and updated_at timestamps
4. Detects tables with changed structure (columns added/removed/modified)
5. Rebuilds changed tables (DROP + CREATE + import data)
6. Reconciles indexes/constraints to match remote (constraint-aware; all drops then all creates)
7. For unchanged structure: runs DELETE phase (removes records missing from remote), then UPSERT phase (inserts new / updates modified records)
8. CASCADE RECHECK: if parent table had deletions, re-checks child tables
9. Syncs views and resets auto-increment sequences

Full Clone (db-sync:clone)

1. Opens SSH tunnel to the remote server
2. Shows the refresh plan, including local-only tables (not on remote) to be dropped, and asks for a single confirmation
3. Creates a local backup
4. Drops local-only tables (skipped with --keep-local-tables)
5. Dumps schema from remote using pg_dump
6. Drops all local tables and recreates from dump
7. Copies data from remote with keyset pagination (by primary key) + bulk INSERT; a failed batch falls back to row-by-row inserts. Tables without a primary key are skipped (they cannot be paginated or de-duplicated reliably) and reported at the end.
8. Resets sequences

Foreign Key Handling

The package builds a dependency graph from foreign key constraints and uses topological sorting to determine the correct order for:

• Inserts: parent tables first (parents-first order)
• Deletes: child tables first (children-first order)

Self-referencing tables (e.g. categories with parent_id) are handled via recursive CTEs.

Architecture

The package uses an adapter pattern for database operations:

Key services:

Docker & DDEV

This package requires SSH tunnels to work. For Docker and DDEV setup (SSH agent forwarding, autossh installation), see the Docker & DDEV section in laravel-autossh-tunnel documentation.

Add postgresql-client to your Dockerfile for schema operations (pg_dump, psql).

Important: The pg_dump version must be >= the PostgreSQL server version. Debian base images ship with older versions (e.g. Bookworm includes PG 15), so you may need the official PostgreSQL APT repository:

Adding Database Drivers

To support a new database (e.g. MySQL), implement DatabaseAdapterInterface and register it in BaseDbSyncCommand::resolveAdapter().

License

MIT