Download the PHP package mb4it/laravel-dbtodb-migration without Composer

mb4it/laravel-dbtodb-migration

English | Русский

Laravel 12 package: an Artisan command that copies rows from one configured database connection to one or more target tables on another connection. Runs are driven by named migrations in config/dbtodb_mapping.php, with chunked reads, optional keyset pagination, per-target column maps and transforms, filters, upsert vs insert, strict validation, JSON reports, and optional profiling logs.

Requirements

• PHP ^8.2
• Laravel ^12.0

Installation

Define the database connections referenced by your migrations in config/database.php.

Supported target drivers

Target-table metadata resolution, required-column validation, and automatic primary-key detection are supported for these Laravel target connection drivers:

• sqlite
• pgsql
• mysql
• mariadb
• sqlsrv

Table prefixes configured on the target connection are honored when reading target metadata.

Quick start

Use this path when you only need to copy a few columns from one source table into one target table. The command uses migrations.default when --migration is omitted, so a simple run does not need --source or --target once the migration defines them.

1. Install and publish the package config:

2. Add the source and target Laravel database connections in config/database.php (for example legacy_mysql and pgsql_app).

3. Edit config/dbtodb_mapping.php and replace the published example with a default migration:

4. Run a safe validation pass first. --dry-run reads data, applies filters/transforms, validates target columns in strict mode, and writes only the JSON report:

5. If the report looks correct, run the migration for real:

6. For production-sized data, prefer adding keyset_column and an explicit chunk size in the full table syntax below.

Documentation coverage and safety notes

This README documents the public configuration surface for the current migration-centric format: installation, supported drivers, quick start, named migrations, ordered steps, CLI options, runtime/memory settings, automatic target-type transforms, profile logging, sequence synchronization, filters, column transforms, fan-out/fan-in routing, and edge-case behavior. Before running against production data, review the target schema, run --dry-run, keep strict enabled unless you intentionally want looser validation, and store the generated JSON report for audit/debugging.

The package no longer accepts the older top-level tables / columns / filters config shape; use migrations.<name> for every run.

Complete complex config example

The example below shows a production-oriented config/dbtodb_mapping.php with global runtime defaults, auto transforms, ordered steps, source SQL filters, target routing filters, transforms, deduplication, mixed write operations, and sequence synchronization. It assumes that the Laravel connections legacy_mysql and pgsql_app already exist in config/database.php, and that target tables/columns match the mapped payloads.

Named migrations

Add as many migrations as you need under the top-level migrations node. Select one with --migration=name.

--source and --target are still available as overrides for local/debug runs, but normal migrations should define source and target in config.

Full table syntax

Use the short syntax for simple column maps. Switch to the full target definition when you need source/target filters, transforms, upsert keys, operation, deduplication, row-error handling, or source runtime settings. Supported target operations are upsert, insert, and truncate_insert (truncate_insert clears each target table once per run before the first write).

Ordered steps

A migration can contain ordered steps. Omit --step to run all steps in order, or pass --step=name to run one step.

Command options

Runtime and memory

Global defaults live in runtime.defaults; a migration may override them with migrations.{name}.runtime.defaults, and a source table may override chunk, transaction_mode, and keyset_column in its source definition.

A source table can opt in to keyset pagination by setting keyset_column (e.g. 'id'). Requirements: the column must be present in the source SELECT (the resolver adds it automatically when you use a column map), it must be sortable, and values should be unique and monotonically increasing — otherwise rows can be skipped or duplicated across chunks. Without keyset_column, the reader falls back to offset pagination.

Auto transforms

The package coerces values to match common target column shapes after your transforms run. Configure under auto_transforms:

Auto transforms run after explicit column transforms and use resolved target-table metadata. bool casting accepts 1, true, 'true', '1', 'yes', 'on' (and their false counterparts). Empty strings become null only when empty_string_to_null is enabled and the target column is nullable. Invalid JSON strings are kept by default; set json_invalid to null or fail for stricter behavior.

Profile logging and sequence sync

• --profile writes per-pipeline / per-chunk timings, memory snapshots, and start/end events to the channel named in profile_logging. Define the channel in config/logging.php (e.g. a daily file).
• sync_serial_sequences realigns auto-increment / serial / identity counters on the target connection after a successful run. Drivers supported: pgsql (setval), mysql/mariadb (ALTER TABLE … AUTO_INCREMENT), sqlite (sqlite_sequence), sqlsrv (DBCC CHECKIDENT). The sync only fires when no pipelines failed and the run was not a --dry-run.

Filters

Source filters support: =, !=, >, >=, <, <=, in, not_in, like, not_like, null, not_null, between, not_between, exists_in, where_column, date, year, and month. Filters may be written as a simple associative map, a list of rule arrays, nested and / or groups, or a PHP callable that receives the source Illuminate\Database\Query\Builder.

Source and target filters run at different stages:

1. Source filters are applied to the SQL query before rows are read from the source database. They reduce the shared input set for the whole source-table pipeline.
2. Target filters are evaluated in PHP for each target, after a row has passed source filters and before target column mapping/transforms. They do not query the target table and do not see transformed target values; they inspect the original source row that was read. Use target filters for fan-out/routing, for example: write every active user to users, but only source rows with is_admin = 1 to admins.

Target filters use the same DSL where it can be evaluated against the PHP source row. exists_in is SQL-only and is rejected for target filters with a clear validation error. Builder callables are source-only and should not be used for target filters.

Complex source filter example:

Builder callback source filter example (runtime PHP config only; do not use closures if you rely on Laravel config:cache):

Equivalent target/PHP filters can use nested groups and row-level operators. In a target definition, these rules filter the rows already selected by source.filters for that one target only:

Column transforms

Transforms are keyed by target column in the target definition.

Supported string rules: trim, null_if_empty, zero_date_to_null, lower, upper, and slug.

Supported array rules:

• default: use value when the current value is null.
• static: always replace the current value with value.
• map / enum: map values with map (or values) and optional default.
• replace: plain search / replace string replacement.
• regex_replace: pattern / replace regular-expression replacement.
• substr: start (or offset) and optional length.
• lower, upper, slug: case and slug normalization; slug accepts optional separator.
• date_format: output format, with optional input from format.
• cast: type (or to) can be int, float, string, bool, json, or array.
• concat: combine items with optional separator; items may be '$value', ['column' => 'source_col'], or ['value' => 'literal'].
• coalesce: return the first non-null item from items, with optional default.
• from_columns: build a value from multiple source columns using columns plus either separator or a template such as '{last_name}, {first_name}'.
• lookup: resolve the current value (or from_column) through a source/target database table using connection, table, key, and value directly on the rule or inside a nested source / target array; lookups are cached per transform engine instance.
• invoke: call a [class, method] pair.

Closures and invoke callables receive the current value, full source row, source column, target column, and target table: (mixed $value, array $sourceRow, ?string $sourceColumn, ?string $targetColumn, ?string $targetTable) => mixed. Two-argument closures (fn ($value, $row) => …) remain compatible.

lookup caches results per DbToDbTransformEngine instance for the lifetime of one command run. The cache is keyed by (connection, table, key column, value column, lookup value), so high-cardinality lookups grow memory linearly with distinct values. For very large fan-outs, prefer denormalising on the source side or use invoke with your own bounded cache.

Routing patterns

Fan-out (1 source → N targets)

Each target reads the same source row but applies its own filters/transforms/operation. When a source maps to more than one target, every target must declare a non-empty columns map.

Fan-in (N sources → 1 target) with static columns

Two source tables write into the same target table. Use a static transform to mark the origin per pipeline.

Because transforms only fire for columns produced by the column map, the recipe above wires any source column into the target column you want to fill statically. The static transform discards the mapped value and writes the literal.

Behavior reference

These semantics are not always obvious; lock them in before writing migrations against production data:

• Missing source column. When a column listed in columns is absent from a chunk row, that target column is silently skipped for that row. In strict mode, a required (NOT NULL, no default) target column with no value still raises an error.
• Target deduplication. Set deduplicate => ['keys' => [...], 'strategy' => 'first'|'last'] on a target to collapse mapped payload rows by target columns before writing. This is useful for insert / truncate_insert targets with unique indexes; last is the default strategy. If keys is omitted, the target upsert_keys are used when available.
• Row-level write errors. Set on_row_error => 'skip_row' (or write_error_mode => 'skip_row') to keep the fast batch write path, then fall back to row-by-row writes only if a batch fails. Failed rows are counted as skipped and unique error messages are added to the target report. The default is fail.
• Upsert deduplication. Within a single chunk, rows that share the same upsert keys are deduplicated last-wins before the SQL UPSERT is issued — important when the same key appears multiple times in the source.
• truncate_insert. The target table is truncated once per command run, just before the first write to it. Subsequent chunks (or other source pipelines pointing to the same target) only insert.
• Mixed operations across pipelines. Multiple sources can fan into one target with different operations (e.g. one truncate_insert, another insert). The truncate happens once.
• atomic transaction mode. Every target in the pipeline must use the same connection. The executor refuses to start the pipeline otherwise. Use batch for cross-connection routing.
• dry-run. Reads the source, applies filters/transforms, validates strictness, but never opens a write transaction or fires sync_serial_sequences.
• continue-on-error. Failures in one pipeline are reported in the JSON report and the table view; remaining pipelines still run.
• Strict mode. When strict is true (default), every target column referenced by columns must exist on the target table, and required columns must end up in the payload after transforms.
• Auto-increment sync. Runs only after a successful write phase. Skipped automatically for unsupported drivers.
• PostgreSQL placeholder limit. Insert/upsert batches that would exceed 65535 placeholders are split automatically based on the column count of the target table.

Developing this package

License

MIT. See LICENSE.