Follow me on Twitter, happy to take your suggestions on topics or improvements /Chris
TLDR; this is somewhat of a long read but there is mic drop in there I promise. There is a bit of misery and pain in the middle but we will come out victorius :)
This article is part of a series:
- Docker — from the beginning part I, this covers why Docker, the basic concepts and the commands we need to manage things like images and containers.
- Docker — from the beginning, Part II, this is about Volumes and how we can use volumes to persist data but also how we can turn our development environment into a Volume and make our development experience considerably better
- Docker — from the beginning, Part III, we are here
- Docker — from the beginning, Part IV, this is how we manage more than one service using Docker Compose ( this is 1/2 part on Docker Compose)
- Docker - from the beginning, Part V, this part is the second and concluding part on Docker Compose where we cover Volumes, Environment Variables and working with Databases and Networks
This the third part of our series. In this part, we will focus on learning how we work with Databases and Docker together. We will also introduce the concept of linking as this goes tightly together with working with Databases in a containerized environment.
In this article we will cover the following:
- Cover some basics about working with MySql , it’s always good to cover some basics on managing a database generally and MySql, particularly as this, is the chosen database type for this article
- Understand why we need to use a MySql Docker image , we will also cover how to create a container from said image and what environment variables we need to set, for it to work
- Learn how to connect to our MySql container , from our application container using linking, this is about realizing how to do basic linking between two containers and how this can be used to our advantage when defining the database startup configuration
- Expand our knowledge on linking , by covering the new way to link containers, there are two ways of doing linking, one way is more preferred than the other, which is deprecated, so we will cover how the new way of doing things is done
- Describe some good ways of managing our database , such as giving it an initial structure and seed it
Resources
Using Docker and containerization is about breaking apart a monolith into microservices. Throughout this series, we will learn to master Docker and all its commands. Sooner or later you will want to take your containers to a production environment. That environment is usually the Cloud. When you feel you've got enough Docker experience have a look at these links to see how Docker can be used in the Cloud as well:
- Containers in the Cloud Great overview page that shows what else there is to know about containers in the Cloud
- Deploying your containers in the Cloud Tutorial that shows how easy it is to leverage your existing Docker skill and get your services running in the Cloud
- Creating a container registry Your Docker images can be in Docker Hub but also in a Container Registry in the Cloud. Wouldn't it be great to store your images somewhere and actually be able to create a service from that Registry in a matter of minutes?
Working with databases in general and MySql in particular
With databases in general we want to be able to do the following:
- read , we want to be able to read the data by using different kinds of queries
- alter/create/delete , we need to be able to change the data
- add structure , we need a way to create a structure like tables or collections so our data is saved in a specific format
- add seed/initial data , in a simple database this might not be needed at all but in more complex scenario you would need some tables to be prepopulated with some basic data. Having a seed is also great to have under the development phase as it makes it easy to render certain view or test different scenarios if there is already pre existing data or that the data is put in a certain state, e.g a shopping cart has items and you want to test the checkout page.
There are many more things we want to do to a database like adding indexes, adding users with different access rights and much much more but let’s focus on these four points above as a references for what we, with the help of Docker should be able to support.
Installing and connecting to MySql
There are tons of ways to install MySql not all of them are fast :/. One of the easier ways on a Linux system is typing the following:
sudo apt-get install mysql-server
On a Mac you would be using brew and then instead type:
brew install mysql
In some other scenarios you are able to download an installer package and follow a wizard.
Once you are done installing MySql you will get some information similar to this, this will of course differ per installation package:
The above tells us we don’t have a root password yet, YIKES. We can fix that though by running mysql-secure-installation . Let’s for now just connect to the database running the suggested mysql -uroot .
NOOO, what happened? We actually got this information in the larger image above, we needed to start MySql either by running brew services start mysql , which would run it as a background service or using mysql-server start which is more of a one off. Ok, let’s enter brew services start :
The very last thing is it says Successfully started mysql :
Let’s see if Matthew is correct, can we connect ?
And we get a prompt above mysql>, we are in :D
Ok so we managed to connect using NO password, we should fix that and we dont have any database created, we should fix that too :)
Well it’s not entirely true, we do have some databases, not just any databases with content created by yourself, but rather supportive ones that we shouldn’t touch:
So next up would be to create and select the newly created database, so we can query from it:
Ok, great, but wait, we don’t have any tables? True true, we need to create those somehow. We could be creating them in the terminal but that would just be painful, lots and lots of multiline statements, so let’s see if we can feed MySql a file with the database and all the tables we want in it. Let’s first define a file that we can keep adding tables to:
// database.sql
// creates a table `tasks`
CREATE TABLE IF NOT EXISTS tasks (
task_id INT AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
start_date DATE,
due_date DATE,
status TINYINT NOT NULL,
priority TINYINT NOT NULL,
description TEXT,
PRIMARY KEY (task_id)
);
// add more tables below and indeces etc as our solution grows
Ok then, we have a file with database structure, now for getting the content in there we can use the source command like so ( masking over the user name):
If you want the full path to where your file is located just type PWD where your file is at and type source [path to sql file]/database.sql. As you can see above, we need to select a database before we run our SQL file so it targets a specific database and then we verify that the table has been created with SHOW TABLES; We can also use the same command to seed our database with data, we just give it a different file to process, one containing INSERT statements rather than CREATE TABLE...
Ok then. I think that’s enough MySql for now, let’s talk MySql in the context of Docker next.
Why we need a MySql Docker image and how to get it up and running as a container
Ok so let’s now say we want a container for our application. Let’s furthermore say that our solution needs a database. It wouldn’t make much sense to install MySql on the host the computer is running on. I mean one of the mantras of containers is that we shouldn’t have to care about the the host system the containers are running on. Ok so we need MySql in a container, but which container, the apps own container or a separate container? That’s a good question depending on your situation, you could either install MySql with your app or run it in a separate container. Some argument for that is:
- rolling updates, your database can be kept online while your application nodes do individual restarts, you won’t experience downtime.
- scalability, you can add nodes to scale in a loosely coupled fashion
There are a lot of arguments for and against and only you know exactly what works best for you — so you do you :)
MySql as stand alone image
Let’s talk about the scenario in which we pull down a MySql image. Ok, we will take this step by step so first thing we do is try to run it and as we learned from previous articles the image will be pulled down for us if we don’t have it, so here is the command:
docker run --name=mysql-db mysql
Ok, so what’s the output of that?
Pulling down the image, good, aaaand error.
We are doing all kinds of wrong :(.
Database is uninitialized, password option is not specified and so on. Let’s see if we can fix that:
docker run --name mysql-db -e MYSQL_ROOT_PASSWORD=complexpassword -d -p 8001:3306 mysql
and the winner is:
Argh, our container we started before is up and running despite the error message it threw. Ok, let’s bring down the container:
docker rm mysql-db
and let’s try to run it again with the database set like above:
Ok, we don’t get a whole bunch of logging to the terminal, because we are running in Daemon mode so let’s run docker ps to check:
At this point we want to connect to it from the outside. Our port forwarding means we need to connect to it on 0.0.0.0:8001 :
mysql -uroot -pcomplexpassword -h 0.0.0.0 -P 8001
Ok, just a comment above we can either specify the password or just write -p and we will be prompted for the password on the next row. Let’s have a look at the result:
Ok, we managed to connect to our database, it is reachable, great :).
But wait, can we reach a database, inside of container, from another container? Well here is where it gets tricky. This is where we need to link the two containers.
Connecting to database from Node.js
Ok let’s first add some code in our app that tries to connect to our database. First off we need to install the NPM package for mysql :
npm install mysql
Then we add the following code to the top of our app.js file:
// app.js
const mysql = require('mysql');
const con = mysql.createConnection({
host: "localhost",
port: 8001,
user: "root",
password: "complexpassword",
database: 'Customers'
});
con.connect(function (err) {
if (err) throw err;
console.log("Connected!");
});
So let’s try this in the terminal:
Pain and misery :(
So why won’t it work.
This is because caching_sha2_password is introduced in MySQL 8.0, but the Node.js version is not implemented yet.
Ok so what, we try Postgres or some other database? Well we can actually fix this by connecting to our container like so:
mysql -uroot -pcomplexpassword -h 0.0.0.0 -P 8001
and once we are at the mysql prompt we can type the following:
mysql> ALTER USER 'root' IDENTIFIED WITH mysql_native_password BY 'complexpassword';
mysql> FLUSH PRIVILEGES;
Let’s try to run our node app.js command again and this time we get this:
Finally!
Ok, so some call to this mysql_native_password seems to fix to whole thing. Let’s go deeper into the rabbit hole, what is that?
MySQL includes a mysql_native_password plugin that implements native authentication; that is, authentication based on the password hashing method in use from before the introduction of pluggable authentication
Ok, so that means MySql 8 have switched to some new pluggable authentication that our Node.js mysql library hasn’t been able to catch up on. That means we can either pull down an earlier version of MySql or revert to native authentication, your call :)
Linking
The idea of linking is that a container shouldn’t have to know any details on what IP or PORT the database, in this case, is running on. It should just assume that for example the app container and the database container can reach each other. A typical syntax looks like this:
docker run -d -p 5000:5000 --name product-service --link mypostgres:postgres chrisnoring/node
Let’s break the above down a bit:
- app container, we are creating an app container called product-service
- --link we are calling this command to link our product-service container with the existing mypostgres container
-
link alias, the statement --link mypostgres:postgres means that we specify what container to link with mypostgres and gives it an alias
postgres. We will use this alias internally inproduct-servicecontainer when we try to connect to our database
Ok, so we think we kind of get the basics of linking, let’s see how that applies to our existing my-container and how we can link it to our database container mysql-db. Well because we started my-container without linking it we need to tear it down and restart it with our --link command specified as an additional argument like so:
docker kill my-container && docker rm my-container
That brings down the container. Before we bring it up though, we actually need to change some code, namely the part that has to do with connecting to our database. We are about to link it with our database container using the --link mysql-db:mysql argument which means we no longer need the IP or the PORT reference so our connection code can now look like this:
// part of app.js, the rest omitted for brevity
const con = mysql.createConnection({
**host: "mysql",**
user: "root",
password: "complexpassword",
database: 'Customers'
});
// and the rest of the code below
The difference is now our host is no longer stating localhost and the port is not explicitly stating 8001 cause the linking figures that out, all we need to focus on is knowing what alias we gave the database container when we linked e.g mysql. Because we both changed the code and we added another library myql we will need to rebuild the image, like so:
docker build - t chrisnoring/node .
now let’s get it started again, this time with a link:
docker run -d -p 8000:3000 --name my-container --link mysql-db:mysql chrisnoring/node
and let’s have a look at the output:
We use the docker logs my-container to see the logs from our app container and the very last line says Connected! which comes from the callback function that tells us we successfully connected to MySql.
You know what time it is ? BOOOM!
Expand our knowledge on linking — there is another way
Ok, so this is all well and good. We managed to place the application in one container and the database in another container. But… This linking we just looked at is considered legacy which means we need to learn another way of doing linking. Wait… It’s not as bad as you think, it actually looks better syntactically.
This new way of doing this is called creating networks or custom bridge networks. Actually, that’s just one type of network we can create. The point is that they are dedicated groups that you can say that your container should belong to. A container can exist in multiple networks if it has cross cutting concerrns for more than one group. That all sounds great so show me some syntax right?
The first thing we need to do is to create the network we want the app container and database container to belong to. We create said network with the following command:
docker network create --driver bridge isolated_network
Now that we have our network we just need to create each container with the newly created network as an additional argument. Let’s start with the app container:
docker run -d -p 8000:3000 --net isolated_network --name my-container chrisnoring-node
Above we are now using the --net syntax to create a network that we call isolated_network and the rest is just the usual syntax we use to create and run our container. That was easy :)
What about the database container?
docker run -p 8001:3306 --net isolated_network --name mysql-db -e MYSQL_ROOT_PASSWORD=complexpassword -d mysql
As you can see above we just create and run our database container but with the added --net isolated_network .
Looking back at this way of doing it, we no longer need to explicitly say that one container needs to be actively linked to another, we only place a container in a specific network and the containers in that network knows how to talk to each other.
There is a lot more to learn about networks, there are different types and a whole host of commands. I do think we got the gist of it, e.g how it differs from legacy linking. Have a look at this link to learn more docs on networking
General database management in a container setting
Ok, so we talked at the beginning of this section how we can create the database structure as a file and how we even can create our seed data as a file and how we can run those once we are at the MySql prompt after we connected to it. Here is the thing. How we get that structure and seed data in there is a little bit up to you but I will try to convey some general guidelines and ideas:
-
structure, you want to run this file at some point. To get in into the database you can either connect using the
mysqlclient and refer to the file being outside in the host computer. Or you can place this file as part of your app repo and just let the Dockerfile copy the file into the container and then run it. Another way is looking at libraries such as Knex or Sequelize that support migrations and specify the database structure in migrations that are being run. This is a bit different topic altogether but I just wanted to show that there are different ways for you to handle this -
seed, you can treat the seed the same way as you do the structure. This is something that needs to happen after we get our structure in and it is a one-off. It’s not something you want to happen at every start of the app or the database and depending on whether the seed is just some basic structural data or some data you need for testing purposes you most likely want to do this manually, e.g you connect to
mysqlusing the client and actively runs the source command
Summary
Ok, this article managed to cover some general information on MySql and discussed how we could get some structural things in there like tables and we also talked about an initial/test seed and how to get it into the database using standalone files.
Then we discussed why we should run databases in Docker container rather than installed on the host and went on to show how we could get two containers to talk to each other using a legacy linking technique. We did also spend some time cursing at MySql 8 and the fact the mysql Node.js module is not in sync which forced us to fix it.
Once we covered the legacy linking bit we couldn’t really stop there but we needed to talk about the new way we link containers, namely using networks.
Lastly we shared some general guidelines on database management and hopefully, you are now at a point where you understand how we can add databases to our app and keep developing the app.
Follow me on Twitter, happy to take your suggestions on topics or improvements /Chris
Latest comments (24)
If you're doing this module on Windows host with Git Bash, mysql server can be set up manually the following way:
path-to-mysql-directory/binto PATH variablemysqld --initialize --consoleNow you're good to follow along the rest!
Great job :)
both have the same exposed port. this isn't allowed right? or am i doing it wrong? i have an error
hi. Yes, you are correct. Should 8001 for the database, or at least not 8000. Thanks for noticing :)
Hi @chris can you add this line before "Let’s try to run our node app.js command again and this time we get this: node app.js",
create table with the name Customer before running node app.js with below command
CREATE DATABASE Customers;
without creating the db, i was getting error, but after creating the db it resolved.
I ain't sure if this is how it needs to be done, as am no expert with sql dbs. Please correct me if am wrong.
Fell into the same trap as well. Thanks for pointing it out.
That is correct. We created the Customers database on the host mysql server but we left the container database untouched. Manual creation of the Customers database is required on the container.
There are things I did not get when we used
--net. How do the containers communicate ?Mine could not find the MySQL container with the previous "mysql" alias. After running
docker inspect mysql-dbI used the IP stated from the output atNetworkSettings.Networks.isolated_network.Gatewayas the mysql host parameter.I doubt that this is a reliable solution, how should I proceed to make it easier ?
Hey, nice work!
Just one thing, I'm stuck on where we setup the network. I've created my network with
docker network create --driver bridge isolatedand then ran my containers with
docker run -d -p 8000:3000 --net isolated --name jayy jayywalker/nodedocker run -p 8001:3306 --net isolated --name db -e MYSQL_ROOT_PASSWORD=dddd -d mysqlOf course since I've ran the app container before the mysql it won't find it, but even after restarting the app container I receive the following error:
It worked fine using link.
What string are you using as host in the node app? I've noticed that when you use networks instead of links you have to reference to other containers inside the networks by their names.
This needs mentioning in the article, at the end of the "Expand our knowledge on linking — there is another way" section.
thanks for the feedback Johan.. Haven't had time to update... But yes will look into improving this section
hi Jordan. Thanks for your comment. Let me get back to you in a couple of days
you should be using the name of the mysql container as host property
I am getting the same error even after I have set the host property as "mysql". Could you please point where I am going wrong?
Thanks for reaching out Mayank. Let m get back to you shortly
Thanks
Great content!
Small nitpick - after creating a network, you won't be able to run database container with the command in the article, because port 8000 will already be allocated by first container :)
you've got sharp eyes, thanks for noticing this :)
Great article (and series), Chris!
I’ve got a quick question about persistence with the MySQL container. I might have missed it somewhere, but was wondering where the actual MySQL data winds up so that it isn’t lost when the container is restarted later.
Ie. On a native Linux installation these would wind up in /var/lib/mysql on the host machine. Where does the dockerized MySQL container put them? Does it implicitly mount a host file system volume in the same location?
You actually bring up an important issue Scott. The whole point about containers is that they are disposable. A good architecture assumes that any container may be destroyed and re-created (from a Dockerfile) at any later moment. But if we destroy the MySQL container we will eventually lose the database. Not a big deal for development, but sounds scary in production, right? One particular consequence, we have to stick to mysql image once downloaded and we won't be able to update to a newer image or to make changes to the image, without destroying the container and losing all data.
The solution? Separate data (mysql database) from the application (mysql container). That's what volumes are for - theoretically. The first idea coming to mind, let's put /var/lib/mysql (or whatever directory MySQL uses as storage) into a volume and mount it into the container, right? Not sure about you, but I had a bunch of low-level issues trying to port this directory from a Windows to a Linux host. The problem turned out to be the difference in how MySQL reads and writes to disk on different platforms.
I don't have a viable solution for this yet, and I'll be happy to hear one.
Hi Scott. Great question! I've been asking it myself as well :)
I am using Docker Desktop on Mac, so in my case Docker is using a disk image named
Docker.rawlocated in~/Library/Containers/com.docker.docker/Data/vms/0. And this is where it stores all containers and images. So, you won't see exact the same structure of folders and files on a host as you see in the container.Regards,
-Vitaliy
Awesome series Chris, I think there's a typo: In the linking example you're using postgres instead of the MySQL db one 😜
Some comments may only be visible to logged-in visitors. Sign in to view all comments.