Database migrations

PHP.GT/Database supports Database migrations to keep your schema tracked in version control.

[!INFO] WebEngine applications can have their migrations applied automatically with the gt migrate command. Read about database use in WebEngine.

Migrations are run as numbered SQL files and let the migrator track progress and integrity.

Migration location and file naming

Files must start with a sequential number. Keep note that even on simple applications, over time there will be more and more migrations created, and to ensure that file sequences are always kept in alphabetical order, an appropriate number of zeros should be prefixed on the files. A good recommended first migration file starts with 0001, allowing for 9,999 migrations to be produced before they need rotating.

Default location:

query/_migration/

Example files:

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

Rules to keep in mind:

• migration files must use the .sql extension.
• numbering must be continuous, with no gaps.
• file hashes are stored and checked for integrity - once a migration has been run, the migration file contents can not change without an exception being thrown.

Programmatic migration flow

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

$settings = new Settings(__DIR__ . "/query", Settings::DRIVER_SQLITE, __DIR__ . "/app.sqlite");
$migrator = new Migrator($settings, __DIR__ . "/query/_migration");

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

Now we can rerun the migration process safely and only apply new files.

CLI migration command

This package includes bin/migrate. This is the same command that's exposed by the gt migrate command in WebEngine.

php bin/migrate execute
php bin/migrate execute --force

--force recreates the schema, dropping all data and existing tables, starting the migrations from 1.

Resetting migrations

Introduced in v1.7

If you need to rerun migrations without dropping the whole schema, use --reset.

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

• --reset (with no number) resets to the latest migration number and reruns only the newest migration file.
• --reset 3 resets to migration 3 and reruns from migration 4 onward.
• This is useful when developing or repairing migration history while keeping the existing schema in place.

A future release will automatically handle migrations written for development branches, allowing developers to store their own functionality in migration files named dev001-first-feature.sql, dev002-second-feature.sql, etc. and automatically sequence the files to the end of the main list when ready.

Configuration

The commands read project configuration from database.* keys, including:

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

Example code: example/05-database-migrations.php

---

Check out the examples to see some real-world usage.