How to implement soft deletes in Prisma

Soft deletes are a method for handling data removal in databases. Instead of permanently erasing a record, a soft delete marks it as deleted, often by setting a flag or a timestamp. This approach is useful for preserving data integrity and maintaining a record of deletions without losing the actual data.

Implementing Soft Deletes in Prisma

Prisma, an open-source database toolkit, supports soft deletes through its modeling and query capabilities. Implementing soft deletes in Prisma involves a few key steps: updating your data model, modifying queries, and handling the deleted data in your application logic.

Update the Prisma Schema

First, modify your Prisma schema to include a field that indicates whether a record is deleted. Commonly, a deletedAt field of type DateTime is used, which is null when the record is not deleted.

model YourModel {
  id        Int      @id @default(autoincrement())
  name      String
  deletedAt DateTime? // Nullable field to track soft deletes
}

Modifying Queries for Soft Deletes

Inserting and Updating Records

When inserting or updating records, you don’t need to change anything regarding the deletedAt field. It should remain null by default for new records.

Soft Deleting Records

To soft delete a record, update the deletedAt field with the current timestamp instead of using the delete operation.

const softDeleteRecord = async (id) => {
  return await prisma.yourModel.update({
    where: { id },
    data: { deletedAt: new Date() },
  });
};

Querying Non-Deleted Records

When querying, you need to filter out soft-deleted records. This is done by adding a condition to check that the deletedAt field is null.

const getActiveRecords = async () => {
  return await prisma.yourModel.findMany({
    where: { deletedAt: null },
  });
};

Restoring Soft Deleted Records

To restore a soft-deleted record, simply set the deletedAt field back to null.

const restoreRecord = async (id) => {
  return await prisma.yourModel.update({
    where: { id },
    data: { deletedAt: null },
  });
};

Handling Permanently Deleting Records

If you need to permanently delete a record, use Prisma’s delete operation. This should be used cautiously, as it cannot be undone.

const permanentlyDeleteRecord = async (id) => {
  return await prisma.yourModel.delete({
    where: { id },
  });
};

Advantages of Soft Deletes

1. Data Recovery: Easily recover deleted data.
2. Audit Trails: Maintain a history of deletions.
3. Data Integrity: Preserve relationships with other data that might break with hard deletes.

Implementing soft deletes in Prisma enhances data management by providing flexibility and security in handling deletions. It’s a valuable technique for many applications, particularly those requiring robust data auditability and recovery mechanisms.

Manually Editing Records

For Prisma workflows, Basedash works as an AI-native BI layer over your database so engineers and non-technical teammates can inspect migration outcomes, ask follow-up questions, and track changes in shared dashboards.