Using Sequelize & MySQL Docker image for development (Part 3)

Mr. Leo

Published in

mr-leo

6 min readDec 10, 2018

This post in one of part in my series about Building real APIs with NodeJS for beginners.

All main contents in this series in case we want to navigate quickly:

All these codes in this project can be found here.

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.

MySQL Setup

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.

Install Docker

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 run run a command in a new container.
mysql:5.7.24 is 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.
--rm automatically remove the container when it exists.
--name=my-mysql we want to assign a meaningful name to our container.
--env set environment variables of container. In this case, we want to set MySQL root password.
--detach run container in background.
--publish for 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 messagecommand 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 sequelize and mysql2 packages. mysql2 is a dialect, that helps Sequelize can communicate with our MySQL database.

$ npm install --save sequelize
$ npm install --save mysql2

Initialize Sequelize

After install 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:

time-log-project
-- app.js
|-- package.json
|-- config
    |-- config.json
|-- migrations
|-- models
    |-- index.js
|-- seeders

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.

The 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.

The 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.

Create Database

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 name and 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.

Migration file for creating TimeLog model

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.

Don’t hesitate to clap if you considered this a worthwhile read!

~ Happy coding with Mr. Leo ~