Setup your database with Docker

Yeah, I wanted to do one of those for a long time 😁

So, we have to stay at home for the moment, I decided to make a “walkthrough” about something I used sometimes and I couldn’t remember all the commands. So, let’s build our MySQL database within a Docker container 🐳 !

Get the Docker image

#

I will be using the mysql image from the Docker hub.

1docker pull mysql

Run the container

#

For further details, you can read the official documentation for the image on Docker hub. I will just go through the basic steps I use.

1docker run --name my_container -e MYSQL_ROOT_PASSWORD=toor -d mysql:latest

• --name : setup a name for our container
• -e : setup environment variables
  • MYSQL_ROOT_PASSWORD : need explainations ?
• -d : run in deamon mode
• mysql:latest : use the mysql image in the latest version

Administrate the database

#

Once the container is running in background, we have to connect to it :

1docker exec -it my_container /bin/bash

Here, we connect to the running container and pop a shell in interactive mode. We have to connect to the database :

1mysql -uroot -ptoor

Now we can start to use SQL commands for our database.

Create the database

#

1CREATE DATABASE my_db;
2USE my_db

Create the table

#

1CREATE TABLE compotes (id INT, name TEXT, note INT);

Manipulate the table

#

INSERT

#

We want now to add some data in our table :

1INSERT INTO compotes (id, name, note) VALUES (0, "Apple", 3);
2INSERT INTO compotes (id, name, note) VALUES (1, "Apple & banana", 5), (2, "Apple & pear", 4), (3, "Apple & apple", 3);

UPDATE

#

Oh no, I f’cked up my data ! Apple & banana is not that tasty !

1UPDATE compotes SET (note=4) WHERE id=1;

Better.

DELETE

#

So drunk I added apple & apple in my table sight

1DELETE FROM compotes WHERE name="Apple & apple";

OK, we are good.

SELECT

#

I want to see my beautiful compotes now !

1SELECT * FROM compotes;
2
3SELECT name, note FROM compotes;
4
5SELECT * FROM compotes WHERE id > 1 AND note = 3;

Use SQL script

#

We don’t want to re-type all the database when building a new container !

SQL scripts are cool for that :

 1# populate.sql
 2# SQL script to build the compotes table in our database
 3
 4create database my_db;
 5use my_db
 6
 7create table compotes(id int,
 8	name text,
 9	note int);
10
11insert into compotes(id, text, note) values
12	(0, "Apple", 3),
13	(1, "Apple & banana", 5),
14	(2, "Apple & pear", 4);

And execute the script on the running container :

1docker exec -i my_container sh -c 'exec mysql -uroot -ptoor' < populate.sql