Uncategorized

Generating a second MYSQL database for unit testing with Docker Compose

When working with PHPUnit I love to have a separate database to run tests in isolation. For best results, this also involves trying really hard to make sure my DB seeding is up to date and as robust as possible. When a project is seeded, it should provide all the testing variations required.

Locally I usually use a Docker MYSQL container to run the local DBs. When docker-compose up is run there is a single default DB created:

  mysql:
    image: mysql:5.7
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: "secret"
      MYSQL_DATABASE: "database"
      MYSQL_USER: "username"
      MYSQL_PASSWORD: "secret"
    networks:
      - coolnetwork

The credentials are all editible within the environment section of the container definition.

Sadly there is no simple way to spin up a second DB here, so we need to run a SQL script on boot.

Adding boot script

To make sure a script is run automatically on container creation, it needs to be placed inside a folder on the container called docker-entrypoint-initdb.d. The best way to do this is to create a volume and map a local file to this folder:

  mysql:
    image: mysql:5.7
    ports:
      - "3306:3306"
    volumes:
      - ./docker/testing-db.sql:/docker-entrypoint-initdb.d/testing-db.sql
    environment:
      MYSQL_ROOT_PASSWORD: "secret"
      MYSQL_DATABASE: "database"
      MYSQL_USER: "username"
      MYSQL_PASSWORD: "secret"
    networks:
      - atomicnet

So in the above, ./docker/testing-db.sql will be copied to /docker-entrypoint-initdb.d/testing-db.sql and run when the container is booted.

The last step is to populate this .sql file. Create it somewhere in your project (for example “./docker/testing-db.sql“) and add this SQL command:

CREATE DATABASE IF NOT EXISTS `testing`;

This will then create the a DB called testing on boot.

Obviously, if you aren’t using the same filename or path, make sure you update the reference inside your docker-compose file.