Support adding a NOT VALID to foreign key constraint in Postgres

[shayonj]

Issue Creation Checklist

• I have read the contribution guidelines

Feature Description

Postgres has the ability to add NOT VALID to a foreign key constraint. When added, it skips the long check on a table. In certain cases this can be a desirable and more reliable way of adding constraints on large production databases.

It'd be great if there was an easy and nice way to support this using the ORM DSL, instead of having to write raw SQL to accomplish the task.

Is your feature request related to a problem? Please describe.

No

Describe the solution you'd like

It'd be great if there was an easy and nice way to support this using the ORM DSL, instead of having to write raw SQL to accomplish the task.

Example: Introducing a notValid: true key in the options object. Default being false, to preserve existing behavior.

queryInterface.addConstraint('Posts', {
  fields: ['username'],
  type: 'foreign key',
  name: 'custom_fkey_constraint_name',
  references: { //Required field
    table: 'target_table_name',
    field: 'target_column_name'
  },
  notValid: true,
});

Why should this be in Sequelize

Since the query being generated is from sequelize query interface, it perhaps makes sense for this to be in sequelize package.

Describe alternatives/workarounds you've considered

Writing raw SQL statements.

Additional context

This feature is geared more towards giving developers simpler options to write more reliable SQL statements when working on large production databases (albeit with tradeoffs). In similar vein as #13901. Having this would mean that developers can use the DSL over raw SQL, making it a good dev UX.

Feature Request Checklist

Is this feature dialect-specific?

• No. This feature is relevant to Sequelize as a whole.
• Yes. This feature only applies to the following dialect(s): Postgres

Would you be willing to implement this feature by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I don't know how to start, I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

I am more than happy to work on this and create PR if/when the feature is approved. Thank you!

[WikiRik]

Sounds good, looking forward to the PR!

[shayonj]

Is it possible for this feature #13910 (once approved and merged) and #13903 (already approved & merged) to be released as part of V6?

[WikiRik]

Is it possible for this feature #13910 (once approved and merged) and #13903 (already approved & merged) to be released as part of V6?

I've put them in the v6 project so we'll try to include them in a future v6 release, thanks for your message!