Docker compose for PHP, NGINX and MYSQL

In this tutorial I'll show you how to create a PHP application that will connect to a MySQL database. To serve the php application, we'll use PHP-FPM and NGINX docker containers.

The purpose of this tutorial is to give you an idea on how to create the application with the dependencies it needs. For the real projects please use a framework like Symfony which will come loaded with all best practices.

Let's start with building the docker configuration for our project.

The application will need to know how to connect to the database, and also we'll need some configurations for each container on the runtime.

./docker/database-root.env

The database container requires to configure the ROOT user password. For this, we'll create a single environment file which will have only the password:

MYSQL_ROOT_PASSWORD=kisphp

./docker/database.env

The application will use a separate user to connect to the database. For this we'll create another file that we can share between the mysql docker container and the php-fpm container.

MYSQL_HOST variable is not necessary for the mysql container, but is necessary for the php container.

MYSQL_HOST=database
MYSQL_USER=kisphp_user
MYSQL_PASSWORD=kisphp_pass
MYSQL_DATABASE=kisphp_db

We'll include another container, so we can access the database directly, and so we can manage it easier if we want to. For this, we'll need to configure a phpmyadmin container that we'll expose on port 8080. But first, we create the configuration file for it.

./docker/phpmyadmin.env

PMA_HOST=database
PMA_USER=root
PMA_PASSWORD=kisphp
PMA_ARBITRARY=1

./docker/mysql/Dockerfile

For this tutorial, there is no need to define your own mysql docker image. But I'll keep the configurations here in case you need to make some changes to the mysql server, this is one simple way to do it.

FROM mysql:5.7

COPY mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.conf

./docker/mysql/mysqld.cnf

In the next file, usually you can add extra parameters to tweak your mysql configurations to meet your requirements.

# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

./docker/mysql/sql/10-schema.sql

On local environments, you might want to have a database created with some data in it. We'll use this sql file to do exactly that. This way we'll have a database with two tables and some data in each of them that we can use in our demo application.

-- phpMyAdmin SQL Dump
-- version 5.1.1
-- https://www.phpmyadmin.net/
--
-- Host: database
-- Generation Time: Dec 25, 2021 at 11:17 AM
-- Server version: 5.7.36
-- PHP Version: 7.4.27

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Table structure for table `offices`
--

CREATE TABLE `offices` (
   `id` int(10) UNSIGNED NOT NULL,
   `city` varchar(64) NOT NULL DEFAULT '',
   `phone` varchar(64) NOT NULL DEFAULT '',
   `address_line_1` varchar(255) NOT NULL DEFAULT '',
   `address_line_2` varchar(255) NOT NULL DEFAULT '',
   `state` varchar(64) NOT NULL DEFAULT '',
   `country` varchar(32) NOT NULL DEFAULT '',
   `postal_code` varchar(16) NOT NULL DEFAULT '',
   `territory` varchar(16) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `offices`
--

INSERT INTO `offices` (`id`, `city`, `phone`, `address_line_1`, `address_line_2`, `state`, `country`, `postal_code`, `territory`) VALUES
(1, 'San Francisco', '+1 000 000 0001', '100 Market Street', 'Suite 300', 'CA', 'USA', '94080', 'USA'),
(2, 'Boston', '+1 000 000 0002', '1550 Court Place', 'Suite 102', 'MA', 'USA', '02107', 'USA'),
(3, 'NYC', '+1 000 000 0003', '523 East 53rd Street', 'apt. 5A', 'NY', 'USA', '10022', 'USA'),
(4, 'Paris', '+33 00 000 0001', '43 Rue Jouffroy D\'abbans', '', '', 'France', '75017', 'EMEA'),
(5, 'Tokyo', '+81 00 000 0001', '4-1 Kioicho', '', 'Chiyoda-Ku', 'Japan', '102-8578', 'Japan'),
(6, 'Sydney', '+61 0 0000 0001', '5-11 Wentworth Avenue', 'Floor #2', '', 'Australia', 'NSW 2010', 'APAC'),
(7, 'London', '+44 00 0000 0001', '25 Old Broad Street', 'Level 7', '', 'UK', 'EC2N 1HN', 'EMEA');


-- --------------------------------------------------------

--
-- Table structure for table `employees`
--

CREATE TABLE `employees` (
  `id` int(10) UNSIGNED NOT NULL,
  `id_office` int(10) UNSIGNED NOT NULL,
  `reports_to` int(10) UNSIGNED DEFAULT NULL,
  `first_name` varchar(64) NOT NULL DEFAULT '',
  `last_name` varchar(64) NOT NULL DEFAULT '',
  `email` varchar(255) NOT NULL DEFAULT '',
  `job_title` varchar(64) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `employees`
--

INSERT INTO `employees` (`id`, `id_office`, `reports_to`, `first_name`, `last_name`, `email`, `job_title`) VALUES
(1002, 1, NULL, 'Diane', 'Murphy', 'dmurphy@example.com', 'President'),
(1056, 1, 1002, 'Mary', 'Patterson', 'mpatterso@example.com', 'VP Sales'),
(1076, 1, 1002, 'Jeff', 'Firrelli', 'jfirrelli@example.com', 'VP Marketing'),
(1088, 6, 1056, 'William', 'Patterson', 'wpatterson@example.com', 'Sales Manager (APAC)'),
(1102, 4, 1056, 'Gerard', 'Bondur', 'gbondur@example.com', 'Sale Manager (EMEA)'),
(1143, 1, 1056, 'Anthony', 'Bow', 'abow@example.com', 'Sales Manager (NA)'),
(1165, 1, 1143, 'Leslie', 'Jennings', 'ljennings@example.com', 'Sales Rep'),
(1166, 1, 1143, 'Leslie', 'Thompson', 'lthompson@example.com', 'Sales Rep'),
(1188, 2, 1143, 'Julie', 'Firrelli', 'jfirrelli@example.com', 'Sales Rep'),
(1216, 2, 1143, 'Steve', 'Patterson', 'spatterson@example.com', 'Sales Rep'),
(1286, 3, 1143, 'Foon Yue', 'Tseng', 'ftseng@example.com', 'Sales Rep'),
(1323, 3, 1143, 'George', 'Vanauf', 'gvanauf@example.com', 'Sales Rep'),
(1337, 4, 1102, 'Loui', 'Bondur', 'lbondur@example.com', 'Sales Rep'),
(1370, 4, 1102, 'Gerard', 'Hernandez', 'ghernande@example.com', 'Sales Rep'),
(1401, 4, 1102, 'Pamela', 'Castillo', 'pcastillo@example.com', 'Sales Rep'),
(1501, 7, 1102, 'Larry', 'Bott', 'lbott@example.com', 'Sales Rep'),
(1504, 7, 1102, 'Barry', 'Jones', 'bjones@example.com', 'Sales Rep'),
(1611, 6, 1088, 'Andy', 'Fixter', 'afixter@example.com', 'Sales Rep'),
(1612, 6, 1088, 'Peter', 'Marsh', 'pmarsh@example.com', 'Sales Rep'),
(1619, 6, 1088, 'Tom', 'King', 'tking@example.com', 'Sales Rep'),
(1621, 5, 1056, 'Mami', 'Nishi', 'mnishi@example.com', 'Sales Rep'),
(1625, 5, 1621, 'Yoshimi', 'Kato', 'ykato@example.com', 'Sales Rep'),
(1702, 4, 1102, 'Martin', 'Gerard', 'mgerard@example.com', 'Sales Rep');

-- --------------------------------------------------------

--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_leader` (`reports_to`),
  ADD KEY `employee_office` (`id_office`);

--
-- Indexes for table `offices`
--
ALTER TABLE `offices`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1703;

--
-- AUTO_INCREMENT for table `offices`
--
ALTER TABLE `offices`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- Constraints for table `employees`
--
ALTER TABLE `employees`
  ADD CONSTRAINT `employee_leader` FOREIGN KEY (`reports_to`) REFERENCES `employees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `employee_office` FOREIGN KEY (`id_office`) REFERENCES `offices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

./docker/php/Dockerfile

Let's build the PHP-FPM container and add some tools to it so it will better work with our application.

FROM php:7.4-fpm

COPY . /app

WORKDIR /app

RUN apt-get update \
&& apt-get install -y zip unzip libzip-dev default-mysql-client \
&& docker-php-ext-install zip pdo_mysql

# Install composer for the application dependencies
RUN curl -sS https://getcomposer.org/installer | php \
&& mv composer.phar /bin/composer

# Install project dependencies
RUN composer install -noa

By default, php-fpm will expose port 9000. You'll see it defined in our docker-compose.yml file later.

./docker/nginx/Dockerfile

Besides the php-fpm container, we'll need the nginx container as well, since this one will take care of the requests.

The NGINX container needs actually only the public directory from the application which in our case is /app/public. For this, we'll start building our docker image from the php-fpm one and we'll just copy the files from the public directory, including the index.php file.

FROM php-fpm as base

# -----------------------------------------------------------
FROM nginx

ENV UNAME=web

RUN apt-get update \
&& apt-get install -y curl vim mc tree jq \
# Cleanup
&& apt-get clean \
&& apt-get autoremove -y --purge

# copy nginx virtual host configuration for the prokect
COPY ./docker/nginx/site.conf /etc/nginx/conf.d/default.conf

WORKDIR /app

# copy public directory from the php-fpm docker image loaded here as base
COPY --from=base /app/public /app/public

./docker/nginx/site.conf

This is the file where you can configure your nginx server

server {
    # listen on all requests on port 80
    listen 80;

    # all hosts requests matches this application
    server_name _;

    # here is the public directory of your website (the application itself will be on /app directory)
    root /app/public;

    location / {
        try_files $uri /index.php$is_args$args;
    }

    # handle requests for images
    location ~* \.(jpg|jpeg|png|gif) {
        try_files $uri /index.php$is_args$args;
    }

    # handle requests for php files
    location ~ \.php$ {
        # pass the reuqest to the PHP-FPM container called "php-fpm" or port 9000
        fastcgi_pass php-fpm:9000;
        fastcgi_split_path_info ^(.+\.php)(/.*)$;
        include fastcgi_params;

        fastcgi_param SCRIPT_FILENAME $realpath_root$fastcgi_script_name;
        fastcgi_param DOCUMENT_ROOT $realpath_root;
    }
}

./composer.json

Since we use PHP, we'll use composer as a dependency manager and for this tutorial we'll require only two dependencies: twig/twig for the templates and kisphp/dbal for the database connection. There an optional third dependency for development only symfony/var-dumper.

{
    "require": {
        "twig/twig": "^3.3",
        "kisphp/dbal": "^0.2.0"
    },
    "require-dev": {
        "symfony/var-dumper": "^5.4"
    }
}

./public/index.php

This is the front controller of our application where we put all together:

  • loading dependencies
  • configure template engine
  • configure database connection
  • run the query to the database to retrieve the information
  • display the data
<?php

// use statements for dependencies classes
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\DriverManager;
use Kisphp\Db\KisphpDbal;
use Twig\Environment;
use Twig\Loader\FilesystemLoader;

// load autoloader
require_once __DIR__ . '/../vendor/autoload.php';

// Configure database connection ..
$config = new Configuration();
$parameteres = [
    'driver' => 'pdo_mysql',
    'host' => getenv('MYSQL_HOST'),
    'dbname' => getenv('MYSQL_DATABASE'),
    'user' => getenv('MYSQL_USER'),
    'password' => getenv('MYSQL_PASSWORD'),
    'charset'  => 'utf8',
    'driverOptions' => [
        1002 => 'SET NAMES utf8',
    ]

];
$connection = DriverManager::getConnection($parameteres, $config);
$kisphpDbal = new KisphpDbal($connection);
// .. end database connection

// Configure template engine ..
$loader = new FilesystemLoader(__DIR__ . '/../templates');
$twig = new Environment($loader);
// .. end template configuration


// Run a database query to get data from database
$query = $kisphpDbal->query("SELECT e.id, e.first_name, e.last_name, CONCAT(ee.first_name, ' ', ee.last_name) as reports_to_name, e.reports_to, e.job_title, o.city as office
FROM employees AS e
LEFT JOIN offices AS o ON (o.id = e.id_office)
LEFT JOIN employees AS ee ON (ee.id = e.reports_to)
");

/** @var User $tree */
$tree = [];

// load template and show data from database
echo $twig->render('homepage.twig', [
    'rows' => $query->fetchAllAssociative(),
]);

./templates/homepage.twig

Create the homepage template that will extend the main layout of our small website.

{% extends 'layout.twig' %}

{% block body %}
<table class="table table-condensed">
    <tr>
        <th>Name</th>
        <th>Office</th>
        <th>Reports To</th>
        <th>Job Title</th>
    </tr>
    {% for row in rows %}
    <tr>
        <td>{{ row.first_name }} {{ row.last_nName }}</td>
        <td>{{ row.office }}</td>
        <td>{{ row.reports_to }}</td>
        <td>{{ row.job_title }}</td>
    </tr>
    {% endfor %}
</table>
{% endblock %}

./templates/layout.twig

Create the layout template and load Bootstrap as frontend styling.

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
</head>
<body>

<div class="container">
{% block body %}
this block will be overwritten by the block defined in `homepage.twig` file
{% endblock %}
</div>

</body>
</html>

./docker-compose.yml

Now, that we have all the files configured, let's write our docker-compose file to glue all of them together.

version: '3'
services:
  database:
    # we build our own docker image with custom configurations
    build:
      context: docker/mysql
    # or we use the default mysql docker image with the default configuration
#    image: mysql:5.7
    # expose port 3306 so we can connect to the server with desktop applications like MySQL Workbench, DBeaver, Heidi SQL, etc
    ports:
    - 3306:3306
    env_file:
      - docker/database-root.env  # load the root user password (user root with this password will have full privileges)
      - docker/database.env       # load application specific user and database 
    volumes:
      - ./_data/mysql:/var/lib/mysql # create a local volume for the database storage so you won't loose the data if you delete the docker container
      - ./docker/mysql/sql/:/docker-entrypoint-initdb.d/ # load the sql files from here on database creation

  phpmyadmin:
    image: phpmyadmin
    restart: always
    ports:
      - 8080:80 # open http://localhost:8080 in your browser to access phpmyadmin console
    links:
      - database
    env_file:
      - docker/phpmyadmin.env # load environment variables necessary to create the connection to the database container

  php-fpm:
    # for your php application you need to create a docker image
    build:
      dockerfile: docker/php/Dockerfile
      context: "."
    image: php-fpm
    env_file:
      - docker/database.env # load database configurations to be able to connect to the database
      # here you can also have more files with env variables for specific purposes
    ports:
    - 9000
    volumes:
    - ./:/app # on local development you need to create this volume to load your local project so you can keep making changes to it
    # keep in mind, that the Dockerfile will copy the local project into docker context, will build the application and it will store it in the docker image, but with this option here, you'll overwrite the directory with the application from the container.

  php-nginx:
    build:
      dockerfile: docker/nginx/Dockerfile
      context: "."
    image: php-nginx
    ports:
    - 80:80 # open http://localhost in your browser to see the application running
    volumes:
    - ./:/app

./.gitignore

When you use git to store your project in a repository, you need to ignore some files that gets generated.

vendor
_data

./Makefile

We'll create a makefile to ease our work and to have some small automation on local environment

.PHONY: up clean

up:
	composer install -noa
	docker-compose up -d --build

clean:
	docker-compose stop
	docker-compose rm -f
	rm -rf _data/ vendor/

Once you have all those files created, run the following command to start the project:

make up

To stop and delete the docker containers, run the clean command

make clean