Tutorials
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