Proof of Concept: Database Migrator

Консольная программа для управления миграциями.

Command

• init — инициализация проекта: создание папки для миграций и конфигурационного файла.
• up — применение всех ожидающих миграций до самой свежей.
• down — откат последней примененной миграции (или нескольких).
• fixture — применение всех фикстур.
• create — создание файла миграции (удобно при разработке).
• verify — последовательный запуск up и сразу down для последней версии миграций (удобно при разработке).
• redo — последовательный запуск down и сразу up для последней миграции (удобно при разработке).

setup

Например, для базы данных с именем main под управлением сервера postgres:

mkdir -p ./migration/pgsql/{main,main-fixture} 

Описываем конфигурацию:

$migrator = new Migrator(
    collection: new MigrationCollection(
        new Migration(
            path: __DIR__ . '/migration/postgres/main',
            driver: new PdoDriver(
                dsn: 'pgsql:host=postgres;port=5432;dbname=main',
                username: 'postgres',
                password: 'postgres',
            )
        )
    ),
);

migration

Команды миграции описываются на языке SQL, например:

-- @up
CREATE TABLE IF NOT EXISTS public.entity (
    id serial NOT NULL,
    parent_id integer NOT NULL,
    created_at timestamp(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT entity_pkey PRIMARY KEY (id)
);
CREATE INDEX IF NOT EXISTS "I_entity_parent_id" ON public.entity USING btree (parent_id);

-- @down
DROP INDEX IF EXISTS I_entity_parent_id;
DROP TABLE IF EXISTS public.entity;

Управляющие команды:

• @up
• @down
• @skip

Если команды не указаны, то весь код будет вычитан как секция up.
Если нужно скипнуть файл целиком, то можно добавить в название постфикс skip, например 202501011025_name_skip.sql

CLI application

use DI\Container;
use kuaukutsu\poc\migration\internal\connection\PDO\Driver;
use kuaukutsu\poc\migration\example\presentation\DownCommand;
use kuaukutsu\poc\migration\example\presentation\CreateCommand;
use kuaukutsu\poc\migration\example\presentation\FixtureCommand;
use kuaukutsu\poc\migration\example\presentation\VerifyCommand;
use kuaukutsu\poc\migration\example\presentation\InitCommand;
use kuaukutsu\poc\migration\example\presentation\RedoCommand;
use kuaukutsu\poc\migration\example\presentation\UpCommand;
use kuaukutsu\poc\migration\tools\PrettyConsoleOutput;
use kuaukutsu\poc\migration\Migration;
use kuaukutsu\poc\migration\MigrationCollection;
use kuaukutsu\poc\migration\Migrator;
use kuaukutsu\poc\migration\MigratorInterface;
use Symfony\Component\Console\Application;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\CommandLoader\ContainerCommandLoader;

require dirname(__DIR__) . '/vendor/autoload.php';

$container = new Container(
    [
        Migrator::class => factory(
            fn(): Migrator => new Migrator(
                collection: new MigrationCollection(
                    new Migration(
                        path: __DIR__ . '/migration/sqlite/db',
                        driver: new Driver(
                            dsn: 'sqlite:' . __DIR__ . '/data/sqlite/db.sqlite3',
                        )
                    )
                ),
                eventSubscribers: [
                    new PrettyConsoleOutput(),
                ],
            )
        ),
    ]
);

$console = new Application();
$console->setCommandLoader(
    new ContainerCommandLoader(
        $container,
        [
            'migrate:init' => InitCommand::class,
            'migrate:up' => UpCommand::class,
            'migrate:down' => DownCommand::class,
            'migrate:redo' => RedoCommand::class,
            'migrate:verify' => VerifyCommand::class,
            'migrate:fixture' => FixtureCommand::class,
            'migrate:create' => CreateCommand::class,
        ],
    )
);

try {
    exit($console->run());
} catch (Exception $e) {
    exit(Command::FAILURE);
}

Example

make app

/example $ php cli.php migrate:init
[sqlite/db] initialization: setup.sql done

/example $ php cli.php migrate:up
[sqlite/db] up: 202501011024_entity_create.sql done
[sqlite/db] up: 202501021024_account_create.sql done
[sqlite/db] up: 202501021025_account_email.sql done

/example $ php cli.php migrate:down
[sqlite/db] down: 202501021025_account_email.sql done
[sqlite/db] down: 202501021024_account_create.sql done
[sqlite/db] down: 202501011024_entity_create.sql done

With exactly all

If any migration fails, the entire batch is rolled back, leaving the database unchanged.

/example $ php cli.php migrate:up --exactly-all
[sqlite/db] up: 202501011024_entity_create.sql done
[sqlite/db] up: 202501021024_account_create.sql done
[sqlite/db] up: 202501021025_account_email.sql done

With repeatable

/example $ php cli.php migrate:up --with-repeatable
[sqlite/db] up: 202501011024_entity_create.sql done
[sqlite/db] up: 202501021024_account_create.sql done
[sqlite/db] up: 202501021025_account_email.sql done
[sqlite/db] repeatable: 202501011024_entity_correction.sql done
[sqlite/db] repeatable: 202501011024_entity_correction_2.sql done

Down with latest version

/example $ php cli.php migrate:up --limit=1
[sqlite/db] up: 202501011024_entity_create.sql, vers: 1772723563954 done

/example $ php cli.php migrate:up --limit=2
[sqlite/db] up: 202501021024_account_create.sql, vers: 1772723566084 done
[sqlite/db] up: 202501021025_account_email.sql, vers: 1772723566084 done

/example $ php cli.php migrate:down --latest-version
[sqlite/db] down: 202501021025_account_email.sql, vers: 1772723566084 done
[sqlite/db] down: 202501021024_account_create.sql, vers: 1772723566084 done

Redo with latest version

/example $ php cli.php migrate:up
[sqlite/db] up: 202501021024_account_create.sql, vers: 1772723718828 done
[sqlite/db] up: 202501021025_account_email.sql, vers: 1772723718828 done

/example $ php cli.php migrate:redo --latest-version
[sqlite/db] down: 202501021025_account_email.sql, vers: 1772723718828 done
[sqlite/db] down: 202501021024_account_create.sql, vers: 1772723718828 done
[sqlite/db] up: 202501021024_account_create.sql, vers: 1772723727397 done
[sqlite/db] up: 202501021025_account_email.sql, vers: 1772723727397 done

Verify

/example $ php cli.php migrate:create test --db=sqlite/db                                                                                   
/example $ php cli.php migrate:create test2 --db=sqlite/db        
/example $ php cli.php migrate:create test3 --db=sqlite/db

/example $ php cli.php migrate:verify
[sqlite/db] up: 202603070850_test.sql, vers: 177287432696 done
[sqlite/db] up: 202603070850_test2.sql, vers: 177287432696 done
[sqlite/db] up: 202603070850_test3.sql, vers: 177287432696 done
[sqlite/db] down: 202603070850_test3.sql, vers: 177287432696 done
[sqlite/db] down: 202603070850_test2.sql, vers: 177287432696 done
[sqlite/db] down: 202603070850_test.sql, vers: 177287432696 done

With limit

/example $ php cli.php migrate:verify --limit=1
[sqlite/db] up: 202603070850_test.sql, vers: 177287441498 done
[sqlite/db] down: 202603070850_test.sql, vers: 177287441498 done

error

/example $ php cli.php migrate:verify
[sqlite/db] up: 202603070850_test.sql, vers: 177287479980 done
[sqlite/db] up: 202603070850_test2.sql error
SQLSTATE[HY000]: General error: 1 incomplete input
-- SQL CODE
INSERT INTO ededede


[sqlite/db] down: 202603070850_test.sql, vers: 177287479980 done
202603070850_test2.sql: SQLSTATE[HY000]: General error: 1 incomplete input

Static analysis

To run static analysis:

make check

Unit testing

The package is tested with PHPUnit. To run tests:

make tests