Branchable MySQL: Managing multiple dev environments

When teams start to grow, having a single dev environment becomes an issue. People start stepping on each others toes. A common problem is that two people want to apply incompatible migrations on the database. That problem is impossible to fix if folks are working on parallel branches. If we can have a database for each branch of a project, that will remove much of the pain of having multiple devs applying changes to the db.

There are already projects that solve this problem: PlanetScale and Neon.

A common case where this problem arises is when two devs want to add a column to the same table.

We have a people table in the database. One of the devs wants to add the last_name column and the other one wants to add the address.

Dev1's code thinks the table will have 3 columns after he applies his operation: id, name, last_name.

Dev2's code also thinks the table will have 3 columns: id, name, address.

In reality the table will have 4 columns. So neither of them will be able to run their code unless they talk to each other and figure out how to make this work.

This is far from ideal.

What we want instead, is that each one of them can develop their features independently.

They both apply to the same table, but each table lives on an instance that was 'replicated' from the original.

How can we implement the ideal case?

MySQL writes data (by default) to the directory /var/lib/mysql/data.

We can use an Union filesystem. And configure MySQL to use a different directory to read and write data.

That way we can have a feature/user-last-name 'branch' read and write data from a directory like /app/user-last-name/mysql/data.

And a feature/user-address 'branch' read and write data from a directory like /app/user-address/mysql/data.

Those branches can be mounted using fuse-overlayfs by executing the following commands:

# Directory /app/base contains data from the original branch

fuse-overlayfs -o lowerdir=/app/base,upperdir=/app/user-last-name,workdir=/tmp/user-last-name overlayfs /app/user-last-name

fuse-overlayfs -o lowerdir=/app/base,upperdir=/app/user-address,workdir=/tmp/user-address overlayfs /app/user-address

This means both 'branches' of the database are able to coexist and have different schemas during their lifetime.

Experimenting with a use case

I had this idea in my head for months. I finally convinced myself that it was worth a shot.

I decided to do a little implementation using Docker and python FastAPI. Exposing a simple interface so that it's easy to create and delete branches.

The project is live on github branchable-mysql.

The container image is published on Docker Hub branchable-mysql.

To start using the image let's create a docker-compose.yml file.

version: "3"

services:
  mysql:
    image: mliezun/branchable-mysql
    platform: linux/amd64
    privileged: true
    restart: always
    volumes:
      - appdata:/app/

volumes:
  appdata:

Then you can execute docker-compose up and the MySQL server should start running.

After that, connect easily to the db docker compose exec mysql mysql -uroot -h127.0.0.1 --skip-password -P33061. You should enter to an interactive mysql console.

Let's create an initial schema, a table and insert some data so that we can see how branching works. On the console that we just opened execute:

mysql> create schema s1;
Query OK, 1 row affected (0.01 sec)

mysql> use s1;
Database changed
mysql> create table people (id int primary key auto_increment, name varchar(255) not null);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into people select 0, 'Miguel';
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from people;
+----+--------+
| id | name   |
+----+--------+
|  1 | Miguel |
+----+--------+
1 row in set (0.00 sec)

That's enough for now, we're ready to start creating branches.

On a separate terminal, without closing the previous mysql interactive console, execute:

docker compose exec mysql /app/scripts/create_branch.sh base feature/user-last-name

{"branch_name":"feature/user-last-name","base_branch":"base","port":33062}

Now you can login to the new database branch using port 33062 docker compose exec mysql mysql -uroot -h127.0.0.1 --skip-password -P33062

mysql> use s1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table people add column last_name varchar(255) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from people;
+----+--------+-----------+
| id | name   | last_name |
+----+--------+-----------+
|  1 | Miguel |           |
+----+--------+-----------+
1 row in set (0.00 sec)

In a new terminal we can create a another branch:

docker compose exec mysql /app/scripts/create_branch.sh base feature/user-address

{"branch_name":"feature/user-address","base_branch":"base","port":33063}

Then connect using port 33063 docker compose exec mysql mysql -uroot -h127.0.0.1 --skip-password -P33063

mysql> use s1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table people add column last_name varchar(255) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from people;
+----+--------+
| id | name   |
+----+--------+
|  1 | Miguel |
+----+--------+
1 row in set (0.00 sec)

mysql> alter table people add column address varchar(255) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from people;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  1 | Miguel |         |
+----+--------+---------+
1 row in set (0.00 sec)

As you can see, we have 3 servers running at the same time, each one with different schemas.

This is great for local development and for having branch-aware dev environments.

Final thoughts

I hope you find this blogpost useful. If you want to start using branchable-mysql go ahead. If you encounter any issues please report them in the github repo or create a pull request.