Unable to create relationship between more than 2 tables

[mogusbi]

I am trying to create a relationship between 3 tables but I am having difficulty doing so because of unique constraints set on the foreign key on the joining table.

What I am trying to achieve is a user, who is assigned to a group and within that group has certain permissions to do things (i.e. a CRUD matrix) with the end result being something along the lines of this

{
  "emailAddress": "test@example.com",
  "id": "UUID string goes here",
  "username": "Test user",
  "tasks": {
    "exampleTask": [
      "create",
      "update"
    ],
    "otherTask": [
      "create",
      "read",
      "update",
      "delete"
    ]
  }
}

This is what I have

// user.model.ts

@Table
export class User extends Model<User> {
  @Column({
    allowNull: false,
    type: DataType.STRING,
    unique: true,
    validate: {
      notEmpty: true,
      isEmail: true
    }
  }) public emailAddress: string;

  @Column({
    defaultValue: DataType.UUIDV4,
    primaryKey: true,
    type: DataType.UUID
  }) public id: string;

  @Column({
    type: DataType.STRING,
    allowNull: false,
    validate: {
      notEmpty: true
    }
  }) public password: string;

  @BelongsToMany(
    () => Permission,
    () => TaskPermission
  ) public permissions: Permission[];

  @BelongsToMany(
    () => Task,
    () => UserTask
  ) public tasks: Task[];

  @Column({
    allowNull: false,
    type: DataType.STRING,
    unique: true,
    validate: {
      notEmpty: true
    }
  }) public username: string;
}

// task.model.ts

@Table
export class Task extends Model<Task> {
  @Column({
    defaultValue: DataType.UUIDV4,
    primaryKey: true,
    type: DataType.UUID
  }) public id: string;

  @Column({
    allowNull: false,
    type: DataType.STRING,
    validate: {
      notEmpty: true
    }
  }) public name: string;

  @BelongsToMany(
    () => Permission,
    () => TaskPermission
  ) public permissions: Permission[];
}

// permission.model.ts

@Table
export class Permission extends Model<Permission> {
  @Column({
    defaultValue: DataType.UUIDV4,
    primaryKey: true,
    type: DataType.UUID
  }) public id: string;

  @Column({
    allowNull: false,
    type: DataType.STRING,
    validate: {
      notEmpty: true
    }
  }) public name: string;
}

// task-permission.model.ts

@Table
export class TaskPermission extends Model<TaskPermission> {
  @Column({
    defaultValue: DataType.UUIDV4,
    primaryKey: true,
    type: DataType.UUID
  }) public id: string;

  @ForeignKey(
    () => Permission
  )
  @Column
  public permissionId: string;

  @ForeignKey(
    () => Task
  )
  @Column
  public taskId: string;

  @ForeignKey(
    () => User
  )
  @Column
  public userId: string;
}

[RobinBuschmann]

Hey @mogusbi, I don't really get it, sorry. What is the actual problem with this setup? Do you get any errors or what exactly blocks you to achieve your goal? Thank you

[mogusbi]

Yes, when I try to assign more than one permission to a task (i.e. I want to give User A the permission to read and write on Task A), I'm met with this; error: duplicate key value violates unique constraint "TaskPermission_taskId_key

[RobinBuschmann]

Can you provide the definition of theUserTask model as well?

[mogusbi]

Ah sorry, I thought I had included all the definitions! Here you go;

@Table
export class UserTask extends Model<UserTask> {
  @Column({
    defaultValue: DataType.UUIDV4,
    primaryKey: true,
    type: DataType.UUID
  }) public id: string;

  @ForeignKey(
    () => Task
  )
  @Column
  public taskId: string;

  @ForeignKey(
    () => User
  )
  @Column
  public userId: string;
}

[RobinBuschmann]

Hmm, looks good as well. Can you also provide the create table query of UserTask and the insert query which causes the error?

[mogusbi]

Yep, here you go (along with all the other queries);

CREATE TABLE IF NOT EXISTS "Permission" ("id" UUID , "name" VARCHAR(255) NOT NULL, PRIMARY KEY ("id"));

CREATE TABLE IF NOT EXISTS "Task" ("id" UUID , "name" VARCHAR(255) NOT NULL, PRIMARY KEY ("id"));

CREATE TABLE IF NOT EXISTS "User" ("emailAddress" VARCHAR(255) NOT NULL UNIQUE, "id" UUID , "password" VARCHAR(255) NOT NULL, "username" VARCHAR(255) NOT NULL UNIQUE, UNIQUE ("emailAddress"), UNIQUE ("username"), PRIMARY KEY ("id"));

CREATE TABLE IF NOT EXISTS "TaskPermission" ("id" UUID , "permissionId" UUID REFERENCES "Permission" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "taskId" UUID REFERENCES "Task" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "userId" UUID REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("permissionId", "userId"), UNIQUE ("taskId"), PRIMARY KEY ("id"));

CREATE TABLE IF NOT EXISTS "UserTask" ("id" UUID , "taskId" UUID REFERENCES "Task" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "userId" UUID REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("taskId", "userId"), PRIMARY KEY ("id"));

INSERT INTO "Permission" ("id","name") VALUES ('9586138a-6b5a-47ce-81df-54b1f265b58b','create'),('caca4781-325a-4be0-bc3c-5cd0fc8beba4','read'),('d2836e00-b860-45c3-91e4-de595dad983d','update'),('53323cf5-85a6-401f-82c8-5e193c9b3f0f','delete');

INSERT INTO "Task" ("id","name") VALUES ('bc78380e-7edd-489c-944a-62a496e17755','System');

INSERT INTO "User" ("emailAddress","id","password","username") VALUES ('me@mogusbi.co.uk','c08670b5-c301-4225-a451-a9e8b9501f84','Password1!','mo.gusbi') RETURNING *;

INSERT INTO "User" ("emailAddress","id","password","username") VALUES ('test@mogusbi.co.uk','f2237f7f-215e-4611-b474-2c641f8ca45c','Password1!','test') RETURNING *;

INSERT INTO "UserTask" ("id","taskId","userId") VALUES ('49f21de4-1c8b-46b3-b08f-e951d7b0013d','bc78380e-7edd-489c-944a-62a496e17755','c08670b5-c301-4225-a451-a9e8b9501f84');

INSERT INTO "TaskPermission" ("id","permissionId","taskId","userId") VALUES ('e70a981f-2f69-45c1-8e27-d5519929cac0','9586138a-6b5a-47ce-81df-54b1f265b58b','bc78380e-7edd-489c-944a-62a496e17755','c08670b5-c301-4225-a451-a9e8b9501f84'),('53631a95-3d16-4daf-a730-9cdedb6a8a55','9586138a-6b5a-47ce-81df-54b1f265b58b','bc78380e-7edd-489c-944a-62a496e17755','f2237f7f-215e-4611-b474-2c641f8ca45c');

Also I should have mentioned this at the start but I am using postgres 9.6.3 and sequelize 4

[RobinBuschmann]

Hmm, ok. The following seems to cause the issue: UNIQUE ("permissionId", "userId"), UNIQUE ("taskId"). It should rather be UNIQUE ("permissionId", "userId", "taskId"). But I assume that this is a sequelize issue, not a sequelize-typescript one. If you have time to reproduce this with pure sequelize you could check this? If not, I can do so on next weekend - before Saturday I will not have time to do that.

For now you could try to overwrite the automatically created unique constraint in the TaskPermission model by explicitly setting the unique identifier:

@Table
export class TaskPermission extends Model<TaskPermission> {
  @Column({
    defaultValue: DataType.UUIDV4,
    primaryKey: true,
    type: DataType.UUID
  }) public id: string;

  @ForeignKey(
    () => Permission
  )
  @Column({unique: 'task_permission_user_key'})
  public permissionId: string;

  @ForeignKey(
    () => Task
  )
  @Column({unique: 'task_permission_user_key'})
  public taskId: string;

  @ForeignKey(
    () => User
  )
  @Column({unique: 'task_permission_user_key'})
  public userId: string;
}

Hopefully this helps

[mogusbi]

How odd, It didn't work - the unique constraints are completely ignored...

CREATE TABLE IF NOT EXISTS "TaskPermission" ("id" UUID , "permissionId" UUID REFERENCES "Permission" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "taskId" UUID REFERENCES "Task" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "userId" UUID REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("permissionId", "userId"), UNIQUE ("taskId"), PRIMARY KEY ("id"));

I'll have a go using sequelize alone and post the results

[mogusbi]

I've attempted to recreate this set up using just sequelize, you can find it here: https://github.com/mogusbi/sequelize-test

It appears to be working fine. Here are the generated queries

CREATE TABLE IF NOT EXISTS "Permissions" ("id" UUID , "name" VARCHAR(255) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));

CREATE TABLE IF NOT EXISTS "Tasks" ("id" UUID , "name" VARCHAR(255) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));

CREATE TABLE IF NOT EXISTS "Users" ("emailAddress" VARCHAR(255) NOT NULL UNIQUE, "id" UUID , "password" VARCHAR(255) NOT NULL, "username" VARCHAR(255) NOT NULL UNIQUE, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, UNIQUE ("emailAddress"), UNIQUE ("username"), PRIMARY KEY ("id"));

CREATE TABLE IF NOT EXISTS "TaskPermissions" ("id" UUID , "permissionId" UUID, "taskId" UUID, "userId" UUID, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, "TaskId" UUID REFERENCES "Tasks" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "PermissionId" UUID REFERENCES "Permissions" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "UserId" UUID REFERENCES "Users" ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("TaskId"), UNIQUE ("PermissionId", "UserId"), PRIMARY KEY ("id"));

CREATE TABLE IF NOT EXISTS "UserTasks" ("id" UUID , "taskId" UUID, "userId" UUID, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, "UserId" UUID REFERENCES "Users" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "TaskId" UUID REFERENCES "Tasks" ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("UserId", "TaskId"), PRIMARY KEY ("id"));

INSERT INTO "Tasks" ("id","name","createdAt","updatedAt") VALUES ('bc78380e-7edd-489c-944a-62a496e17755','System','2017-08-25 10:13:59.505 +00:00','2017-08-25 10:13:59.505 +00:00');

INSERT INTO "Permissions" ("id","name","createdAt","updatedAt") VALUES ('9586138a-6b5a-47ce-81df-54b1f265b58b','create','2017-08-25 10:13:59.490 +00:00','2017-08-25 10:13:59.490 +00:00'),('caca4781-325a-4be0-bc3c-5cd0fc8beba4','read','2017-08-25 10:13:59.490 +00:00','2017-08-25 10:13:59.490 +00:00'),('d2836e00-b860-45c3-91e4-de595dad983d','update','2017-08-25 10:13:59.490 +00:00','2017-08-25 10:13:59.490 +00:00'),('53323cf5-85a6-401f-82c8-5e193c9b3f0f','delete','2017-08-25 10:13:59.490 +00:00','2017-08-25 10:13:59.490 +00:00');

INSERT INTO "Users" ("emailAddress","id","password","username","createdAt","updatedAt") VALUES ('me@mogusbi.co.uk','c08670b5-c301-4225-a451-a9e8b9501f84','Password1!','mo.gusbi','2017-08-25 10:13:59.596 +00:00','2017-08-25 10:13:59.617 +00:00') RETURNING *;

INSERT INTO "TaskPermissions" ("id","permissionId","taskId","userId","createdAt","updatedAt") VALUES ('c2373b80-1ca7-4477-bb18-f22e62de24d5','9586138a-6b5a-47ce-81df-54b1f265b58b','bc78380e-7edd-489c-944a-62a496e17755','c08670b5-c301-4225-a451-a9e8b9501f84','2017-08-25 10:13:59.676 +00:00','2017-08-25 10:13:59.676 +00:00'),('73a8d9b4-02e3-4b11-9025-da558df0152d','d2836e00-b860-45c3-91e4-de595dad983d','bc78380e-7edd-489c-944a-62a496e17755','c08670b5-c301-4225-a451-a9e8b9501f84','2017-08-25 10:13:59.676 +00:00','2017-08-25 10:13:59.676 +00:00'),('4db156ad-15cf-4efd-846e-d35040f811d9','9586138a-6b5a-47ce-81df-54b1f265b58b','bc78380e-7edd-489c-944a-62a496e17755','f2237f7f-215e-4611-b474-2c641f8ca45c','2017-08-25 10:13:59.676 +00:00','2017-08-25 10:13:59.676 +00:00');

INSERT INTO "UserTasks" ("id","taskId","userId","createdAt","updatedAt") VALUES ('49f21de4-1c8b-46b3-b08f-e951d7b0013d','bc78380e-7edd-489c-944a-62a496e17755','c08670b5-c301-4225-a451-a9e8b9501f84','2017-08-25 10:13:59.692 +00:00','2017-08-25 10:13:59.692 +00:00');

[RobinBuschmann]

But the created queries are identical except of the naming and default timestamps. So that the wrongly defined Unique's are generated as well:

CREATE TABLE IF NOT EXISTS "TaskPermissions" ("id" UUID , "permissionId" UUID, "taskId" UUID, "userId" UUID, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, "TaskId" UUID REFERENCES "Tasks" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "PermissionId" UUID REFERENCES "Permissions" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "UserId" UUID REFERENCES "Users" ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("TaskId"), UNIQUE ("PermissionId", "UserId"), PRIMARY KEY ("id"));

See UNIQUE ("TaskId"), UNIQUE ("PermissionId", "UserId"), PRIMARY KEY ("id"));

[mogusbi]

You are correct. I've updated that repo I linked to - it throws this error when I try inserting data into the database

Error: insert or update on table "UserTask" violates foreign key constraint "UserTask_taskId_fkey"

[RobinBuschmann]

@mogusbi Since this isn't a sequelize-typescript issue, can you report it to sequelize then?

[mogusbi]

OK, I was able to get this working to create the following query

CREATE TABLE IF NOT EXISTS "TaskPermission" ("id" UUID , "permissionId" UUID, "taskId" UUID, "userId" UUID, "PermissionId" UUID, UNIQUE ("permissionId", "taskId", "userId"), PRIMARY KEY ("id"));

What I had to do is update my associations to the following (in addition to manually setting the unique index in the TaskPermission table):

Task.belongsToMany(models.Permission, {
    as: 'permissions',
    constraints: false, // <--- Added this
    foreignKey: 'taskId',
    through: {
        model: models.TaskPermission,
        unique: false // <--- Added this
    }
});

User.belongsToMany(models.Permission, {
    as: 'permissions',
    constraints: false, // <--- Added this
    foreignKey: 'userId',
    through: {
        model: models.TaskPermission,
        unique: false  // <--- Added this
    }
});