- In this lesson, we're going to see how to access
a MySQL relational database in Rust.
We're gonna see how to use the Rust crate for MySQL.
Rust has lots of crates for accessing different kinds
of resource databases, NoSQL databases,
message queues and such like.
So there's a crate for MySQL.
You need to include that in your project
in order to access the MySQL database.
So we'll see how to do that, quite simple.
We'll see how to connect to a database.
There's a connection string you've gotta specify
in order to connect to a MySQL database.
You specify what machine is the database running on,
what port number, things like that,
and then we'll see how to do queries
and how to do inserts and updates
and such like in the database.
So this is where the project's located,
lesson17_database_access.
Let me open up that project in Visual Studio Code.
Okay, so here we are.
The source code is actually quite small, really,
certainly compared to the demo we saw
with the CSV parser in the previous lesson,
and you'll notice I've got a MySQL.
I've got a schema file here
which creates a database structure, tables,
and inserts some data, and I've got a Dockerfile.
Interesting.
So we're gonna run my SQL in a Docker container.
I'm not gonna assume you've already got my SQL installed
on your machine.
You may have. You may not have.
Instead, I'm just going to show you
how to basically run my SQL in a Docker container.
So you'd have to have Docker installed on your machine.
If you don't have it installed,
then if you just go into Chrome,
go into a browser and search for Docker Desktop,
you can install Docker Desktop on Windows, Mac or Linux.
Quite straightforward, takes about five minutes,
and then once you've installed Docker Desktop, start it,
and then you'll be able to run commands
to build images and to run containers.
An image is basically a self-contained application,
and a container is a running instance of that image.
So we'll see how to do this in a moment.
So it's certainly easier using a containerized MySQL
than having to install MySQL directly on your machine.
That's for sure.
So all you need to do is to make sure
you've got Docker Desktop or Docker installed
on your machine, as I was saying.
So in the project,
there were a couple of files we need to look at,
myschema.sql and Dockerfile.
So let's have a look.
So myschema.sql creates a schema.
In MySQL terminology, a schema is like a database, okay?
So this will create a database called MYSCHEMA,
and then in that database, it creates various tables.
We're not gonna use half of this in the demo,
but I just wanted to show you how it'd look
in a realistic situation.
In the MYSCHEMA schema,
I'm gonna create a table called EMPLOYEES,
and an employee has an ID, which is AUTO_INCREMENT
and which is also the PRIMARY KEY.
An employee has a Name, which is a string,
and a Salary and a Region where they work.
I've got an enhanced employees table called EMPLOYEES2
which has all of the stuff above
but it also has an image, okay?
So if you wanted to store binary large object,
then you could put it in there, and it goes on and on.
I've got various other tables
which are kind of academically interesting.
I just wanted it to be a kind of realistic database schema,
and then what I do at the end is I insert some sample data.
So USE MYSCHEMA, in other words,
using that database that we just talked about,
into the EMPLOYEES table, insert some employees, okay?
So I think it's about a dozen
or so employees inserted there,
and I've inserted table data into other tables as well.
All right, so that's myschema.sql.
It's just a regular SQL file.
You would run it in some kind of database tool
to construct a database and to populate these tables.
Okay, so there's nothing particularly,
you know, revelatory there.
The Dockerfile is more interesting.
So I won't go into the theory of Docker now.
Maybe that's another course,
but the idea is you can build your own custom image,
and an image is a self-contained file system, really.
It's almost like a self-contained application.
It contains everything you need to do,
everything you need to run an application
plus all of its dependencies all packaged up
in a single image, completely standalone.
The idea being you don't need to have anything else
pre-installed on the machine apart from Docker.
The image is a completely standalone package,
like a mini virtual machine, but in miniature.
So in a Dockerfile, you have a series of instructions
in blue here.
Each instruction helps to build a file system
for the Docker image.
So the first statement or the first instruction you have
in a Dockerfile, oh, and by the way, these are comments.
You would've guessed.
The first instruction you typically have is FROM,
and that pulls down from the Internet,
there's a standard image on the Internet
called mysql version 8 point something.
When you have an image name, the image has a name,
and after the colon, it also has a version, okay?
So I've specifically pulled down from,
there's an online repository called Docker Hub.
From Docker Hub, I'm pulling down the mysql standard image.
It's MySQL in an image, version 8.
If you don't specify a version number or tag version,
it'll pull down the latest version.
I specifically want to run or pull down the image
for mysql version 8.0.27.
If you go onto, there's a website, hub.docker.com,
hub.docker.com, where you can search for these images.
So that will be downloaded from Docker Hub on the Internet,
and it'll become part of my image, okay?
Think of an image as being like a small virtual machine.
When you run MySQL in a Dockerized container,
every database listens on a particular port number,
and as it happens, MySQL listens on port 3306.
So whenever you have something containerized
listening on a port, what you're meant to do
is to expose that port number, okay?
It's a technical detail that I'd rather not
get into just now, but generally,
whenever you've got containerized applications
that need to listen on a port,
you need to expose that port to the outside world.
Okay, so I've done that.
Right, next thing,
this statement here defines an environment variable.
Inside your Dockerized container,
there will be an environment variable
called MYSQL_ROOT_PASSWORD.
It's the password of the root user.
Now, if you look at the documentation online,
if you go into the Docker Hub, hub.docker.com,
and if you search for MySQL, you'll find,
if you look at the documentation for MySQL, each image,
well, lots of images have certain environment variables
which they expect you to set,
an environment in which the MySQL engine will run,
and if you look at the documentation for MySQL,
it says you must set at least
the MYSQL_ROOT_PASSWORD environment variable, okay?
So I've called it password.
Fair enough, and then the last instruction
is quite interesting.
What I've done now, an image is like a file system actually.
This Docker image that we're trying to build up
is like a miniature file system,
and what I'm doing here is I'm copying,
from my development machine, I'm copying myschema.sql here
into the Docker image particularly,
in particular into this folder in the Docker image, okay?
So the Docker image will have a directory
called docker-entrypoint-initdb.d.
That is a special directory name.
Most relational databases, when they run in a container,
when you start the image, when you run it,
it'll look in this special directory first
looking for schema or looking for SQL files
in order to build the schema of the database
and maybe to populate it with data.
So if you wanted to, you could copy multiple SQL files
into the same directory.
So for example, I could have a file1.sql
which creates the schema, and then I could also copy in,
imagine I've got another file over here called file2.sql
and copy that into the directory as well.
So the Docker image, when it's been built,
will have the mysql base image, which is just the engine,
plus an environment variable,
and it will also run these scripts.
It'll run this script.
Because that script is in the special folder,
it'll run that script to maybe create the schema,
and then it'll run that script
because it's in the special folder to populate the schema.
I wonder what would happen if I specified
these files the other way around.
What do you think would happen there?
Which file would it execute first?
The answer is it does it alphabetically, believe it or not.
So it doesn't matter the order that you copy them
into the directory.
This directory, at the end of the day,
it's just a directory with a couple of files.
So at startup, when you first kind of run the image,
it will execute the first file name
in that directory alphabetically.
So what a lotta developers do,
if you have a lot of SQL files you need to copy in,
they'll name the files something like a 0002
and 0001 just to guarantee that this file
will be executed first, because alphabetically,
numerically, it comes before this one.
There we are. So that's a curiosity.
Let me just revert back to what I had originally.
So this Docker file is like a recipe
that I can give to Docker to build an image
containing the tables described in my schema.
So the next step is to actually build the SQL image
based on the Docker file we just specified.
So what you do is you say docker build,
you can also say docker image build, if you prefer,
minus t, t means tag name.
You give your image a name.
I've called my image mysql-image, okay?
And then you give it a directory.
So what I'm gonna do is I'm going to run that command
from the root folder of my demo project,
and dot basically will be the folder
where your docker file is, right?
So let's do that now,
docker build minus t mysql-image,
and then don't forget the dot.
Okay, so in the root folder of my project,
docker, I could say docker image build,
or I could just say docker build,
so people tend to just say docker build, minus t.
Give my image a tag name of whatever you fancy,
mysql-image, and don't forget the dot.
The dot says in the current folder,
it looked for the Dockerfile,
and it'll basically execute the statements in here
or the instructions, okay?
And it'll grab myschema.sql from the current folder as well.
So let's run that.
It's gonna pull down images from Docker hub.
Okay, so at this point obviously,
you need an Internet connection.
It goes without saying that you need Docker installed,
and that was quite quick actually.
So it has built an image
hopefully called mysql-image.
So to verify that the image has been built successfully,
do a docker image ls, list the images
that now exist on my machine.
Okay, so let's do that then, docker image ls.
I've got lots of images on my machine,
so I'm looking for one called mysql-image.
So if I scroll up to the top,
hopefully here somewhere I've got a mysql-image built.
In fact, I can't see it there.
You can actually be more specific.
You can say docker image ls, and there,
you can actually give it the name
of the image you're looking for, mysql-image,
and then it'll only list the images with that name.
Okay, so it was there. I couldn't find it.
So I've got a repository,
that's just the image name, mysql-image.
So technically speaking, when you build an image,
the name that we specified
when we said docker build minus t mysql.image,
the minus t was the tag name.
It's actually the repository name.
That's the actual name of the image,
and tag here is a version number.
I didn't give it a version number.
I didn't say, like, :0.0.1.
So it'll just give it, like, a pseudo tag number, latest.
Created five weeks ago. Interesting.
So anyway, what I've got so far is I've got an image.
What I wanna do next is to basically run it.
So to run an instance of an image,
that's what a container is, okay?
So you say docker run,
and then you say you give a name to your container.
So a container is a running instance of an image.
So docker run, give my container a name, mysql-container.
The minus d option, obviously you can look up
the documentation on Docker, the Dock docs, in fact.
Minus d means that it'll run the container in detached mode.
It'll run it in a background window
rather than running in the current window.
We don't lose our console window, in other words,
and then the minus p option,
the minus p option is important.
What's happening, inside my docker container,
the MySQL engine will always be listening
on that port number internally kind of inside Docker,
and what you've gotta do is you've gotta map
that internal port number to an external port
on your actual machine, okay?
And that's what the minus p option does.
The minus p option says this port on my machine
will be basically seized by Docker,
and Docker will map it to this port number
inside the container, okay?
So from the outside world,
that's the port number that I'll use,
and that port number's like a wormhole
into this port number inside the container.
It's like a gateway into the container.
Right, well, I need to do that,
so docker run --name, give my container a name,
mysql-container.
Run it in detached mode.
If you didn't say that, then it would just run
and kind of block the current console.
You'd have to then open another console window
to actually do anything.
Run it detached from the console, minus p.
On my machine, port number 3306 will be mapped
to port 3306 inside the container.
So there we go.
Oh, and then we have to say the container
will be an instance of which image exactly.
Oh, mysql image, mysql-image.
So I think that's about it. Let me just double check.
Run a container whose name is going to be that
in detached mode in the background.
Map this port number on my machine
to that number inside the container, and by the way,
the container will be an instance of that image.
Well, that looks okay to me.
We'll see. Let's have a look.
Ah, now then, errors. These things do happen.
It says I already have a container named mysql-container.
Well, okay, so in that case, what you need to do
is you need to stop that other container.
I can say docker container stop
mysql-container.
Stop that other container that's currently running, it says.
Okay, so it stopped, and then I can remove that container
to get rid of it from my Docker registry,
docker container rm.
You stop the container first, and then you remove it,
mysql-container.
Rightio, so let's just do a quick,
you can say docker container ls.
Let's see what containers I've got at the moment.
Right, so it was going to list all the containers
that I have at the moment, and there aren't any.
So that means I should now be able
to actually start the container,
like I hoped to a moment ago.
Okay then, so docker run.
Run a container whose name will be that,
an instance of that image.
Run it in detached mode with that port mapping.
Right. Okay, let's see if that works.
Right, well, it hasn't given me an error message.
So in order to check that it's actually running,
you can say docker container ls.
In fact, when you say docker container ls,
it only lists the containers which are actually running.
Sometimes a container starts and then it errors
or it starts and then it completes.
Docker container ls would only list the ones
which are actually running at the moment.
If you say minus a, then it'll list all containers,
even the ones which have stopped, okay,
that have terminated maybe or have ended,
so that's probably more useful.
List all containers, please.
Okay, so, oh, interesting.
Here, I've got a few other containers
that I wasn't expecting.
That's, oh, I know what that is.
That's from my Spring Boot course.
Very good, but this is the one of interest.
You can have as many containers running as you want.
You typically have lots and lots of containers running.
I've got a container. It's spread over a couple of lines.
I wonder if this would look easier
if I collapse it down a bit.
Oh, that's better, isn't it?
You can see each container has a container ID,
and the name that we've given it, oh that's the image.
It's gonna be an instance of that image.
That's the command that would be executed
when the container was executed.
Created 41 seconds ago. How time flies.
It's still up. It hasn't crashed and burned.
Oh, there's a port mapping, 3306 is mapped,
and that's the name of my container,
and it's an instance of this image over here.
So that looks fine.
I've got a MySQL database running in a container on 3306.
So that means we can then press ahead
and have a look at our Rust application
which is gonna talk to that database.
It's quite exciting, isn't it?