Hi everyone 👋
In this short one, we're gonna look at how to seed a MySQL database directly from
- some familiarity with Docker and Docker Compose
- some familiarity with MySQL or MariaDB
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
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
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
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 < /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 !