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" = '[email protected]' 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!