MySql-миграции: что это и как реализовать простым php-скриптом

Когда разрабатываешь веб-приложение не один, а в команде и/или на нескольких машинах, рано или поздно сталкиваешься с проблемой синхронизации кода проекта и базы данных. Для управления кодом есть системы контроля версий, в частности, git, а для СУБД придуманы миграции.

Есть много готовых разнообразных инструментов, которые занимаются миграциями, но!

Очень часто все, что мы хотим - это просто залить в базу изменения, которые сделаны другим разработчиком или же самим собой на другой машине. И желательно при этом затратив минимальные усилия, в том числе и на изучение и настройку незнакомой системы. К тому же далеко не всегда мы располагаем полным доступом к серверу для установки оных инструментов.

Поэтому всех, кому интересно узнать, как самим сделать простую утилиту миграций, написав полсотни строк php-кода, прошу в статью.

Идея миграций

Идея довольно проста: в проекте создаем отдельную папку sql, куда складываем sql-файлы с миграциями, то есть, со скриптами, которые меняют содержимое базы, а также один php-файл, который эти миграции и накатывает.

Нужно учесть 2 вещи: во-первых, каждая миграция должна выполняться строго один раз, а во-вторых, в строго определенном порядке. Это разумно и обязательно, потому как если нам прилетают от коллеги 2 миграции, одна из которых создает таблицу users, а другая добавляет в нее тестовых пользователей, то мы хотим выполнить эти скрипты именно в таком порядке и не добавить при этом данные в базу больше одного раза.

Проблему повторных выполнений миграций мы решим, записывая в отдельную таблицу уже отработавшие скрипты, а порядок выполнения установим четкими правилами именования sql-файлов. Как это решается в коде, увидим чуть позже, а пока займемся подготовкой самих тестовых миграций (исходники всех миграций и php-скрипта в конце статьи)

Создаем тестовые sql-скрипты

Пусть у нас есть тестовая база данных под названием test. Мы работаем с ней какое-то время и решили внедрить миграции. Есть разумное правило: самая первая миграция должна содержать в себе полный дамп уже существующих сущностей в базе. Уточню: миграции помогают не только последовательно расширять уже существующую базу, но еще и накатить эту самую базу с нуля, например, для новых людей в команде.

Перед началом выполнения миграций я предполагаю, что указанная база данных уже существует, поэтому в миграции нигде не указывается название базы. На мой взгляд, это хорошо в том плане, что на одной машине программист может работать с разными базами и с разными их версиями. Поэтому у нас скрипта создания базы не будет.

В тестовом примере рассмотрим такой ход событий.
Первая миграция накатывает уже существующие данные и создает таблицу versions - она содержит уже выполненные скрипты.
Вторая создает таблицу users с тремя полями: id, email, password.
Третья добавляет в users три тестовых записи.
А четвертая добавляет в users новую колонку active.

Напишем первую миграцию: дамп базы и таблица versions. Пусть на момент внедрения миграций у нас есть таблица goods с парой товаров. Их нужно скинуть в скрипт и в тот же скрипт добавить таблицу versions. В итоге файл будет выглядеть так.
0000_base.sql

    -- Дамп существующей базы --
    
    -- Таблица товаров --
    create table `goods` (
        `id` int(10) unsigned not null auto_increment,
        `name` varchar(255) not null,
        `price` int(11) not null,
        primary key (id)
    )
    engine = innodb
    auto_increment = 1
    character set utf8
    collate utf8_general_ci;
    
    -- Данные из таблицы товаров --
    insert into `goods` (`name`, `price`) values 
        ('Ноутбук', 30000),
        ('Телефон', 20000);
    
    -- /Дамп существующей базы --
    
    
    -- Таблица versions --
    create table if not exists `versions` (
        `id` int(10) unsigned not null auto_increment,
        `name` varchar(255) not null,
        `created` timestamp default current_timestamp,
        primary key (id)
    )
    engine = innodb
    auto_increment = 1
    character set utf8
    collate utf8_general_ci;    

В файле мы видим структуру goods и 2 строчки с данными, а также versions. Эта таблица содержит 3 столбца: первичный ключ id, name - имя файла с миграцией, created - дата ее накатывания. Как Вы догадываетесь, в versions мы будем добавлять строки после накатывания новых миграций. А пока создадим оставшиеся 3 миграции.

0002_add_users.sql

    -- Таблица пользователей --
    create table if not exists `users` (
        `id` int(10) unsigned not null auto_increment,
        `email` varchar(255) not null,
        `password` varchar(255) not null,
        primary key (id)
    )
    engine = innodb
    auto_increment = 1
    character set utf8
    collate utf8_general_ci;

0003_insert_data_into_users.sql

    -- Добавляем тестовых пользователей --
    insert into `users` (`email`, `password`) values 
        ('test1@gmail.com', '111111'),
        ('test2@gmail.com', '222222'),
        ('test3@gmail.com', '333333')

0004_add_column_active_to_users.sql

    -- Добавляем колонку active в users --
    alter table `users` 
        add column `active` tinyint(1) not null default 1 after `password`;

На заметку: возможно, Вам не удобно писать sql-скрипты руками, Вы привыкли создавать и заполнять таблицы через phpMyAdmin или другой инструмент. Спешу успокоить, все известные мне утилиты позволяют генерировать такой sql-код автоматически. То есть Вы можете работать с базой, как удобно, а при подготовке файла-миграции вытащить нужный скрипт из условного phpMyAdmin-a в режиме copy-paste.

Обратим внимание на правила наименования файлов - это важная часть.

• 0001_base.sql
• 0002_add_users.sql
• 0003_insert_data_into_users.sql
• 0004_add_column_active_to_users.sql

Порядковые номера в начале каждого файла нужны, чтобы правильно отсортировать файлы. Только в этом случае миграции будут выполняться в правильном порядке. После _ идет краткое описание миграции, которое нужно исключительно для нашего удобства, чтобы понимать, что делает тот или иной скрипт, не заглядывая в него.

В нашем варианте мы предполагаем, что миграций не будет больше 9999 штук, и номер всегда должен состоять из 4-х цифр. Например, 10-я миграция будет называться 0010_description, а 101-я - 0101_description.

Теперь у нас все готово к написанию php-скрипта для выполнения миграций - migration.php.

Пишем php-код для запуска миграций

Подумаем, что нам нужно уметь делать.

• Во-первых, получить список всех sql-файлов из папки sql.
• Во-вторых, понять, какие из них уже накатились ранее (сравнить этот список с тем, что лежит в таблице versions).
• В-третьих, залить содержимое отобранных файлов в базу, заодно записав в versions их названия.

Каркас migration.php, базовые константы и функции

Нам нужно написать фунцию подключения к базе данных и общую логику работы скрипта. Создадим файл migration.php, кинем его в папку sql рядом с миграциями и напишем в нем следующее:

    // Объявляем нужные константы
    define('DB_HOST', 'localhost');
    define('DB_USER', 'root');
    define('DB_PASSWORD', 'root');
    define('DB_NAME', 'test');
    define('DB_TABLE_VERSIONS', 'versions');
    
    
    // Подключаемся к базе данных
    function connectDB() {
        $errorMessage = 'Невозможно подключиться к серверу базы данных';
        $conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
        if (!$conn)
            throw new Exception($errorMessage);
        else {
            $query = $conn->query('set names utf8');
            if (!$query)
                throw new Exception($errorMessage);
            else
                return $conn;
        }
    }
    
    
    // Получаем список файлов для миграций
    function getMigrationFiles($conn) {
        // ...
    }
    
    
    // Накатываем миграцию файла
    function migrate($conn, $file) {
        // ...
    }
    
    
    // Стартуем
    
    // Подключаемся к базе
    $conn = connectDB();
    
    // Получаем список файлов для миграций за исключением тех, которые уже есть в таблице versions
    $files = getMigrationFiles($conn);
    
    // Проверяем, есть ли новые миграции
    if (empty($files)) {
        echo 'Ваша база данных в актуальном состоянии.';
    } else {
        echo 'Начинаем миграцию...

';
    
        // Накатываем миграцию для каждого файла
        foreach ($files as $file) {
            migrate($conn, $file);
            // Выводим название выполненного файла
            echo basename($file) . '
';
        }
    
        echo '
Миграция завершена.';    
    }

Пробежимся по коду.

• Сначала объявляем константы для подключения к базе и с названием таблицы versions (она будет упоминаться в коде несколько раз).
• Функция connectDB возвращает mysqli-объект для работы с базой.
• getMigrationFiles($conn) возвращают список актуальных, еще не выполненых миграций.
• migrate($conn, $file) накатывает одну миграцию - по имени файла.

Дальше сама логика работы.

• Подключаемся к базе и получаем список актуальных миграций.
• Если он пустой, то просто выводим сообщение, что база в актуальном состоянии.
• Если файлы миграции имеются, то запускаем для каждого функцию migrate, выводя название файла для информации. А в конце пишем позитивное сообщение, что миграция завершена.

    // Получаем список файлов для миграций
    function getMigrationFiles($conn) {
        // Находим папку с миграциями
        $sqlFolder = str_replace('\\', '/', realpath(dirname(__FILE__)) . '/');
        // Получаем список всех sql-файлов
        $allFiles = glob($sqlFolder . '*.sql');
    
        // Проверяем, есть ли таблица versions 
        // Так как versions создается первой, то это равносильно тому, что база не пустая
        $query = sprintf('show tables from `%s` like "%s"', DB_NAME, DB_TABLE_VERSIONS);
        $data = $conn->query($query);
        $firstMigration = !$data->num_rows;
        
        // Первая миграция, возвращаем все файлы из папки sql
        if ($firstMigration) {
            return $allFiles;
        }
    
        // Ищем уже существующие миграции
        $versionsFiles = array();
        // Выбираем из таблицы versions все названия файлов
        $query = sprintf('select `name` from `%s`', DB_TABLE_VERSIONS);
        $data = $conn->query($query)->fetch_all(MYSQLI_ASSOC);
        // Загоняем названия в массив $versionsFiles
        // Не забываем добавлять полный путь к файлу
        foreach ($data as $row) {
            array_push($versionsFiles, $sqlFolder . $row['name']);
        }
    
        // Возвращаем файлы, которых еще нет в таблице versions
        return array_diff($allFiles, $versionsFiles);
    }

Почти каждая строка прокомментирована, но поясню еще подробнее.
В переменную $sqlFolder попадает полный абсолютный путь к текущему файлу migration.php. А соответственно, и к папке sql с миграциями. Замена \\ на / нужна для тех случаев, когда realpath возвращает путь с обратными слешами вместо прямых (например, в windows). Первый \ нужен для экранирования второго.

Дальше функция glob вытащит все файлы из указанной папки по нужной маске *.sql.

Затем нужно понять, не в первый ли раз мы собираемся накатывать миграции. Определить это можно только по наличию таблицы versions в базе. Мы же помним, что она создается в самой первой миграции. Если запрос show tables from test like "versions" вернет пустой список, значит таблицы еще нет, в переменную $firstMigration запишем true и вернем из функции весь список файлов.

Если же таблица versions существует, то вытаскиваем из нее список файлов - уже выполненные миграции. И возвращаем из функции файлы, которых нет в таблице versions. В этом поможет array_diff.

Все, список нужных файлов мы получили, осталось понять, как закинуть их содержимое в базу - функция migrate.

    // Накатываем миграцию файла
    function migrate($conn, $file) {
        // Формируем команду выполнения mysql-запроса из внешнего файла
        $command = sprintf('mysql -u%s -p%s -h %s -D %s < %s', DB_USER, DB_PASSWORD, DB_HOST, DB_NAME, $file);    
        // Выполняем shell-скрипт
        shell_exec($command);
    
        // Вытаскиваем имя файла, отбросив путь
        $baseName = basename($file);
        // Формируем запрос для добавления миграции в таблицу versions
        $query = sprintf('insert into `%s` (`name`) values("%s")', DB_TABLE_VERSIONS, $baseName);
        // Выполняем запрос
        $conn->query($query);
    }

Из интересного в этой функции только shell_exec($command) - выполнение строки $command в терминале. Залить содержимое sql-файла в базу можно такой командой
mysql -uuser -ppassword -h host -D database < file.sql
Оную команду формируем с помощью sprintf и передаем ее в shell_exec. Собственно миграция накатилась, осталось добавить соответствующее название файла в таблицу versions - это заурядный запрос вида
insert into `versions` (name) values('0001_base.sql')

И это весь код, который нужен нам для создания простой системы миграций. Теперь создадим пустую базу данных test, закинем, если еще не успели, папку sql куда-нибудь в проект и откроем в браузере sql/migration.php.

На заметку: если Вы не хотите на боевом сайте светить содержимым своих миграций возможным злоумышленникам, то поместите сами sql-миграции в место, недоступное для просмотра извне. migration.php можно закинуть куда угодно, хоть в корень проекта, только придется немного поправить пути к папке с миграциями. Думаю, с этим делом Вы без труда справитесь сами :-)

Итак, migration.php в браузере запущен, если все сделано правильно, то увидим такую картину

    Начинаем миграцию...
    
    0001_base.sql
    0002_add_users.sql
    0003_insert_data_into_users.sql
    0004_add_column_active_to_users.sql
    
    Миграция завершена.

Посмотрите в базу и убедитесь, что нужные таблицы и данные созданы, в versions лежат 4 строки с названиями файлов. Теперь обновите страницу в браузере и увидите сообщение "Ваша база данных в актуальном состоянии." Все правильно, все миграции сделаны, а новых пока нет.

Чтобы окончательно убедиться в работоспособности системы, удалите все три таблицы из базы и оставьте в папке sql только первую миграцию. Обновите страницу - накатится одна миграция, таблица goods и versions с единственной строкой. Добавьте в папку миграции 0002_add_users.sql и 0003_insert_data_into_users.sql, обновите, выполнятся еще 2 миграции и в базе появится users с данными. И наконец последний файл и снова запуск - в users добавится новая колонка active.

Итоги и исходники

Что же у нас получилось?
Все работает, как мы и задумали.
Теперь можно править базу, добавляя все изменения с ней в миграции, не боясь, что кто-то и когда-то будет работать с неактуальной базой.

Главное, не забывать в нужные моменты при изменении базы создавать соответствующие миграции, соблюдая правила именования файлов, и запускать скрипт migration.php.

Как видим, код получился действительно короткий и простой (половину занимают комментарии). И при этом прямые свои обязанности он выполняет - накатывает миграции, не выполняя ничего лишнего.

Я намеренно не стал усложнять код, добавив обработку ошибок при накатывании скриптов. Также не рассматривал тему даунгрейда - так называемые обратные миграции, которые по идее должны автоматически откатывать неудачные изменения к предыдущим версиям, но на практике это опасная процедура и ее все равно приходится делать руками.

Если Вы работаете над большим и долгим проектом с постоянным редактированием базы сотней программистов, то такая простейшая система скорее всего не подойдет. Нужна одна из упомянутых выше серьезных утилит для работы с миграциями, где доступно больше возможностей и на проработана отказоусточивость.

Но не секрет, что в таких проектах уже давно подобная система используется, Вы можете даже не знать, в какой момент сборщик накатывает новые миграции. Кстати, это можно сделать и при помощи моего любимого gulp-a, но это уже другая история, и возможно, тема отдельной статьи.

Задавайте вопросы и оставляйте отзывы в комментариях, и конечно, скачивайте исходники