Скачать или смотреть How to Resolve Postgres Migration Error: Database error code: 23502 in NestJS with Prisma

Discover step-by-step guidance on how to handle null values in PostgreSQL migrations when using NestJS with Prisma, ensuring a smooth transition for your database schema updates.
---
This video is based on the question https://stackoverflow.com/q/69637388/ asked by the user 'Vakindu' ( https://stackoverflow.com/u/3639372/ ) and on the answer https://stackoverflow.com/a/69643584/ provided by the user 'Tasin Ishmam' ( https://stackoverflow.com/u/8855844/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Postgres NestJS Prisma migration - Database error code: 23502 column of relation contains null values

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Resolve Postgres Migration Error: Database error code: 23502 in NestJS with Prisma

When working with databases, especially when you're updating your data models, encountering issues can be a common hurdle. A frequently reported error arises during migrations involving PostgreSQL, NestJS, and Prisma that states: Database error code: 23502. ERROR: column "aHash" of relation "Invitation" contains null values. This happens when you try to add fields to an existing table that cannot accept null values, which is common for those who are new to Prisma migrations.

In this blog, we'll explore the problem in depth and provide a step-by-step solution to help you smoothly transition your model updates without losing your existing data.

Understanding the Problem

You recently added two new fields, ahash and sAddress, to the Invitation table of your database:

[[See Video to Reveal this Text or Code Snippet]]

The error you're facing indicates that your existing rows, which total six, do not have values for the new fields ahash and sAddress. PostgreSQL does not allow inserting a null value into a column that is defined as NOT NULL, which is the root cause of the error.

Proposed Solution

To resolve the migration error and successfully add your new fields, you can follow these steps systematically:

Step 1: Make New Fields Optional

Change your Prisma schema so that the new fields ahash and sAddress are optional by adding a ? after their types:

[[See Video to Reveal this Text or Code Snippet]]

Then, run the migration command to update your database structure. This allows existing records to remain intact with the possibility of having null values.

Step 2: Populate Existing Records

Next, you'll need to update your existing records to ensure that every row has non-null values for the new fields. You can achieve this by creating a script in your application that sets default values (or calculates values based on existing data) for all current records in the Invitation table:

[[See Video to Reveal this Text or Code Snippet]]

Run this script to ensure all existing records will now have valid values for both ahash and sAddress.

Step 3: Make Fields Mandatory Again

Once all existing records are populated with non-null values, you can revert the Prisma schema back to the original structure where your fields are marked as mandatory again. Modify the schema as follows:

[[See Video to Reveal this Text or Code Snippet]]

Run the migration again, and this time, you should not encounter any error, as the database records now comply with the NOT NULL constraint.

Conclusion

Following these steps allows you to update your Prisma model and PostgreSQL database without losing any existing data and avoiding migration errors. By making fields optional initially, populating existing records, and then enforcing mandatory fields, you ensure a smooth and efficient database management process.

This method not only helps to keep your data integrity intact but also enhances your understanding of migrations in Prisma, empowering you to handle future schema changes with confidence!