How to tell Sequelize to not insert if multiple rows already have the same value in multiple columns-CodePudding

Disclaimer: I'm a front end engineer all this is new to me.

Building a reporting service that schedules reports.

Using sequelize and postgress. I want to make sure that you cant have multiple entries with the same userId/email/cadence/siteId columns.

For example if user:9, email: [email protected], cadence: weekly, siteId: 4 already exist in DB don't insert the same data gain.

To the best of my research I'm attempting to add constraints to the migration file which is as follows:

exports.shorthands = undefined;

exports.up = (pgm) => {
  pgm.createTable(
    'report',
    {
      id: 'id',
      site_id: { type: 'varchar(255)', notNull: true },
      report_name: { type: 'varchar(255)', notNull: true },
      user_email: { type: 'varchar(255)', notNull: true },
      portfolo_id: { type: 'varchar(40)' },
      cadence: { type: 'varchar(40)', notNull: true },
      user_id: { type: 'varchar(40)', notNull: true },
      createdAt: {
        type: 'timestamptz',
        notNull: true,
        default: pgm.func('current_timestamp'),
      },
      updatedAt: {
        type: 'timestamptz',
        notNull: true,
        default: pgm.func('current_timestamp'),
      },
    },
    {
      constraints: {
        unique: ['site_id', 'user_email', 'portfolo_id', 'cadence'],
      },
    },
  );
  pgm.createIndex('report', 'cadence');
  pgm.createIndex('report', 'user_id');
  pgm.createIndex('report', 'user_email');
};

exports.down = (pgm) => {
  pgm.dropTable('report');
};

everything loads up fine but I'm able to hit my endpoint over and over with the same payload.

Any help would be greatly appreciated.

CodePudding user response：

You can simply use the findOrCreate() method.

Edit to add for further clarity: You would not use the findOrCreate() method in the migration that you've shared here, but instead in the code for the endpoint you have described hitting over and over.