François' Blog

PHP Database Migrations

2018-06-19

While working on Let's Connect! / eduVPN I reached the point of needing to perform a database migration, i.e. modify the database schema. I wanted to add a column to a table.

Software exists to do this, e.g. Phinx that seems to be a popular choice. It supports many databases and makes managing migrations easy!

One feature was not explicitly mentioned there which I really wanted to have: the ability to migrate the database without involving the system administrator. I don't want to require them to run a migration script before things start working again. Ideally the migration is immediately done when needed at "run time". The call to trigger the migration can be added to your index.php before you start using the database. It should be as simple as that.

Of course, this requires a design that optimizes specifically for this flow:

As currently only SQLite is supported, this makes it both easier, because one only has to deal with SQLite, and harder to support migrations as SQLite does not support many different ALTER TABLE commands that are common with other relational databases.

Most libraries work around this by creating some higher level language to support all databases with one code base or migration "language". This makes it easier to change databases when needed. Supporting more databases also requires more QA, so for now I decided to only support SQLite and keep doing this as long as possible...

With SQLite most migrations will require creating a new table, copy the old data in and remove the old table. As an example:

ALTER TABLE foo RENAME TO _foo;
CREATE TABLE foo (a INTEGER NOT NULL, b INTEGER DEFAULT 0);
INSERT INTO foo (a) SELECT a FROM _foo;
DROP TABLE _foo;

This example renames the existing table foo to _foo, creates a new table foo with the additional column b and copies the data from _foo back to foo and eventually deletes the _foo table when the copying is done. Just to be complete: adding a column is one of the ALTER TABLE commands that is actually supported, but just to show an example that works with all kinds of migrations.

One would think this is very slow, but some simple tests doing this with a table that contains around 100.000 rows is barely noticeable.

To make integrating this in applications easier, I created php-sqlite-migrate. This library is easy to use and contains a lot of documentation to get you started.

The API is not yet stable, I'm thinking of adding support for a "schema" directory that contains the database schema and migrations files, that will make it cleaner to add to your code as you don't need to embed the SQL queries in the source code.

Let me know what you think!

UPDATE (2018-06-25): use ; now in example migration.

Last updated on 2018-06-25

Return to Index