README

Versioned schema migrations for the QuestDB time-series database, built on 22h/questdb-client.

Migrations are PHP classes with up() and down(), executed in the order of their version.
Executed versions are stored in a QuestDB table (append-only, so the history is kept).
Migrate to a version, roll back a number of steps or to a version, show the status, dry runs.
Waits until QuestDB applied the DDL of WAL tables and fails when QuestDB suspended a table.
Optional lock against concurrent deployments, optional dependency injection into migrations.

Requires PHP 8.4 and QuestDB 9.3 or newer.

Installation

composer require 22h/questdb-migration

Write a Migration

Put the migrations into a directory, every file contains the class of the same name. The class name is the version, versions are sorted as strings, so use a timestamp: Version20250101120000.

<?php

declare(strict_types=1);

namespace App\QuestMigration;

use TwentyTwo\QuestDB\Client;
use TwentyTwo\QuestDBMigration\MigrationInterface;

final class Version20250101120000 implements MigrationInterface
{
    public function getDescription(): string
    {
        return 'Create the sensor_data table';
    }

    public function up(Client $client): void
    {
        $client->execute()->execute(
            'CREATE TABLE IF NOT EXISTS sensor_data (ts TIMESTAMP, sensor_id SYMBOL, reading DOUBLE)
             TIMESTAMP(ts) PARTITION BY DAY WAL'
        );
    }

    public function down(Client $client): void
    {
        $client->execute()->execute('DROP TABLE sensor_data');
    }
}

A migration that cannot be reverted throws IrreversibleMigrationException in down().

QuestDB has no transactions for DDL. When the second statement of a migration fails, the first one stays applied. The failed migration is neither recorded nor reverted automatically (reverting could drop a table that existed before), so write migrations that can run again after the cause is fixed: CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS, one statement per migration where possible.

Run Migrations

use TwentyTwo\QuestDB\Client;
use TwentyTwo\QuestDBMigration\DirectoryMigrationLoader;
use TwentyTwo\QuestDBMigration\MigrationManager;
use TwentyTwo\QuestDBMigration\QuestDBMigrationRepository;

$client = new Client('http://localhost:9000');

$manager = new MigrationManager(
    new DirectoryMigrationLoader(['App\\QuestMigration' => __DIR__ . '/migrations']),
    new QuestDBMigrationRepository($client), // table "migration_versions", the name can be changed
    $client,
);
$manager->setLogger($logger); // optional, any PSR-3 logger

$manager->migrate();                         // all pending migrations
$manager->migrate('Version20250101120000');  // up to and including this version
$manager->rollback();                        // the highest executed version
$manager->rollback(3);                       // the three highest executed versions
$manager->rollbackTo('Version20250101120000'); // everything above, the version stays executed

Every method returns the versions it executed or reverted. The classes are loaded by the Composer autoloader, so the namespace has to be autoloadable. The loader throws instead of skipping anything it cannot load: a missing directory, a file without its class, a class without MigrationInterface or a version that exists in two directories.

Dry Run and Status

$plan = $manager->planMigrate();     // also planRollback() and planRollbackTo()
foreach ($plan->migrations as $migration) {
    echo $migration->version, ' ', $migration->direction->value, PHP_EOL;
}
$manager->execute($plan);

$manager->isUpToDate();              // e.g. for a health check, reading never creates the version table

foreach ($manager->status() as $status) {
    printf(
        "%s %s %s\n",
        $status->version,
        $status->isExecuted() ? 'executed ' . $status->executed?->executedAt->format('c') : 'pending',
        $status->description,
    );
}

status() also lists executed versions whose class was deleted (isAvailable() is false), they cannot be rolled back.

Errors

Every exception implements TwentyTwo\QuestDBMigration\Exception\ExceptionInterface. MigrationFailedException carries the version and direction of the failed migration and the original error as previous exception.

WAL Tables

QuestDB applies DDL and writes to WAL tables asynchronously, and a statement that fails while it is applied suspends the table instead of failing the query. After every migration the manager waits until the tables it changed are applied and fails when a table got suspended. Resume it with ALTER TABLE ... RESUME WAL after fixing the cause.

Concurrent Deployments

QuestDB has no locks. If two processes can migrate at the same time (several containers, overlapping deployments), pass a lock. Steps another process did while this one waited are skipped.

use Symfony\Component\Lock\LockFactory;
use TwentyTwo\QuestDBMigration\SymfonyMigrationLock;

$manager = new MigrationManager($loader, $repository, $client, lock: new SymfonyMigrationLock(
    new LockFactory($store)->createLock('questdb-migration'),
));

Requires symfony/lock and a store all processes share. Implement MigrationLockInterface for anything else.

Services in Migrations

Migrations are created with new $class(). To inject services, take them from a PSR-11 container (requires psr/container, the service id is the class name) or implement MigrationFactoryInterface:

use TwentyTwo\QuestDBMigration\ContainerMigrationFactory;

$manager = new MigrationManager($loader, $repository, $client, new ContainerMigrationFactory($container));

Upgrading

See CHANGELOG and UPGRADE-0.3.

Development

composer check              # Mago lint + format check, PHPStan, unit tests
composer cs-fix             # format with Mago
composer test-integration   # needs QUESTDB_URL, see below

The integration tests run the migrations of tests/Fixtures/migrations against a real QuestDB. They create and drop tables prefixed with questdb_migration_test_, so only point QUESTDB_URL to a throwaway instance:

docker run -d --rm --name questdb-migration-test -e QDB_DEV_MODE_ENABLED=true -p 29000:9000 questdb/questdb:9.3.3
QUESTDB_URL=http://localhost:29000 composer test-integration

Dev mode allows ALTER TABLE ... SUSPEND WAL, without it the test for suspended tables is skipped. GitLab CI runs the suite against QuestDB 9.3.3, 9.4.3 and 10.0.1 with basic auth (QUESTDB_USER, QUESTDB_PASSWORD), plus latest as a non-blocking early warning.

License

MIT, see LICENSE.

22h / questdb-migration

Package info

Security

Statistics