README

A modern schema-first migration engine for PHP.

PHP Schema Engine lets you define your database structure using a clean PHP DSL, compare it against the current database schema, generate SQL automatically, and apply changes through a lightweight CLI.

Current version: 0.3.0

Features

• Schema-first database definition
• Fluent PHP DSL
• MySQL/MariaDB support
• Automatic schema introspection
• Table and column diffing
• Partial index diffing
• SQL generation
• Migration execution
• Migration history
• Model generation
• Dry-run mode
• Destructive operation protection
• Database reset support
• Snapshot generation
• Foreign key support
• Debug trace mode

Installation

composer require eril/schema-engine

Initialization

Generate the default configuration file:

php bin/migrate --init

This creates:

schema-engine.php
database/schema.php
database/bootstrap.sql
database/snapshots/

Configuration

Generated config example:

<?php

return [

    // Schema file path
    'schema' => 'database/schema.php',

    'bootstrap' => 'bootstrap.php',

    // Database connection configuration
    'database' => [
        'driver' => 'mysql',
        'host' => $_ENV['DB_HOST'] ?? '127.0.0.1',
        'port' => $_ENV['DB_PORT'] ?? 3306,
        'database' => $_ENV['DB_NAME'] ?? 'app',
        'username' => $_ENV['DB_USER'] ?? 'root',
        'password' => $_ENV['DB_PASS'] ?? '',
    ],

    // Model generator setup
    'generator' => [
        'models' => [
            'enabled' => false,
            'namespace' => 'App\\Models',
            'path' => '/app/Models',
            'extends' => null,
        ],
    ],
];

Optional bootstrap file:

bootstrap.php

Useful for:

• loading .env
• bootstrapping frameworks
• loading helpers
• custom runtime setup

Basic Usage

Create a schema file:

<?php

use SchemaEngine\DSL\Schema;
use SchemaEngine\DSL\Table;

return function (Schema $schema) {

    $schema->table('users', function (Table $t) {

        $t->id();

        $t->string('name');

        $t->string('email')
            ->unique();

        $t->timestamps();
    });

};

Run a dry-run first:

php bin/migrate --dry-run

Apply the migration:

php bin/migrate --yes

Defining Tables

$schema->table('users', function ($t) {

    $t->id();

    $t->string('name');

    $t->string('email')
        ->unique();

    $t->int('age')
        ->default(0);

    $t->timestamps();
});

Column Types

$t->id();

$t->string('name');

$t->char('code', 2);

$t->text('body');

$t->longText('content');

$t->int('age');

$t->bigInt('views');

$t->float('rating');

$t->double('score');

$t->decimal('price', 10, 2);

$t->boolean('active');

$t->datetime('published_at');

$t->timestamp('created_at');

$t->json('meta');

$t->uuid('uuid');

Column Modifiers

$t->string('email')
    ->unique();

$t->string('slug')
    ->index();

$t->string('name')
    ->nullable();

$t->int('age')
    ->default(0);

$t->timestamp('created_at')
    ->defaultCurrentTimestamp();

$t->timestamp('updated_at')
    ->defaultRaw('CURRENT_TIMESTAMP');

Shortcuts

$t->id();

$t->timestamps();

$t->createdAt();

$t->updatedAt();

$t->softDeletes();

$t->rememberToken();

Indexes

Single-column indexes:

$t->string('email')
    ->unique();

$t->string('slug')
    ->index();

Composite indexes:

$t->unique(['email', 'tenant_id']);

$t->index(['first_name', 'last_name']);

Indexes are automatically introspected and partially diffed.

Current V1 behavior:

• missing indexes are automatically added
• removed indexes are automatically dropped
• changed indexes are ignored intentionally with warnings

Example warning:

Index 'email_unique' on table 'users' differs from desired schema and was ignored.

Foreign Keys

Simple inferred relation:

$t->foreign('user_id');

Explicit references:

$t->foreign('user_id')
    ->constrained() // or references('users')
    ->cascadeOnDelete();

Explicit references:

$t->foreign('user_id')
    ->constrained() // or references('users')
    ->cascadeOnDelete();

ou:

$t->foreign('author_id')
    ->references('users');

Custom referenced column:

$t->uuid('author_id')
    ->references('users', 'uuid');

Available relation actions:

->cascadeOnDelete()

->cascadeOnUpdate()

->restrictOnDelete()

->restrictOnUpdate()

->nullOnDelete()

->nullOnUpdate()

In V1, foreign keys are generated when creating new tables only. Foreign key changes after table creation are not automatically migrated yet.

Model Generation

Generate models from your schema:

php bin/migrate --generate-models

Example generated model:

<?php

namespace App\Models;

class User
{
    protected string $table = 'users';
}

Generator configuration:

'generator' => [
    'models' => [
        'enabled' => true,
        'namespace' => 'App\\Models',
        'path' => '/app/Models',
        'extends' => 'App\\Core\\Model',
    ],
],

CLI

Command	Description
--init	Initialize a new project
--yes	Don't ask for confirmation
--dry-run	Preview pending changes
--force	Execute dangerous SQL like delete
--status	Show migration history
--sql	Export SQL for pending schema changes
--schema-sql	Export SQL for the entire schema
--rollback	Rollback the latest batch
--fresh	Drop all tables
--clear-history	Clear histries
--generate-models	Generate model classes

Dry-run:

php bin/migrate --dry-run

Apply migrations without confirmation:

php bin/migrate --yes

Allow destructive operations:

php bin/migrate --force

Show migration history:

php bin/migrate --status

Reset database:

php bin/migrate --fresh --force --yes

Reset database and clear history:

php bin/migrate --fresh --force --yes --clear-history

Generate models:

php bin/migrate --generate-models

Show stack trace on exceptions:

php bin/migrate --trace

Migration History

Executed operations are stored in:

schema_migrations

This table is managed internally by the engine and ignored during schema diffing.

Exporting SQL

PHP Schema Engine can generate SQL without executing migrations.

Export migration SQL (diff)

Generate the SQL required to migrate the current database schema:

php bin/migrate --sql

Example:

ALTER TABLE users
ADD COLUMN phone VARCHAR(20);

CREATE TABLE posts (...);

This command uses schema introspection and the diff engine.

Export full schema SQL

Generate SQL for the entire schema definition:

php bin/migrate --schema-sql

Example:

CREATE TABLE users (...);

CREATE TABLE posts (...);

This command ignores the current database and generates SQL directly from database/schema.php.

Save SQL to a file

bin/migrate --schema-sql > install.sql

or

bin/migrate --sql > migration.sql

$ notice not to use php because > stdout is not a tty

This is useful for:

• Manual deployments
• Database reviews
• Backup scripts
• Installation packages
• CI/CD pipelines

Safety

By default, destructive operations are blocked.

This includes:

• dropping tables
• dropping columns
• dropping indexes

To allow destructive operations:

php bin/migrate --force

Debugging

Enable stack traces during execution:

php bin/migrate --trace

Example output:

[ERROR] Table users already exists
File: .../Migrator.php:62

Stack trace:

#0 .../Migrator.php:62 run()
#1 .../MigrateCommand.php:88 printPlannedSql()
#2 .../bin/migrate:24 run()

Current Limitations

PHP Schema Engine 0.3.0 is intentionally conservative.

Current V1 limitations:

• MySQL/MariaDB only
• Index diffing is partial and intentionally conservative
• Existing indexes are not automatically modified
• Foreign key changes after table creation are not automatically migrated
• Rename detection is disabled by default
• Table recreation is not implemented yet
• No PostgreSQL grammar yet
• No SQLite grammar yet
• No ORM/query builder layer yet

Philosophy

PHP Schema Engine follows a schema-first approach.

The schema file is the source of truth.

Schema DSL
    ↓
Schema Metadata
    ↓
Database Introspection
    ↓
Diff Engine
    ↓
SQL Generation
    ↓
Migration Execution

The DSL is designed to stay expressive, while the internal architecture keeps metadata, diffing, SQL generation, and execution separated.

Roadmap

V0.1

• Schema DSL
• MySQL introspection
• Column diffing
• Partial index diffing
• SQL generator
• CLI
• Migration history
• Model generation
• Foreign keys on create

V0.2

• migrate:fresh
• migrate:reset
• better CLI output
• schema snapshots

V0.3

• foreign key diffing
• rollback support

V0.4

• advanced index diffing
• table recreation mode
• explicit rename operations

V1.0

• stable public API
• stronger type system
• advanced MySQL grammar
• multi-driver foundation

License

MIT