Composite Primary Key Support

[mrhwick]

Composite Primary Keys

Related Issues:

I searched for all issues that reference composite primary keys in some way. Most of these are directly issues resulting from our lack of composite key support. Some only indirectly involve composite keys.

• attach with additional attributes #29
• Failed withRelated three tables #82
• Allowing composite idAttribute on model #86
• Composite idAttributes #93
• save 'succeeds' but no record added to table in db #119
• Using through() without setting extra id column in join table #125
• Updating a record in a joining table #134
• how to get and use attributes of model while loading relations #178
• How do I change the insert statement or any other statement to return something other than 'id'? #203
• Any documentation on composite keys? #279
• Error when destroy model with composite key #344
• How to handle composite ids? #360
• model.save() attempts to update and then throws error when hasChanged() is false #372
• query.del with composite key #420
• How to do multiple inserts in one query? #445
• composite id: how to handle them #663
• Updating a model with a composite primary key #720
• save() always calls insert, never update #727
• Multiple column foreign key and associations? #835
• How do I get insert to omit 'returning id'? #853
• How to deal with multiple column idAttribute #865
• How to pass an attribute (different to idAttribute) with 'withRelated' #1010
• Composite keys in relations #1016
• Null relations ignored on Model#serialize despite omitNew being false #1421
• Composite Unique Index Insert or Update #1439
• postgresql returning id where sqlite works just fine #1490

Introduction

Composite or Compound primary keys are used when a single column value is not enough to define the unique ID of a record on some table. These are widely used, especially in the context of multi-tenant applications. There have been many calls for implementation of composite primary keys.

[bgerd]

Just discovered this while migrating an existing postgres schema to Bookshelf. Is there a best practice pattern / workaround for handling composite primary keys?

All I could find is that you have to override idAttribute with a getter returning null ... https://bookshelfjs.org/api.html#Model-instance-idAttribute

[pkantsedalov]

@bgerd I am trying that on the latest versions of both bookshelf and knex and have the same problem: it tries to fetch data using null column. So, the solution doesn't seem to be workable in the latest versions.

By the way, this feature is a must. Without that bookshelf is dying: you can't upgrade your dependencies to the latest versions w/o composite PKs

[jayarjo]

If it doesn't support composite keys, how on earth does it claim to support Many-to-Many?

[keyvanestermann]

If it doesn't support composite keys, how on earth does it claim to support Many-to-Many?

Bookshelf assumes (wrongly) that a pivot table HAS to have an "id attribute", when most pivot tables are composed with the primary keys of the individual entities in your many-to-many relation.

I am baffled this is still an issue, since I have been reading about it for 3 years.

If anyone is reading this : my solution to this problem was to create an "ID" field on my table, set on auto_increment with a UNIQUE index.
It serves absolutely no purpose, but Bookshelf is happy.