Database migrations

Database migrations let us keep schema changes in version control and apply them in a predictable sequence.

Migration directory

By convention, migration files live in:

query/_migration/

The current migrator reads .sql files from that directory.

File naming rules

Migration filenames must:

• end with .sql
• begin with a numeric prefix
• stay in ascending numerical order without duplicates

Examples:

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

The migrator extracts the number from the start of the filename. If a file does not follow that format, it raises MigrationFileNameFormatException.

What the migrator records

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

For each applied migration it records:

• the migration number
• an MD5 hash of the file contents
• when the migration was applied

That hash is how integrity checks work. If a migration has already been recorded and the file contents later change, the migrator stops with MigrationIntegrityException.

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);
$migrator->checkIntegrity($files, $migrator->getMigrationCount());
$migrator->performMigration($files, $migrator->getMigrationCount());

That flow is safe to rerun. Once a migration number has been recorded, only later files are applied.

What the checks do

checkFileListOrder() verifies that the filenames are ordered correctly.

checkIntegrity() verifies that previously recorded migrations still match the hashes stored in the migration table.

performMigration() runs any pending files in order and records the result.

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

--force drops and recreates the schema before rerunning the migrations.

--reset reruns the last migration. --reset 3 restarts from migration 4 onwards.

The command also supports temporary connection overrides:

• --base-directory
• --driver
• --database
• --host
• --port
• --username
• --password

Those flags override the corresponding configuration values for that command invocation.

Configuration keys

The migrator command reads these configuration keys:

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

Schema selection

For non-SQLite drivers, the migrator selects or creates the schema before running the files.

For SQLite, the database file itself is the schema, so there is no separate schema-selection step.

Note

In WebEngine the same migrator sits behind gt migrate, so the file layout and the migration numbering rules stay the same.

---

To see the library in runnable scripts, continue with Examples.