DEV Community

Cover image for init/seed a MySQL database from a docker-compose file
yactouat
yactouat

Posted on • Updated on

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

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 create and seed a database programmatically if some data 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

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;
Enter fullscreen mode Exit fullscreen mode

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

version: "3.9"

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
Enter fullscreen mode Exit fullscreen mode

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 !

Discussion (0)