Sequelize associations

example.md

Model

...
  User.init(
    {
      username: { type: DataTypes.STRING, primaryKey: true },
      password: { ...def },
      fio: { ...def },
    },
    { sequelize, modelName: "user" }
  );

  UserRole.init({ name: { ...def } }, { sequelize, modelName: "userrole" });
...

1

UserRole.hasOne(User); or User.belongsTo(UserRole); or UserRole.hasMany(User);

DDL

CREATE TABLE IF NOT EXISTS `userroles` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);

CREATE TABLE IF NOT EXISTS `users` (`username` VARCHAR(255) PRIMARY KEY, `password` VARCHAR(255) NOT NULL, `fio` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `userroleId` INTEGER REFERENCES `userroles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE);

Data

[
  {
    "id": 1,
    "name": "Администратор",
    "createdAt": "2020-10-27T08:29:50.347Z",
    "updatedAt": "2020-10-27T08:29:50.347Z"
  }
]
[
  {
    "username": "admin",
    "password": "password",
    "fio": "Иванов Иван Иванович",
    "createdAt": "2020-10-27T08:29:50.356Z",
    "updatedAt": "2020-10-27T08:29:50.356Z",
    "userroleId": null
  }
]

2

UserRole.belongsTo(User); or User.hasOne(UserRole); or User.hasMany(UserRole);

DDL

CREATE TABLE IF NOT EXISTS `userroles` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `userUsername` VARCHAR(255) REFERENCES `users` (`username`) ON DELETE SET NULL ON UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS `users` (`username` VARCHAR(255) PRIMARY KEY, `password` VARCHAR(255) NOT NULL, `fio` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);

Data

[
  {
    "id": 1,
    "name": "Администратор",
    "createdAt": "2020-10-27T08:26:06.367Z",
    "updatedAt": "2020-10-27T08:26:06.367Z",
    "userUsername": null
  }
]
[
  {
    "username": "admin",
    "password": "password",
    "fio": "Иванов Иван Иванович",
    "createdAt": "2020-10-27T08:26:06.374Z",
    "updatedAt": "2020-10-27T08:26:06.374Z"
  }
]

3

User.belongsToMany(UserRole, { through: "user_userrole" });

DDL

CREATE TABLE IF NOT EXISTS `users` (`username` VARCHAR(255) PRIMARY KEY, `password` VARCHAR(255) NOT NULL, `fio` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);

CREATE TABLE IF NOT EXISTS `userroles` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);

CREATE TABLE IF NOT EXISTS `user_userrole` (`createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `userUsername` VARCHAR(255) NOT NULL REFERENCES `users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE, `userroleId` INTEGER NOT NULL REFERENCES `userroles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (`userUsername`, `userroleId`));

Data