Loopback-next: [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.

[Bernd-L]

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 ?

[achrinza]

Just adding my 2 cents;

I think the basic implementation should be done as this is a common requirement in logical database designs with sub-types and weak entities where the IDs are not unique by themselves.

[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?