Non-indexed relationship fields – better defaults and optional config

[molomby]

Currently, anywhere a foreign key is stored in the DB, the field is indexed. Ideally, the creation of an index would:

• Have a sensible default, based on the relationship fields configured. Ie. sometimes not being automatically added, unlike from the current behaviour.
• Be configurable, to support cases where the developer has something specific in mind. Eg. direct DB access, independent of the Keystone API).

Current Behaviour

Currently, anytime Keystone creates a relationship all foreign keys added to the DB are indexed. In many cases, this makes sense. Certainly, if you have a corresponding relationship field setup on the other list, there's an implication you'll be selecting items from that "side" so a index is probably needed. So, for example this:

export const lists: Lists = {
  Author: list({
    access: allowAll,
    fields: {
      name: text({ validation: { isRequired: true } }),
      posts: relationship({ ref: 'Post.author', many: true }),  // ⬅️ We want to select posts from the author side of the rel
      // ...
    },
  }),
  Post: list({
    access: allowAll,
    fields: {
      title: text({ validation: { isRequired: true } }),
      author: relationship({ ref: 'Author.posts' }),  // ⬅️ We want to select the author from the post side of the rel
      // ...
    },
  }),
  // ...
};

Produces...

CREATE TABLE "Author" (
    id text PRIMARY KEY,
    name text NOT NULL DEFAULT ''::text,
    -- ...
);
CREATE UNIQUE INDEX "Author_pkey" ON "Author"(id text_ops);
CREATE UNIQUE INDEX "Author_email_key" ON "Author"(email text_ops);
CREATE TABLE "Post" (
    id text PRIMARY KEY,
    title text NOT NULL DEFAULT ''::text,
    author text REFERENCES "Author"(id) ON DELETE SET NULL ON UPDATE CASCADE
    -- ...
);
CREATE UNIQUE INDEX "Post_pkey" ON "Post"(id text_ops);
CREATE INDEX "Post_author_idx" ON "Post"(author text_ops);   -- ⬅️ Will be used when reading the `Author.posts` field

Which is probably correct.

But if we don't need to retrieve posts according to their author, surely that index is unneeded. Eg, ideally, this would not produce the same DB schema:

export const lists: Lists = {
  Author: list({
    access: allowAll,
    fields: {
      name: text({ validation: { isRequired: true } }),
      // posts: relationship({ ref: 'Post.author', many: true }),  // ⬅️ Field removed!
      // ...
    },
  }),
  Post: list({
    access: allowAll,
    fields: {
      title: text({ validation: { isRequired: true } }),
      author: relationship({ ref: 'Author' }),   // ⬅️ This is resolved using the Author primary key (as before); no additional index needed
      // ...
    },
  }),
  // ...
};

A similar situation arrises with many-to-many relationships (which may also only be accessed from one side) but that case is slightly more complex as a unique constraint is needed across both columns to prevent duplicate records. Currently, you'll see a DB schema like this:

CREATE TABLE "_Post_contributors" (
    "A" TEXT NOT NULL,
    "B" TEXT NOT NULL
);
CREATE UNIQUE INDEX "_Post_contributors_AB_unique" ON "_Post_contributors"("A", "B");
CREATE INDEX "_Post_contributors_B_index" ON "_Post_contributors"("B");

Sometimes, that second, non-unique index is redundant.

Solution Thoughts

As above, Keystone can do a better job of defaulting based on what it already knows but I believe it should be possible to override the default using the conventional isIndexed: true | false | 'unique' config. Most field types support this option – relationship fields currently do not.

[asgeo1]

Yup, having a similar issue. I want to pass isIndexed: 'unique' to my relationship to prevent duplicate data, but it seems to not be possible currently.

[marekryb]

You can do this through extendPrismaSchema (like in this example) by adding @unique() to prisma schema directly.
Not streamlined approach but should work.