Composite **Primary Keys** – HowTo? (Prisma is fine, SDL generator fails)

Get Help and Help Others
1

I’ve created a simple table with a compound (composite) primary key, which got generated fine by yarn rw prisma migrate dev. This is how it looks like:

model InterviewParticipantInfo {
  tokenId String
  key     String
  value   String

  @@id([tokenId, key])
}

However running yarn rw g sdl InterviewParticipantInfo (with and without --crud) gives:

  ┌─────────────────────────────────────────────────────────────────────────┐
  │                                                                         │
  │   WARNING: Cannot generate CRUD SDL without an `@id` database column.   │
  │                                                                         │
  │   If you are trying to generate for a many-to-many join table           │
  │   you'll need to update your schema definition to include               │
  │   an `@id` column. Read more here:                                      │
  │   https://redwoodjs.com/docs/schema-relations                           │
  │                                                                         │
  └─────────────────────────────────────────────────────────────────────────┘

← Reverted because: Failed: Could not generate SDL
◼ Generating types ...

I only found this post from @rob mentioning in 2020:

We don’t currently know what to do about composite keys, the @@id([postId, categoryId]) bit.

But that post was all about many-to-many relationships. Also all of the docs mention the @@id only twice on the very same topic. But as once can clearly see, the above model is not trying to generate a many-to-many relationship here. Just a good old composite primary key, which the docs unfortunately don’t mention anywhere afaik.

2

I think --crud might now be the default. Did you try with --no-crud?

3

That post may have been about many-to-many posts, but it applied to any @@id with multiple columns. We still haven’t looked into modifying this behavior that I’m aware of. We strongly suggest a single column id primary key! It’s really easy to add one, even if you never refer to it (you can have all of your services work on the composite key values). This was a convention we borrowed from Rails as it greatly simplifies things.

If you really don’t want to add that id column, I think your smoothest path will be to copy an existing SDL and modify it to match your table’s schema.

4

There is an old PR that aims to support compound keys in scaffolding. They work fine if you compose the Prisma model, SDL, and implement the Prisma query accordingly. But the forms and pages for scaffolding are not there.

See: DRAFT: Support compound primary key ids in SDL and Scaffold generators by dthyresson · Pull Request #6051 · redwoodjs/redwood · GitHub

I cannot recall how, why, where I stopped implementation … I think it was because of the changes needed to the test scenario generation and logic.

Happy to have someone take it on and move it to next steps.

5

Note: there are some considerations when using compound keys in GraphQL with Apollo Client.

Typically, you cache key will be “Typename:ID” where the id is your primary key or id field in your SDL.

If you intend to use Apollo cache or caching of fragments, you’ll need to add typePolicies to indicate what the cache key needs to be. ie: “Patricipant:tokenid|key” os something to that effect.

See docs on type policies here: Customizing the behavior of cached fields - Apollo GraphQL Docs

One alternative is to:

  • keep id as your primary key
  • add a unique constraint/index on the compound fields.

This way sdl, scaffolding will work and the compoundness of the info is intact.

Not ideal perhaps, but a workaround.