Spike: Declarative definition of FOREIGN KEY and UNIQUE constraints

[bajtos]

To support strong relations with referential integrity enforced by the database, we need a mechanism allowing models to define FOREIGN KEY and UNIQUE constraints in a declarative way.

Let's consider the relation "Customer has one Account", using account.customerId as the relational property. As part of the domain model definition, we need to define the following constraints:

• FOREIGN KEY: the value stored in account.customerId must be an existing customer id.
• UNIQUE: there can be at most one record (Account instance) with a given customer id.

Things to consider:

• UNIQUE constraint can be composite. E.g. on GitHub, we want the combination of (user, repo) to be unique.

• FOREIGN KEY constraints can be composite too. See e.g. here:

  FOREIGN KEY (ref_num, ref_type) REFERENCES accounts
    (acc_num, acc_type)

• In LB3 (and loopback-datasource-juggler), we may already have partial support for foreign keys.

Acceptance criteria

• Read through juggler code-base and find out what's the current implementation.
• Describe the current implementation - what works, how does it works, what is missing. (E.g. composite constraints).
• Propose a solution for expressing FK/UNIQUE constraints in model/property definitions. (No decorators involved here.)
• Propose a solution for defining FK/UNIQUE constraints using TypeScript decorators. Do we want to define new decorators like @foreignKey() and @unique()?
• Create a list of user stories to implement the proposals

There should be a pull request with any spike/PoC bits of code used to verify the proposals, and a /_SPIKE_.md file with the spike documentation (description or the current status, high-level overview of the proposals, etc.)

Out of scope

• Implementation in our connectors - see Memory connector should enforce referential integrity #2333 and Database migration should create SQL constraints for relations #2332

[emonddr]

Timebox 2.5 weeks please.

[bajtos]

spike pull request: #2712

[bajtos]

Created the following follow-up stories:

• New syntax for INDEX and FOREIGN KEY definition New syntax for INDEX and FOREIGN KEY definition #2766
• Document how to configure indexes and foreign keys Document how to configure indexes and foreign keys #2767
• Connector helpers to obtain INDEX and FOREIGN KEY configurations from model definitions Connector helpers to obtain INDEX and FOREIGN KEY configurations from model definitions loopback-connector#143
• Spike: a template implementation of index & constraint migration in SqlConnector Spike: a template implementation of index & constraint migration in SqlConnector loopback-connector#144

Closing this spike as done.

[achrinza]

@odykyi Thanks for the ethusiasm to help others! ❤️

However please refrain from re-posting the same solution on other issues unless it directly resolves the issue at hand.

This may pollute the contributors' (email) notifications which may make it difficult to sort through other issues.

For example, this is a spike for a new feature that has already been closed. Hence a workaround isn't needed.

For others closed/stale issues, it's better to open a new issue if you're experiencing a similar issue as the codebase may have changes significantly since the original issue was opened.

Nonetheless, I hope this doesn't discourage from future contributions to open source projects, including LoopBack 😃