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

Create DB.

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

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');

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.

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();

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

License

MIT licensed