PHP Database Migrations
Published on 2018-06-19 | Last modified on 2018-06-25
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:
- use the least amount of code and SQL queries possible as to not slow down the "normal" flow, i.e. when no migration is needed;
- make sure migrations either complete fully, or not at all (use transactions);
- make sure only one request can trigger the database migrations and make the other requests either wait or return "Internal Server Error" during the migration.
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!