Sunday 22 January 2023

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