Docker: adding a MariaDB container to my PHP & Nginx ones

G'day:

I'm pretty much just noting down how I've progressed my PHP8 test app in this one (see PHP: returning to PHP and setting up a PHP8 dev environment and other articles around this date tagged with the PHP8 label, around this date). I need a DB added to the PHP8 and Nginx containers I already have, for the next bit of stuff I want to look at.

---

docker/docker-compose.yml

I've added a mariadb service, and set some environment variables in the PHP8 service as well:

version: "3"
services:
  nginx:
  	# […]

  php:
    build:
      context: php
      dockerfile: Dockerfile

    environment:
      - MARIADB_DATABASE=${MARIADB_DATABASE}
      - MARIADB_USER=${MARIADB_USER}
      - MARIADB_PASSWORD=${MARIADB_PASSWORD}

    stdin_open: true
    tty: true

    volumes:
      - ..:/var/www

  mariadb:
    build:
      context: mariadb
      dockerfile: Dockerfile

    environment:
      - MARIADB_ROOT_PASSWORD=${MARIADB_ROOT_PASSWORD}
      - MARIADB_DATABASE=${MARIADB_DATABASE}
      - MARIADB_USER=${MARIADB_USER}
      - MARIADB_PASSWORD=${MARIADB_PASSWORD}

    ports:
      - "3382:3306"

    stdin_open: true
    tty: true

    volumes:
      - mariaDbData:/var/lib/mariadb

volumes:
  mariaDbData:

Those env vars are ones the MariaDB image docs on Dockerhub mandate. I'm also passing them into the PHP container so that it doeesn't need them recorded anywhere.

---

docker/.env

COMPOSE_PROJECT_NAME=php8

MARIADB_DATABASE=db1
MARIADB_USER=user1

# the following are to be provided to `docker-compose up`
MARIADB_ROOT_PASSWORD=
MARIADB_PASSWORD=

The only notable thing here is that - because this file is going into source control - I am not specifying the passwords; I'm just signifiying they need to exist.

---

docker/mariadb/Dockerfile

FROM mariadb:latest

COPY ./docker-entrypoint-initdb.d/ /docker-entrypoint-initdb.d/

CMD ["mysqld"]

EXPOSE 3306

And in ./docker-entrypoint-initdb.d/ I have these:

# docker/mariadb/docker-entrypoint-initdb.d/1.createAndPopulateTestTable.sql

USE db1;

CREATE TABLE test (
    id INT NOT NULL,
    value VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,

    PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO test (id, value)
VALUES
    (101, 'Test row 1'),
    (102, 'Test row 2')
;

ALTER TABLE test MODIFY COLUMN id INT auto_increment;

# docker/mariadb/docker-entrypoint-initdb.d/2.createAndPopulateNumbersTable.sql
USE db1;

CREATE TABLE numbers (
    id INT NOT NULL,
    en VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    mi VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,

    PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO numbers (id, en, mi)
VALUES
    (1, 'one', 'tahi'),
    (2, 'two', 'rua'),
    (3, 'three', 'toru'),
    (4, 'four', 'wha'),
    (5, 'five', 'rima'),
    (6, 'rima', 'ono')
;

ALTER TABLE numbers MODIFY COLUMN id INT auto_increment;

I'm seeding the DB with some test data. Any files dropped into that /docker-entrypoint-initdb.d/ directory in the image will be picked up by the MariaDB process when it first creates the DB (see the docs for the image again: Docker hub › MariaDB › Initializing a fresh instance).

---

Shell scripts

Because this rig requires one to pass passwords to docker-compose up, I've created a coupla shell scripts to remind me to do it right:

#!/bin/bash
# docker/bin/rebuildContainers.sh

# usage
# cd to directory containing docker-compose.yml
# bin/rebuildContainers.sh [DB root password] [DB user password]
# EG:
# cd ~/src/php8/docker
# bin/rebuildContainers.sh 123 1234

clear; printf "\033[3J"
docker-compose down --remove-orphans --volumes
docker-compose build --no-cache
MARIADB_ROOT_PASSWORD=$1 MARIADB_PASSWORD=$2 docker-compose up --force-recreate --detach

#!/bin/bash
# docker/bin/restartContainers.sh

# usage
# cd to directory containing docker-compose.yml
# bin/restartContainers.sh [DB root password] [DB user password]
# use same passwords as when initially calling rebuildContainers.sh

# EG:
# cd ~/src/php8/docker
# bin/restartContainers.sh 123 1234

clear; printf "\033[3J"
docker-compose stop
docker-compose up --detach nginx
MARIADB_PASSWORD=$2 docker-compose up --detach php
MARIADB_ROOT_PASSWORD=$1 MARIADB_PASSWORD=$2 docker-compose up --detach mariadb

This just saves some typing. In all honesty I am using 123 and 1234 for the respective passwords, but it doesn't matter. It's a good practice to not have passwords anywhere in source code, and this seems a reasonable way to me to make sure the values end up being where they need to be.

---

readme.md

I'll spare you the content here (the heading there is linked to the file), all I did to that was update my installation instructions to use the docker/bin/rebuildContainers.sh script instead of individual statements, and added a section about docker/bin/restartContainers.sh.

---

Test

Where would I be without a test. I've thrown a quick one together to test that the test data is there. And I will run this in conjunction with the rest of the tests, to make sure I have not caused any regressions.

// test/integration/DbTest.php

namespace adamcameron\php8\test\integration;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DriverManager;
use PHPUnit\Framework\TestCase;
use \stdClass;

/** @testdox Tests the stub DB */
class DbTest extends TestCase
{

    /** @testdox it can fetch records from the test table */
    public function testFetchRecords()
    {
        $expectedRecords = [
            ["id" => 101, "value" => "Test row 1"],
            ["id" => 102, "value" => "Test row 2"]
        ];

        $connection = $this->getDbalConnection();
        $result = $connection->executeQuery("SELECT id, value FROM test ORDER BY id");

        $actualRecords = $result->fetchAllAssociative();

        $this->assertEquals($expectedRecords, $actualRecords);
    }

    private function getDbalConnection() : Connection
    {
        $parameters = $this->getConnectionParameters();
        return DriverManager::getConnection([
            'dbname' => $parameters->database,
            'user' => $parameters->username,
            'password' => $parameters->password,
            'host' => $parameters->host,
            'port' => $parameters->port,
            'driver' => 'pdo_mysql'
        ]);
    }

    private function getConnectionParameters() : stdClass
    {
        return (object) [
            "host" => "mariadb",
            "port" => "3306",
            "database" => getenv("MARIADB_DATABASE"),
            "username" => getenv("MARIADB_USER"),
            "password" => getenv("MARIADB_PASSWORD")
        ];
    }
}

All pretty straight forward. Note how I'm reading the DB info from the environment variables in getConnectionParameters. Take my word for it for now on the DB-handling code. I'll get to that in a different article.

---

That's it. Nothing insightful. I'm just documenting what I've done and why.

Righto.

--
Adam