How to create a foreign key with NOT NULL in Keystonejs 6?

Question

I wish to know how to create a foreign key with NOT NULL in Keystonejs 6 Schema file. I use postgresQl AND ORM Prisma. I can't create a relationship field with isRequired = true, which means NOT NULL. Someone can explain how to add NOT NULL for the relationship field in Keystonejs 6 Schema file? Either maybe it's impossible?

Answer 1

Yeah, relationship fields currently don't support the validation.isRequired or db.isNullable options. This is true even when the list being configured holds the foreign key (ie. a many-to-one relationship or one-to-one with db.foreignKey: true).

There are plans to support these options but the work isn't trivial. For example, these constraints can effect the order in which nested creates need to be performed. Keystone will also need to validate the config makes sense and doesn't for example, have isNullable: false on both sides of a one-to-one relationship (which would make inserting records impossible).

If you want to emulate similar functionality right now it's possible using hooks. I think you'd need...

• A validate-input hook on the list with the foreign key, to ensure a item was linked on create (and not removed on update)
• validate-input and validate-delete hooks on the other list to ensure links weren't broken when updating or deleting items from the other side.

Since this solution's implemented in the app layer it doesn't give you as strong a guarantee as a proper database constraint, but it's a start.

Answer 2

2023 Update: db.extendPrismaSchema

There's a better way to do this now using the db.extendPrismaSchema config. It lets developers override or modify the Prisma schema Keystone generates at the field-level, list-level or for the whole project.

Configuration takes the form of a function that's given the Prisma schema text that would usually be used. This gives you the opportunity to either mutate the snippet, replace it entirely or use something like loancrate/prisma-schema-parser to parse the string and manipulate it as an abstract syntax tree.

The extend-prisma-schema example project has some usage, including an example of solving the problem raised here – a relationship field that produces a non-nullable DB column:

  tags: relationship({
    ref: 'Tag.posts',
    db: {
      extendPrismaSchema: field => {
        return field
          .replace(/tags Tag\?/g, 'tags Tag')
          .replace(/tagsId String\?/g, 'tagsId String');
      },
    },
  }),

Keystone relationship fields add two fields to the Prisma schema; the code above removes the ? symbol from both field definitions, resulting in a non-nullable column in the DB.