init/seed a MySQL database from a docker-compose file

#docker #mysql #bash

Hi everyone 👋

In this short one, we're gonna look at how to seed a MySQL database directly from docker-compose.

prerequisites

some familiarity with Docker and Docker Compose
some familiarity with MySQL or MariaDB

the problem

You find yourself in a situation where you need to run some script or even dedicated code from within your application to do some checks in order to interact with a table or create and seed a database programmatically if some data or schemas is not present in your database ?

In my opinion, that's an issue as:

your database and your application become tightly coupled
you're wasting precious time setting the aforementioned up

Unfortunately, and this is openly opinionated, this tight coupling has been made mainstream with a lof of web frameworks providing powerful data migrations systems.

a solution: use a containerized seeder service

The idea is to create a container that only runs once to seed the db and then dies not to be restarted when you do a docker compose up; the reason why we're creating a separate service instead of running the command directly in the main database docker service is that we dont want the seeding script to be run if, let's say, we implemented data persistency between container restarts for this service...

Let's illustrate this !

First, let's write some really simple SQL that will create a table if not exists:

CREATE TABLE IF NOT EXISTS `test_table` (
  `test_field` varchar(255) DEFAULT NULL,
  `test_field2` int(11) DEFAULT 0
) ENGINE=InnoDB;

... now on to our docker-compose file:

volumes:
  db-vol:

services:

  # We have 3 services: the database, the db seeder, and phpmyadmin

  db:
    image: mysql:latest
    container_name: db
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: "true"
      MYSQL_DATABASE: test_db
    restart: unless-stopped
    volumes:
      - db-vol:/var/lib/mysql
    ports:
      - "3306:3306"

  db_seeder:
    image: mysql:latest
    volumes:
      - ./db.sql:/db.sql
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: "true"
    entrypoint: [ "bash", "-c", "sleep 10 && mysql --user=root --host=db --port=3306 test_db < /db.sql && exit"] 
    depends_on:
      - db

  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

In this file, the first service is db, this is our main database in our application stack that restarts unless stopped; it has a volume called db-vol which will allow us to persist data between container restarts.

The second service is db_seeder, this is this container that has copied in it our seeding SQL script (under volumes); it is explicitely set to depend on the main database service (with the depends_on directive), as we can not run a SQL script against a db if the db does not exist yet right ?
When the seeder container is created, a bash instruction is run in it that says:

sleep a little (to make sure all SQL connections had time to be set up)
connect to the main database service: mysql --user=root --host=db --port=3306
run a sql script against the specified database, our test_db: test_db < /db.sql
exit the shell

Our 3rd service is a phpmyadmin GUI that lets you check easily what's inside your db and if everything works as expected (user root with host db in this example).

Hope that helps ! see ya !