How to add a database structure migration feature to your legacy php projects

There is a ton of legacy projects that exist in the universe written in PHP. Old codebases are hard to maintain, and it’s not easy to set up a development environment for legacy applications. I had the same issue; I have a lot of solutions to build a stateless development environment for legacy projects. Today I will focus on database-related one.

Let’s think you start to work on a legacy project and have made some changes to the database on your local. What do you need to do? You will probably fetch the production database to your local (with/without data), write some SQL to alter your DB structure, save queries, and apply them to your production database.

It’s a simple task if you have a simple application. But web applications are mostly like living organisms. Invariably they will be getting more and more complex. You will sometimes forget to apply a statement to your production database. It will cause many problems until you realize which statement you forgot.

There is a solution for following the structural database changes. It’s called Schema Migration. You can start to use a migration library to follow database structure changes. Also, you can go future and past in your DB schema with a migration tool (be careful, it will remove all data dependent in that update if you go back. it will remove field data if you remove a field.)

Migration libraries mostly have simple logic. You will have versioned up and down scripts, and it will run those scripts. But it may be hard to add libraries to legacy apps.

So, I created a library for the same purpose. Other libraries are too big and complex; I built a library on top of the PDO library, which has a few simple commands. I want to show you how you can add that library to your legacy app and start to use it. Let’s begin.

The library name is Migratos, its a god of all migration libraries :)

You can install with composer require 7cups/migratos command. But you will need to create a wrapper for that. Eventually, it’s just a library.

#console.php
<?php

require_once(__DIR__.'/vendor/autoload.php');

use SevenCupsMigratos\Command\MigrationCommand;
use Symfony\Component\Console\Application;
 

$db = new PDO('mysql:host=localhost;dbname=test', 'databaseuser', 'databaseuserpass'); 

$application = new Application('My Console','1.0');

$application->add(new MigrationCommand($db)); 

$application->run();

Migratos only care for PDO instances. Maybe you have a wrapper library over PDO; it will work if extended from PDO.

We loaded our application autoloader (which was generated by the composer) and then created our PDO instance; then, we will add a migration command to CLI application. (It is using symfony/command library to manage CLI, you can use MigrationCommand with your console app if you have one)

But before, do not forget to create a folder named migration in your root directory. mkdir migration

Then we just run the CLI app.

Let’s do some tests. You can run that command: php console.php --help. You will get help text of usage of symfony/console. Then you can run the php console.php list command to get what tools exist in the console.php, and you will see that app:migration subcommand.

Lets test this: php console.php app:migration --help. There are five options that exist for use with that subcommand.

At the first usage, you will need a base.sql file. Because you have a database schema already. Try php console.php -b command. It will ask to write a new SQL file with some content. Answer yes. In that step, only the migration table create statement exists in base.sql file.

#base.sql
 CREATE TABLE migratos_migration_versions (
    id int(11) NOT NULL AUTO_INCREMENT,
    version int(11),
    run_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    direction ENUM('u','d'),
    current_version varchar(255),
    PRIMARY KEY (id)
)

Now, you have to export your database structure manually (I will add an automated base.sql creation soon), then paste the content of that file. Let’s say we have just a table; then, our base.sql will look like this;

#base.sql
 CREATE TABLE migratos_migration_versions (
    id int(11) NOT NULL AUTO_INCREMENT,
    version int(11),
    run_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    direction ENUM('u','d'),
    current_version varchar(255),
    PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS users(
   id int(11) NOT NULL AUTO_INCREMENT,
   username varchar(255),
   type ENUM('admin','member'),
   password varchar(255),
   PRIMARY KEY (id)
)

You see. This is our base database structure. But do not forget to add IF NOT EXISTS statement to the table creating query. Your database already exists; you would not want to override existing data.

Now, we will run php console.php app:migration --init command to initialize our local database. Do not run this command on production!!!

And your local database structure is ready to use. You can copy your local migratos_migration_versions table to your production database if you want to use that tool in the production server. It contains data for initial data, which is your existing production database. All runs after an initial run will update your database structure from that point. This is zero-point.

OK!

Let’s create a new migration file. Let’s say we need to add an email field to the user table. Run that command: php console.php app:migration -c, and create two empty files, one for up and one for down scripts. Under migration folder d_1660740441.sql u_1660740441.sql, you will see two new files.

Add your up SQL script to u_1660740441.sql file;

ALTER TABLE users ADD COLUMN email varchar(255) null;

And add your down SQL script to d_1660740441.sql file;

ALTER TABLE users DROP COLUMN email;

Ready, you can run the migration command php console.php app:migration -u to append changes to the database. It will print out SQL statements before running on the database.

Then changes will be applied to your database. You can commit that file, and all developers in the team can create their own migration files, and the system admin will know which changes happened to the database structure.

Do you want to roll back changes? Easy peasy!

php console.php app:migration -d 1660740441 this command will revert changes made by u_1660740441.sql file and upper versions. For instance, you have 2 migration files u_1 and u_2. If you run down the command for 1, it reverts all updates bigger than 1.

Good for you!

There is a demo repository in GitHub; https://github.com/delirehberi/legacy-app

#php #library #database #command #en