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?

p6l-richard · Accepted Answer · 2023-03-21 13:50:50Z

11

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:

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])
  }

Create migration file without applying it:

Run npx prisma migrate dev --create-only

NB: the --create-only flag avoids the application
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

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.

edited Mar 21, 2023 at 13:50

answered Feb 24, 2022 at 10:01

p6l-richard

4293 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

busaud Over a year ago

It would be more helpful if you wrote the full commands.

Samyar Over a year ago

the --create-only flag in my case resets the databsae

Danila · Accepted Answer · 2022-01-19 20:12:51Z

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

answered Jan 19, 2022 at 20:12

Danila

19k2 gold badges57 silver badges80 bronze badges

4 Comments

Aerodynamika Over a year ago

Thanks. Well, I actually added some new properties on some tables and just want to run the migration to update my PostgreSQL. However, Prisma migration file insists on dropping those tables. Do you know how to avoid that?

Danila Over a year ago

Well, if you added some properties and they are not optional (non nullable) and have no default values then the only option is to drop the table otherwise it will be invalid (because older row won't have them). Is that what happened or you did something else?

Aerodynamika Over a year ago

They do have default values, but I guess I first need to go through the existing records and populate them with those new default values? Or Prisma should do that automatically when it adds that property?

Danila Over a year ago

Yes, Prisma should create migration with default values. If all your fields really have default values or are optional then this is a bug.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related