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

D1 SQLite: Writing queries with the D1 Client API

Writing queries with the D1 Client API In the previous post we defined our database schema, got up and running with migrations, and loaded some seed data into our database. In this post we will be working with our new database and seed data. If you want to participate, make sure to follow the steps in the first post. We’ve been taking a minimal approach so far by using only wrangler and sql scripts for our workflow. The D1 Client API has a small surface area. Thanks to the power of SQL, we will have everything we need to construct all types of queries. Before we start writing our queries, let's touch on some important concepts. Prepared statements and parameter binding This is the first section of the docs and it highlights two different ways to write our SQL statements using the client API: prepared and static statements. Best practice is to use prepared statements because they are more performant and prevent SQL injection attacks. So we will write our queries using prepared statements. We need to use parameter binding to build our queries with prepared statements. This is pretty straightforward and there are two variations. By default we add ? ’s to our statement to represent a value to be filled in. The bind method will bind the parameters to each question mark by their index. The first ? is tied to the first parameter in bind, 2nd, etc. I would stick with this most of the time to avoid any confusion. ` I like this second method less as it feels like something I can imagine messing up very innocently. You can add a number directly after a question mark to indicate which number parameter it should be bound to. In this exampl, we reverse the previous binding. ` Reusing prepared statements If we take the first example above and not bind any values we have a statement that can be reused: ` Querying For the purposes of this post we will just build example queries by writing them out directly in our Worker fetch handler. If you are building an app I would recommend building functions or some other abstraction around your queries. select queries Let's write our first query against our data set to get our feet wet. Here’s the initial worker code and a query for all authors: ` We pass our SQL statement into prepare and use the all method to get all the rows. Notice that we are able to pass our types to a generic parameter in all. This allows us to get a fully typed response from our query. We can run our worker with npm run dev and access it at http://localhost:8787 by default. We’ll keep this simple workflow of writing queries and passing them as a json response for inspection in the browser. Opening the page we get our author results. joins Not using an ORM means we have full control over our own destiny. Like anything else though, this has tradeoffs. Let’s look at a query to fetch the list of posts that includes author and tags information. ` Let’s walk through each part of the query and highlight some pros and cons. ` * The query selects all columns from the posts table. * It also selects the name column from the authors table and renames it to author_name. * It aggregates the name column from the tags table into a JSON array. If there are no tags, it returns an empty JSON array. This aggregated result is renamed to tags. ` * The query starts by selecting data from the posts table. * It then joins the authors table to include author information for each post, matching posts to authors using the author_id column in posts and the id column in authors. * Next, it left joins the posts_tags table to include tag associations for each post, ensuring that all posts are included even if they have no tags. * Next, it left joins the tags table to include tag names, matching tags to posts using the tag_id column in posts_tags and the id column in tags. * Finally, group the results by the post id so that all rows with the same post id are combined in a single row SQL provides a lot of power to query our data in interesting ways. JOIN ’s will typically be more performant than performing additional queries.You could just as easily write a simpler version of this query that uses subqueries to fetch post tags and join all the data by hand with JavaScript. This is the nice thing about writing SQL, you’re free to fetch and handle your data how you please. Our results should look similar to this: ` This brings us to our next topic. Marshaling / coercing result data A couple of things we notice about the format of the result data our query provides: Rows are flat. We join the author directly onto the post and prefix its column names with author. ` Using an ORM we might get the data back as a child object: ` Another thing is that our tags data is a JSON string and not a JavaScript array. This means that we will need to parse it ourselves. ` This isn’t the end of the world but it is some more work on our end to coerce the result data into the format that we actually want. This problem is handled in most ORM’s and is their main selling point in my opinion. insert / update / delete Next, let’s write a function that will add a new post to our database. ` There’s a few queries involved in our create post function: * first we create the new post * next we run through the tags and either create or return an existing tag * finally, we add entries to our post_tags join table to associate our new post with the tags assigned We can test our new function by providing post content in query params on our index page and formatting them for our function. ` I gave it a run like this: http://localhost:8787authorId=1&tags=Food%2CReview&title=A+review+of+my+favorite+Italian+restaurant&content=I+got+the+sausage+orchette+and+it+was+amazing.+I+wish+that+instead+of+baby+broccoli+they+used+rapini.+Otherwise+it+was+a+perfect+dish+and+the+vibes+were+great And got a new post with the id 11. UPDATE and DELETE operations are pretty similar to what we’ve seen so far. Most complexity in your queries will be similar to what we’ve seen in the posts query where we want to JOIN or GROUP BY data in various ways. To update the post we can write a query that looks like this: ` COALESCE acts similarly to if we had written a ?? b in JavaScript. If the binded value that we provide is null it will fall back to the default. We can delete our new post with a simple DELETE query: ` Transactions / Batching One thing to note with D1 is that I don’t think the traditional style of SQLite transactions are supported. You can use the db.batch API to achieve similar functionality though. According to the docs: Batched statements are SQL transactions ↗. If a statement in the sequence fails, then an error is returned for that specific statement, and it aborts or rolls back the entire sequence. ` Summary In this post, we've taken a hands-on approach to exploring the D1 Client API, starting with defining our database schema and loading seed data. We then dove into writing queries, covering the basics of prepared statements and parameter binding, before moving on to more complex topics like joins and transactions. We saw how to construct and execute queries to fetch data from our database, including how to handle relationships between tables and marshal result data into a usable format. We also touched on inserting, updating, and deleting data, and how to use transactions to ensure data consistency. By working through these examples, we've gained a solid understanding of how to use the D1 Client API to interact with our database and build robust, data-driven applications....

Dec 23, 2024

6 mins

JavaScript

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

D1 SQLite: Writing queries with the D1 Client API

Let's innovate together!

You might also like

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

QR Code Scanning & Generation

D1 SQLite: Writing queries with the D1 Client API