Javascript

How to Implement Soft Delete with Prisma using Partial Indexes

Published Feb 2, 2024

Updated Feb 2, 2024

4 min read

Most APIs and applications that allow you to manage content have some form of deletion functionality. Sometimes you may want to make something recoverable after it’s been deleted, or perform the actual deletion later, and soft deletion is a way to accomplish this.

Entities in your database may also have unique identifiers such as slugs associated with them as well, and when you delete things you typically expect these identifiers to become available for use again. In the case of soft deletes though your deleted entities will still be in the database! If you’re using Postgres or SQLite you can get the best of both worlds by using partial unique indexes, which will in the case of soft deletes would only add slugs on undeleted records to the index; however if you’re connecting a partial index compatible database with Prisma then everything isn’t rainbows and sunshine when it comes to partial indexes.

A Simple Soft Delete Implementation

Before getting into the issue with partial unique indexes, let’s implement soft delete in Prisma using middleware. The following example is adapted from the Prisma documentation regarding soft deletes, though with some slight alterations for convenience.

import { Prisma, PrismaClient } from "@prisma/client";

const MODELS_SUPPORTING_SOFT_DELETE = ["Post"] as const;

const prisma = new PrismaClient({
  log: [{ emit: "event", level: "query" }],
});

prisma.$use(async (params, next) => {
  const supportedModels = MODELS_SUPPORTING_SOFT_DELETE as readonly string[];

  if (params.model && supportedModels.includes(params.model)) {
    // Change 'findUnique' actions to 'findFirst' as you cannot filter by
    // anything except ID / unique with findUnique, which adding the 'deletedAt'
    // check breaks.
    if (params.action === "findUnique" || params.action === "findFirst") {
      params.action = "findFirst";
      params.args.where.deletedAt = null;
    }

    // Handle similar actions as above but with 'OrThrow' as Prisma uses a
    // separate action for that.
    if (
      params.action === "findUniqueOrThrow" ||
      params.action === "findFirstOrThrow"
    ) {
      params.action = "findFirstOrThrow";
      params.args.where.deletedAt = null;
    }

    // Exclude deleted records from 'findMany' only if they have not been
    // explicitly requested. Default to non-deleted records if filters are
    // left unspecified.
    if (params.action === "findMany") {
      if (params.args.where) {
        if (params.args.where.deletedAt === undefined) {
          params.args.where.deletedAt = null;
        }
      } else {
        params.args.where = { deletedAt: null };
      }
    }
  }

  return next(params as Prisma.MiddlewareParams);
});

The notable change here is this version could be extended to work with multiple models fairly easily by modifying the MODELS_SUPPORTING_SOFT_DELETE constant, and we also handle the throwable variants of the find actions as well. The latter change requires we create a custom type named ExpandedPrismaAction with additional actions as the imported Prisma types don’t have these actions for some reason even though they are encountered in actual usage.

Anything specified in the model's constant is assumed to have a field named deletedAt that is nullable. In this example specifically it’s a timestamp field since it’s nice to know when something has been deleted, but it could also be a boolean if you so choose.

With that done, soft deletion is transparently implemented, at least insofar as select queries go. If you plan on blindly running update and delete queries as well, then you will need to add middleware for those actions as well.

The Problem

So as stated before we plan on using unique slugs with our model, in this case slugs for posts, and to free slugs for usage later we need to exclude them from the unique index without deleting the post records outright. Unfortunately, at the time of writing this article Prisma doesn’t allow you to define partial indexes in your schema when defining unique indexes using @@unique inside of your schema. Thankfully, there is a way to workaround this issue.

The Solution

To workaround this issue you can manage your unique indexes outside of the Prisma schema, though it does come with its own pitfalls that I will cover later. This can be done by creating partial indexes in a migration file instead of defining them in the schema file. It should be mentioned that if you do this then you should ensure a conflicting index is not defined in the schema, or else Prisma will try to overwrite the other index.

First let’s create a basic schema with the following contents defining posts:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite" // or "postgresql", etc
  url      = env("DATABASE_URL")
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]

  @@map("users")
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  slug        String
  content     String
  author      User      @relation(fields: [authorId], references: [id])
  authorId    Int       @map("author_id")
  publishedAt DateTime? @map("published_at")
  deletedAt   DateTime? @map("deleted_at")

  // FIXME: https://github.com/prisma/prisma/issues/6974
  // @@unique([slug], where: { deletedAt: null })

  @@map("posts")
}

In the above schema I’ve left a comment that mentions where a partial unique index definition would go if it was supported. The problem with keeping the definition solely in the migration is that it makes it difficult to discover, and also doesn’t explain the reason why it’s there.

Now that we have a Post model with a deletedAt column, create an empty migration and do the following:

prisma migrate dev --create-only --name 'add_soft_delete'

Then the contents of the newly created migration file will be as follows:

CREATE UNIQUE INDEX "posts_slug_key"
    ON "posts" ("slug")
    WHERE "deleted_at" IS NULL;

With that all initialized and the middleware setup, and router handler defined, everything should work! Check out the full example on GitHub as I don’t show the full implementation of the example API in this article. The route handlers don’t look any different than you would expect as the middleware handles all of the heavy lifting for us.

It is worth noting that DELETE handlers do look different with soft-delete as we’re not intercepting delete calls to Prisma. This is how the implementation of the deletion looks for this example:

router.delete("/:slug", async (req, res) => {
  const { slug } = req.params;

  const existingPost = await prisma.post.findFirst({ where: { slug } });
  if (!existingPost) {
    return res.sendStatus(404);
  }

  await prisma.post.update({
    where: { id: existingPost.id },
    data: { deletedAt: new Date() },
  });
  res.sendStatus(204);
});

Caveats

So there are some caveats to implementing partial delete in this way. The first being that the schema doesn’t reflect the state of the database 1:1. Thankfully, Prisma doesn’t care about untracked indexes existing, but knowing the index exists requires looking in the migration files instead of the schema. This is worked around by leaving a comment, but it still isn’t great.

Another downside is that upsert calls do not work with the Prisma client. Making upserts with Prisma requires a unique key, but since Prisma doesn’t track this unique key the typings needed to call upsert do not exist. This can be worked around by making raw queries without the query builder.

Summary

I hope this article has helped you ease the integration of soft deletes into your application. You can find a full example on → GitHub ← that includes all of the above code snippets, a buildable project, and instructions on how to get it all working.

This Dot is a consultancy dedicated to guiding companies through their modernization and digital transformation journeys. Specializing in replatforming, modernizing, and launching new initiatives, we stand out by taking true ownership of your engineering projects.

We love helping teams with projects that have missed their deadlines or helping keep your strategic digital initiatives on course. Check out our case studies and our clients that trust us with their engineering.

About the author(s)

Jamie Kuppens
I’m a software engineer with an interest in web development and some more esoteric things like emulator development.
@Reshurum @Reshurum

Drizzle ORM: A performant and type-safe alternative to Prisma

Introduction I’ve written an article about a similar, more well-known TypeScript ORM named Prisma in the past. While it is a fantastic library that I’ve used and have had success with personally, I noted a couple things in particular that I didn’t love about it. Specifically, how it handles relations with add-on queries and also its bulk that can slow down requests in Lambda and other similar serverless environments. Because of these reasons, I took notice of a newer player in the TypeScript ORM space named Drizzle pretty quickly. The first thing that I noticed about Drizzle and really liked is that even though they call it an ‘ORM’ it’s more of a type-safe query builder. It reminds me of a JS query builder library called ‘Knex’ that I used to use years ago. It also feels like the non-futuristic version of EdgeDB which is another technology that I’m pretty excited about, but committing to it still feels like a gamble at this stage in its development. In contrast to Prisma, Drizzle is a ‘thin TypeScript layer on top of SQL’. This by default should make it a better candidate for Lambda’s and other Serverless environments. It could also be a hard sell to Prisma regulars that are living their best life using the incredibly developer-friendly TypeScript API’s that it generates from their schema.prisma files. Fret not, despite its query-builder roots, Drizzle has some tricks up its sleeve. Let’s compare a common query example where we fetch a list of posts and all of it’s comments from the Drizzle docs: ` Sweet, it’s literally the same thing. Maybe not that hard of a sale after all. You will certainly find some differences in their APIs, but they are both well-designed and developer friendly in my opinion. The schema Similar to Prisma, you define a schema for your database in Drizzle. That’s pretty much where the similarities end. In Drizzle, you define your schema in TypeScript files. Instead of generating an API based off of this schema, Drizzle just infers the types for you, and uses them with their TypeScript API to give you all of the nice type completions and things we’re used to in TypeScript land. Here’s an example from the docs: ` I’ll admit, this feels a bit clunky compared to a Prisma schema definition. The trade-off for a lightweight TypeScript API to work with your database can be worth the up-front investment though. Migrations Migrations are an important piece of the puzzle when it comes to managing our applications databases. Database schemas change throughout the lifetime of an application, and the steps to accomplish these changes is a non-trivial problem. Prisma and other popular ORMs offer a CLI tool to manage and automate your migrations, and Drizzle is no different. After creating new migrations, all that is left to do is run them. Drizzle gives you the flexibility to run your migrations in any way you choose. The simplest of the bunch and the one that is recommended for development and prototyping is the drizzle-kit push command that is similar to the prisma db push command if you are familiar with it. You also have the option of running the .sql files directly or using the Drizzle API's migrate function to run them in your application code. Drizzle Kit is a companion CLI tool for managing migrations. Creating your migrations with drizzle-kit is as simple as updating your Drizzle schema. After making some changes to your schema, you run the drizzle-kit generate command and it will generate a migration in the form of a .sql file filled with the needed SQL commands to migrate your database from point a → point b. Performance When it comes to your database, performance is always an extremely important consideration. In my opinion this is the category that really sets Drizzle apart from similar competitors. SQL Focused Tools like Prisma have made sacrifices and trade-offs in their APIs in an attempt to be as database agnostic as possible. Drizzle gives itself an advantage by staying focused on similar SQL dialects. Serverless Environments Serverless environments are where you can expect the most impactful performance gains using Drizzle compared to Prisma. Prisma happens to have a lot of content that you can find on this topic specifically, but the problem stems from cold starts in certain serverless environments like AWS Lambda. With Drizzle being such a lightweight solution, the time required to load and execute a serverless function or Lambda will be much quicker than Prisma. Benchmarks You can find quite a few different open-sourced benchmarks of common database drivers and ORMs in JavaScript land. Drizzle maintains their own benchmarks on GitHub. You should always do your own due diligence when it comes to benchmarks and also consider the inputs and context. In Drizzle's own benchmarks, it’s orders of magnitudes faster when compared to Prisma or TypeORM, and it’s not far off from the performance you would achieve using the database drivers directly. This would make sense considering the API adds almost no overhead, and if you really want to achieve driver level performance, you can utilize the prepared statements API. Prepared Statements The prepared statements API in Drizzle allows you to pre-generate raw queries that get sent directly to the underlying database driver. This can have a very significant impact on performance, especially when it comes to larger, more complex queries. Prepared statements can also provide huge performance gains when used in serverless environments because they can be cached and reused. JOINs I mentioned at the beginning of this article that one of the things that bothered me about Prisma is the fact that fetching relations on queries generates additional sub queries instead of utilizing JOINs. SQL databases are relational, so using JOINs to include data from another table in your query is a core and fundamental part of how the technology is supposed to work. The Drizzle API has methods for every type of JOIN statement. Properly using JOINs instead of running a bunch of additional queries is an important way to get better performance out of your queries. This is a huge selling point of Drizzle for me personally. Other bells and whistles Drizzle Studio UIs for managing the contents of your database are all the rage these days. You’ve got Prisma Studio and EdgeDB UI to name a couple. It's no surprise that these are so popular. They provide a lot of value by letting you work with your database visually. Drizzle also offers Drizzle Studio and it’s pretty similar to Prisma Studio. Other notable features - Raw Queries - The ‘magic’ sql operator is available to write raw queries using template strings. - Transactions - Transactions are a very common and important feature in just about any database tools. It’s commonly used for seeding or if you need to write some other sort of manual migration script. - Schemas - Schemas are a feature specifically for Postgres and MySQL database dialects - Views -Views allow you to encapsulate the details of the structure of your tables, which might change as your application evolves, behind consistent interfaces. - Logging - There are some logging utilities included useful for debugging, benchmarking, and viewing generated queries. - Introspection - There are APIs for introspecting your database and tables - Zod schema generation - This feature is available in a companion package called drizzle-zod that will generate Zod schema’s based on your Drizzle tables Seeding At the time of this writing, I’m not aware of Drizzle offering any tools or specific advice on seeding your database. I assume this is because of how straightforward it is to handle this on your own. If I was building a new application I would probably provide a simple seed script in JS or TS and use a runtime like node to execute it. After that, you can easily add a command to your package.json and work it into your CI/CD setup or anything else. Conclusion Drizzle ORM is a performant and type-safe alternative to Prisma. While Prisma is a fantastic library, Drizzle offers some advantages such as a lightweight TypeScript API, a focus on SQL dialects, and the ability to use JOINs instead of generating additional sub queries. Drizzle also offers Drizzle Studio for managing the contents of your database visually, as well as other notable features such as raw queries, transactions, schemas, views, logging, introspection, and Zod schema generation. While Drizzle may require a bit more up-front investment in defining your schema, it can be worth it for the performance gains, especially in serverless environments....

Sep 6, 2023

7 mins

TypeScriptJavaScriptPrisma

QR Code Scanning & Generation

QR Code Scanning & Generation with ZXing and Angular QR codes provide a very valuable way of sharing information with users, and many applications rely on them for various purposes. Common examples of places where QR codes can be useful include but are not limited to theaters, social gatherings and other booking services. Integrating QR codes can seem intimidating, but thankfully they’re not too difficult to handle with Angular and the help of a couple of robust software libraries such as ZXing. What is ZXing? ZXing, also known as “Zebra Crossing” is a software library that allows developers to easily integrate scanning many different types of 1D and 2D barcodes. This library was originally written in Java, however many ports to other languages and platforms exist, including JavaScript. There are a couple of ways to use ZXing. You could either directly use the JavaScript library, which is framework-agnostic, or you could use a framework-specific library. I’m going to demonstrate how to use ZXing to scan QR codes with the Angular version of the library called ngx-scanner specifically, which has a full-fledged scanner component that makes it somewhat easy to integrate. Let’s Scan some QR Codes with Angular! Getting ngx-scanner up and running doesn’t take much setup at all. All you have to do is add an import to ZXingScannerModule inside of the module that you will be scanning QR codes in, and add a component to your template. Below shows a real-world example of how the component would be used. ` You’ll notice a lot of observables being referenced here. I’ll go over them one by one. Let’s start with the devices subject: ` This subject has data sent to it whenever cameras have been identified by the scanning component. We want to keep track of these so that we’re able to select a camera to use. In our case we’ll just select the first camera for simplicity's sake, as you can see happens with the selectedDevice$ observable that we have bound to the [device] attribute on the scanner: ` We utilize shareReplay here so we don’t need to re-scan the devices whenever additional subscribers check what devices are available. enable$ is a simple observable that emits true whenever devices have been detected, and startCamera$ is an observable that toggles between true and false whenever data is pushed to the toggleCamera$ subject. ` The scan function from RxJs is utilized so we have the previous state and can use that to determine in which direction the stored boolean should toggle to. Finally we push successfully scanned codes to scanSuccess$ which we can then subscribe to in the template. All together the final component code should look something like this: ` If we use this component then you should get something that looks like this: Scanning QR codes is awesome, but we can also generate our own QR codes as well! Generating our own QR Codes Unfortunately the ZXing scanner component we’re using is limited to just scanning QR codes, however there is another component library called ngx-kjua that can do this. Like ZXing this component is also based off of another pre-existing library that is framework-agnostic, in this case, kjua. Fortunately kjua is very easy to use. You simply supply it a code and it will render a QR code. ` How the QR code is rendered can change. There is an optional [render] attribute that you can specify that takes a few values. You can input ‘image’, ‘canvas’ or ‘svg’. The default render mode is ‘svg’. It should be noted that ‘svg’ and ‘canvas’ render modes made copying the image more difficult, so use ‘image’ if you want to make that possible. The component code associated with this template is very small. The component using ngx-kjua has a code observable that is updated with a new random code whenever a “Generate” button is pressed. ` I’ve opted to go with the randomstring package in this case. This component could be modified to instead allow for custom codes using a textbox if you want to encode URLs or other useful values that typically go inside of QR codes. The result looks like this: Summary Thanks to projects like ZXing and kjua it is easy to integrate handling of QR codes in Angular applications, be it for scanning them or creating them. I hope this guide has helped you. You can find the full example showcasing generation and scanning of QR codes in our blog-demos repository on GitHub....

Dec 13, 2023

4 mins

Angular

Introduction to Zod for Data Validation

As web developers, we're often working with data from external sources like APIs we don't control or user inputs submitted to our backends. We can't always rely on this data to take the form we expect, and we can encounter unexpected errors when it deviates from expectations. But with the Zod library, we can define what our data ought to look like and parse the incoming data against those defined schemas. This lets us work with that data confidently, or to quickly throw an error when it isn't correct. Why use Zod? TypeScript is great for letting us define the shape of our data in our code. It helps us write more correct code the first time around by warning us if we are doing something we shouldn't. But TypeScript can't do everything for us. For example, we can define a variable as a string or a number, but we can't say "a string that starts with user_id_ and is 20 characters long" or "an integer between 1 and 5". There are limits to how much TypeScript can narrow down our data for us. Also, TypeScript is a tool for us developers. When we compile our code, our types are not available to the vanilla JavaScript. JavaScript can't validate that the data we actually use in our code matches what we thought we'd get when we wrote our TypeScript types unless you're willing to manually write code to perform those checks. This is where we can reach for a tool like Zod. With Zod, we can write data schemas. These schemas, in the simplest scenarios, look very much like TypeScript types. But we can do more with Zod than we can with TypeScript alone. Zod schemas let us create additional rules for data parsing and validation. A 20-character string that starts with user_id_? It's z.string().startsWith('user_id_').length(20). An integer between 1 and 5 inclusive? It's z.number().int().gte(1).lte(5). Zod's primitives give us many extra functions to be more specific about *exactly* what data we expect. Unlike TypeScript, Zod schemas aren't removed on compilation to JavaScript—we still have access to them! If our app receives some data, we can verify that it matches the expected shape by passing it to your Zod schema's parse function. You'll either get back your data in exactly the shape you defined in your schema, or Zod will give you an error to tell you what was wrong. Zod schemas aren't a replacement for TypeScript; rather, they are an excellent complement. Once we've defined our Zod schema, it's simple to derive a TypeScript type from it and to use that type as we normally would. But when we really need to be sure our data conforms to the schema, we can always parse the data with our schema for that extra confidence. Defining Data Schemas Zod schemas are the variables that define our expectations for the shape of our data, validate those expectations, and transform the data if necessary to match our desired shape. It's easy to start with simple schemas, and to add complexity as required. Zod provides different functions that represent data structures and related validation options, which can be combined to create larger schemas. In many cases, you'll probably be building a schema for a data object with properties of some primitive type. For example, here's a schema that would validate a JavaScript object representing an order for a pizza: ` Zod provides a number of primitives for defining schemas that line up with JavaScript primitives: string, number, bigint, boolean, date, symbol, undefined, and null. It also includes primitives void, any, unknown, and never for additional typing information. In addition to basic primitives, Zod can define object, array, and other native data structure schemas, as well as schemas for data structures not natively part of JavaScript like tuple and enum. The documentation contains considerable detail on the available data structures and how to use them. Parsing and Validating Data with Schemas With Zod schemas, you're not only telling your program what data should look like; you're also creating the tools to easily verify that the incoming data matches the schema definitions. This is where Zod really shines, as it greatly simplifies the process of validating data like user inputs or third party API responses. Let's say you're writing a website form to register new users. At a minimum, you'll need to make sure the new user's email address is a valid email address. For a password, we'll ask for something at least 8 characters long and including one letter, one number, and one special character. (Yes, this is not really the best way to write strong passwords; but for the sake of showing off how Zod works, we're going with it.) We'll also ask the user to confirm their password by typing it twice. First, let's create a Zod schema to model these inputs: ` So far, this schema is pretty basic. It's only making sure that whatever the user types as an email is an email, and it's checking that the password is at least 8 characters long. But it is *not* checking if password and confirmPassword match, nor checking for the complexity requirements. Let's enhance our schema to fix that! ` By adding refine with a custom validation function, we have been able to verify that the passwords match. If they don't, parsing will give us an error to let us know that the data was invalid. We can also chain refine functions to add checks for our password complexity rules: ` Here we've chained multiple refine functions. You could alternatively use superRefine, which gives you even more fine grained control. Now that we've built out our schema and added refinements for extra validation, we can parse some user inputs. Let's see two test cases: one that's bound to fail, and one that will succeed. ` There are two main ways we can use our schema to validate our data: parse and safeParse. The main difference is that parse will throw an error if validation fails, while safeParse will return an object with a success property of either true or false, and either a data property with your parsed data or an error property with the details of a ZodError explaining why the parsing failed. In the case of our example data, userInput2 will parse just fine and return the data for you to use. But userInput1 will create a ZodError listing all of the ways it has failed validation. ` ` We can use these error messages to communicate to the user how they need to fix their form inputs if validation fails. Each error in the list describes the validation failure and gives us a human readable message to go with it. You'll notice that the validation errors for checking for a valid email and for checking password length have a lot of details, but we've got three items at the end of the error list that don't really tell us anything useful: just a custom error of Invalid input. The first is from our refine checking if the passwords match, and the second two are from our refine functions checking for password complexity (numbers and special characters). Let's modify our refine functions so that these errors are useful! We'll add our own error parameters to customize the message we get back and the path to the data that failed validation. ` Now, our error messages from failures in refine are informative! You can figure out which form fields aren't validating from the path, and then display the messages next to form fields to let the user know how to remedy the error. ` By giving our refine checks a custom path and message, we can make better use of the returned errors. In this case, we can highlight specific problem form fields for the user and give them the message about what is wrong. Integrating with TypeScript Integrating Zod with TypeScript is very easy. Using z.infer<typeof YourSchema> will allow you to avoid writing extra TypeScript types that merely reflect the intent of your Zod schemas. You can create a type from any Zod schema like so: ` Using a TypeScript type derived from a Zod schema does *not* give you any extra level of data validation at the type level beyond what TypeScript is capable of. If you create a type from z.string.min(3).max(20), the TypeScript type will still just be string. And when compiled to JavaScript, even that will be gone! That's why you still need to use parse/safeParse on incoming data to validate it before proceeding as if it really does match your requirements. A common pattern with inferring types from Zod schemas is to use the same name for both. Because the schema is a variable, there's no name conflict if the type uses the same name. However, I find that this can lead to confusing situations when trying to import one or the other—my personal preference is to name the Zod schema with Schema at the end to make it clear which is which. Conclusion Zod is an excellent tool for easily and confidently asserting that the data you're working with is exactly the sort of data you were expecting. It gives us the ability to assert at runtime that we've got what we wanted, and allows us to then craft strategies to handle what happens if that data is wrong. Combined with the ability to infer TypeScript types from Zod schemas, it lets us write and run more reliable code with greater confidence....

Nov 15, 2024

7 mins

JavaScriptZod

Let's innovate together!

We're ready to be your trusted technical partners in your digital innovation journey.

Whether it's modernization or custom software solutions, our team of experts can guide you through best practices and how to build scalable, performant software that lasts.

How to Implement Soft Delete with Prisma using Partial Indexes

A Simple Soft Delete Implementation

The Problem

The Solution

Caveats

Summary

Jamie Kuppens

You might also like

Drizzle ORM: A performant and type-safe alternative to Prisma

QR Code Scanning & Generation

Introduction to Zod for Data Validation

Let's innovate together!

You might also like

Drizzle ORM: A performant and type-safe alternative to Prisma

QR Code Scanning & Generation

Introduction to Zod for Data Validation