Using Sequelize & MySQL Docker image for development (Part 3)
Setup database and ORM usually makes us a little bit anxious when we move to new tech stack. However, in this section, I will point out the easiest way to make everything ups and runs smoothly.
Honestly, I didn’t have any MySQL instance installed on my machine when I did this project. So I decided to install the latest version of MySQL at that moment (v.8.0.13). Unluckily, I met so many problems with MySQL and Sequelize connection when I tried to execute some sequelize CLI commands. It took me near one day to investigate and realize that the root cause is the version 8.0.x of MySQL does not work well with sequelize dialect mysql (we’ll meet it later in this post). One of these errors still obsesses me is:
ERROR: Client does not support authentication protocol requested by server; consider upgrading MySQL client
Finally, I turned back to the older version 5.7.x, and this time I didn’t want to clutter my machine up anymore, so I installed MySQL service from Docker. That’s way will help us less dependence on our local machine.
Docker Quick Introduction
For anyone new to Docker, please don’t research too soon into Docker document. It can make you confuse. Instead, you should take a look around on the Internet some articles or posts that explain simply about Docker. For example this one.
Technically, you can understand Docker as a self-contained machine, it has its own OS, its own file system and anything else you would expect to find in a virtualized machine. However, one container only runs one program. For example, we’ll run our MySQL server in one container.
Docker is a standard environment, it means that if your service can run on your local machine, it’ll run on server production as well for sure. That gets rid of one of the famous case: The code works on developer machine, but not on production server.
Docker is easy to install. Just download installation package here and run it.
After installing Docker successfully, we can run this command
$ docker run --rm --name=my-mysql --env MYSQL_ROOT_PASSWORD=password --detach --publish 3306:3306 mysql:5.7.24
Keep calm, I’ll try to explain clearly the meaning of this command
docker runrun a command in a new container.
mysql:5.7.24is an MySQL image and its version is 5.7.24. In the first time, we don’t have this image yet so you’ll see docker will pull that image to our local.
--rmautomatically remove the container when it exists.
--name=my-mysqlwe want to assign a meaningful name to our container.
--envset environment variables of container. In this case, we want to set MySQL root password.
--detachrun container in background.
--publishfor easy understanding, our MySQL service will listen on port 3306 in our machine.
Now we have MySQL server is running in background. Try to connect to the database
mysql -uroot -ppassword -h 127.0.0.1 -P 3306 --protocol=tcp
(In case we have an error message
command not found: mysql it means that you don’t have mysql client on your local. Please install it as the guide here)
Sequelize and Sequelize CLI Setup
As I mentioned in this part, Sequelize is an ORM that helps us to interact with our database.
Most of the time, we’ll interact with our database through Sequelize CLI helper. For example bootstrap our project by creating a database, migrating changes to our database, seeding data, etc…
Let’s install this package globally first
$ npm install -g sequelize-cli
Next, we’ll install
mysql2 is a dialect, that helps Sequelize can communicate with our MySQL database.
$ npm install --save sequelize $ npm install --save mysql2
sequelize package successfully, we’ll use sequelize CLI to generate migrations, seeders, models directors and config file for us automatically.
Run this command at the root folder of our project
$ sequelize init
Our project structure should look like this now:
We’ll go through each of folders or files that have been generated from CLI to understand more about how Sequelize can work with our application.
config.json file includes database connection information. It supports multiple environments: development, test, production, etc… Please notice that in our test environment, we’ll set logging to false. It means that whenever you run tests (we’ll do later), the console won’t show query logs that sequelize uses to talk with our database.
index.js file inside
/models folder is just a sequelize configuration file. All these codes here are generated automatically.
migrations folder will include all activities related to our database later. The structure file name looks like
<generated-time>-<name-of-activity>. For example when we want to create a user table by using sequelize CLI, it’ll automatically create a new file name
2018112112033-create-user.js then we’ll define our table inside that file. This folder is empty in the beginning.
seeders is a folder includes the file we’ll populate data to our application. The structure file name is the same as these files in
migrations folder. And it’s empty as well in the beginning.
Now we’ll create database thank to sequelize CLI
$ sequelize db:create
You can take a look more sequelize CLI commands at their official document.
Create User Model
$ sequelize model:generate --name User --attributes name:string,password:string,role:string
Here we create a new model name
User with attribute
password and their types as well.
Sequelize CLI will helps to generate a file inside folder migrations for us. If you take a look into this file you’ll see it has 2 parts: up and down.
up: is what we define mainly for our model.
down: is used to undo these actions in up part in case you want to rollback something from database.
Similarly, we’ll generate models: Project, Task, TimeLog.
$ sequelize model:generate --name Project --attributes name:string $ sequelize model:generate --name Task --attributes name:string,point:integer $ sequelize model:generate --name TimeLog --attributes timeInHour:decimal(3,1)
For Task and TimeLog, we have to modify migration files a little bit to describe relationship with Project and User.
At this point, we already define all our models by codes. Now we need to create tables into our database corresponding.
$ sequelize db:migrate
This time, Sequelize will take all files inside
migrations folder and execute its script to create tables into database for us.
Nice! At this time, we have most of the basic stuff and configurations to evolve our application to next step. See you in the next section.