PHP.GT

Database migrations

Database migrations let us keep schema changes in version control and apply them in a predictable order, so development machines and production servers are ensured to be running the same schema.

[!NOTE] In WebEngine, the same migrator is surfaced through gt migrate, so the file layout and flags on this page apply directly to a WebEngine project. The WebEngine overview is at https://www.php.gt/webengine/database/.

Storing migration files

The default migration directory is:

query/_migration/

Migration files must:

  • end with .sql or .php.
  • start with a numeric prefix.
  • use a continuous numerical sequence with no gaps.
  • keep the same contents once they have been run.

Examples:

  • 0001-create-user.sql
  • 0002-add-created-at.sql
  • 0003-index-user-email.sql

The numeric prefix must be at the start of the filename. A file like a002-second.sql is ignored rather than treated as migration 2.

What the migrator stores

The migrator keeps its state in a database table, _migration by default.

For each migration it records:

  • the migration number.
  • a hash of the file contents.
  • when it was applied.

That hash is what makes integrity checks possible. If we edit a migration after it has already run, the migrator will stop and tell us the history has changed.

Running migrations in code

use Gt\Database\Connection\Settings;
use Gt\Database\Migration\Migrator;

$settings = new Settings(
	"query",
	Settings::DRIVER_SQLITE,
	"app.sqlite"
);

$migrator = new Migrator($settings, "query/_migration");
$migrator->createMigrationTable();

$files = $migrator->getMigrationFileList();
$migrator->checkFileListOrder($files);

$current = $migrator->getMigrationCount();
$migrator->checkIntegrity($files, $current);
$migrator->performMigration($files, $current);

That flow is safe to rerun. Only pending migrations will be applied.

CLI usage

The package ships with bin/migrate, and WebEngine exposes the same behaviour through gt migrate.

php bin/migrate execute
php bin/migrate execute --force
php bin/migrate execute --reset
php bin/migrate execute --reset 3

What the flags do:

  • no flag: apply pending canonical migrations
  • --force: drop the schema and run again from migration 1
  • --reset: rerun only the latest canonical migration
  • --reset 3: rerun from migration 4 onwards

For SQLite, --force resets the database by removing the database file and rebuilding it from the migration set.

Development migrations

For feature branches, there is also a dev migration flow:

query/_migration/dev/

These files are tracked separately in the _migration_dev table, and are intended for branch-local schema changes while work is still in progress.

Use:

migrate --dev
migrate --dev-merge
  • --dev runs canonical migrations first, then branch-local dev migrations.
  • --dev-merge promotes applied dev migrations into canonical files in query/_migration/, automatically renaming to the numerical sequence.

This keeps feature branch work flexible, while still ending up with one canonical migration history in the main branch.

[!TIP] A sensible workflow is to let each developer keep a local sequence in query/_migration/dev/, then have the feature author run gt migrate --dev-merge before the work is merged.

Configuration keys

The CLI and migrators read these settings:

  • database.query_path
  • database.migration_path
  • database.migration_table
  • database.dev_migration_path
  • database.dev_migration_table
  • database.driver
  • database.schema
  • database.host
  • database.port
  • database.username
  • database.password

Example: https://github.com/PhpGt/Database/blob/master/example/05-database-migrations.php

To see these features in runnable scripts, move on to Examples.