olivierwilkinson / prisma-extension-soft-delete Goto Github PK

I'm implementing soft deletion in Prisma ORM using the prisma-extension-soft-delete. My schema includes a User model with an email field marked as unique. The issue arises when a user registers, deletes their account (soft delete), and then attempts to re-register with the same email. Prisma throws a unique constraint violation error.

Therefore, the current soft deletion implementation doesn't account for reusing unique fields (like email) after a soft delete. This leads to a violation of the unique constraint when the same unique data is entered again.

Is it possible to consider a workaround where a prefix is added to unique fields upon deletion? For example, modifying the email field to something like email_[timestamp] during the soft delete process. This approach would theoretically allow the reuse of the original unique data for new records. Or maybe I've overlooked something and it is possible to solve this with the current implementation? Thanks for your time!

I use another prisma extensions that doesn't work with prisma-extension-soft-delete.
It's a very simple extension to wrap queries into a transaction and set a config for a row level security use case.

I've tried to chain them in both ways.
This way works on most queries client.$extends(extension).$extends(softDeleteExtension()).
But some queries never return and keep running forever so I never get to the following instruction and my backend responses get stuck.

Any clue where it might come from ?

RLS extension:

Prisma.defineExtension((prisma) => {
  if (!organizationId) {
    throw new NotFoundIssue('Organization not found');
  }

  return prisma.$extends({
    query: {
      $allModels: {
        async $allOperations({ args, query }) {
          const [_, result] = await prisma.$transaction([
            prisma.$queryRaw`SELECT set_config('app.current_organization_id', ${organizationId}, TRUE)`,
            query(args),
          ]);
          return result;
        },
      },
    },
  });
});

Soft delete extension:

export const softDeleteExtension = () => {
  return createSoftDeleteExtension({
    models: {
      Workspace: true,
    },
    defaultConfig: {
      field: 'archivedAt',
      createValue: (archived: boolean) => {
        if (archived) return new Date();
        return null;
      },
    },
  });
};

Currently the only way to hard delete a model setup for soft delete is to use executeRaw.

Add a custom method to the model, something like deleteHard or hardDelete, or maybe even deleteUnsafeHard

I get this error when adding the extension to Prisma using Bun.

Running bunx prisma generate doesn't resolve the issue.

Not sure if the extension supports Bun?

node_modules/prisma-extension-soft-delete/dist/lib/createSoftDeleteExtension.js:5:7
1 | "use strict";
2 | Object.defineProperty(exports, "__esModule", { value: true });
3 | exports.findOppositeRelation = exports.relationsByModel = void 0;
4 | const client_1 = require("@prisma/client");
5 | if (!client_1.Prisma.dmmf) {
6 |     throw new Error("Prisma DMMF not found, please generate Prisma client using `npx prisma generate`");
              ^
error: Prisma DMMF not found, please generate Prisma client using `npx prisma generate`

To manage row level security, I created a Prisma extension that applies logic to all operations using $allModels and $allOperations.

Now that I want to implement a soft delete system, I've installed this extension and extended my prisma client to use it.

However, as the client already uses the extension for the RLS, there's a conflict because both extensions define a logic with $allOperations, which means that only the last extension called will be taken into account.

Would it be possible to export all the logic contained in $allOperations separately, so that you can choose whether to import the complete extension (createSoftDeleteExtension) or just the logic?

Exporting only the logic would allow me to merge the code of the two extensions into a single one, in order to define the $allOperations only one time

should i be able to include soft deleted rows when using deletedAt ?

.findUnique({ where: { id: row.id, NOT: { deletedAt: null } } })

generates

 WHERE ("public"."Table"."id" = $1 AND (NOT "public"."Table"."deletedAt" IS NULL) AND "public"."Table"."deletedAt" IS NULL)

Hey!

First, thank you for creating these!

However, it appears that the middleware works, but not the extension.

I'm using @prisma/client 5.10.2 and prisma.$use is deprecated.

So I first tried using prisma-extension-soft-delete and implementing with the following:

prisma.$extends(
  createSoftDeleteExtension({
    models: {
      Post: true,
    },
    defaultConfig: {
      field: 'deletedAt',
      createValue: (deleted) => {
        if (deleted) return new Date()
        return null
      },
    },
  })
)

but when I do, objects are returned when I have deletedAt set to a date.

However, when I use the middleware:

prisma.$use(
  createSoftDeleteMiddleware({
    models: {
      Post: true,
    },
    defaultConfig: {
      field: 'deletedAt',
      createValue: (deleted) => {
        if (deleted) return new Date()
        return null
      },
    },
  })
)

it does work as expected, and objects that have a deletedAt are properly removed from the results.

do you know why this is?

Thanks in advance!

I have a nested many-to-many relationship that uses a soft delete boolean (isDeleted).

The result includes the deleted item as null when it should be redacted:

{
    "teams": [
        {
            "team": null
        }
    ],
}

Desired result:

{
    "teams": [],
}

For now, I decided to use your awesome prisma-extension-nested-operations package and filter the nulls.

It would be good to be able to setup soft delete cascades, either through the existing Prisma schema cascades or through a custom API.

Due to the nature of nested operations it probably won't be possible to produce single queries that do this, however some will be able to. In the cases where it is simply not possible to do with a single query a second operation and a transaction will probably be needed.

This was requested in the soft delete Prisma issue

Hello and thank you for your work on this extension. 👋

I am afraid the recent release of v1.0.1 introduced a significant regression.
We author some of our type resolvers like this, to return nested entities:

import { Resolvers } from '../../generated';

export const ProjectType: Resolvers = {
  Project: {
    template: (project, _, { prisma }) => prisma.project.findUniqueOrThrow({ where: { id: project.id } }).template(),
    theme: (project, _, { prisma }) => prisma.project.findUniqueOrThrow({ where: { id: project.id } }).theme(),
    music: (project, _, { prisma }) => prisma.project.findUniqueOrThrow({ where: { id: project.id } }).music(),
};

Unfortunately, with the latest update, we get the following errors:

prisma.project.findUniqueOrThrow(...).template is not a function
prisma.project.findUniqueOrThrow(...).theme is not a function
prisma.project.findUniqueOrThrow(...).music is not a function

I assume the problem occurred in 0cb041c.

Let me know if I can provide more details. Cheers! :)

In our app we set a timestamp + userId pair when an entity is created, updated, or deleted - so for deletions, deletedAt and deletedById (a foreign key of the user table).

It would be great if createValue would allow us to set multiple fields when a record is deleted so both these could be set. Or alternatively, to keep the API backwards compatible, a createValues plural option could be added so the behaviour of the original option doesn't need to change.

I'd imagine it being used something like this:

const softdeletes = (user: User) => createSoftDeleteExtension({
    models: {
        User: true,
        Post: true,
    }
    defaultConfig: {
        field: "deletedAt",
        createValues: (deleted) => (
            deleted ? { deletedAt: new Date(), deletedById: user.id } : { }
        )
    }
})

export const getConnectionForUser = (user: User) => {
    return prisma.$extend(sofdeletes(user))
}

If this is of interest, I'd be happy having a crack at a PR?

Hi - thanks for this extension, it's looking like exactly what I need.

I'm wondering if it's possible to apply it to all models without having to list them all, something like this:

createSoftDeleteExtension({ models: { $allModels: true } })

Or alternatively, is there some way I can introspect the Prisma client to get a list of all models so I can construct a map of model name => true myself to pass in as config? I don't know Prisma well yet!

Hey, I was checking out your neat library, but I was not get it working with Deno. It seems like there are some assumptions made with where the prisma client is located/generated. I'm not sure what it would take to modify things but being able to provide the library with a prisma instance through some initialization options might work?

Thank you for this amazing library!

I have a work around for this issue but I thought i'd post to check if there is a potential bug:

I'd expect to not have to specify deletedAt

Example not working

  const collaboration = await ctx.prisma.collaborator.findUnique({
    where: {
      userId_planId: {
        userId: userId,
        planId: planId,
      },
    },
    select: {
      planAccessType: true,
    },
  });

It works when i do this

  const collaboration = await ctx.prisma.collaborator.findUnique({
    where: {
      userId_planId: {
        userId: userId,
        planId: planId,
      },
      deletedAt: null,
    },
    select: {
      planAccessType: true,
    },
  });

Here is my configuration:

  const extendedClient = client.$extends(
    createSoftDeleteExtension({
      models: {
        Collaborator: true,
      },
      defaultConfig: {
        field: "deletedAt",
        createValue: (deleted) => {
          if (deleted) return new Date();
          return null;
        },
      },
    })
  );

Hi, first of all, thanks for your amazing library.

I'm facing problem with query table with non-deleted and deleted records.

I did try query like:

const where = {
    OR: [{ archivedAt: null }, { archivedAt: { not: null } }],
};

This is my table structure:

model Template {
	...fields
    archivedAt DateTime?
}

I feel like this kind using "OR" is not convenient if we have more query conditions, do we have method or plan for a query that can include the deleted records?
For example "TypeORM"

repository.withDelete().find()

Maybe the method for this library could be:

prisma.findManay({
    where: ...
    withDeleted: true
})

take this schema.prisma as an example:

model User {
  id String @id
  name String

  role    role? @relation(fields: [roleId], references: [id])
  roleId String?

  deletedAt DateTime?
}

model Roles {
  id String @id
  name String
}

And then I want to get the count of roles with users count:

await prisma.role.findMany({
			include: {
				_count: {
					select: {
						users: true,
					},
				},
				users: true,
			},
		});

response will contain the deleted users count but users will be empty