path: root/posts/php_database_migrations.md
diff options
authorFran├žois Kooman <fkooman@tuxed.net>2018-06-19 08:41:48 +0200
committerFran├žois Kooman <fkooman@tuxed.net>2018-06-19 08:41:48 +0200
commit11b6d9c01a65cfdfac8629bcd8a65ab6c7b11893 (patch)
treec427c5f4c38d949c651687cc471b46fbe9e15c35 /posts/php_database_migrations.md
parentb765b2ed62071807af1f9b5d90862a52074e77e4 (diff)
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
index 0000000..b64db01
--- /dev/null
+++ b/posts/php_database_migrations.md
@@ -0,0 +1,71 @@
+title: PHP Database Migrations
+published: 2018-06-19
+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
+ migration.
+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
+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:
+ INSERT INTO foo (a) SELECT a FROM _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!