How to run Prisma schema update without erasing the PostgreSQL data?

Question

I have a PostgreSQL db that is used by a Nest.Js / Prisma app.

We changed the name of a field in the Prisma schema and added a new field.

Now, when we want to update the PostreSQL structure, I'm running, as suggested by Prisma, the following commands:

npx prisma generate 

and then

npx prisma migrate dev --name textSettings-added --create-only

The idea is to use the --create-only flag to review the migration before it is actually made.

However, when I run it I get a list of the changes to be made to the DB and the following message:

 We need to reset the PostgreSQL database "my_database" at "[email protected]:5432".
Do you want to continue? All data will be lost.

Of course I choose not to continue, because I don't want to lose the data. Upon inspection I see that the migration file actually contains DROP TABLE for the tables that I simply wanted to modify. How to avoid that?

So how do I run the update without affecting the data?

UPDATE: I saw that running with --create-only creates a migration which can then be implemented on the DB level using prisma migrate dev, however, in that migration file there are still some commands that drop my previous tables because of some new parameters inside. How can I run prisma migration without deleting my PostgreSQL data?

UPDATE 2: I don't want Prisma to drop my tables when I just updated them. The migration file generated, however, drops them and then alters them. Do you know what's the best procedure to avoid this drop? I saw somewhere I could first manually update the DB with the new options and then run the migration, so Prisma can find a way to update it, but that seems too manual to me... Maybe some other ideas?

Answer 1

For some cases like renaming tables or columns, Prisma's generated migration files need to be updated if they already contain data.

If that applies to your use case, Prisma's docs suggest to:

1. Make updates to the prisma schema, e.g.:

  model Profile {
    id        Int    @id @default(autoincrement())
 -   biograpy  String
 +   biography String
    userId    Int    @unique
    user      User   @relation(fields: [userId], references: [id])
  }

2. Create migration file without applying it:

   Run npx prisma migrate dev --create-only

   NB: the --create-only flag avoids the application

3. Update the migration script to remove the drops and instead write your custom query, e.g.:

-- inside migrations/your_migration.sql

RENAME biograpy TO biography

4. Save and apply the migration: npx prisma migrate dev

Note that those changes can lead to downtime (renaming a field or model), for which they have outlined the expand and contract pattern.

Answer 2

It might be a Prisma bug: https://github.com/prisma/prisma/issues/8053

I also recently had this situation. It probably should not try to run migration if you only want to create migration file.

But overall it is expected with Prisma to recreate your db sometimes. If you migration is breaking then it will be required to reset the data anyway when you apply it.

I suggest you to create some seeding script so you could consistently re-create the database state, it's very useful for your development environment.

More info