Run controlled database migrations with PHP and SQLite

Adding a field to a development database can be quick. Applying the change to existing records, coordinating several application versions and understanding a failed transition require a more explicit contract. A migration describes an inspectable move from one state to another: the expected starting point, the permitted changes and the conditions that demonstrate completion.

This guide develops a complete PHP example using SQLite. Two fictional tasks acquire a review status while retaining their identifiers and original text. A migration register records executed steps and their checksums. The program was actually executed with PHP 8.4.27 and SQLite 3.53.4. Documentation was reviewed on 9 November 2026. Observations refer to the isolated database files described here rather than a customer deployment.

Describe the business transition before writing SQL

The original tasks table has an identifier and a title. The revised application also needs to distinguish unreviewed tasks from approved ones. For existing records, the justified starting value is unreviewed. Adding a field must not retrospectively assert that a person has already checked the work. Choose that meaning before choosing a convenient SQL default. A technically valid value can still make an unsupported business claim.

Several acceptance conditions follow. Every existing identifier must remain the same. Titles containing punctuation and Unicode characters must survive. Existing tasks need a permitted review state. The database must reject unknown state values. The recorded migration must match the SQL actually executed. Development, review and operations can assess those conditions together. A message saying that a column was added would establish only one limited part of this contract.

Check schema, data and running application versions together

A database can be reachable while remaining incompatible with the program using it. A new release may expect a review column that has not yet been installed. An older export may copy all returned columns and accidentally change its output format after a column is added. Inventory the actual readers and writers: the web application, background tasks, imports, reports and manually operated tools. Each participates in the transition.

For this small example, an old query explicitly selecting id and title remains useful after the extension. That observation does not establish compatibility for an unknown application. Positional handling of SELECT *, fixed CSV layouts and custom serialisers deserve specific checks. Test concrete calls against expected results. The installed version number of a framework cannot answer how an individual export interprets an extra value.

Keep an immutable record of executed migrations

The schema_migrations table records each step's identifier, SHA-256 checksum and application time. The identifier locates the intended step. The checksum establishes whether the SQL currently shipped is the same content already recorded. A mismatch stops this runner before additional steps execute. An older program package also stops when the database contains an executed migration absent from that package.

Keep applied migration files unchanged. A correction receives a new identifier. In this deliberately strict byte-based scheme, even a change to whitespace or line endings changes the checksum. Establish consistent repository line endings and inspect the final release artefact. The checksum is a consistency control. It is neither a substitute for authorised code review nor a signature protecting against an attacker able to alter both code and the database register.

Choose transaction behaviour for the actual database engine

This example specifically uses SQLite and starts its write transaction with BEGIN IMMEDIATE. The SQLite transaction documentation explains that this start can fail when another writer is active. The runner then does not begin a second concurrent schema change. Its command-line entry point configures a bounded wait so that an occupied maintenance window produces an observable outcome.

A shared PHP interface does not make database engines transactionally equivalent. The PDO transaction documentation and the MySQL manual on implicit commits describe relevant DDL boundaries. Do not assume that the combined rollback of schema changes and register entries demonstrated here applies unchanged to MySQL. Determine which operations end transactions on the intended system and plan recovery around that behaviour.

The complete executable example

The following file is named migrate.php. It contains both the migration definitions and the execution function. Its direct entry point is a command-line tool. A missing database path is rejected so that a mistyped new filename does not silently create an empty database. Migration SQL comes from the reviewed project artefact. This is not an endpoint for accepting SQL through a form or from an untrusted file.

<?php
declare(strict_types=1);

function projectMigrations(): array {
    return [
        '001_tasks' => <<<'SQL'
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL CHECK(length(trim(title)) > 0)
);
SQL,
        '002_review_state' => <<<'SQL'
ALTER TABLE tasks ADD COLUMN review_state TEXT NOT NULL
    DEFAULT 'unreviewed'
    CHECK(review_state IN ('unreviewed', 'approved'));
CREATE INDEX tasks_review_state_id ON tasks(review_state, id);
SQL,
    ];
}

function migrate(PDO $db, array $migrations): array {
    if ($db->getAttribute(PDO::ATTR_DRIVER_NAME) !== 'sqlite') {
        throw new InvalidArgumentException('Dieses Beispiel benötigt SQLite.');
    }
    foreach ($migrations as $id => $sql) {
        if (!is_string($id) || !preg_match('/^[0-9]{3}_[a-z0-9_]+$/D', $id)
            || !is_string($sql) || trim($sql) === '') {
            throw new InvalidArgumentException('Ungültige Migrationsdefinition.');
        }
    }
    ksort($migrations, SORT_STRING);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->exec('BEGIN IMMEDIATE');
    try {
        $db->exec('CREATE TABLE IF NOT EXISTS schema_migrations (
            id TEXT PRIMARY KEY,
            checksum TEXT NOT NULL CHECK(length(checksum) = 64),
            applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        )');
        $applied = $db->query('SELECT id, checksum FROM schema_migrations')
                      ->fetchAll(PDO::FETCH_KEY_PAIR);
        foreach ($applied as $id => $checksum) {
            if (!array_key_exists($id, $migrations)
                || !hash_equals($checksum, hash('sha256', $migrations[$id]))) {
                throw new RuntimeException('Unbekannter oder veränderter Stand: ' . $id);
            }
        }
        $record = $db->prepare('INSERT INTO schema_migrations (id, checksum)
                               VALUES (:id, :checksum)');
        $executed = [];
        foreach ($migrations as $id => $sql) {
            if (array_key_exists($id, $applied)) continue;
            $db->exec($sql);
            $record->execute(['id' => $id, 'checksum' => hash('sha256', $sql)]);
            $executed[] = $id;
        }
        $db->exec('COMMIT');
        return $executed;
    } catch (Throwable $failure) {
        try {
            $db->exec('ROLLBACK');
        } catch (Throwable $rollbackFailure) {
            error_log('SQLite-Rücknahme prüfen: ' . $rollbackFailure->getMessage());
        }
        throw $failure;
    }
}

if (realpath($_SERVER['SCRIPT_FILENAME'] ?? '') === __FILE__) {
    if (PHP_SAPI !== 'cli') {
        http_response_code(404);
        exit;
    }
    if ($argc !== 2 || !is_file($argv[1])) {
        fwrite(STDERR, "Aufruf: php migrate.php /pfad/zur/vorhandenen.sqlite\n");
        exit(64);
    }
    try {
        $db = new PDO('sqlite:' . realpath($argv[1]));
        $db->exec('PRAGMA busy_timeout = 3000');
        $ids = migrate($db, projectMigrations());
        echo json_encode(['ausgeführt' => $ids], JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR), "\n";
    } catch (Throwable $failure) {
        fwrite(STDERR, 'Migration fehlgeschlagen: ' . $failure->getMessage() . "\n");
        exit(1);
    }
}

After completing the preparation steps, a command can look like this:

php migrate.php /path/to/existing.sqlite

The result lists only the identifiers executed during that invocation. An empty list is expected for a valid repeat run. Also inspect the process exit status: a rejected run returns an error and must not allow a release pipeline to proceed with starting the new application. The example does not configure access to a real customer database or perform a production deployment.

Complete all historical checks before applying new steps

The runner first compares every recorded identifier against the supplied migration set. A missing identifier or different checksum ends the transaction. Only after that complete comparison does it apply pending steps. The order matters. A new migration must not execute before a subsequently inspected historical file turns out to have changed. Preflight therefore covers the whole known history rather than only the next pending entry.

PHP array order does not determine the result because the validated identifiers are sorted. Three-digit numbering is a convention for this small teaching project. A larger history needs a durable ordering contract and a way to resolve concurrently developed changes. Reordering published steps automatically on the target machine would be inappropriate. Resolve the complete package before release and deploy the same artefact that was actually reviewed and tested.

Preserve existing rows through the schema extension

The second migration adds review_state with a concrete default and a restricted set of values. The ALTER TABLE documentation describes rules and restrictions for added columns. In the executed fixture, both existing rows subsequently report unreviewed. Independent queries compare their original identifiers and titles in full. This includes apostrophes, an ampersand and German Unicode text.

The additional index belongs to the same step. Its structural purpose is to support looking up tasks by review state. Whether that index benefits a particular workload requires a separate investigation. This migration checks that the index exists and is installed together with the schema change. It does not demonstrate a performance improvement. Structural acceptance and later query analysis therefore retain separate questions and separate evidence.

Exercise a failure after a schema change has taken effect

The important negative case contains two statements. First it adds a temporary test column. Then it tries to insert an identifier already present in the table. The second statement actually fails. After rollback, independent inspection finds no temporary column, unchanged original records and no migration entry for the failed step. The test examines the resulting state rather than treating any exception as sufficient evidence.

Choose similarly relevant failure points for your own migration. Examples include a violated constraint after an earlier alteration, an invalid historical value encountered during conversion or conflicting rows while creating a unique index. Carry out those experiments on a protected copy with controlled data. Record which invariants must still hold afterward. A failure message can accompany a partial change on an incorrectly designed process, so the message alone cannot establish recovery.

Accept the backup through an actual restoration

Before extending the schema, the test creates a backup using VACUUM INTO. The SQLite documentation explains its consistent logical snapshot and requirements for the output file. A further copy is opened as the restoration target. Its integrity, original columns and all fixture values are checked. Only that restored copy is migrated again. The original backup remains at the preceding schema version.

A filename in a backup directory is not restoration evidence. Operational preparation also needs a storage location, an access path during failure and an accountable person. Establish which newer writes would be lost by returning to the saved state. A database snapshot cannot by itself settle the state of subsequently generated files, external messages or later changes in another service. Plan the recovery boundary across those dependencies rather than assuming that one file restores the complete application.

Interpret the observations from the executed checks

The test program evaluates 38 conditions using its own SQLite files and two fictional historical rows. Alongside successful transitions, it checks changed hashes, an older release, a DDL failure, a competing database writer and real command-line invocations. The observations concern consistency and control flow. They do not establish an acceptable lock duration for a large production dataset or behaviour under application load.

Plan the entire release window

Decide before starting when writes will stop or how they will be controlled. One migration process does not automatically coordinate every background task. The SQLite explanation of isolation helps distinguish connections and the state visible to them. The application still needs its own policy for active requests, readers and maintenance behaviour. Those policies should be known before a schema lock is acquired.

After migration, inspect the most important actual workflows. Successful DDL does not establish that an old importer still writes appropriate values or that a new filter finds unreviewed tasks correctly. Define the expected outcomes in advance. The responsible person can then decide whether the release should continue using specific acceptance steps. An unexpected result should trigger an agreed response rather than improvised SQL edits directly against the production data.

Separate expansion from later removal

A compatible change can often be delivered through several releases. First the schema receives the additional structure. Then a suitable program version starts using it. An earlier structure is removed only after the relevant readers and writers have demonstrably moved. This sequence exposes dependencies and provides an appropriate interval for observation. The sequencing alone does not guarantee uninterrupted operation or eliminate the need to assess locking.

Restoring older program files also needs a compatibility decision. After our added status field, an explicitly limited older read query can continue to work. Following a later removal or renaming, the same rollback might fail. Document which application versions can use the schema resulting from each package. A generic rollback button cannot determine whether the older application's assumptions still match the data or whether newer writes remain meaningful to it.

Confirm target identity independently

The existing-file check prevents one particular mistake: a wrong new name does not create an empty database. It cannot reliably identify a different SQLite database that already exists. The runner is deliberately not a general application-identity system. A real release needs an additional inspectable target contract, such as an unambiguous project configuration combined with a known expected starting state. Opening a file successfully does not establish that it belongs to the intended application.

Compare those details against independent evidence before the maintenance window. Relevant facts can include an application marker, the permitted environment and an expected schema. A plausible filename is insufficient. Permission to open a database does not prove that it is the correct target for this job. Record the checked target relationship and ensure that a newly started process uses the same authoritative configuration instead of rediscovering the target through an unrelated default.

Distinguish a change of meaning from an added column

Our extension gives historical tasks an explicitly unreviewed state. Other changes can alter the interpretation of existing values: a free-text category becomes a restricted choice or an entered string becomes a normalised date. A matching destination column is not enough for those transitions. Establish which historical values already conform, which can be converted unambiguously and which require a human decision. That classification is part of the migration design.

Keep ambiguous values as an explicit work queue. Turning “03/04” into one specific date without a known input convention would invent information. A documented review state can be more useful than a convenient replacement value. Also decide how the responsible business role finds and corrects outstanding cases. A successful UPDATE establishes a technical change. Acceptance of the resulting meaning needs a suitable business check with access to the original context.

Integrate parallel migration development before deployment

Two branches can each allocate the next identifier and pass their separate tests while conflicting in the combined package. At integration, inspect the complete set: are identifiers unique, does one step depend on another and has any published file changed? The final ordering belongs to the tested release. It should not be invented on the target system from incidental file timestamps or the order in which a directory happens to be enumerated.

A useful integration experiment starts from the last common database state and runs the actual combined migration set. Also open a database that has already advanced further and observe the older package's preflight. Our small test actually rejects that second direction. The rejection creates an understandable reason to investigate. Resolve the mismatch before release rather than inserting replacement checksums on the server and making the historical inconsistency disappear from view.

Embed the limited runner in a reviewed operating procedure

The migration register describes SQL already applied. It does not compare every live table against a universal expected schema. A later manual alteration can therefore create a mismatch while all recorded checksums still match their files. Add suitable schema and data acceptance checks for the actual application. Constrain direct changes operationally and retain a traceable process for exceptional maintenance. A history table alone cannot detect every form of drift.

The supplied SQL is also trusted project code. This runner does not parse it to prohibit nested transaction statements or business-destructive deletions. Those decisions belong to the review before execution. After a migration exception, discard the connection used for that failed operation and inspect the state. The example does not justify an automatically generated repair or blind continuation. Its value lies in a narrow tested contract and deliberate integration into the project's release procedure.

Design a large data conversion separately

The small status extension needs no lengthy business-data cleanup. A substantial conversion can have different limits. Before applying this approach to a large database, determine how much work occupies one transaction and which other processes would wait. These 38 conditions are not a load test. A representative data distribution and realistic concurrent operations require a separate assessment suited to that proposed change.

If a conversion intentionally runs in batches, its progress needs a dependable contract as well. A resumed run must distinguish records already handled successfully from those still pending. Coordinate the data change and its progress record so that a failure cannot save only one side. Otherwise resumption may skip work or repeat it incorrectly. Stable identifiers, understandable repetition rules and an inspectable count of outstanding cases help define the process. A percentage bar with no relationship to those facts would not establish completion.

Checklist for a traceable migration run

• Write down the business starting point and permitted destination state.
• Identify the actual database, library version and release artefact.
• Create a backup and verify restoration using an independent copy.
• Inventory historical values and all relevant readers and writers.
• Compare every applied identifier and checksum before new changes begin.
• Exercise a controlled failure after an earlier effective change.
• Accept the rollback state through schema, data and register inspection.
• Clarify lock handling, timeout and competing processes for the maintenance window.
• Evaluate process status and executed identifiers in the release pipeline.
• Agree on acceptance, stopping conditions and accountable resumption beforehand.

Start with one real question about the existing data

The next useful step is to describe one planned change as an inspectable transition. Choose a small field, a bounded index addition or a precisely defined cleanup. Prepare a copy containing appropriate edge cases and state the properties that must remain true before implementing the change. Code, backup and failure handling can then be assessed against the same concrete conditions rather than a vague expectation that deployment should work.

The executable runner provides a manageable learning example for that process. An existing project must also respect its database engine, established migration system and release authority. The essential outcome is a defensible relationship between a known change and a checked data state. That relationship supports a reasoned release decision and a recovery path whose consequences are understood before the first production write.