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 миграции.
-- Таблица пользователей -- 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); > Анонсы статей, обсуждения интернет-магазинов, vue, фронтенда, php, гита.
Истории из жизни айти и обсуждение кода.