Setting up dockerized cron jobs: example using PHP and MySQL

<?php

echo "connecting to DB".PHP_EOL;

if( in_array ('pdo_mysql', get_loaded_extensions())) {
    // ! this code is bad, credentials are not read from a file or from env variables !
    $dsn = "mysql:host=db;dbname=test_db";
    $opt = [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC];
    echo "connected to DB".PHP_EOL;
    try {
        $connection = new PDO($dsn, "root", '', $opt);

        $val = 'testStr';
        // nothing is dynamic here, this code really sucks !
        $sql = 'INSERT INTO test_table(test_field) VALUES(:val)';   
        $statement = $connection->prepare($sql);
        $statement->execute([
            ':val' => $val
        ]);   

    } catch (\PDOException $pdoe) {
        echo $pdoe->getMessage();
    }
}

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` MEDIUMINT NOT NULL AUTO_INCREMENT,
  `test_field` varchar(255)
) ENGINE=InnoDB;

# run the test script every minute and output whatever it echoes in a log file
# using `>` instead of `>>` to append a new line at each cron iteration
* * * * * /usr/local/bin/php /cron_scripts/test_cron.php >> /cron_scripts/test_cron.log 2>&1

FROM php:8.1-cli

# installing cron package
RUN apt-get update && apt-get -y install cron

# installing PHP PDO extension to talk to MySQL
RUN docker-php-ext-install pdo_mysql

# putting our test PHP script somewhere in the filesystem
RUN mkdir /cron_scripts
WORKDIR /cron_scripts
COPY test_cron.php /cron_scripts

# creating the log file that will be written to at each cron iteration
RUN touch test_cron.log

# copy the crontab in a location where it will be parsed by the system
COPY ./crontab /etc/cron.d/crontab
# owner can read and write into the crontab, group and others can read it
RUN chmod 0644 /etc/cron.d/crontab
# running our crontab using the binary from the package we installed
RUN /usr/bin/crontab /etc/cron.d/crontab

# persisting db data in volume
volumes:
  db-vol:

services:

  # We have 4 services: the database, the db seeder, a cron that writes in db, and phpmyadmin to see the results of our running cron in a friendly UI

  db:
    image: mysql:latest
    container_name: db
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: "true"
      # this is the name of our database, that will be created automatically
      MYSQL_DATABASE: test_db
    restart: unless-stopped
    volumes:
      - db-vol:/var/lib/mysql
    ports:
      - "3306:3306"

  # we'll use a seeder container to create our test table, that our scheduled PHP script will write to
  db_seeder:
    image: mysql:latest
    # we copy our table creation script into the container
    volumes:
      - ./table.sql:/table.sql
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: "true"
    # we give the db time to initialize then we import our SQL script
    entrypoint: [ "bash", "-c", "sleep 10 && mysql --user=root --host=db --port=3306 test_db < /table.sql && exit"] 
    depends_on:
      - db

  # this is the container that will execute our cron
  cron:
    build:
      context: ./
      dockerfile: ./Dockerfile
    # run crond as main process of container
    entrypoint: [ "bash", "-c", "cron -f"] 
    depends_on:
      - db_seeder

  # our nice UI to browse our test table
  phpmyadmin:
      image: phpmyadmin:latest
      restart: unless-stopped
      ports:
          - 8080:80
      environment:
          # we specify that we connect to an arbitrary server with the flag below
          # "arbitrary" means you're able to specify which database server to use on login page of phpmyadmin      
          - PMA_ARBITRARY=1
      depends_on:
        - db_seeder