README

🌐 English | Русский | Українська

Introduction

Tereta/Migration is a facade over tereta/dbal for working with table schemas. It can:

• get the list of database tables, including ordered by foreign-key relations;
• dump a table structure (columns, indexes, foreign keys) into an XML document;
• restore a table from such an XML document;
• apply migrations from .sql and .php files.

The Tereta\Migration\Database facade combines the services:

• Tereta\Migration\Services\Schema - dumping and restoring the structure as XML;
• Tereta\Migration\Services\Migration - applying migrations.

Since SQL generation is delegated to tereta/dbal, the same drivers are supported:

• SQLite
• PostgreSQL
• MySQL

Requirements

• PHP 8.2+
• ext-pdo
• ext-xml
• tereta/dbal

Installation

Install via Composer:

composer require tereta/migration

Getting started

The constructor accepts a PDO instance:

use Tereta\Migration\Database;

$schema = new Database($pdo);

The facade supports dependency injection (DI): all services (DbalSchema, Schema, Migration) can be passed into the constructor.

use Tereta\Migration\Database;
use Tereta\Migration\Services\Schema as SchemaService;
use Tereta\Migration\Services\Migration;
use Tereta\Dbal\Schema as DbalSchema;

$schema = new Database(
    $pdo,
    new DbalSchema(),
    new SchemaService(),
    new Migration()
);

Usage

Table list - list()

The list() method returns an array of database table names.

$tables = $schema->list();
// ['author', 'book', 'customer', 'orders']

If you pass true, the tables are ordered by foreign-key relations (FOREIGN KEY)

$tables = $schema->list(true);
// author and customer first, then book, then orders

• ->list($sortByRelations = false) - the list of tables; with true it orders them by foreign-key relations.

Structure dump - dump()

The dump() method dumps a table structure into a DOMDocument. The document contains columns, indexes and foreign keys.

$document = $schema->dump('book');
echo $document->saveXML();

Document structure:

<schema table="book">
    <columns>
        <column name="id" type="INTEGER" nullable="false" primary="true" generated="false" autoincrement="true"/>
        <column name="author_id" type="INTEGER" nullable="true" primary="false" generated="false" autoincrement="false"/>
    </columns>
    <indexes>
        <index name="author_id_index" unique="false">
            <column>author_id</column>
        </index>
    </indexes>
    <foreignKeys>
        <foreignKey foreignTable="author" onUpdate="NO ACTION" onDelete="NO ACTION">
            <column>author_id</column>
            <foreignColumn>id</foreignColumn>
        </foreignKey>
    </foreignKeys>
</schema>

• ->dump($table) - returns a DOMDocument with the table structure.

Structure restore - restore()

The restore() method accepts a DOMDocument produced by dump() and creates the table from scratch. A table can be moved between different databases: the creation SQL is built through tereta/dbal for the target driver, so a structure dumped from SQLite can be restored, for example, into PostgreSQL.

$document = $source->dump('book');

$target->restore($document);

• ->restore($document) - recreates the table from the XML document; supports a fluent interface.

Restore limitations. Some constructs cannot be expressed by the CREATE builder, so they are silently skipped on restore:

• generated columns;
• composite (multi-column) non-unique indexes and composite foreign keys;
• referential actions other than CASCADE (RESTRICT / SET NULL are lost).

Migrations - migrate()

The migrate() method applies migration files once each. What has already been applied is recorded in a tracking table (migrations by default). A migration is identified by its file name (basename): on every call the tracking table is created if it does not exist yet, the set of applied names is loaded, and only the missing files are executed - in the order they were passed - after which they are marked as applied.

$schema->migrate([
    __DIR__ . '/migrations/001_create_widget.sql',
    __DIR__ . '/migrations/002_seed_widget.php',
]);

You can also pass a single file as a string:

$schema->migrate(__DIR__ . '/migrations/001_create_widget.sql');

Two file formats are supported, chosen by extension:

*.sql - the content is executed as is through PDO::exec():

CREATE TABLE widget (id INTEGER PRIMARY KEY, name TEXT)

*.php - the file is included; a .php file declares a Closure, which is called with PDO and the tereta/dbal builder Tereta\Dbal\Builder as arguments:

<?php

declare(strict_types=1);

use Tereta\Dbal\Builder;

return function (PDO $pdo, Builder $builder): void {
    $builder->insert('widget')->value('name', 'one')->execute();
};

• ->migrate($files, $table = 'migrations') - applies migrations, tracking the applied ones in the $table table; returns $this.

Known limitations

• Restore is incomplete for complex constructs. Generated columns, composite non-unique indexes and composite foreign keys, as well as referential actions other than CASCADE, are skipped on restore() (see the restore() section).
• Migrations run without a shared transaction. Each file is executed and recorded in the tracking table sequentially, without wrapping in a transaction: DDL in MySQL triggers an auto-commit anyway and would break the transaction, so a single transaction per migration is unsafe across all drivers.