[Prisma] Beware of bugs when removing default values [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
null
.
1. Explicitly pass 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.
Discussion