sequelize-es6-class-sample

This is the experimental code for the Sequelize ORM.

Usage no npm install needed!

<script type="module">
  import sequelizeEs6ClassSample from 'https://cdn.skypack.dev/sequelize-es6-class-sample';
</script>

README

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

License

MIT licensed