DEV Community

Atul Vishwakarma
Atul Vishwakarma

Posted on

1 1

Common Data Loss Scenarios & Solutions in Prisma Schema Changes

Common Data Loss Scenarios & Solutions in Prisma Schema Changes

When evolving a database schema using Prisma, care must be taken to ensure data integrity and avoid loss. Below, we explore common data loss scenarios and provide step-by-step solutions to address them effectively.


1. Enum to String Conversion

Issue:

Converting an enum column to a string type can result in data inconsistencies.

Example:

  • Before: payedBy Payment_By @default(NONE)
  • After: payedBy String?

Solution:

  1. Add a new column:

    ALTER TABLE "Orders" ADD COLUMN "payedBy_new" TEXT;
    
  2. Copy data:

    UPDATE "Orders" SET "payedBy_new" = "payedBy"::text;
    
  3. Drop old column:

    ALTER TABLE "Orders" DROP COLUMN "payedBy";
    
  4. Rename the new column:

    ALTER TABLE "Orders" RENAME COLUMN "payedBy_new" TO "payedBy";
    

2. Changing Column Type (e.g., Int to Decimal)

Issue:

Directly altering a column type can cause data loss.

Example:

  • Before: amount Int
  • After: amount Decimal

Solution:

  1. Add a new column:

    ALTER TABLE "TableName" ADD COLUMN "amount_new" DECIMAL;
    
  2. Copy data:

    UPDATE "TableName" SET "amount_new" = "amount"::DECIMAL;
    
  3. Drop old column:

    ALTER TABLE "TableName" DROP COLUMN "amount";
    
  4. Rename the new column:

    ALTER TABLE "TableName" RENAME COLUMN "amount_new" TO "amount";
    

3. Making a Nullable Column Non-Nullable

Issue:

Enforcing a non-null constraint without handling existing NULL values can break queries.

Example:

  • Before: email String?
  • After: email String

Solution:

  1. Populate NULL values with placeholders:

    UPDATE "TableName" SET "email" = 'placeholder@email.com' WHERE "email" IS NULL;
    
  2. Alter the column:

    ALTER TABLE "TableName" ALTER COLUMN "email" SET NOT NULL;
    

4. Changing JSON Structure

Issue:

Modifying the structure of a JSON column can lead to data mismatches.

Example:

  • Before: {oldField: "value"}
  • After: {newField: "value"}

Solution:

  1. Add a temporary column:

    ALTER TABLE "TableName" ADD COLUMN "metadata_new" JSONB;
    
  2. Transform the data:

    UPDATE "TableName" SET "metadata_new" = jsonb_build_object(
       'newField',
       CASE WHEN metadata->>'oldField' IS NOT NULL THEN metadata->>'oldField' ELSE NULL END
    ) WHERE metadata IS NOT NULL;
    
  3. Drop the old column:

    ALTER TABLE "TableName" DROP COLUMN "metadata";
    
  4. Rename the new column:

    ALTER TABLE "TableName" RENAME COLUMN "metadata_new" TO "metadata";
    

5. Array Type Changes

Issue:

Converting an array type (e.g., String[] to Int[]) can cause errors if the data types don’t align.

Example:

  • Before: tags String[]
  • After: tags Int[]

Solution:

  1. Add a new column:

    ALTER TABLE "TableName" ADD COLUMN "tags_new" INTEGER[];
    
  2. Convert data:

    UPDATE "TableName" SET "tags_new" = ARRAY(
       SELECT NULLIF(value, '')::INTEGER
       FROM unnest("tags") AS value
       WHERE value ~ '^[0-9]+$'
    );
    
  3. Drop the old column:

    ALTER TABLE "TableName" DROP COLUMN "tags";
    
  4. Rename the new column:

    ALTER TABLE "TableName" RENAME COLUMN "tags_new" TO "tags";
    

6. Adding/Removing Unique Constraints

Issue:

Adding a unique constraint without addressing duplicate values can cause migration failures.

Example:

  • Before: email String
  • After: email String @unique

Solution:

  1. Identify duplicates:

    SELECT email, COUNT(*) FROM "TableName" GROUP BY email HAVING COUNT(*) > 1;
    
  2. Handle duplicates:

    WITH duplicates AS (
       SELECT email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS row_num
       FROM "TableName"
    )
    UPDATE "TableName" t
    SET email = t.email || '_' || d.row_num
    FROM duplicates d
    WHERE t.email = d.email AND d.row_num > 1;
    
  3. Add the constraint:

    ALTER TABLE "TableName" ADD CONSTRAINT email_unique UNIQUE (email);
    

Best Practices for Safe Schema Changes

  1. Backup First:

    pg_dump -U username -d database_name > backup.sql
    
  2. Test in Development:

    • Create a development database.
    • Restore the backup.
    • Test migrations.
  3. Use Transactions:

    BEGIN;
    -- Migration steps
    COMMIT;
    
  4. Implement Rollback Plans:

    • Save original data in a backup table.
    • Rollback if necessary.

Conclusion

Schema changes in Prisma require meticulous planning and execution. By following these structured solutions and best practices, you can ensure safe migrations while maintaining data integrity.


If you found this article helpful, consider buying me a coffee to support my work!

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay