[EPIC] Composite primary/foreign keys

[bajtos]

See https://github.com/strongloop/loopback/issues/2080 for the original discussion in LB 2.x/3.x.

LoopBack should support models using composite keys, where a primary key or a foreign key is composed from multiple properties.

Let's consider the following models as an example:

// A (GitHub) repository is identified by the pair [user, name]
// and has many issues
@model
class Repository {
  @property({id: true})
  user: string;

  @property({id: true}
  name: string;

  @hasMany(() => Issue)
  issues: Issue[];
}

// An issue belongs to a repository
@model
class Issue {
  // how to specify @belongsTo relation?
}

Functional areas we need to cover:

• How to define a model with a composite primary key (e.g. Repository above)?
• Connectors must understand composite ids and convert them to a concept the target database understands. SQL is easy because it supports composite primary keys OOTB, NoSQL like MongoDB and Cloudant can be tricky.
  • create a new model
  • find a model by id
  • update (patch/replace) a model by id
  • delete a model by id
  • (etc.)
• Autoupdate/automigrate need to support composite primary keys too
• How to define composite foreign key in a model belonging to another model (e.g. Issue above)
• Relation repositories must work with composite keys
• How to expose a model with a composite primary key via REST API?
• How to expose a relation using a composite foreign key via REST API?
• Controller templates need to produce code that works with composite primary/foreign keys.

[lucaslenz]

+1

[evalenzuela]

+1

[mathiasarens]

+1

[bajtos]

@sneakyLance @evalenzuela @mathiasarens Posting +1 comments is rather unhelpful. Not only it spams people watching this issue with comments that are not much relevant, it also makes it difficult for us to see which issue as more popular than others. Please upvote the issue by pressing the 👍 button at the bottom of the issue description.

[creatornader]

Upvoting this for visibility, as I am dealing with the same issue currently. It's a major need for any relational database as you stated. In lieu of native Loopback support (or until we get that), can't we just use a single primary key and define a new method which takes all entries in the database and does a dumb search to find the match for the second key? I'm relatively new to Loopback so forgive me in any conceptual shortcomings.

[bajtos]

can't we just use a single primary key and define a new method which takes all entries in the database and does a dumb search to find the match for the second key? I'm relatively new to Loopback so forgive me in any conceptual shortcomings.

That may work for queries, but how would you go about enforcing uniqueness when creating new records?

[ffflabs]

I'm trying to do it using findorCreate and upsertwithWhere. However you are limited to POST requests, since you can't address the instance itself with PUT

[SouthRibbleTech]

I need to have a composite key made up of 3 columns, so the solution needs to be flexible, not just to handle two columns but as many as the developer requires.

[Tanja-4732]

What's the best workaround for this? Or are we stuck to denormalization?

[sestienne]

Hi, When we use a primary key as a string, when an object is created with POST method, the response is given with a number on the primary key instead of what we specified. Any reason/idea why ?

[htrujill]

Any update on this?

[gioppoluca]

We also need this. Any update?

[cardea-fletcher]

I spent a long time debugging my app before realizing this functionality wasn't supported :(

Even though it's possible to define a model that enforces uniqueness via 2 properties marked as id: 1 and id: 2, it won't support other database operations, like delete or update --> it simply fails with a "bad SQL message"

[AceCodePt]

Any update on this?

[abhayverma]

We are using loopback for our enterprise solution, but for the use case of having Composite IDs we are hitting a roadblock with Loopback4. I don't want to opt for a npm migration package since it will add up the db code reference touchpoint and code size. Please suggest when will this be released for lb4?

[KingGoujian]

Also interested in this. Any update?

[dan-stauffer-aus]

wow, this issue was identified 5 years ago....is it ever going to be supported?

[tsraza]

TypeORM supports joining on multiple foreign keys: https://typeorm.io/#/relations/joincolumn-options

[MartialSeron]

Hi, First comment in 2022 ! Any update on this very needed feature ?

[achrinza]

Hi @MartialSeron, just to give an update:

Generally ORM-related issues are soft-blocked by the TypeScript definitions enhancement effort for the underlying Juggler ORM (see: https://github.com/loopbackio/loopback-datasource-juggler/pull/1904) of which the benefit would surface we work on enhancing the @loopback/repository abstraction layer.

The good news is that we've managed to make substantial progress on that front which means that we now have better insights into ways that this feature could be implemented. Though there's still a number of nooks and crannies that needs to be ironed out.

The other good news is that a number of the Juggler relational database connectors already have support for composite IDs within the Models themselves, which means that, in theory, implementing this feature would be immediately usable on those aforementioned connectors without additional work.

There's no ETA as it's not being actively worked on, but we are always open to contributions in the form of discussions or PRs from the community just like with the latest ORM-related pull requests to implement referencesMany and polymorphic relations.

[MartialSeron]

Thanks for the update @achrinza. I'm trying Loopback for a new project and still discovering it (still struggling with the basics of Typescript by the way) but when I feel more confident about it, maybe I'll dig into that.

[ewrayjohnson]

Composite keys/indexes are use for other than the primary key. This feature was instrumental on apps I delivered using LB3 for performance and uniqueness constraints. With LB3 no longer supported, this makes the migration to LB4 less attractive than other framework alternatives. I wonder why so many features of LB3 have still not been implemented in LB4 after so much time?