README

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 package exposes three PDO-bound facades, each over its service:

Tereta\Migration\Schema - dumping and restoring table structure as XML, listing tables;
Tereta\Migration\Data - dumping and restoring table rows as XML;
Tereta\Migration\Migration - applying migrations.

SQL generation is delegated to tereta/dbal, so the same drivers from that package are supported (see the tereta/dbal package README.md)

Requirements

PHP 8.2+
ext-pdo
ext-dom
tereta/dbal

Installation

Install via Composer:

composer require tereta/migration

Getting started

Each facade takes a PDO instance in its constructor, so the connection is not passed on every call:

use Tereta\Migration\Schema;
use Tereta\Migration\Migration;
use Tereta\Migration\Data;

$schema    = new Schema($pdo);    // structure: dump / restore / list
$migration = new Migration($pdo); // migrations
$data      = new Data($pdo);      // rows: dump / restore

Working with schemas (Tereta\Migration\Schema)

Schema is about the structure of tables, not the data inside them. It does three things: list the tables, dump a table structure (columns, keys, indexes) into XML, and recreate the table from that XML. Handy when you need to move the database structure elsewhere or save it to a file.

Usually it is enough to pass only the connection — new Schema($pdo). The other constructor parameters are needed only if you want to swap the internal implementation (DI); by default they are created automatically.

public __construct(
        private PDO $pdo,                   // the database connection
        ?DbalSchema $schema = null,         // the tereta/dbal schema engine (its own by default)
        ?SchemaInterface $structure = null  // the service that builds and reads XML
    )

A usage example of the functions:

$schema = new Schema($pdo);

// list of tables (with true — ordered by foreign-key relations)
$tables  = $schema->list();
$ordered = $schema->list(true);

// dump a table structure into a DOMDocument and bring the table to that structure
$document = $schema->dump('book');
$schema->restore($document);

Functions

$schema->list($sort) — the list of table names; with $sort = true it is ordered by foreign-key relations (parent tables come before dependent ones).
$schema->dump($table) — dumps a table structure (columns, indexes, foreign keys) into a DOMDocument.
$schema->restore($domDocument) - brings the table to the structure from an XML document (the result of dump()): if the table does not exist it is created, otherwise it is altered (ALTER) — columns and indexes are reconciled to the XML (including dropping the ones no longer present). Returns $this, so calls can be chained.
$schema->restoreFiles($xmlFiles)- restores several tables from an array of XML files; tables are applied in foreign-key dependency order. Returns $this.

Working with data (Tereta\Migration\Data)

Data is about the data (rows) of a table, not its structure. It can dump a table's rows into an XML file and insert them back. Handy when you need to move a table's contents into another database or save them to a file.

Dump and restore work in a streaming fashion (XMLWriter / XMLReader), without loading the whole table into memory - so they are fine for large tables too.

Restore runs inside a single transaction: if something fails midway, the rows already inserted are rolled back - the table will not be left half-filled. The table must already exist (create its structure via Schema).

Usually a PDO connection is enough — new Data($pdo). The second constructor parameter is for swapping the implementation via DI (?DataInterface); by default its own service is created.

// $customData implements Tereta\Migration\Interfaces\Data
new Data($pdo, $customData);

A usage example of the functions:

$data = new Data($pdo);

// dump a table's rows into an XML file
$data->dump('widget', '/tmp/widget.xml');

// restore the rows from a file (the table must already exist)
$data->restore('/tmp/widget.xml');

// restore several files at once — in foreign-key dependency order
$data->restoreList([
    '/tmp/author.xml',
    '/tmp/book.xml',
]);

Functions

$data->dump($table, $path) - dumps the rows of table $table into the XML file $path (streaming). Returns $this.
$data->restore($file) - inserts the rows from the XML file $file into the table (it must already exist); everything in a single transaction — on failure what was already inserted is rolled back. Returns $this.
$data->restoreList($files) - restores several files (an array of paths or a single path); files are applied in foreign-key dependency order. Returns $this.

Working with migrations (Tereta\Migration\Migration)

Migration applies migrations from files - .sql and .php - one after another. Each file is applied only once: what has already been applied is recorded in a tracking table (migrations by default), and a file is identified by its name. So a repeated migrate() call is safe - already applied files are skipped. Each file runs inside its own transaction.

MySQL / MariaDB. do not support transactions for DDL statements (CREATE TABLE, ALTER TABLE, DROP TABLE, …). Every such statement commits immediately and on its own, so it cannot be rolled back.

Files are applied in the order of the numeric prefix before the first _ (001_, 002_, …), regardless of the order in which you passed the array.

$migration = new Migration($pdo);

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

A .php migration returns a function that receives the connection (and optionally the tereta/dbal builder):

<?php
use Tereta\Dbal\Builder;

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

Usually new Migration($pdo) is enough. The second constructor parameter is for swapping the implementation via DI (?MigrationInterface); by default its own service is created.

// $customMigration implements Tereta\Migration\Interfaces\Migration
new Migration($pdo, $customMigration);

A usage example of the functions:

$migration = new Migration($pdo);

// a single file
$migration->migrate(__DIR__ . '/migrations/001_create_widget.sql');

// a custom tracking table instead of 'migrations'
$migration->migrate(__DIR__ . '/migrations/002_seed_widget.php', 'my_migrations');

// migrate() returns $this — calls can be chained
$migration
    ->migrate(__DIR__ . '/migrations/003_index.sql')
    ->migrate(__DIR__ . '/migrations/004_data.php');

tereta / migration

Maintainers

Package info

Statistics

Security

README

Introduction

Requirements

Installation

Getting started

Working with schemas (Tereta\Migration\Schema)

Functions

Working with data (Tereta\Migration\Data)

Functions

Working with migrations (Tereta\Migration\Migration)