Unique constraint failed for key-value tables

When testing Prisma client extensively via test-utils automation, we noticed a pattern.

Many databases have the following pattern in a table to store key-value pairs (usually for a config):

CREATE TABLE `copp_config` (
  `name` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
  `value` varchar(255) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci COMMENT='Used to store the configuration options';

In this case, name is a string with default '' without any auto increment value (database or via Prisma) via this model:

model copp_config {
  name  String @default("") @id
  value String @default("")
}

This generates a Prisma client with optional ID.

export type copp_configCreateInput = {
  name?: string | null
  value?: string | null
}

prisma.copp_config.create({data: { valye } })

Since, tools like test-utils see that the ID is optional, they don't supply an id field.

This fails for a second insert without supplying the ID field at runtime with a unique constraint failure.

Note: the suggested workarounds are independent and can be applied in isolation.

Workaround 1

We can identify this case and generate id field for this model as a required field in Prisma client (opinionation). This requires the user to supply a value for the first insert as well, which otherwise could have been handled via the default value.

Workaround 2

Prisma test utils can use the default value from dmmf to determine this case and provide an ID. (See internal discussion https://prisma-company.slack.com/archives/CEYCG2MCN/p1583489586176600 with information what this looks like in dmmf)

Test utils can provide ID field data manually (instead of relying on the default) when we encounter a field that is an ID, String and has a fixed default like ('', '0').

Database affected in test-utils:

prisma / prisma-test-utils

Unique constraint failed for key-value tables #56

Workaround 1

Workaround 2