Data Layerv2.0

Migration

Entity-schema diff engine, SQL migration runner, and dialect-aware DDL generation for the MonkeysLegion ecosystem. Compares PHP 8.4 entity attributes against a live database and produces safe, dependency-ordered SQL.

Features

✅ Attribute-Driven Schema: Reads #[Entity], #[Field], #[Id], #[Index], #[Timestamps], #[SoftDeletes], #[AuditTrail], #[Versioned], #[ManyToOne], #[OneToOne], #[ManyToMany] — zero manual mapping
✅ Structural Diff Engine: Computes column adds/drops/modifications, index changes, and FK changes between desired and current state
✅ Multi-Dialect: MySQL, PostgreSQL, SQLite — each with proper quoting, type mapping, and DDL syntax
✅ FK Dependency Ordering: Topological sort (Kahn's algorithm) ensures referenced tables are created before referencing tables
✅ Migration Runner: Batch tracking, rollback, refresh, fresh, status, and dry-run (pretend)
✅ File Generation: Generates timestamped PHP migration classes with up() and down() methods
✅ Schema Introspection: Reads live database metadata into the same TableDefinition structures used by the entity builder
✅ SQL Injection Prevention: IdentifierValidator blocks dangerous patterns, enforces length limits, and warns on reserved words
✅ Protected Tables: migrations and ml_migrations are never dropped automatically
✅ PHP 8.4 Native: Property hooks, readonly classes, match expressions, named arguments

Requirements

PHP 8.4+
monkeyscloud/monkeyslegion-core ^2.0
monkeyscloud/monkeyslegion-di ^2.0
monkeyscloud/monkeyslegion-entity ^2.0
monkeyscloud/monkeyslegion-database ^2.0

Installation

composer require monkeyscloud/monkeyslegion-migration:^2.0

Architecture

The package is decomposed into six independent subsystems:

MigrationGenerator (facade)
├── Schema
│   ├── EntitySchemaBuilder   → Reads entity attributes → TableDefinition
│   ├── SchemaIntrospector    → Reads live DB → TableDefinition
│   ├── TableDefinition       → Immutable table structure
│   ├── ColumnDefinition      → Immutable column structure
│   ├── IndexDefinition       → Immutable index structure
│   └── ForeignKeyDefinition  → Immutable FK structure
├── Diff
│   ├── SchemaDiffer          → Compares two states → DiffPlan
│   ├── DiffPlan              → Structured diff (creates, alters, drops)
│   ├── TableDiff             → Per-table diff (columns, indexes, FKs)
│   └── ColumnChange          → Before/after for modified columns
├── Renderer
│   └── SqlRenderer           → DiffPlan → dialect-specific SQL
├── Dialect
│   ├── SqlDialect (interface)
│   ├── MySqlDialect
│   ├── PostgreSqlDialect
│   └── SqliteDialect
├── Runner
│   ├── MigrationRunner       → Executes migration files
│   ├── BatchTracker          → Tracks executed migrations in ml_migrations
│   ├── RunResult             → Result value object
│   └── MigrationStatus       → Status of each migration file
└── Security
    └── IdentifierValidator   → SQL identifier validation

Quick Start

Schema Update (CLI)

The most common workflow uses the schema:update CLI command provided by monkeyslegion-cli:

# Preview what SQL will be generated (dry-run)
php ml schema:update --dump-sql

# Apply changes directly to the database
php ml schema:update --force

Programmatic Usage

use MonkeysLegion\Migration\MigrationGenerator;
use MonkeysLegion\Database\Contracts\ConnectionInterface;

$generator = new MigrationGenerator($connection);

// Compute the diff between entities and the live database
$sql = $generator->diff(
    entities: [App\Entity\User::class, App\Entity\Post::class],
    schema:   $currentSchema, // or pass null to auto-introspect
);

echo $sql;
// CREATE TABLE `users` (
//   `id` INT NOT NULL AUTO_INCREMENT,
//   `email` VARCHAR(255) NOT NULL,
//   `created_at` DATETIME NULL,
//   PRIMARY KEY (`id`)
// ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Entity Attributes

The EntitySchemaBuilder reads all MonkeysLegion v2 entity attributes:

Basic Entity

use MonkeysLegion\Entity\Attributes\{Entity, Id, Field, Timestamps, SoftDeletes};

#[Entity(table: 'users')]
#[Timestamps]
#[SoftDeletes]
class User
{
    #[Id]
    #[Field(type: 'int', autoIncrement: true)]
    public int $id;

    #[Field(type: 'string', length: 255, unique: true)]
    public string $email;

    #[Field(type: 'string', length: 100)]
    public string $name;

    #[Field(type: 'boolean', default: true)]
    public bool $active;
}

This generates:

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT TRUE,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE UNIQUE INDEX `uq_users_email` ON `users` (`email`);

Relationships

use MonkeysLegion\Entity\Attributes\{ManyToOne, OneToOne, ManyToMany, JoinTable};

class Post
{
    #[Id]
    #[Field(type: 'int', autoIncrement: true)]
    public int $id;

    // ManyToOne → creates `author_id` FK column
    #[ManyToOne(targetEntity: User::class)]
    public User $author;

    // ManyToMany → creates join table via #[JoinTable]
    #[ManyToMany(targetEntity: Tag::class)]
    #[JoinTable(name: 'post_tags', joinColumn: 'post_id', inverseColumn: 'tag_id')]
    public array $tags;
}

The builder automatically:

Creates FK columns with the correct type (matching the referenced PK)
Sets ON DELETE behavior (SET NULL for nullable, RESTRICT for required)
Generates join tables with composite primary keys for ManyToMany

Supported Attributes

Attribute	Level	Effect
`#[Entity(table: '...')]`	Class	Sets the table name (default: snake_case of class name)
`#[Id]`	Property	Marks as primary key
`#[Field(...)]`	Property	Defines column type, length, nullable, default, unique, etc.
`#[Column(name: '...')]`	Property	Overrides the DB column name
`#[Index(columns: [...], unique: bool)]`	Class/Property	Creates indexes (composite when on class)
`#[Timestamps]`	Class	Adds `created_at` and `updated_at` columns
`#[SoftDeletes]`	Class	Adds `deleted_at` column
`#[AuditTrail]`	Class	Adds `created_by`, `updated_by`, `created_ip`, `updated_ip`
`#[Versioned]`	Property	Marks as optimistic lock column
`#[Uuid]`	Property	Uses UUID type for the primary key
`#[Virtual]`	Property	Skips — no database column
`#[ManyToOne]`	Property	Creates FK column + constraint
`#[OneToOne]`	Property	Creates FK column + constraint (owning side only)
`#[ManyToMany]` + `#[JoinTable]`	Property	Creates a join table

Schema Diffing

Structured Diff (v2 API)

$plan = $generator->computeDiff(
    entities: [User::class, Post::class],
    schema:   null, // auto-introspect from live DB
);

if ($plan->isEmpty()) {
    echo "Schema is up to date.\n";
} else {
    echo $plan->toHumanReadable();
    // CREATE TABLE posts (4 columns, 1 indexes, 1 FKs)
    // ALTER TABLE users: +1 columns, ~1 modified

    echo "Total changes: {$plan->changeCount()}\n";
}

DiffPlan Structure

The DiffPlan contains three lists:

Property	Type	Description
`createTables`	`list<TableDefinition>`	Full table definitions to create
`alterTables`	`list<TableDiff>`	Per-table diffs (columns, indexes, FKs)
`dropTables`	`list<string>`	Table names to drop

Each TableDiff contains:

Property	Type	Description
`addedColumns`	`list<ColumnDefinition>`	New columns
`modifiedColumns`	`list<ColumnChange>`	Changed columns (from → to)
`droppedColumns`	`list<string>`	Removed column names
`addedIndexes`	`list<IndexDefinition>`	New indexes
`droppedIndexes`	`list<string>`	Removed index names
`addedForeignKeys`	`list<ForeignKeyDefinition>`	New FK constraints
`droppedForeignKeys`	`list<string>`	Removed FK names

Column Change Detection

The differ detects changes in:

Type — e.g. VARCHAR(100) → VARCHAR(255)
Nullability — NOT NULL → NULL
Default value — DEFAULT 'active' → DEFAULT 'pending'
Enum values — ENUM('a','b') → ENUM('a','b','c')
Length/Precision — DECIMAL(10,2) → DECIMAL(12,4)

SQL Rendering

The SqlRenderer converts a DiffPlan into dialect-specific DDL:

use MonkeysLegion\Migration\Renderer\SqlRenderer;
use MonkeysLegion\Migration\Dialect\MySqlDialect;

$renderer = new SqlRenderer(new MySqlDialect());

// Forward SQL (up)
$sql = $renderer->render($plan);

// Reverse SQL (down — for rollback)
$rollbackSql = $renderer->renderReverse($plan);

// Individual statements (no FK-check wrapper)
$statements = $renderer->renderStatements($plan);

The rendered SQL automatically includes:

FK check disable/enable wrappers (MySQL: SET FOREIGN_KEY_CHECKS=0/1)
Proper identifier quoting per dialect (` for MySQL, " for PostgreSQL/SQLite)
Engine suffixes (ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 for MySQL)

Dialect Support

Each dialect implements the SqlDialect interface:

Feature	MySQL	PostgreSQL	SQLite
Quoting	`name`	`"name"`	`"name"`
Auto-increment	`AUTO_INCREMENT`	`SERIAL` / `BIGSERIAL`	`AUTOINCREMENT`
UUID type	`CHAR(36)`	`UUID`	`TEXT`
Boolean	`TINYINT(1)`	`BOOLEAN`	`INTEGER`
JSON	`JSON`	`JSONB`	`TEXT`
Transactional DDL	❌	✅	✅
FK inline (CREATE TABLE)	❌ (ALTER)	❌ (ALTER)	✅ (inline)
DROP INDEX	`DROP INDEX idx ON tbl`	`DROP INDEX idx`	`DROP INDEX idx`
ALTER COLUMN	`MODIFY COLUMN`	`ALTER COLUMN ... TYPE`	❌ (rebuild)

Type Mapping

All dialects support these logical types:

Logical Type	MySQL	PostgreSQL	SQLite
`int`	`INT`	`INTEGER`	`INTEGER`
`bigint`	`BIGINT`	`BIGINT`	`INTEGER`
`smallint`	`SMALLINT`	`SMALLINT`	`INTEGER`
`string`	`VARCHAR(n)`	`VARCHAR(n)`	`TEXT`
`text`	`TEXT`	`TEXT`	`TEXT`
`boolean`	`TINYINT(1)`	`BOOLEAN`	`INTEGER`
`datetime`	`DATETIME`	`TIMESTAMP`	`TEXT`
`date`	`DATE`	`DATE`	`TEXT`
`decimal`	`DECIMAL(p,s)`	`NUMERIC(p,s)`	`REAL`
`float`	`FLOAT`	`REAL`	`REAL`
`uuid`	`CHAR(36)`	`UUID`	`TEXT`
`json`	`JSON`	`JSONB`	`TEXT`
`enum`	`ENUM(...)`	`VARCHAR(255)`	`TEXT`
`ipaddress`	`VARCHAR(45)`	`INET`	`TEXT`

Migration Runner

Running Migrations

use MonkeysLegion\Migration\Runner\{MigrationRunner, BatchTracker};

$tracker = new BatchTracker($connection);
$runner  = new MigrationRunner($connection, $tracker);

// Run all pending migrations
$result = $runner->run('/path/to/var/migrations');

echo "Executed: " . count($result->executed) . "\n";
echo "Duration: {$result->durationMs}ms\n";
echo "Success: " . ($result->success ? 'yes' : 'no') . "\n";

if ($result->error) {
    echo "Error: {$result->error}\n";
}

Rollback

// Rollback the last batch
$result = $runner->rollback();

// Rollback the last 3 individual migrations
$result = $runner->rollback(steps: 3);

// Rollback a specific batch
$result = $runner->rollback(batch: 5);

Reset, Refresh, Fresh

// Reset — rollback ALL migrations
$result = $runner->reset();

// Refresh — rollback all then re-run all
$result = $runner->refresh('/path/to/var/migrations');

// Fresh — drop ALL tables then re-run all
$result = $runner->fresh('/path/to/var/migrations');

Migration Status

$statuses = $runner->status('/path/to/var/migrations');

foreach ($statuses as $status) {
    printf(
        " %s  %-40s  batch: %d\n",
        $status->ran ? '✅' : '⏳',
        $status->name,
        $status->batch ?? 0,
    );
}

Dry Run (Pretend)

$statements = $runner->pretend('/path/to/var/migrations');

foreach ($statements as $stmt) {
    echo "$stmt\n";
}

Transactional DDL

The runner automatically wraps migration execution in transactions for dialects that support transactional DDL:

Dialect	Transactional DDL	Behavior
MySQL	❌	Each DDL statement auto-commits
PostgreSQL	✅	Full migration wrapped in transaction
SQLite	✅	Full migration wrapped in transaction

Migration File Generation

$path = $generator->generate(
    entities: [User::class, Post::class],
    schema:   $currentSchema,
    name:     'add_posts_table',
);

echo "Generated: {$path}\n";
// var/migrations/M20260426120000_Add_posts_table.php

Generated file:

<?php
declare(strict_types=1);

namespace App\Migration;

use MonkeysLegion\Database\Contracts\ConnectionInterface;

final class M20260426120000_Add_posts_table
{
    public function up(ConnectionInterface $db): void
    {
        $db->pdo()->exec(<<<'SQL'
        CREATE TABLE `posts` (
          `id` INT NOT NULL AUTO_INCREMENT,
          `title` VARCHAR(255) NOT NULL,
          `author_id` INT NOT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        SQL);
    }

    public function down(ConnectionInterface $db): void
    {
        $db->pdo()->exec(<<<'SQL'
        DROP TABLE IF EXISTS `posts`
        SQL);
    }
}

Schema Backup

$backupPath = $generator->backup();
echo "Backup saved to: {$backupPath}\n";
// var/backups/schema_20260426120000.sql

Batch Tracking

The BatchTracker uses the ml_migrations table to track which migrations have been executed:

-- MySQL
CREATE TABLE IF NOT EXISTS ml_migrations (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    migration   VARCHAR(255) NOT NULL,
    batch       INT NOT NULL,
    executed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The table is automatically created on first use.

Security

Identifier Validation

All SQL identifiers pass through IdentifierValidator before being used in DDL:

use MonkeysLegion\Migration\Security\IdentifierValidator;

// Validates: length (≤64), pattern (alphanumeric + underscore), no injection
IdentifierValidator::validate('users');          // ✅
IdentifierValidator::validate('user; DROP --');  // ❌ InvalidArgumentException

// Check reserved words
IdentifierValidator::isReservedWord('SELECT');   // true
IdentifierValidator::isReservedWord('users');    // false

// Validate with warnings (reserved words are valid but flagged)
$warnings = IdentifierValidator::validateWithWarnings('order');
// ['Identifier "order" is a SQL reserved word. It will be quoted but may cause confusion.']

Blocked Patterns

The validator rejects identifiers containing:

Pattern	Risk
`--`	SQL comment injection
`/` / `/`	Block comment injection
`;`	Statement termination
`'` / `"`	String escape
`\`	Escape character
`\x00`	Null byte injection

v2 Component Access

The MigrationGenerator facade exposes its internal components for advanced usage:

$generator = new MigrationGenerator($connection);

$builder      = $generator->getEntityBuilder();   // EntitySchemaBuilder
$introspector = $generator->getIntrospector();     // SchemaIntrospector
$differ       = $generator->getDiffer();           // SchemaDiffer
$renderer     = $generator->getRenderer();         // SqlRenderer
$dialect      = $generator->getDialect();          // SqlDialect (MySQL|PG|SQLite)

Testing

composer test              # Run PHPUnit
composer test:unit         # Unit tests only
composer test:integration  # Integration tests only
composer test:coverage     # Generate HTML coverage report
composer phpstan           # Run PHPStan level 8
composer check             # CS + PHPStan + tests