8

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?

x19
  • 8,277
  • 15
  • 68
  • 126

3 Answers3

9

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

BlaviButcher
  • 118
  • 1
  • 6
  • 1
    **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. – skplunkerin Mar 07 '23 at 21:25
4

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.

Danila
  • 15,606
  • 2
  • 35
  • 67
1

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
Jdemig
  • 31
  • 8