Andrian Kogoshvili
Bare-bone PHP Framework #4 Migrations

Bare-bone PHP Framework #4 Migrations

Andrian Kogoshvili's photo
Andrian Kogoshvili
·

3 min read

Migrations are grate so let's add them 😃 Create Migrations folder and add Migration.php

<?php
namespace Migrations;
use Core\Database;
class Migration
{
    protected $db;
    protected $folder = __DIR__;

    public function __construct(Database $db)
    {
        // We will need database instance to interact with database
        $this->db = $db;
        // Create table for keeping track of our migrations if it doesn't exist already
        $this->_createMigrationsTable();
    }

    public function migrate() {}
    private function _getLatestVersion() {}
    private function _createMigrationsTable() {}
    private function _addMigration($filename, $version) {}
}

The first method _createMigrationsTable is used to create migrations table if needed.

private function _createMigrationsTable()
{
    // SQL query to create table for tracking migrations if it doesn't exist already
    $query = "CREATE TABLE IF NOT EXISTS `migrations` (
        `id` INT NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(255) NOT NULL,
        `version` VARCHAR(32) NOT NULL,
        `created_at` DATETIME NOT NULL,
        PRIMARY KEY (`id`)
    )";
    // Execute query
    return $this->db->exec($query);
}

Second method _addMigrations will be used to insert new migration to migrations table

private function _addMigration($filename, $version)
{
    // Migrations name will be same as file name
    $migrationName = basename(explode('-', $filename)[1], '.sql');
    // Query to insert new entry to migrations table
    $query = "INSERT INTO `migrations` (`name`, `version`, `created_at`) VALUES ('$migrationName', '$version', now())";
    // Execute query
    return $this->db->exec($query);
}

Third method _getLatestVersion will be used to get latest version of migration that was executed

private function _getLatestVersion()
{
    // Query to get value in version colum of latest entry in the table
    $query = "SELECT version FROM `migrations` ORDER BY id DESC LIMIT 1";
    // Need to prepere query before execution to be able to access its respons
    $stmt = $this->db->query($query);
    // Execute query
    $stmt->execute();
    // Get result or default to 0.0.0
    return $stmt->fetch(\PDO::FETCH_ASSOC)['version'] ?? '0.0.0';
}

Last method migrate execute migration

public function migrate()
{
    // Get lates version from migrations table
    $latest = (int)str_replace('.', '', $this->_getLatestVersion());
    // Get list of sql files in the folder
    $files = array_filter(
        scandir($this->folder),
        fn ($file) => strpos($file, '.sql')
    );

    $migrations = [];
    // Create array of file names and versions: 0.1.2-posts.sql -> ["0.1.2" => "0.1.2-posts.sql"]
    foreach ($files as $file) {
        $migrations = array_merge(
            $migrations,
            [explode('-', $file)[0] => $file]
        );
    }

    foreach ($migrations as $version => $filename) {
        // For each migration file compare its version to latest on in migrations table
        if ((int)str_replace('.', '', $version) > $latest) {
            // Get file content
            $content = file_get_contents($this->folder . DIRECTORY_SEPARATOR . $filename);
            // Execute sql from file
            $this->db->exec($content);
            // Add new migration entry
            $this->_addMigration($filename, $version);
        }
    }
}

Finally lest add this to index.php so migration will run if needed every time we do request

container()->make(Migrations\Migration::class)->migrate();

Now we can add files with pattern x.x.x-name.sql, e.g., 0.0.1-post.sql with SQL, and it will be executed.

PHPTutorial