Javascript

How to Implement Soft Delete with Prisma using Partial Indexes

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.

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

Understanding Vue.js's <Suspense> and Async Components

In this blog post, we will delve into how and async components work, their benefits, and practical implementation strategies to make your Vue.js applications more efficient and user-friendly. Without further ado, let’s get started! Suspense Let's kick off by explaining what Suspense components are. They are a new component that helps manage how your application handles components that need to await for some async resource to resolve, like fetching data from a server, waiting for images to load, or any other task that might take some time to complete before they can be properly rendered. Imagine you're building a web page that needs to load data from a server, and you have 2 components that fetch the data you need as they will show different things. Typically, you might see a loading spinner or a skeleton while the data is being fetched. Suspense components make it easier to handle these scenarios. Instead of manually managing loading states and error messages for each component that needs to fetch data, Suspense components let you wrap all these components together. Inside this wrapper, you can define: 1. What to show while the data is loading (like a loading spinner). 2. The actual content that should be displayed once the data is successfully fetched. This way, Vue Suspense simplifies the process of handling asynchronous operations (like data fetching) and improves the user (and the developer) experience by providing a more seamless and integrated way to show loading states and handle errors. There are two types of async dependencies that can wait on: - Components with an async setup() hook. This includes components using with top-level await expressions. *Note: These can only be used within a component.* - Async Components. Async components Vue's asynchronous components are like a smart loading system for your web app. Imagine your app as a big puzzle. Normally, you'd put together all the pieces at once, which can take time. But what if some pieces aren't needed right away? Asynchronous components help with this. Here's how they work: - Load Only What's Needed: Just like only picking up puzzle pieces you need right now, asynchronous components let your app load only the parts that are immediately necessary. Other parts can be loaded later, as needed. - Faster Start: Your app starts up faster because it doesn't have to load everything at once. It's like quickly starting with the border of a puzzle and filling in the rest later. - Save Resources: It uses your web resources (like internet data) more wisely, only grabbing what’s essential when it's essential. In short, asynchronous components make your app quicker to start and more efficient, improving the overall experience for your users. Example: ` Combining Async Components and Suspense Let's explore how combining asynchronous components with Vue's Suspense feature can enhance your application. When asynchronous components are used with Vue's Suspense, they form a powerful combination. The key point is that async components are "suspensable" by default. This means they can be easily integrated with Suspense to improve how your app handles loading and rendering components. When used together, you can do the following things: - Centralized Loading and Error Handling: With Suspense, you don't have to handle loading and error states individually for each async component. Instead, you can define a single loading indicator or error message within the Suspense component. This unified approach simplifies your code and ensures consistency across different parts of your app. - Flexible and Clean Code Structure: By combining async components with Suspense, your code becomes more organized and easier to maintain. An asynchronous component has the flexibility to operate independently of Suspense's oversight. By setting suspensible: false in its options, the component takes charge of its own loading behavior. This means that instead of relying on Suspense to manage when it appears, the component itself dictates its loading state and presentation. This option is particularly useful for components that have specific loading logic or visuals they need to maintain, separate from the broader Suspense-driven loading strategy in the application. In practice, this combo allows you to create a user interface that feels responsive and cohesive. Users see a well-timed loading indicator while the necessary components are being fetched, and if something goes wrong, a single, well-crafted error message is displayed. It's like ensuring that the entire puzzle is either revealed in its completed form or not at all rather than showing disjointed parts at different times. How it works When a component inside the boundary is waiting for something asynchronous, shows fallback content. This fallback content can be anything you choose, such as a loading spinner or a message indicating that data is being loaded. Example Usage Let’s use a simple example: In the visual example provided, imagine we have two Vue components: one showcasing a selected Pokémon, Eevee, and a carousel showcasing a variety of other Pokémon. Both components are designed to fetch data asynchronously. Without , while the data is being fetched, we would typically see two separate loading indicators: one for the Eevee Pokemon that is selected and another for the carousel. This can make the page look disjointed and be a less-than-ideal user experience. We could display a single, cohesive loading indicator by wrapping both components inside a boundary. This unified loading state would persist until all the data for both components—the single Pokémon display and the carousel—has been fetched and is ready to be rendered. Here's how you might structure the code for such a scenario: ` Here, is the component that's performing asynchronous operations. While loading, the text 'Loading...' is displayed to the user. Great! But what about when things don't go as planned and an error occurs? Currently, Vue's doesn't directly handle errors within its boundary. However, there's a neat workaround. You can use the onErrorCaptured() hook in the parent component of to catch and manage errors. Here's how it works: ` If we run this code, and let’s say that we had an error selecting our Pokemon, this is how it is going to display to the user: The error message is specifically tied to the component where the issue occurred, ensuring that it's the only part of your application that shows an error notification. Meanwhile, the rest of your components will continue to operate and display as intended, maintaining the overall user experience without widespread disruption. This targeted error handling keeps the application's functionality intact while indicating where the problem lies. Conclusion stands out as a formidable feature in Vue.js, transforming the management of asynchronous operations into a more streamlined and user-centric process. It not only elevates the user experience by ensuring smoother interactions during data loading phases but also enhances code maintainability and application performance. I hope you found this blog post enlightening and that it adds value to your Vue.js projects. As always, happy coding and continue to explore the vast possibilities Vue.js offers to make your applications more efficient and engaging!...

Apr 24, 2024

6 mins

VueWeb PerformanceUXJavaScript

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

Understanding Vue.js's <Suspense> and Async Components

You might also like

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

QR Code Scanning & Generation

Understanding Vue.js's <Suspense> and Async Components