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.