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, но это уже другая история, и возможно, тема отдельной статьи.
Задавайте вопросы и оставляйте отзывы в комментариях, и конечно, скачивайте исходники
Истории из жизни айти и обсуждение кода.