Sequelize ORM workshop

In this workshop we use Sequelize ORM in a simple node app.

You will practice

Sequelize models - Use Sequelize model to execute queries on your DB

Migrations - Manage DB changes using Sequelize migrations

Prerequisites - complete workshop #9, have a football DB in your machine

Download and install docker
Open console and pull latest mysql image: docker pull mysql/mysql-server
Run mysql container: docker run -p 3306:3306 --name mysql_workshop -e MYSQL_ROOT_PASSWORD=password -e MYSQL_ROOT_HOST=% -e MYSQL_DATABASE=mysql_workshop -d mysql/mysql-server
Validate container is up: docker ps
!!! Alternatively to docker - install SQL sever without docker: https://dev.mysql.com/downloads/mysql/
Download workshop_create_table.sql and execute it in workbench. This will create the tables for our Football DB.
Download workshop_instert_data.sql and execute it in workbench. This will insert data to the tables in our DB.

Workshop steps:

Clone this repository: git clone git@github.com:monday-u-com/workshop-10.git
CD workshop-10 directory
Explore server/db folder files - config (where we set connection details to our db) and models (model definition of player entity)
Run npm install
Run npm start
Open browser and go to "http://localhost:3042/players" - this will load all players from our db

Task #1

Implement getPlayer function in storage_service.js. Use the Player model to find the specific player and return it. Verify it works: http://localhost:3042/player/1

Solution

Task #2

Implement createPlayer function in storage_service.js. Use the Player model to create a new player in our DB. Verify it works, insert new player using Postman or the command: curl -X POST localhost:3042/player -H 'Content-Type: application/json' -d '{ "player_id":9999,"player_name":"player name","age":30,"position":"Attacker","country":"uk"}' and load it's data: http://localhost:3042/player/9999

Solution

Task #3

Using npx sequelize-cli model:generate - create a new model for Salary (run the following from db folder) - npx sequelize-cli model:generate --name Salary --attributes start_date:date,end_date:date,amount:integer,player_id:integer. This will create a migration file in server/db/migrations.

Explore the new files that were generated in migrations and model folder.

Now execute the migration: npx sequelize-cli db:migrate - this will create the Saralies table in our DB.

Solution

Task #4

Implement createSalary function in storage_service.js. Use the Salary model to create a new salary in our DB. Verify it works, insert new Salary using Postman or the command: curl -X POST localhost:3042/salary -H 'Content-Type: application/json' -d '{ "start_date":"2022-01-02","end_date":"2023-01-01","amount":100000,"player_id":1}'

Solution