Prisma Client Just Became a Lot More Flexible: Prisma Client Extensions (Preview)

Prisma Client extensions (in Preview) enable many new use cases. This article will explore various ways you can use extensions to add custom functionality to Prisma Client.

Table Of Contents

• Introduction
• Using Prisma Client extensions
  • The components of an extension
  • Sharing an extension
• Sample use cases
  • Example: Computed fields
  • Example: Transformed fields
  • Example: Obfuscated fields
  • Example: Instance methods
  • Example: Static methods
  • Example: Model filters
  • Example: Readonly client
  • Example: Input transformation
  • Example: Input validation
  • Example: JSON field types
  • Example: Query logging
  • Example: Retry transactions
  • Example: Callback-free interactive transactions
  • Example: Audit log context
  • Example: Row level security
• Tell us what you think

Introduction

Prisma Client extensions provide a powerful new way to add functionality to Prisma in a type-safe manner. With them, you'll be able to create simple, flexible solutions to problems that aren't natively supported by the ORM (yet). You can define extensions in TypeScript or JavaScript, compose them, and even create multiple lightweight Prisma Client instances with different extensions.

When you're ready, you can share your extensions with the community as code snippets or by packaging them and publishing them to npm. This article will show you what's possible with extensions and hopefully inspires you to create and share your own!

Note: We believe Prisma Client extensions will open up many new possibilities when working with Prisma. However, just because a problem can be solved with an extension doesn't mean it won't ever be addressed with a first-class feature. One of our goals is to experiment and explore solutions with our community before integrating them into Prisma natively.

Using Prisma Client extensions

To use Prisma Client extensions, you'll need to first enable the clientExtensions preview feature in your Prisma schema file:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["clientExtensions"]
}

Then, you can call the $extends method on a Prisma Client instance. This will return a new, "extended" client instance without modifying the original instance. You can chain calls to $extends to use multiple extensions, and create separate instances with different extensions:

const prisma = new PrismaClient();
const extendedPrisma = prisma.$extends(myExtensionA).$extends(myExtensionB);

The components of an extension

There are four different types of components that can be included in an extension:

• Model components allow you to add new methods to models. This is a convenient way to add new operations alongside default methods like findMany, create, etc. You can use this as a repository of common query methods, encapsulate business logic for models, or do anything you might do with a static method on a class.
• Client components can be used to add new top-level methods to Prisma Client itself. Use this to extend the client with functionality that isn't tied to specific models.
• Query components let you hook into the query lifecycle and perform side effects, modify query arguments, or alter the results in a type-safe way. These are an alternative to middleware that provide full type safety and can be applied ad-hoc to different extended client instances.
• Result components add custom fields and methods to query result objects. These allow you to implement virtual / computed fields, define business logic for model instances in a single place, and transform the data returned by your queries.

A single extension can include one or more components, as well as an optional name to display in error messages:

const prisma = new PrismaClient().$extends({
  name: "myExtension",
  model: { /* ... */ },
  client: { /* ... */ },
  query: { /* ... */ },
  result: { /* ... */ },
});

To see the full syntax for defining each type of extension component, please refer to the docs.

Sharing an extension

You can use the Prisma.defineExtension utility to define an extension that can be shared with other users:

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

export default Prisma.defineExtension({
  model: {
    $allModels: {
      // new method
      findOrCreate(...) { } // code for the new method goes inside the brackets
    },
  },
});

When publishing shared extensions to npm, we recommend using the prisma-extension-<package-name> convention. This will make it easier for users to find and install your extension in their apps.

For example, if you publish an extension with the package name prisma-extension-find-or-create, users can install it like:

npm install prisma-extension-find-or-create

And then use the extension in their app:

import { PrismaClient } from "@prisma/client";
import findOrCreate from "prisma-extension-find-or-create";

const prisma = new PrismaClient().$extends(findOrCreate);
const user = await prisma.user.findOrCreate({ /* ... */ });

Read our documentation on sharing extensions for more details.

Sample use cases

We have compiled a list of use cases that might be solved with extensions, and we've created some examples of how these extensions could be written. Let's take a look at these use cases and their implementations:

Note: Prisma Client extensions are still in Preview, and some of the examples below may have some limitations. Where known, caveats are listed in the example README files on GitHub.

Example: Computed fields

View full example on GitHub

This example demonstrates how to create a Prisma Client extension that adds virtual / computed fields to a Prisma model. These fields are not included in the database, but rather are computed at runtime.

Computed fields are type-safe and may return anything from simple values to complex objects, or even functions that can act as instance methods for your models. Computed fields must specify which other fields they depend on, and they may be composed / reused by other computed fields.

const prisma = new PrismaClient() .$extends({ result: { user: { fullName: { needs: { firstName: true, lastName: true }, compute(user) { return ${user.firstName} ${user.lastName}; }, }, }, }, }) .$extends({ result: { user: { displayName: { needs: { fullName: true, email: true }, compute(user) { return ${user.fullName} <${user.email}>; }, }, }, }, });

```typescript
const users = await prisma.user.findMany({ take: 5 });

for (const user of users) {
  console.info(`- ${user.displayName}`);
}

model User {
  id        String @id @default(cuid())
  firstName String
  lastName  String
  email     String
}

Example: Transformed fields

View full example on GitHub

This example shows how to use a Prisma Client extension to transform fields in results returned by Prisma queries. In the example, a date field is transformed to a relative string for a specific locale.

This shows a way to implement internationalization (i18n) at the data access layer in your application. However, this technique could allow you to implement any kind of custom transformation or serialization/deserialization of fields on your query results.

const prisma = new PrismaClient().$extends({ result: { post: { createdAt: { needs: { createdAt: true }, compute(post) { return formatDistanceToNow(post.createdAt, { addSuffix: true, locale: de, }); }, }, }, }, });

```typescript
const posts = await prisma.post.findMany({ take: 5 });

for (const post of posts) {
  console.info(`- ${post.title} (${post.createdAt})`);
}

model Post {
  id        String   @id @default(cuid())
  title     String
  createdAt DateTime @default(now())
}

Example: Obfuscated fields

View full example on GitHub

This example is a special case for the previous Transformed fields example. It uses an extension to hide a sensitive password field on a User model. The password column is not included in selected columns in the underlying SQL queries, and it will resolve to undefined when accessed on a user result object. It could also resolve to any other value, such as an obfuscated string like "********".

const prisma = new PrismaClient().$extends({ result: { user: { password: { needs: {}, compute() { return undefined; }, }, }, }, });

```typescript
const user = await prisma.user.findFirstOrThrow();
console.info("Email: ", user.email);       // "[email protected]"
console.info("Password: ", user.password); // undefined

model User {
  id       String @id @default(cuid())
  email    String
  password String
}

Example: Instance methods

View full example on GitHub

This example shows how to add an Active Record-like interface to Prisma result objects. It uses a result extension to add save and delete methods directly to User model objects returned by Prisma Client methods.

This technique can be used to customize Prisma result objects with behavior, analogous to adding instance methods to model classes.

const prisma = new PrismaClient().$extends({ result: { user: { save: { needs: { id: true, email: true }, compute({ id, email }) { return () => prisma.user.update({ where: { id }, data: { email } }); }, },

  delete: {
    needs: { id: true },
    compute({ id }) {
      return () => prisma.user.delete({ where: { id } });
    },
  },
},

}, });

```typescript
const user = await prisma.user.create({
  data: { email: "[email protected]" },
});
user.email = "[email protected]";
await user.save();
await user.delete();

model User {
  id    String @id @default(cuid())
  email String
}

Example: Static methods

View full example on GitHub

This example demonstrates how to create a Prisma Client extension that adds signUp() and findManyByDomain() methods to a User model.

This technique can be used to abstract the logic for common queries / operations, create repository-like interfaces, or do anything you might do with a static class method.

const prisma = new PrismaClient().$extends({ model: { user: { async signUp(email: string, password: string) { const hash = await bcrypt.hash(password, 10); return prisma.user.create({ data: { email, password: { create: { hash, }, }, }, }); },

  async findManyByDomain(domain: string) {
    return prisma.user.findMany({
      where: { email: { endsWith: `@${domain}` } },
    });
  },
},

}, });

```typescript
await prisma.user.signUp("[email protected]", "p4ssword");
await prisma.user.signUp("[email protected]", "s3cret");

const users = await prisma.user.findManyByDomain("example2.com");

model User {
  id       String    @id @default(cuid())
  email    String
  password Password?
}

model Password {
  hash   String
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId String @unique
}

Example: Model filters

View full example on GitHub

This example demonstrates a Prisma Client extension which adds reusable filters for a model that can be composed and passed to a query's where condition. Complex, frequently used filtering conditions can be written once and accessed in many queries through the extended Prisma Client instance.

const prisma = new PrismaClient().$extends({ model: { post: { unpublished: () => ({ published: false }), published: () => ({ published: true }), byAuthor: (authorId: string) => ({ authorId }), byAuthorDomain: (domain: string) => ({ author: { email: { endsWith: @${domain} } }, }), hasComments: () => ({ comments: { some: {} } }), hasRecentComments: (date: Date) => ({ comments: { some: { createdAt: { gte: date } } }, }), titleContains: (search: string) => ({ title: { contains: search } }), } satisfies Record<string, (...args: any) => Prisma.PostWhereInput>, }, });

```typescript
const posts = await prisma.post.findMany({
  where: {
    AND: [
      prisma.post.published(),
      prisma.post.byAuthorDomain("prisma.io"),
      prisma.post.hasRecentComments(yesterday),
      prisma.post.titleContains("GraphQL"),
    ],
  },
});

model User {
  id        String @id @default(cuid())
  firstName String
  lastName  String
  email     String
  posts    Post[]
  comments Comment[]
}

model Post {
  id        String  @id @default(cuid())
  title     String
  published Boolean
  authorId  String
  author   User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  comments Comment[]
}

model Comment {
  id          String   @id @default(cuid())
  text        String
  createdAt   DateTime @default(now())
  commenterId String
  postId      String
  commenter User @relation(fields: [commenterId], references: [id], onDelete: Cascade)
  post      Post @relation(fields: [postId], references: [id], onDelete: Cascade)
}

Example: Readonly client

View full example on GitHub

This example creates a client that only allows read operations like findMany and count, not write operations like create or update. Calling write operations will result in an error at runtime and at compile time with TypeScript.

const WRITE_METHODS = [ "create", "update", "upsert", "delete", "createMany", "updateMany", "deleteMany", ] as const;

const ReadonlyClient = Prisma.defineExtension({ name: "ReadonlyClient", model: { $allModels: Object.fromEntries( WRITE_METHODS.map((method) => [ method, function (args: never) { throw new Error( Calling the \${method}` method on a readonly client is not allowed ); }, ]) ) as { [K in typeof WRITE_METHODS[number]]: ( args:Calling the `${K}` method on a readonly client is not allowed` ) => never; }, }, });

const prisma = new PrismaClient(); const readonlyPrisma = prisma.$extends(ReadonlyClient);

```typescript
const posts = await readonlyPrisma.post.findMany({ take: 5 });
console.log(posts);

// @ts-expect-error:
//   Argument of type '{ data: { title: string; published: boolean; }; }'
//   is not assignable to parameter of type '"Calling the `create` method
//   on a readonly client is not allowed"'.
await readonlyPrisma.post.create({
  data: { title: "New post", published: false },
});

model Post {
  id        String  @id @default(cuid())
  title     String
  published Boolean
}

Example: Input transformation

View full example on GitHub

This example creates an extended client instance which modifies query arguments to only include published posts.

Since query extensions allow modifying query arguments, it's possible to apply various kinds of default filters with this approach.

    // Refine the type - methods other than `create` accept a `where` clause
    args = args as Extract<typeof args, { where: unknown }>;

    // Augment the `where` clause with `published: true`
    return query({
      ...args,
      where: {
        ...args.where,
        published: true,
      },
    });
  },
},

}, });

```typescript
const publishedPostsCount = await prisma.post.count();

model Post {
  id        String  @id @default(cuid())
  title     String
  published Boolean
}

Example: Input validation

View full example on GitHub

This example uses Prisma Client extensions to perform custom runtime validations when creating and updating database objects. It uses a Zod runtime schema to check that the data passed to Prisma write methods is valid.

This could be used to sanitize user input or otherwise deny mutations that do not meet some criteria defined by your business logic rules.

const prisma = new PrismaClient().$extends({ query: { product: { create({ args, query }) { args.data = ProductCreateInput.parse(args.data); return query(args); }, update({ args, query }) { args.data = ProductCreateInput.partial().parse(args.data); return query(args); }, updateMany({ args, query }) { args.data = ProductCreateInput.partial().parse(args.data); return query(args); }, upsert({ args, query }) { args.create = ProductCreateInput.parse(args.create); args.update = ProductCreateInput.partial().parse(args.update); return query(args); }, }, }, });

```typescript
import { z } from "zod";
import { Prisma } from "@prisma/client";

export const ProductCreateInput = z.object({
  slug: z
    .string()
    .max(100)
    .regex(/^[a-z0-9]+(?:-[a-z0-9]+)*$/),
  name: z.string().max(100),
  description: z.string().max(1000),
  price: z
    .instanceof(Prisma.Decimal)
    .refine((price) => price.gte("0.01") && price.lt("1000000.00")),
}) satisfies z.Schema<Prisma.ProductUncheckedCreateInput>;

// Valid product
const product = await prisma.product.create({
  data: {
    slug: "example-product",
    name: "Example Product",
    description: "Lorem ipsum dolor sit amet",
    price: new Prisma.Decimal("10.95"),
  },
});

// Invalid product
try {
  await prisma.product.create({
    data: {
      slug: "invalid-product",
      name: "Invalid Product",
      description: "Lorem ipsum dolor sit amet",
      price: new Prisma.Decimal("-1.00"),
    },
  });
} catch (err: any) {
  console.log(err?.cause?.issues);
}

model Product {
  id          String   @id @default(cuid())
  slug        String
  name        String
  description String
  price       Decimal
  reviews     Review[]
}

model Review {
  id        String  @id @default(cuid())
  body      String
  stars     Int
  product   Product @relation(fields: [productId], references: [id], onDelete: Cascade)
  productId String
}

Example: JSON Field Types

View full example on GitHub

This next example combines the approaches shown in the Input validation and Transformed fields examples to provide static and runtime types for a Json field. It uses Zod to parse the field data and infer static TypeScript types.

This example includes a User model with a JSON profile field, which has a sparse structure that may vary between users. The extension has two parts:

• A result extension that adds a computed profile field. This field uses the Profile Zod schema to parse the underlying untyped profile field. TypeScript infers the static data type from the parser, so query results have both static and runtime type safety.
• A query extension that parses the profile field on input data for the User model's write methods like create and update.

const prisma = new PrismaClient().$extends({ result: { user: { profile: { needs: { profile: true }, compute({ profile }) { return Profile.parse(profile); }, }, }, },

query: { user: { create({ args, query }) { args.data.profile = Profile.parse(args.data.profile); return query(args); }, createMany({ args, query }) { const users = Array.isArray(args.data) ? args.data : [args.data]; for (const user of users) { user.profile = Profile.parse(user.profile); } return query(args); }, update({ args, query }) { if (args.data.profile !== undefined) { args.data.profile = Profile.parse(args.data.profile); } return query(args); }, updateMany({ args, query }) { if (args.data.profile !== undefined) { args.data.profile = Profile.parse(args.data.profile); } return query(args); }, upsert({ args, query }) { args.create.profile = Profile.parse(args.create.profile); if (args.update.profile !== undefined) { args.update.profile = Profile.parse(args.update.profile); } return query(args); }, }, }, });

```typescript
import { z } from "zod";

export type Avatar = z.infer<typeof Avatar>;
export const Avatar = z.object({
  url: z.string().url(),
  crop: z.object({
    top: z.number().min(0).max(1),
    right: z.number().min(0).max(1),
    bottom: z.number().min(0).max(1),
    left: z.number().min(0).max(1),
  }),
});

export type Address = z.infer<typeof Address>;
export const Address = z.object({
  street: z.string(),
  city: z.string(),
  region: z.string(),
  postalCode: z.string(),
  country: z.string(),
});

export type ContactInfo = z.infer<typeof ContactInfo>;
export const ContactInfo = z
  .object({
    email: z.string().email(),
    phone: z.string(),
    address: Address,
  })
  .partial();

export type SocialLinks = z.infer<typeof SocialLinks>;
export const SocialLinks = z
  .object({
    twitter: z.string().url(),
    github: z.string().url(),
    website: z.string().url(),
    linkedin: z.string().url(),
  })
  .partial();

export type Profile = z.infer<typeof Profile>;
export const Profile = z
  .object({
    firstName: z.string(),
    lastName: z.string(),
    avatar: Avatar,
    contactInfo: ContactInfo,
    socialLinks: SocialLinks,
  })
  .partial();

import * as runtime from "@prisma/client/runtime/index";
import { UserPayload } from "@prisma/client";

const users = await prisma.user.findMany({ take: 10 });
users.forEach(renderUser);

type ExtArgs = UserPayload<typeof prisma["$extends"]["extArgs"]>;
type User = runtime.Types.GetResult<ExtArgs, {}, "findUniqueOrThrow">;

function renderUser(user: User) {
  // ...
}

model User {
  id      String @id @default(cuid())
  email   String @unique
  profile Json
}

Example: Query logging

View full example on GitHub

This example shows how to use Prisma Client extensions to perform similar tasks to middleware. In this example, a query extension tracks the time it takes to fulfill each query, and logs the results along with the query and arguments themselves.

This technique could be used to perform generic logging, emit events, track usage, etc.

Note: You may be interested in the OpenTelemetry tracing and Metrics features (both in Preview), which provide detailed insights into performance and how Prisma interacts with the database.

const prisma = new PrismaClient().$extends({ query: { $allModels: { async $allOperations({ operation, model, args, query }) { const start = performance.now(); const result = await query(args); const end = performance.now(); const time = end - start; console.log( util.inspect( { model, operation, args, time }, { showHidden: false, depth: null, colors: true } ) ); return result; }, }, }, });

```typescript
await prisma.user.findMany({
  orderBy: [{ lastName: "asc" }, { firstName: "asc" }],
  take: 5,
});

await prisma.user.groupBy({ by: ["lastName"], _count: true });

model User {
  id        String @id @default(cuid())
  firstName String
  lastName  String
}

Example: Retry transactions

View full example on GitHub

This example shows how to use a Prisma Client extension to automatically retry transactions that fail due to a write conflict / deadlock timeout. Failed transactions will be retried with exponential backoff and jitter to reduce contention under heavy traffic.

function RetryTransactions(options?: Partial<IBackOffOptions>) { return Prisma.defineExtension((prisma) => prisma.$extends({ client: { $transaction(...args: any) { return backOff(() => prisma.$transaction.apply(prisma, args), { retry: (e) => { // Retry the transaction only if the error was due to a write conflict or deadlock // See: https://www.prisma.io/docs/reference/api-reference/error-reference#p2034 return e.code === "P2034"; }, ...options, }); }, } as { $transaction: typeof prisma["$transaction"] }, }) ); }

const prisma = new PrismaClient().$extends( RetryTransactions({ jitter: "full", numOfAttempts: 5, }) );

```typescript
// If one or more transactions fail due to deadlock / write conflict,
// the entire transaction will be retried up to 5 times.
await Promise.allSettled([
  prisma.$transaction(tx => {
    // ...
  }),
  prisma.$transaction(tx => {
    // ...
  }),
  prisma.$transaction(tx => {
    // ...
  }),
]);

model User {
  id        String @id @default(cuid())
  firstName String
  lastName  String
}

Example: Callback-free interactive transactions

View full example on GitHub

This example shows a Prisma Client extension which adds a new API for starting interactive transactions without callbacks.

This gives you the full power of interactive transactions (such as read–modify–write cycles), but in a more imperative API. This may be more convenient than the normal callback-style API for interactive transactions in some scenarios.

type FlatTransactionClient = Prisma.TransactionClient & { $commit: () => Promise<void>; $rollback: () => Promise<void>; };

const ROLLBACK = { [Symbol.for("prisma.client.extension.rollback")]: true };

const prisma = new PrismaClient().$extends({ client: { async $begin() { const prisma = Prisma.getExtensionContext(this); let setTxClient: (txClient: Prisma.TransactionClient) => void; let commit: () => void; let rollback: () => void;

  // a promise for getting the tx inner client
  const txClient = new Promise<Prisma.TransactionClient>((res) => {
    setTxClient = (txClient) => res(txClient);
  });

  // a promise for controlling the transaction
  const txPromise = new Promise((_res, _rej) => {
    commit = () => _res(undefined);
    rollback = () => _rej(ROLLBACK);
  });

  // opening a transaction to control externally
  if (
    "$transaction" in prisma &&
    typeof prisma.$transaction === "function"
  ) {
    const tx = prisma.$transaction((txClient) => {
      setTxClient(txClient);

      return txPromise.catch((e) => {
        if (e === ROLLBACK) return;
        throw e;
      });
    });

    // return a proxy TransactionClient with `$commit` and `$rollback` methods
    return new Proxy(await txClient, {
      get(target, prop) {
        if (prop === "$commit") {
          return () => {
            commit();
            return tx;
          };
        }
        if (prop === "$rollback") {
          return () => {
            rollback();
            return tx;
          };
        }
        return target[prop as keyof typeof target];
      },
    }) as FlatTransactionClient;
  }

  throw new Error("Transactions are not supported by this client");
},

}, });

```typescript
const tx = await prisma.$begin();
const user = await tx.user.findFirstOrThrow();
await tx.user.update(/* ... */);
await tx.$commit(); // Or: await tx.$rollback();

model User {
  id        String @id @default(cuid())
  firstName String
  lastName  String
  email     String
}

Example: Audit log context

View full example on GitHub

This example shows how to use a Prisma Client extension to provide the current application user's ID as context to an audit log trigger in Postgres. User IDs are included in an audit trail tracking every change to rows in a table.

A detailed explanation of this solution can be found in the example's README on GitHub.

function forUser(userId: number) { return Prisma.defineExtension((prisma) => prisma.$extends({ query: { $allModels: { async $allOperations({ args, query }) { const [, result] = await prisma.$transaction([ prisma.$executeRawSELECT set_config('app.current_user_id', ${userId.toString()}, TRUE), query(args), ]); return result; }, }, }, }) ); }

```typescript
const prisma = new PrismaClient();
const user = await prisma.user.findFirstOrThrow();
const product = await prisma.product.findFirstOrThrow();

const userPrisma = prisma.$extends(forUser(user.id));

await userPrisma.product.update({
  where: { id: product.id },
  data: { name: "Updated Name" },
});

model User {
  id    Int    @id @default(autoincrement())
  email String

  productVersions ProductVersion[]

  @@schema("public")
}

model Product {
  id   Int    @id @default(autoincrement())
  name String

  versions ProductVersion[]

  @@schema("public")
}

enum AuditOperation {
  INSERT
  UPDATE
  DELETE

  @@schema("audit")
}

model ProductVersion {
  // Version metadata fields
  versionId        Int            @id @default(autoincrement())
  versionOperation AuditOperation
  versionProductId Int?
  versionUserId    Int?
  versionTimestamp DateTime

  // Mirrored fields from the Product table
  id   Int
  name String

  product Product? @relation(fields: [versionProductId], references: [id])
  user    User?    @relation(fields: [versionUserId], references: [id])

  @@schema("audit")
}

-- Product audit trigger function
CREATE OR REPLACE FUNCTION "audit"."Product_audit"() RETURNS TRIGGER AS $$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO "audit"."ProductVersion"
            VALUES (DEFAULT, 'DELETE', NULL, current_setting('app.current_user_id', TRUE)::int, now(), OLD.*);
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO "audit"."ProductVersion"
            VALUES (DEFAULT, 'UPDATE', NEW."id", current_setting('app.current_user_id', TRUE)::int, now(), NEW.*);
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO "audit"."ProductVersion"
            VALUES (DEFAULT, 'INSERT', NEW."id", current_setting('app.current_user_id', TRUE)::int, now(), NEW.*);
        END IF;
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit
AFTER INSERT OR UPDATE OR DELETE ON "public"."Product"
    FOR EACH ROW EXECUTE FUNCTION "audit"."Product_audit"();

Example: Row level security

View full example on GitHub

This example shows how to use a Prisma Client extension to isolate data between tenants in a multi-tenant app using Row Level Security (RLS) in Postgres.

A detailed explanation of this solution can be found in the example's README on GitHub.

function bypassRLS() { return Prisma.defineExtension((prisma) => prisma.$extends({ query: { $allModels: { async $allOperations({ args, query }) { const [, result] = await prisma.$transaction([ prisma.$executeRawSELECT set_config('app.bypass_rls', 'on', TRUE), query(args), ]); return result; }, }, }, }) ); }

function forCompany(companyId: string) { return Prisma.defineExtension((prisma) => prisma.$extends({ query: { $allModels: { async $allOperations({ args, query }) { const [, result] = await prisma.$transaction([ prisma.$executeRawSELECT set_config('app.current_company_id', ${companyId}, TRUE), query(args), ]); return result; }, }, }, }) ); }

```typescript
const prisma = new PrismaClient();
const user = await prisma.$extends(bypassRLS()).user.findFirstOrThrow();
const companyPrisma = prisma.$extends(forCompany(user.companyId));

const projects = await companyPrisma.project.findMany({
  include: {
    owner: true,
    tasks: {
      include: {
        assignee: true,
      },
    },
  },
});

invariant(projects.every((project) => project.companyId === user.companyId));

model Company {
  id   String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name String

  users    User[]
  projects Project[]
  tasks    Task[]
}

model User {
  id        String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  companyId String @default(dbgenerated("(current_setting('app.current_company_id'::text))::uuid")) @db.Uuid
  email     String @unique

  company       Company   @relation(fields: [companyId], references: [id], onDelete: Cascade)
  ownedProjects Project[]
  assignedTasks Task[]
}

model Project {
  id        String  @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  companyId String  @default(dbgenerated("(current_setting('app.current_company_id'::text))::uuid")) @db.Uuid
  userId    String? @db.Uuid
  title     String

  company Company @relation(fields: [companyId], references: [id], onDelete: Cascade)
  owner   User?   @relation(fields: [userId], references: [id], onDelete: SetNull)
  tasks   Task[]
}

enum TaskStatus {
  Pending
  InProgress
  Complete
  WontDo
}

model Task {
  id        String     @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  companyId String     @default(dbgenerated("(current_setting('app.current_company_id'::text))::uuid")) @db.Uuid
  projectId String     @db.Uuid
  userId    String?    @db.Uuid
  title     String
  status    TaskStatus

  company  Company @relation(fields: [companyId], references: [id], onDelete: Cascade)
  project  Project @relation(fields: [projectId], references: [id], onDelete: Cascade)
  assignee User?   @relation(fields: [userId], references: [id], onDelete: SetNull)
}

-- Enable Row Level Security
ALTER TABLE "Company" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "User" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Project" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Task" ENABLE ROW LEVEL SECURITY;

-- Force Row Level Security for table owners
ALTER TABLE "Company" FORCE ROW LEVEL SECURITY;
ALTER TABLE "User" FORCE ROW LEVEL SECURITY;
ALTER TABLE "Project" FORCE ROW LEVEL SECURITY;
ALTER TABLE "Task" FORCE ROW LEVEL SECURITY;

-- Create row security policies
CREATE POLICY tenant_isolation_policy ON "Company" USING ("id" = current_setting('app.current_company_id', TRUE)::uuid);
CREATE POLICY tenant_isolation_policy ON "User" USING ("companyId" = current_setting('app.current_company_id', TRUE)::uuid);
CREATE POLICY tenant_isolation_policy ON "Project" USING ("companyId" = current_setting('app.current_company_id', TRUE)::uuid);
CREATE POLICY tenant_isolation_policy ON "Task" USING ("companyId" = current_setting('app.current_company_id', TRUE)::uuid);

-- Create policies to bypass RLS (optional)
CREATE POLICY bypass_rls_policy ON "Company" USING (current_setting('app.bypass_rls', TRUE)::text = 'on');
CREATE POLICY bypass_rls_policy ON "User" USING (current_setting('app.bypass_rls', TRUE)::text = 'on');
CREATE POLICY bypass_rls_policy ON "Project" USING (current_setting('app.bypass_rls', TRUE)::text = 'on');
CREATE POLICY bypass_rls_policy ON "Task" USING (current_setting('app.bypass_rls', TRUE)::text = 'on');

Tell us what you think

We hope you are as excited as we are about the possibilities that Prisma Client extensions create!

💡 You can share your feedback with us in this GitHub issue.