How to generate fake records in a relation N-M in Sequelize

Question

I have two tables: User and Scope; with the cardinality of N (User) -> M (Scope). When I insert fake records inside the User and the Scope tables, everything goes well, but if I insert fake records (ids from the User and Scope tables) inside the UserScope relation table, which represents the relation between User and Scope, I receive the errors:

1:

Unhandled rejection SequelizeUniqueConstraintError: Validation error

2:

Unhandled rejection SequelizeForeignKeyConstraintError: Cannot add or update a child row: a foreign key constraint fails (`graph`.`userScope`, CONSTRAINT 'userScope_ibfk_1' FOREIGN KEY (`scopeId`) REFERENCES `scope` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

I had tried to debug via MySQL console (SHOW ENGINE INNODB STATUS\G):

CONSTRAINT 'userScope_ibfk_1' FOREIGN KEY ('scopeId') REFERENCES 'scope' ('id') ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index userScope_userId_scopeId_unique tuple:
DATA TUPLE: 3 fields;
 0: len 4; hex 80000008; asc     ;;
 1: len 4; hex 8000000a; asc     ;;
 2: len 4; hex 80000001; asc     ;;

But in parent table 'graph'. 'scope', in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 696e66696d756d00; asc infimum ;;

And I had found this question that makes me think that the way I'm inserting data in the relation table is not right, i.g, duplicate relations are being added in the relation table.

I'm inserting the relations this way:

import db from "./models";

import faker from "faker";

import times from "lodash.times";

import random from "lodash.random";

const amount = 10;

db.user.bulkCreate(
  times(amount, () => ({
    email: faker.internet.email(),
    password: faker.internet.password(),
    name: `${faker.name.firstName} ${faker.name.lastName}`,
    birth: Date.now()
  }))
)

db.scope.bulkCreate(
  times(amount, () => ({
    title: faker.hacker.verb()
  }))
)

db.userScope.bulkCreate(
  times(amount, () => ({
    scopeId: random(1, amount),
    userId: random(1, amount)
  }))
);

The tables:

I expect to insert fake relations in the UserScope table, without any errors and in a random way.

Obs: I had set the amount to 1 / set manually the ids and I still receiving this error. I had also already read threads like this one, this one but...

score 1 · Accepted Answer · 2019-05-26T01:28:08.583

The cases that can cause the problem:

A field has the constraint unique and the rows have duplicated values, for the unique field
A table reference another table that don't have reverential integrity constraints
The rows values of the reference table don't match some of the referenced key values
The data type of the primary keys are different, e.g MySQL sometimes convert the types in a different way for the same data type

My case is the first one. I was using the random function of Lodash and the functions of Faker, but none of the available functions helped me. I researched and found Chance, but again this had not helped, so I write my script for generate random values and use it in a way that they don't repeat:

const randomNumbers = (length, start=1) => {
  let array = [...Array(length).keys()].map(value => start + value);

  array.sort(() => Math.random() - 0.5);

  return array;
};

export { randomUniqueNumbers };

In the models:

// ...

import { randomUniqueNumbers } from "./util";

let scopeIds = randomUniqueNumbers(length);

let userIds = randomUniqueNumbers(length);

db.userScope
  .bulkCreate(
    times(length, () => ({
      scopeId: scopeIds.pop(),
      userId: userIds.pop()
    }))
  )
  .then(userScope => {})
  .catch(error => console.log(error));

My project on Github.

The relations:

To test the N-M relation I had add another row in the relation table to prove that one scope can be used for one or more users:

INSERT INTO userScope(createdAt, updatedAt, scopeId, userId) VALUES(STR_TO_DATE('18,05,2019','%d,%m,%Y'), STR_TO_DATE('18,05,2019','%d,%m,%Y'), 1, 1);

Obs: If you don't like the Graph QL + Sequelize + Node, I recommend the Prisma package.

Obs ²: Apply the same idea for the other tables that has unique fields

Obs ³: Another option is to implement your own function that uses the concept of seed, like Numpy Random

References:

Cannot add or update a child row a foreign key constraint fails

MySQL error 1452

Foreign key

Unique

Sequelize belongs to many

Foreign key MySQL

How to generate fake records in a relation N-M in Sequelize

1 Answers1