🐜

[Prisma] Beware of bugs when removing default values [MySQL]

2025/02/10に公開

MySQL

Introduction

I created an issue.

Environment

MySQL 8 series
Prisma 5.22.0 ~ 6.3.1 (the latest as of 2025-02-10)

Occurrence

    status String? @default("hoge")

For a field that is both nullable and has a default value, when you remove just the default value during migration:

    status String?

a bug occurs.

Issue

When you pass undefined for the above field during a create, Prisma unexpectedly throws a violation stating that the field is "not nullable":

Null constraint violation on the fields: (`status`)

Of course, if you omit the property entirely in the create call, the same error occurs. However, for some reason, explicitly passing null makes it succeed. Strange.

By the way, checking the debug logs shows that no query is issued to the database, which confirms that this is entirely an issue on Prisma’s side.

Cause

Let’s take a look at the DDL.

-   `limit_kw` decimal(10,6) DEFAULT "hoge",
+   `limit_kw` decimal(10,6) DEFAULT NULL,

Ideally, it should be as shown above. When running prisma db push, the DDL is generated in the above form and the bug does not occur.

-   `limit_kw` decimal(10,6) DEFAULT "hoge",
+   `limit_kw` decimal(10,6),

The above shows the state when the bug occurs. It seems that the system only distinguishes between “removing the default” and “dropping the DEFAULT” constraint. Ideally, DEFAULT NULL should remain. Well, even if it doesn’t, MySQL still allows nulls, but Prisma explicitly requires the default to be specified.

ALTER TABLE `task` ALTER COLUMN `status` DROP DEFAULT;

Workarounds

1. Explicitly pass `null`.

As mentioned above:

await prisma.task.create({
    data: {
        title: 'Task 1',
        status: null,
    },
});

However, this is not very elegant. What if you want to apply the default value later? It’s clear that this approach reduces maintainability.

2. Reapply the migration

This method restores DEFAULT NULL.

First, change the schema to:

    status String

(Make it non-nullable first, then run prisma migrate dev.)

After that, change it back to:

    status String?

(Make it nullable again, and run prisma migrate dev once more.)

This will create two migration SQL files. Delete the first one entirely. With this, the DDL becomes as intended and the process succeeds.