Temporal Tables

[lohart13]

Issue Creation Checklist

• I understand that my issue will be automatically closed if I don't fill in the requested information
• I have read the contribution guidelines

Feature Description

Temporal Table support

Describe the feature you'd like to see implemented

SQL has built in temporal table support in some of the dialects.
This allows for the capture of history/changes made in a table (system versioned), tracking/enforcing data with a time period (application time/period) or both (bi temporal).

There is the option of using bonaval/sequelize-temporal which uses hooks to accomplish history/changes, but it doesn't have the full temporal table support.

The advantages of temporal tables managed at SQL level is:

• The database is in charge of managing the history data.
• Table can we queried to retrieve point in time values

The largest disadvantage is that not all dialects support this feature.
Currently I have found that MSSQL, DB2 and MariaDB support temporal tables however the implementation is different (in the case of MariaDB its very different and lacks support for queries for getting the periods).

I don't know enough about DB2 and MariaDB however I have been using system versioned temporal tables in MSSQL successfully, PR to follow.

Describe why you would like this feature to be added to Sequelize

The advantages of temporal tables managed at SQL level is:

• The database is in charge of managing the history data.
• Table can we queried to retrieve point in time values using existing find/findByPk/findOne queries.

It may be possible to move to a separate package; I don't know enough around the code splitting in Sequelize to see if it's possible to get the same level of integration with the find queries and table creation/altering.
If the same level of integration is not possible, it would be better off using bonaval/sequelize-temporal as this should work with any dialect.

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):
  MSSQL supports system versioned temporal tables.
  DB2 supports application period, system versioned and bi temporal tables
  MariaDB does supports application period, system versioned and bi temporal it however it lacks some features which would make it hard to implement for.

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I will need guidance.
• No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
• No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in implementing my feature.

---

Indicate your interest in the addition of this feature by adding the 👍 reaction. Comments such as "+1" will be removed.

[fzn0x]

I think this is a good addition, haven't heard about it before, I will add the label type: feature for this interesting feature :)