Make a change to the database with Prisma.js without having to reset the whole thing

Question

How can make a change to the database with Prisma.js without having to reset the whole thing?

if I have used this command

npx prisma migrate dev --name role-makeOptional-NormalizedName

I will lose all of the data in my database but I don't lose my data.

In my case I wanted to change String to String? in schema.prisma file

NormalizedName  String?            @unique @db.VarChar(64)

Is there a proper command to avoid losing the data?

Answer 1

Go into your schema and make the change:

NormalizedName  String            @unique @db.VarChar(64)
NormalizedName  String?            @unique @db.VarChar(64)

Then create a draft migration:

$ npx prisma migrate dev --name migration-name --create-only

Then edit the migration in SQL (this allow null values ie optional)

ALTER TABLE myTable ALTER COLUMN myColumn {DataType} NULL;

OR Postgres

ALTER TABLE myTable ALTER COLUMN myColumn DROP NOT NULL;

etc.

Then apply the modified SQL:

$ npx prisma migrate dev

I hope this works for you :)

Answer 2

In a development environment, Prisma Migrate sometimes prompts you to reset the database. Resetting drops and recreates the database, which results in data loss. The database is reset when:

• You call prisma migrate reset explicitly
• You call prisma migrate dev and Prisma Migrate detects drift in the database or a migration history conflict

I'm not sure why Prisma thinks that your change is breaking, but there is probably no other way to make schema change without data loss.

To recreate your database data consider using seeding script

If you are prototyping, consider using the db push command, although it will still result in data reset if Prisma considers that the change is breaking.