title: PHP Database Migrations
While working on [Let's Connect!](https://letsconnect-vpn.org/) /
[eduVPN](https://eduvpn.org/) 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](https://phinx.org/) that seems to be
a popular choice. It supports many databases and makes managing migrations
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
As currently only [SQLite](https://sqlite.org/) is supported, this makes it
both easier, because one only has to deal with SQLite, and harder to support
migrations as SQLite does [not](https://www.sqlite.org/lang_altertable.html)
support many different `ALTER TABLE` commands that are common with other
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
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
This library is easy to use and contains a lot of documentation to get you
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](../about.html) know what you think!
**UPDATE** (2018-06-25): use `;` now in example migration.