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?

Digestant

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 :)

Warpath

NOTE: --create-only doesn't prevent data loss when Prisma detects a drift. It "should" (at least the name implies that it "should") prevent data loss but doesn't; in my experience it drops all tables except _prisma_migrations and then just doesn't re-create the tables. 🤔 Not sure why Prisma prioritizes complete data loss. – Gleeful

This doesn't work for me. Again prisma warns me that data will be lost. – Fazeli

data will still be lost – Hunterhunting

Why did you approved this answer? This isn't working. – Cannabin

You can run npx prisma db push instead of running the migration so this will just push the schema updates without making you loose data. – Villalpando

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.

Corral

If you create your database with db push or for some other reason have no migrations table in your db then prisma will want to reset it every time. – Jared

Answer 3

To apply new migrations without losing data:

prisma migrate deploy

Oestradiol

Answer 4

I ran into a similar issue. What happened was that an error occurred during one of my migrations and because of that it puts prisma into an error state. Prisma doesn't want to run any new migrations while in this error state.

To solve it:

• Removed the broken migration folder that was auto generated inside /prisma/migrations
• Remove the broken row that is created inside the _prisma_migrations table in your database

Cholecystitis

Answer 5

I had this issue when the migration was slightly modified, like the formatting, the spacing got different.

Force a broken migration to be treated as applied

1. Find your migration in the DB _prisma_migrations table and remove its row
2. Run npx prisma migrate resolve --applied MIGRATION_NAME

Multivocal