A whale jumping out of the water

Using PHP and MySQL with Docker

Recently, I’ve been do­ing some work with con­tain­ers. For the unini­ti­ated, a con­tainer is like a vir­tual ma­chine be­cause your ap­pli­ca­tion is run in an iso­lated, con­sis­tent en­vi­ron­ment. Yet, this en­vi­ron­ment is more light­weight than a vir­tual ma­chine. It uti­lizes the un­der­ly­ing op­er­at­ing sys­tem rather than run­ning an en­tire op­er­at­ing sys­tem for each ap­pli­ca­tion. Thus, con­tain­ers are cheap-ish to spin up and easy to share.

The most com­mon plat­form for cre­at­ing con­tain­ers is Docker so that’s what I’ll be us­ing here. You can in­stall Docker from this link. Warning though: If you have Windows Home Edition, you will need Windows Subsystem for Linux Version 2 to run the lat­est ver­sion of Docker. Those on *nix sys­tems should be able to just in­stall the soft­ware.

Database Setup

Let‚Äôs start by set¬≠ting up the data¬≠base. For this part of the tu¬≠to¬≠r¬≠ial, I‚Äôm as¬≠sum¬≠ing you have some knowl¬≠edge of SQL; how¬≠ever, you should just be able to blindly copy and paste in the text. I‚Äôm putting all my work in a folder called tu¬≠to¬≠r¬≠ial. Also, you will have to make a few more fold¬≠ers and Ô¨Āles to fol¬≠low along to the end‚ÄĒjust a fore¬≠warn¬≠ing.

To be¬≠gin, we are go¬≠ing to need an im¬≠age to form the base of our con¬≠tainer. In Docker, an im¬≠age is ba¬≠si¬≠cally a Ô¨Āle sys¬≠tem and some set¬≠tings. Each con¬≠tainer will get its own vir¬≠tual Ô¨Āle sys¬≠tem sep¬≠a¬≠rate from your ac¬≠tual one, and it is re¬≠made fresh every time a con¬≠tainer is spun up. If you want your con¬≠tain¬≠ers to ac¬≠cess per¬≠sis¬≠tent stor¬≠age or mount to your ac¬≠tual Ô¨Ālesys¬≠tem, you‚Äôll have to look into vol¬≠umes. I‚Äôm not go¬≠ing to use a vol¬≠ume here, but it‚Äôs some¬≠thing you should be aware of.

Now, Docker man­ages the life­cy­cle of a con­tainer, but it also acts as a pack­age man­ager for im­ages. Thus, we don’t have to make our own from scratch. Instead, we can use and ex­tend ex­ist­ing im­ages. For our needs here, we’ll ex­tend a ba­sic MySQL im­age, which can be found on the Docker Hub web­site. The web­site will give some ba­sic in­for­ma­tion about the im­age; we mostly care about the im­age name, the avail­able tags, and the en­vi­ron­ment vari­ables.

To cre¬≠ate a new im¬≠age, we‚Äôll need to cre¬≠ate a YAML Ô¨Āle, com¬≠monly known as a DockerÔ¨Āle. This Ô¨Āle con¬≠tains var¬≠i¬≠ous com¬≠mands to cre¬≠ate a con¬≠tainer. Don‚Äôt worry‚ÄĒI‚Äôm go¬≠ing to pro¬≠vide the Ô¨Āles needed for our ad¬≠ven¬≠ture, in¬≠clud¬≠ing this DockerÔ¨Āle, with some ex¬≠plana¬≠tory com¬≠ments. Also, the Docker web¬≠site pro¬≠vides a ref¬≠er¬≠ence if you would like to go into more de¬≠tail. This guide only pro¬≠vides a rel¬≠a¬≠tively high-level overview of Docker.

Here is the DockerÔ¨Āle it¬≠self.

FROM mysql:8.0

# Set an insecure password
ENV MYSQL_ROOT_PASSWORD=example

# Copy over our SQL queries
COPY ./mysql/init.sql /init.sql

# Startup MySQL and run the queries
CMD ["mysqld", "--init-file=/init.sql"]

And you‚Äôll need the req¬≠ui¬≠site SQL Ô¨Āle. I‚Äôve put both of these Ô¨Āles in a folder called mysql in our tu¬≠to¬≠r¬≠ial di¬≠rec¬≠tory.

CREATE DATABASE app;
USE app;

CREATE TABLE message (
    id INT NOT NULL AUTO_INCREMENT,
    message VARCHAR(50) NOT NULL,
    PRIMARY KEY(id)
);

INSERT INTO message (message)
VALUES
    ("Hello World"),
    ("A second message"),
    ("J.Cole went double platinum with no features");

Some Dockerfile Commands

I‚Äôll go ahead and ex¬≠plain a few of the com¬≠mon DockerÔ¨Āle com¬≠mands.

The FROM com­mand tells Docker which im­age to ex­tend. Here, we are build­ing on top of the MySQL im­age with tag 8.0. If you’re feel­ing ad­ven­tur­ous, try chang­ing the tag; the tu­to­r­ial should still work.

The ENV com¬≠mand sets‚ÄĒacts shocked‚ÄĒan en¬≠vi¬≠ron¬≠ment vari¬≠able. Environment vari¬≠ables, broadly speak¬≠ing, are dy¬≠nam¬≠i¬≠cally set val¬≠ues that af¬≠fect the be¬≠hav¬≠ior of run¬≠ning processes. In this case, we are set¬≠ting the pass¬≠word for the data¬≠base. Thus, if you want to log in with an ex¬≠ter¬≠nal tool like MySQL Workbench, use the pass¬≠word ex¬≠am¬≠ple and the de¬≠fault user¬≠name of root. You won‚Äôt need any pro¬≠grams other than Docker to com¬≠plete this tu¬≠to¬≠r¬≠ial, but data¬≠base tool¬≠ing should work with the con¬≠tainer¬≠ized data¬≠base.

The COPY com¬≠mand moves Ô¨Āles from your ac¬≠tual Ô¨Āle sys¬≠tem to the con¬≠tain¬≠er‚Äôs vir¬≠tual Ô¨Ālesys¬≠tem. Keep in mind, con¬≠tain¬≠ers are im¬≠mutable so we have to copy over Ô¨Āles or mount vol¬≠umes dur¬≠ing the cre¬≠ation step. So, what are we copy¬≠ing over here? It‚Äôs just a SQL Ô¨Āle to pop¬≠u¬≠late a table. Obviously, out of the box, the data¬≠base will be a blank slate. I went ahead and added some records as part of the data¬≠base ini¬≠tial¬≠iza¬≠tion process. This tech¬≠nique is prob¬≠a¬≠bly not how you ac¬≠tu¬≠ally do this in a real en¬≠vi¬≠ron¬≠ment, but it keeps the tu¬≠to¬≠r¬≠ial sim¬≠ple.

Finally, the CMD com¬≠mand tells the con¬≠tainer what to run once the con¬≠tainer starts up. Here, I am over¬≠rid¬≠ing the mysql‚Äôs im¬≠age de¬≠fault be¬≠hav¬≠ior be¬≠cause we need to add a Ô¨āag to the com¬≠mand. The ‚Äďinit-Ô¨Āle Ô¨āag al¬≠lows us to run a Ô¨Āle con¬≠tain¬≠ing SQL queries af¬≠ter the data¬≠base ini¬≠tial¬≠izes, which is ex¬≠actly what we want.

This Ô¨Āle by it¬≠self does noth¬≠ing. We‚Äôll have to build the DockerÔ¨Āle into an im¬≠age and run it‚ÄĒbut that‚Äôll come later.

PHP Setup

Cool‚ÄĒwe have a data¬≠base DockerÔ¨Āle. Let‚Äôs do some¬≠thing with it by cre¬≠at¬≠ing a sim¬≠ple PHP page, which will in¬≠volve spin¬≠ning up a sec¬≠ond con¬≠tainer. This pat¬≠tern will get fa¬≠mil¬≠iar. You will spend some amount of time con¬≠Ô¨Āg¬≠ur¬≠ing Docker con¬≠tain¬≠ers for all the com¬≠po¬≠nents of your ap¬≠pli¬≠ca¬≠tion.

The PHP Ô¨Āle it¬≠self cre¬≠ates an html table dis¬≠play¬≠ing the con¬≠tents of the data¬≠base table‚ÄĒpretty much in a one-to-one fash¬≠ion. Most of this Ô¨Āle is html. You don‚Äôt have to un¬≠der¬≠stand all the de¬≠tails of the PHP part, but it‚Äôs just query¬≠ing our data¬≠base.

<!doctype html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <title>Docker Tutorial</title>
    <meta name="description" content="Learn how to use Docker with PHP">
    <meta name="author" content="Matthew Parris">
</head>

<body>
    <h1>Docker Tutorial</h1>
    <div class=".db-table">
        <table>
            <tr>
                <th>Id</th>
                <th>Message</th>
            </tr>
            <?php
            $user = 'root';
            $pass = 'example';

            try {
                $dbh = new PDO('mysql:host=db;port=3306;dbname=app', $user, $pass);
                foreach ($dbh->query('SELECT * from message') as $row) {
                    $html = "<tr><td>${row['id']}</td><td>${row['message']}</td></tr>";
                    echo $html;
                }
                $dbh = null;
            } catch (PDOException $e) {
                print "Error!: " . $e->getMessage() . "<br/>";
                die();
            }
            ?>
        </table>
    </div>
</body>

</html>

An in¬≠ter¬≠est¬≠ing thing to point out here is the data¬≠base con¬≠tain¬≠er‚Äôs host¬≠name. It‚Äôs the same name as the con¬≠tainer. Thus, you don‚Äôt have to Ô¨Āg¬≠ure out what IP the con¬≠tainer has been as¬≠signed. Some DNS magic is mak¬≠ing our lives eas¬≠ier be¬≠hind the scenes.

Now, let‚Äôs look at the Docker Ô¨Āle. It‚Äôs sim¬≠i¬≠lar to the one from ear¬≠lier‚ÄĒso I‚Äôm not go¬≠ing to re¬≠view the syn¬≠tax again. We are go¬≠ing to build from the base im¬≠age found at this link. One thing to note is that we have to in¬≠stall a PHP ex¬≠ten¬≠sion for PDO to es¬≠tab¬≠lish a con¬≠nec¬≠tion to our MySQL data¬≠base. Luckily, the base PHP im¬≠age pro¬≠vides some util¬≠ity scripts to work with these ex¬≠ten¬≠sions. It‚Äôs an easy thing to im¬≠ple¬≠ment but could be eas¬≠ily over¬≠looked.

FROM php:7.4-cli

# Move our PHP file into the container
COPY ./php/index.php /usr/src/app/index.php

# Make things easier if you shell in
WORKDIR /usr/src/app

# Our PHP will be running on port 8000
EXPOSE 8000

# Install the PDO MySQL extension so we can database
RUN docker-php-ext-install pdo_mysql

# Set up a web server
CMD ["php", "-S", "0.0.0.0:8000"]

To note, I‚Äôve placed both this DockerÔ¨Āle as well as the PHP Ô¨Āle in a folder called php. You‚Äôll need to be care¬≠ful about the di¬≠rec¬≠tory struc¬≠ture be¬≠cause it will mat¬≠ter for the next step. Things will crash if you screw up ūüôÉ

Docker Compose

Phew‚ÄĒwe‚Äôre al¬≠most there. We have our im¬≠ages and could im¬≠per¬≠a¬≠tively use the Docker CLI to run the con¬≠tain¬≠ers and net¬≠work them to¬≠gether. But that‚Äôs no fun and is a pain to man¬≠age. Instead, I‚Äôm go¬≠ing to scrib¬≠ble up a Docker Compose Ô¨Āle.

Docker Compose is an ab¬≠strac¬≠tion on top of Docker to Ô¨Āre up a set of con¬≠tain¬≠ers, vol¬≠umes, net¬≠works, and other en¬≠vi¬≠ron¬≠ment stuff. In other words, it is ba¬≠si¬≠cally a de¬≠clar¬≠a¬≠tive way of in¬≠ter¬≠fac¬≠ing with Docker. We can cre¬≠ate a sin¬≠gle YAML Ô¨Āle to spin an en¬≠vi¬≠ron¬≠ment up and down.

version: '3.7'
services:
  db:
    build:
      context: .
      dockerfile: ./mysql/Dockerfile.yaml
    image: tutorial-db
    restart: always
    ports:
      - 3306:3306
  app:
    build:
      context: .
      dockerfile: ./php/Dockerfile.yaml
    image: tutorial-php
    restart: always
    ports:
      - 8000:8000

Run the Containers

Awesome. We now have every­thing we need. Let’s run this thing by telling Docker Compose to spin up our con­tain­ers.

docker-com­pose up -d

You should then be able to nav­i­gate to lo­cal­host:8080 and see the page. It’s not glo­ri­ous, but there should be an html table with some rows pop­u­lated.

Of course, for more re­al­is­tic sce­nar­ios, you aren’t go­ing to have a sin­gle PHP page that con­nects to a pre-pop­u­lated data­base. Your app will likely be split into com­po­nents run­ning as their own mi­croser­vices. Alternatively, you might be tran­si­tion­ing a mono­lithic ap­pli­ca­tion over to a con­tainer. These use cases are ob­vi­ously more com­pli­cated than this silly ex­am­ple, but the point was to fo­cus on Docker. Many of the con­cepts used here will be ap­plic­a­ble to larger pro­jects. Anyway, I hope you learned some­thing.