Automatic Version Control with MySQL Triggers

5 min readApr 12, 2021

Version control and versioning in Symfony and Doctrine based on SQL-Triggers: Our NetBrothers VersionBundle creates versions for records in the SQL-database on the fly.

After our switch from Zend framework to Symfony and from the Propel ORM to Doctrine we were faced with a problem: Versioning is a built-in feature in Propel and is not in Doctrine.
Our existing projects had version control implemented deep down in their foundations and as developers we had gotten used to the comfort of versioning running automatically in the background. So how are we going to deal with our existing projects? And how will we go about versioning in future projects?

As a solution to this problem we developed a Symfony bundle based on versioning in Propel – our NetBrothers VersionBundle.

Concept

The VersionBundle manipulates the database: the base are all tables with a cell called `version`. For each of these tables a version table is created with the same columns as the original table. SQL-triggers are then created for the original tables. These triggers generate a copy of the affected record in the version table with every Insert and Update. The value of the `version` column of the affected record is incremented after every Update.

The benefits of this concept are obvious:
1. Developers don’t actively have to take care of versioning. It suffices to integrate the version control during the creation of entities.
2. The version control is executed inside the MySQL database which greatly reduces the load of the PHP engine.
3. Even if records are changed directly in MySQL the version is generated.

Installation and Setup

The NetBrothers VersionBundle is published in GitHub. You can take a look at the source code over there.
Thanks to the Packagist you can integrate the bundle into your Symfony Project via composer. Just follow the instructions in the README.

It is important to register the configuration of the VersionBundle in the configuration path of your Symfony project. With the default settings Doctrine ignores the table in which the executed migrations are stored. Beyond that it ignores the colums id, version, created_at and updated_at when comparing the original and the version table.

Additionally Doctrine needs to be told to ignore the version tables when creating migrations. To do this you have to add a schema_filter in the doctrine.yaml. An example for a schema filter that excludes version tables can be found in the doctrine_example.yaml. If the schema filter is not set to ignore version tables, automatically generated migrations will delete the version tables.

Usage

The usage of the VersionBundle is as easy as it gets. First you have to make sure that the table you want to version has a column called version. To do so you can simply use the existing Trait VersionColumn in your entity as pictured in the example.

After that create a migration and migrate the data base. This will create the column version in your table. By running

php bin/console netbrothers:version

in the terminal you create the version table and add triggers to the original table.

You can learn about other options and how to use them in the README.

Migrations

From time to time it is necessary to make changes to the database. When changes are made to tables that have existing version tables, you need to add these changes to the version table as well. This has to be done manually in the migration script. Always make sure to keep the same order. When the migration is migrated re-create the triggers by running:

php bin/console netbrothers:version --create-trigger

What is happening?

The workflow always stays the same:

First it is analysed which tables have a version column.
It is checked whether each of these original tables have a version table
a. If a table does not have a version table yet the version table is created and the triggers are added to the original table.
b. If the table has an existing version table, the tables are compared to find
possible differences e.g. in the type of the columns or the default value. If a
difference is found, an error will be thrown. Otherwise — which should be the default case — the existing triggers on the original table are dropped and new triggers are added.
All version tables are checked whether they have an original table belonging to them.
If none is found for a version table, potential existing triggers are dropped.