keystonejs form a multi-column unique constraint

Question

How to form a unique constraint with multiple fields in keystonejs?

const Redemption = list({
  access: allowAll,
  fields: {
    program: relationship({ ref: 'Program', many: false }),
    type: text({ label: 'Type', validation: { isRequired: true }, isIndexed: 'unique' }),
    name: text({ label: 'name', validation: { isRequired: true }, isIndexed: 'unique' }),
  },
  //TODO: validation to check that program, type, name form a unique constraint
})

Answer 1

The best way I can think to do this currently is by adding another field to the list and concatenating your other values into it using a hook. This lets you enforces uniqueness across these three values (combine) at the DB-level.

The list config (and hook) might look like this:

const Redemption = list({
  access: allowAll,
  fields: {
    program: relationship({ ref: 'Program', many: false }),
    type: text({ validation: { isRequired: true } }),
    name: text({ validation: { isRequired: true } }),
    compoundKey: text({
      isIndexed: 'unique',
      ui: {
        createView: { fieldMode: 'hidden' },
        itemView: { fieldMode: 'read' },
        listView: { fieldMode: 'hidden' },
      },
      graphql: { omit: ['create', 'update'] },
    }),
  },
  hooks: {
    resolveInput: async ({ item, resolvedData }) => {
      const program = resolvedData.program?.connect.id || ( item ? item?.programId : 'none');
      const type = resolvedData.type || item?.type;
      const name = resolvedData.name || item?.name;
      resolvedData.compoundKey = `${program}-${type}-${name}`;
      return resolvedData;
    },
  }
});

Few things to note here:

• I've removed the isIndexed: 'unique' config for the main three fields. If I understand the problem you're trying to solve correctly, you actually don't want these values (on their own) to be distinct.
• I've also remove the label config from your example. The label defaults to the field key so, in your example, that config is redundant.
• As you can see, I've added the compoundKey field to store our composite values:
  • The ui settings make the field appear as uneditable in the UI
  • The graphql settings block updates on the API too (you could do the same thing with access control but I think just omitting the field is a bit cleaner)
  • And of course the unique index, which will be enforced by the DB
• I've used a resolveInput hook as it lets you modify data before it's saved. To account for both create and update operations we need to consult both the resolvedData and item arguments - resolvedData gives us new/updated values (but undefined for any fields not being updated) and item give us the existing values in the DB. By combining values from both we can build the correct compound key each time and add it to the returned object.

And it works! When creating a redemption we'll be prompted for the 3 main fields (the compound key is hidden):

And the compound key is correctly set from the values entered:

Editing any of the values also updates the compound key:

Note that the compound key field is read-only for clarity.

And if we check the resultant DB structure, we can see our unique constraint being enforced:

CREATE TABLE "Redemption" (
    id text PRIMARY KEY,
    program text REFERENCES "Program"(id) ON DELETE SET NULL ON UPDATE CASCADE,
    type text NOT NULL DEFAULT ''::text,
    name text NOT NULL DEFAULT ''::text,
    "compoundKey" text NOT NULL DEFAULT ''::text
);

CREATE UNIQUE INDEX "Redemption_pkey" ON "Redemption"(id text_ops);
CREATE INDEX "Redemption_program_idx" ON "Redemption"(program text_ops);
CREATE UNIQUE INDEX "Redemption_compoundKey_key" ON "Redemption"("compoundKey" text_ops);

Attempting to violate the constraint will produce an error:

If you wanted to customise this behaviour you could implement a validateInput hook and return a custom ValidationFailureError message.