|author||François Kooman <firstname.lastname@example.org>||2018-06-19 08:41:48 +0200|
|committer||François Kooman <email@example.com>||2018-06-19 08:41:48 +0200|
add php database migrations post, update some older posts, about
Diffstat (limited to 'posts/php_database_migrations.md')
1 files changed, 71 insertions, 0 deletions
diff --git a/posts/php_database_migrations.md b/posts/php_database_migrations.md
new file mode 100644
@@ -0,0 +1,71 @@
+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!