aboutsummaryrefslogtreecommitdiffstats
path: root/posts/php_database_migrations.md
blob: 0509f0b626f11384f351bc0a2f12199dbf6f89df (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
---
title: PHP Database Migrations
published: 2018-06-19
modified: 2018-06-25
---

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 
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](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:

    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](https://git.tuxed.net/fkooman/php-sqlite-migrate/about/). 
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](../about.html) know what you think!