Home > Back-end >  Postgres NestJS Prisma migration - Database error code: 23502 column of relation contains null value
Postgres NestJS Prisma migration - Database error code: 23502 column of relation contains null value

Time:10-21

I updated my Prisma ORM database model by adding two more fields 'ahash' and 'sAddress' to the 'invitation' table.

The table already contains 6 rows of data.

When I try to migrate the new changes to the Postgresql database, I get the error Database error code: 23502. ERROR: column "aHash" of relation "Invitation" contains null values.

How do I cater for the null values and migrate the model updates smoothly onto the database?

Give me a Step by step account please. I'm new to Prisma migration.

Thanks in advance!

The Prisma invitation model looks like below.


model Invitation {
  id                      String        @id @db.Uuid
  workId                  String        @db.Uuid
  work                    Work          @relation(fields: [workId], references: [id])
  status                  RequestStatus
  coId                    String        @db.Uuid
  oSignature              String
  note                    String  
  aHash                   String
  sAddress                String
  createdAt               DateTime
  respondedAt             DateTime  
}


CodePudding user response:

The problem here is that ahash and sAddress are both mandatory fields. However, they do not exist for the 6 existing rows/records in your database.

If you want to add new columns to an existing database without causing data loss you need to make sure the new columns/field are optional. This can be done in Prisma by marking the type of the field with a ?.

If you need the field to be mandatory, you could do it in three steps:

  1. Create the new ahash and sAddress fields as optional first in your prisma schema and run a migration.
  2. Run a script to update all existing records, so that they have a value for the ahash and sAddress fields.
  3. Mark both fields as mandatory in your Prisma schema and run a migration.

In step 3, you will no longer get the error because none of the records contain a null value for the ahash and sAddress fields.

  • Related