sequelize-es6-class-sample

This is the experimental code for the Sequelize ORM.

Requirements

• Node.js
• NPM
• MariaDB (This sample application targets MariaDB.)

Getting Started

Create sample DB

1. Create DB.

   CREATE DATABASE IF NOT EXISTS `sequelize-es6-class-sample` DEFAULT CHARACTER SET utf8mb4;
   USE `sequelize-es6-class-sample`;

2. Create sample table.

   DROP TABLE IF EXISTS `office`;
   CREATE TABLE `office` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `city` varchar(50) NOT NULL,
     `created` datetime NOT NULL DEFAULT current_timestamp(),
     `modified` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   
   INSERT  INTO `office`(`id`, `city`) VALUES
     (1, 'San Francisco'),
     (2, 'Boston'),
     (3, 'NYC'),
     (4, 'Paris'),
     (5, 'Tokyo'),
     (6, 'Sydney'),
     (7, 'London');
   
   DROP TABLE IF EXISTS `employee`;
   CREATE TABLE `employee` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `officeId` int(10) unsigned NOT NULL,
     `name` varchar(50) NOT NULL,
     `created` datetime NOT NULL DEFAULT current_timestamp(),
     `modified` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
     PRIMARY KEY (`id`),
     UNIQUE KEY `uk_employee_1` (`officeId`, `name`),
     CONSTRAINT `fk_employee_1` FOREIGN KEY (`officeId`) REFERENCES `office` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   
   INSERT  INTO `employee`(`officeId`,`name`) VALUES
     (1, 'Diane'),
     (1, 'Mary'),
     (2, 'Julie'),
     (3, 'Foon Yue'),
     (4, 'Gerard'),
     (5, 'Mami'),
     (6, 'William'),
     (7, 'Larry');

3. Check registration details.

   SELECT office.city, employee.name FROM employee join office on employee.officeId = office.id ORDER BY office.city;
   +---------------+----------+
   | city          | name     |
   +---------------+----------+
   | Boston        | Julie    |
   | London        | Larry    |
   | NYC           | Foon Yue |
   | Paris         | Gerard   |
   | San Francisco | Mary     |
   | San Francisco | Diane    |
   | Sydney        | William  |
   | Tokyo         | Mami     |
   +---------------+----------+

Run the sample program.

The executed SQL is output to "./debug.log".

You can monitor the debug log with the following command.

npm run log;

Search all offices.

npx babel-node src/searchAllOffices;
# Search all offices: [
#   {
#     id: 1,
#     city: 'San Francisco',
#     created: 2021-01-18T02:39:30.000Z,
#     modified: 2021-01-18T02:39:30.000Z
#   },
#   {
#     id: 2,
#     city: 'Boston',
#     created: 2021-01-18T02:39:30.000Z,
#     modified: 2021-01-18T02:39:30.000Z
#   },
#   ...
# ]

Search all offices.

npx babel-node src/searchOffices;

# [
#   {
#     id: 1,
#     city: 'San Francisco',
#     created: 2021-01-18T02:39:30.000Z,
#     modified: 2021-01-18T02:39:30.000Z
#   },
#   {
#     id: 2,
#     city: 'Boston',
#     created: 2021-01-18T02:39:30.000Z,
#     modified: 2021-01-18T02:39:30.000Z
#   },
#   ...
# ]

Search all employees.

npx babel-node src/searchEmployees;

# [
#   {
#     id: 1,
#     officeId: 1,
#     name: 'Diane',
#     created: 2021-01-18T03:10:42.000Z,
#     modified: 2021-01-18T03:10:42.000Z
#   },
#   {
#     id: 2,
#     officeId: 1,
#     name: 'Mary',
#     created: 2021-01-18T03:10:42.000Z,
#     modified: 2021-01-18T03:10:42.000Z
#   },
# ]

Search by adding office information to employees.

npx babel-node src/searchEmployeesJoinOffice;

# Added an office with ID = 8
#   { name: 'Diane', office: { city: 'San Francisco' } },
#   { name: 'Mary', office: { city: 'San Francisco' } },
#   ...
# ]

Add office.

npx babel-node src/addOffices;
# Added an office with ID = 8

Cause deadlock.

npx babel-node src/causeDeadlock;

Usage

Directory structure

.
├── src                              # Sample program file
│   ├── shared                       # Common module
│   │   ├──Model.js                  # Model base class
│   │   └──Database.js               # DB connection class
│   ├── config                       # Configuration file.
│   │   └──database.js               # DB connection config
│   ├── model                        # Model subclass
│   │   ├──OfficeModel.js            # Office model
│   │   └──EmployeeModel.js          # Employee model
│   ├── searchOffices.js             # Office search sample code
│   ├── searchEmployees.js           # Employee search sample code
│   └── searchEmployeesJoinOffice.js # Model subclass
└── .babelrc                         # babel transpile option

How to make a model class.

1. Add a new model class file to the 'src/model' directory.

   Here, we will add a sample model as an example.
   Inherit the shared / Model class.
   Define the table names that the model will access and the columns for that table.
   You can now access the sample table from the sample model.

   src/model/SampleModel.js:

   import Model from '../shared/Model';
   
   export default (class extends Model {
   
     /**
      * Returns the table name.
      */
     static get table() {
       return 'sample';
     }
   
     /**
      * Returns the columns of the table.
      */
     static get attributes() {
       return {
         id: {
           type: this.DataTypes.INTEGER,
           primaryKey: true,
           autoIncrement: true
         },
         name: this.DataTypes.STRING,
         created: this.DataTypes.DATE,
         modified: this.DataTypes.DATE
       };
     }
   }).attach();

2. The following is an example of searching the sample table.

   import SampleModel from './model/SampleModel';
   
   (async () => {
     // Search for sample records.
     const rows = await SampleModel.findAll({ raw: true });
   })();

Reference

• DeadLock investigation in MySQL (InnoDB) - Qiita
• MySQL InnoDB Deadlock For 2 simple insert queries - Database Administrators Stack Exchange
• MySQL :: MySQL 8.0 Reference Manual :: 15.7.1 InnoDB Locking
• Illustrate the mechanism and scope of next key lock in MySQL (InnoDB)

License

MIT licensed