Authoring custom middleware

A middleware you write yourself is a plain object: a name, plus the hook functions you want Prisma ORM to call around your queries. You do not extend a base class, and you do not call a function to install it. You put the object in the middleware array where you create your client, and Prisma ORM calls its hooks from then on. The examples here wrap that object in a function so the middleware can take options, but the object is the middleware, and an object literal typed as SqlMiddleware works just as well.

If you used $use in Prisma ORM 7, the middleware array is its replacement. There is no next() to wrap the query in, so where you used to write one function that ran before the query, called it, and then ran after it, you now write one hook for the moment before and another for the moment after. Because the before-hook can no longer hold a variable open across the query, carry state from a before-hook to an after-hook shows how the after-hook gets what the before-hook worked out. Coming from Prisma ORM 7 has a table of what each Prisma ORM 7 feature became.

The steps that follow build a query logger that prints every query with its row count and how long it took. You create one file, register it in one place, run a query, and see the output.

• A Prisma ORM project with a working src/prisma/db.ts, the file where the client is created, because that is where middleware is registered. If you already have a project, you can use the db.ts you have. You also need a PostgreSQL database to run against, because db.ts reads its connection string from the DATABASE_URL environment variable, which you export in the shell you run the commands from. The PostgreSQL quickstart sets both up in a few minutes:

bun create prisma@latest --provider postgres --no-deploy

cd my-app

bun run db:init

pnpm create prisma@latest --provider postgres --no-deploy
cd my-app
pnpm run db:init

yarn create prisma@latest --provider postgres --no-deploy
cd my-app
yarn db:init

npm create prisma@latest -- --provider postgres --no-deploy
cd my-app
npm run db:init

Create a new file next to your database setup. The logger needs one hook, afterQuery, which Prisma ORM calls once after every query that returns rows. Its first argument, plan, is the query that was sent, as a plain object, and plan.sql is its SQL text. Its second argument, result, is where the numbers come from: result.rowCount is how many rows came back, and result.latencyMs is how long the query took in milliseconds. The familyId: "sql" field says the middleware works with SQL databases only, and step 5 covers when to set it.

import type { SqlMiddleware } from "@prisma/orm-postgres/family-runtime";

export function queryLogger(): SqlMiddleware {

  return {

    name: "query-logger",

    familyId: "sql",

    async afterQuery(plan, result) {

      console.log(

        `[query-logger] ${result.rowCount} rows in ${Math.round(result.latencyMs)}ms · ${plan.sql}`,

      );

    },

  };

}

The built-in middleware and the SqlMiddleware type are imported from @prisma/orm-postgres/family-runtime, which works with any SQL database. Typing the object you return as SqlMiddleware is what gives plan and result their types in your editor.

The name is the label Prisma ORM uses when it talks about your middleware, so it turns up in messages such as RUNTIME.MIDDLEWARE_FAMILY_MISMATCH ("Middleware 'query-logger' requires family 'sql' ..."). Nothing checks that names are unique, so pick a name that will mean something to you when you read it in a message.

If a call gives you back a count instead of rows, afterQuery is not called for it and afterExecute is called instead. On the ORM API the calls that return a count are createAndCount, updateAndCount, and deleteAndCount. Every other call returns rows and reaches afterQuery, including create, update, delete, updateAll, and deleteAll.

The SQL query builder is the other way to write a query on the same client, db.sql.public.User... in place of db.orm.public.User.... With it you build a query and run it in two steps: .build() gives you the query as a plain object, the plan, and a method on db.runtime() runs it. db.runtime().query(plan) returns rows and calls afterQuery, while db.runtime().execute(plan) reports how many rows the statement changed and calls afterExecute.

To have the logger cover the counting calls as well, add a second hook to the object queryLogger() returns in src/prisma/query-logger.ts, next to afterQuery:

    async afterExecute(plan, result) {

      const affected = result.completed ? result.stats.affectedRows : 0;

      console.log(

        `[query-logger] ${affected} affected in ${Math.round(result.latencyMs)}ms · ${plan.sql}`,

      );

    },

result.stats.affectedRows is how many rows that statement changed, and it is the only field on result.stats. It is there only when the statement finished, which is what result.completed tells you, so read the count through the check above rather than on its own. A statement that failed logs 0 affected too, so check result.completed when you need to tell the two apart.

You register a middleware by adding it to the middleware array where you create your client. Two of the imports in the file below come from your contract, which is the Prisma ORM 8 name for your schema: contract.prisma in a generated project, in place of schema.prisma. npx prisma contract emit writes contract.json and contract.d.ts from it, and a project created with npm create prisma@latest already has all three in src/prisma/. This is the smallest db.ts that works, with the logger registered:

import postgres from '@prisma/orm-postgres/runtime';

import { queryLogger } from './query-logger';

import type { Contract } from './contract.d';

import contractJson from './contract.json' with { type: 'json' };

export const db = postgres<Contract>({

  contractJson,

  url: process.env['DATABASE_URL']!,

  middleware: [queryLogger()],

});

The only line added here is the middleware option. The src/prisma/db.ts that npm create prisma@latest generates has more in it than this, including a connectDatabase export that the generated seed.ts imports, so keep that file and add the middleware option to the postgres<Contract>({ ... }) calls in it rather than replacing it with the file above. Those calls only run when the app is started with DATABASE_URL exported, as npm run dev does. With npm run dev:composer, the generated file takes its client from Composer through service.load() instead, and the middleware option in db.ts is never used, so run the app with DATABASE_URL exported while you follow this guide. If you set your project up by hand, The data contract covers where contract.json and contract.d.ts come from.

That one line is all the setup there is, and every query on this client now passes through the logger, whether you wrote it with the ORM API (db.orm.public.User...) or with the SQL query builder.

To see the logger work, put a small script in src/index.ts that writes a row and then reads some back:

import { db } from "./prisma/db";

await db.orm.public.User.create({

  email: `mia+${Date.now()}@prisma.io`,

  name: "Mia",

});

const users = await db.orm.public.User.select("id", "email").limit(5).all();

console.log(`fetched ${users.length} users`);

await db.close();

In a project from the quickstart, the dev script is tsx watch src/index.ts, so running it runs your script and then keeps watching the file:

npm run dev

The logger reports both queries, and prints the SQL that actually ran:

[query-logger] 1 rows in 18ms · INSERT INTO "public"."User" ("email", "name", "updatedAt") VALUES ($1, $2, $3) RETURNING "User"."createdAt", "User"."email", "User"."id", "User"."name", "User"."updatedAt", "User"."username"

[query-logger] 1 rows in 17ms · SELECT "User"."id" AS "id", "User"."email" AS "email" FROM "public"."User" LIMIT 5

fetched 1 users

Both lines came through afterQuery, the create included, because create gives you back the row it inserted rather than a count.

The dev script keeps running after the output appears, so stop it with Ctrl+C when you are done. If you see the query result but no [query-logger] lines, then the query ran on a client that does not have the middleware registered, so check that your script imports db from the file you edited in step 2.

Middleware you write for real work usually takes options, so give the logger a threshold in milliseconds and it reports only the queries that took longer than that. The file below shows afterQuery on its own to keep the change easy to see. If you added afterExecute in step 1, keep it where it is:

import type { SqlMiddleware } from "@prisma/orm-postgres/family-runtime";

export interface QueryLoggerOptions {

  /** Only log queries slower than this many milliseconds. Default: 0, log everything. */

  readonly thresholdMs?: number;

}

export function queryLogger(options?: QueryLoggerOptions): SqlMiddleware {

  const thresholdMs = options?.thresholdMs ?? 0;

  return {

    name: "query-logger",

    familyId: "sql",

    async afterQuery(plan, result) {

      if (result.latencyMs < thresholdMs) return;

      console.log(

        `[query-logger] ${result.rowCount} rows in ${Math.round(result.latencyMs)}ms · ${plan.sql}`,

      );

    },

  };

}

Pass the threshold where you register it, replacing the middleware: [queryLogger()], line of the src/prisma/db.ts from step 2:

middleware: [queryLogger({ thresholdMs: 250 })],

Run the script again and the logger goes quiet, because both queries finish in under 250ms. To see the lines again, register it with no options: middleware: [queryLogger()].

Prisma ORM calls your hooks in the order you listed the middleware in the array, and that order decides what each middleware sees and whether it runs at all:

• Prisma ORM waits for each hook to finish before the query moves on, so a slow afterQuery delays the await in your own code.
• If a middleware throws, the ones listed after it do not run for that hook. A query that a before-hook rejects, as budgets rejects a SELECT with no LIMIT, never reaches any afterQuery, so the logger cannot see it wherever you list it. Put the logger first and its afterQuery has already run when a later middleware throws from its own afterQuery, as the budgets time check does, so those queries are still logged.
• When a middleware changes the query, the middleware listed after it sees the changed version.

A built-in middleware goes into the same array, imported from the same package as SqlMiddleware. To run the logger ahead of budgets, add the import to the top of src/prisma/db.ts and replace its middleware: line:

import { budgets } from '@prisma/orm-postgres/family-runtime';

middleware: [queryLogger({ thresholdMs: 250 }), budgets()],

Prisma ORM sorts the databases it supports into two families, SQL and MongoDB, and a middleware can say which family it is written for. Set familyId: 'sql' when your middleware reads or writes something only a SQL database has, such as plan.sql in the logger above.

If a middleware that declares familyId: 'sql' is registered on a MongoDB client, that client throws at its first query (or at connect(), if you call it), before anything reaches the database. The error's code is RUNTIME.MIDDLEWARE_FAMILY_MISMATCH and the message names the middleware, so you find out straight away rather than from a failure inside your hook much later.

Leave familyId out when your middleware stays away from everything specific to one kind of database. It stays that way as long as you:

• Use any hook except beforeCompile, which only SQL databases have.
• Read what your hook is handed on result rather than plan.sql or plan.ast, which a SQL query has and a MongoDB query does not. result gives you rowCount, latencyMs, completed, stats.affectedRows, and source, which is 'driver' when the database answered the query and 'middleware' when a middleware answered it instead.

A middleware written that way runs on PostgreSQL and on MongoDB, which is how the built-in cache works.

If the middleware only ever goes into a postgres(...) client, keep SqlMiddleware. If you want one object you can put in both a postgres(...) client and a mongo(...) client, type it as CrossFamilyMiddleware instead:

import type { CrossFamilyMiddleware } from '@prisma/orm-postgres/components/runtime';

On the MongoDB side, the client is mongo(...) from @prisma/orm-mongo/runtime, and it takes the same middleware array in the same place. A middleware written for MongoDB alone is typed as MongoMiddleware from @prisma/orm-mongo/family-runtime.

Which hooks Prisma ORM calls depends on what your call gives back. A call that returns rows runs one set of hooks, a call that returns only a count runs a shorter set, and beforeCompile is the only hook that runs for both. Implement whichever hooks you need:

Every hook also receives ctx, written last above, which the logger leaves out of its parameter list because it does not use it. Throwing from onRow fails the call with your error, and the rows still to come are not passed on to your code.

A hook you implement for one kind of call is never called for the other kind, so a middleware that needs to cover both implements a hook on each. beforeQuery and beforeExecute take the same arguments and return nothing, which is why you can write one function and assign it to both, and anything you do return from them is ignored. The intercept hooks cannot share a function that way, because interceptQuery returns { rows } and interceptExecute returns { stats }.

How middleware works walks through the order with an animation.

beforeCompile is where you change a query before it runs. It gives you the query as an AST, the abstract syntax tree, which is the query as a tree of typed objects rather than as text. A SELECT is one object whose kind is 'select', whose from is the table it reads, and whose where is its condition. The hook runs before Prisma ORM turns that tree into SQL text, so a changed tree changes the SQL that reaches the database.

Its argument, the draft, is the query before Prisma ORM writes its SQL. A draft has ast, the tree you change, and meta, which you pass through unchanged. meta.lane says which API built the query: 'orm-client' for the ORM API, and 'dsl' for the SQL query builder. You return a copy of the draft with a new ast, or undefined when there is nothing to change.

The middleware below adds a condition to every SELECT whose own FROM clause names the User table:

import type { SqlMiddleware } from '@prisma/orm-postgres/family-runtime';

import { AndExpr, type BinaryExpr } from '@prisma/orm-postgres/relational-core/ast';

export function scopeUserSelects(condition: () => BinaryExpr | undefined): SqlMiddleware {

  return {

    name: 'scope-user-selects',

    familyId: 'sql',

    async beforeCompile(draft) {

      if (draft.ast.kind !== 'select') return undefined;

      if (draft.ast.from?.kind !== 'table-source') return undefined;

      if (draft.ast.from.name !== 'User') return undefined;

      const predicate = condition();

      if (predicate === undefined) return undefined;

      const where = draft.ast.where ? AndExpr.of([draft.ast.where, predicate]) : predicate;

      return { ...draft, ast: draft.ast.withWhere(where) };

    },

  };

}

The kind check on from keeps the middleware to plain tables: a SELECT that reads from a subquery has a from whose kind is 'derived-table-source', and one that reads from a function has 'function-source'. The name it checks, 'User', is the table name in your database rather than the model name in your contract, because by this point the query is expressed in tables and columns. The two are the same unless the model sets @@map. draft.ast.withWhere(where) gives back a new tree with that WHERE and leaves the original alone.

These checks read only the table in the query's own FROM clause, so User rows that a query reaches any other way do not get the condition. Users you load with .include(...) from another model, such as the author of each post, are not filtered, and neither is a SQL query builder query that joins User onto another table.

The middleware calls condition for each SELECT whose FROM clause names User, after the checks above, so the condition can change from one request to the next. You build it from the same classes. Put values through ParamRef.of rather than LiteralExpr.of, because a ParamRef becomes a bound parameter such as $1 while a literal is pasted into the SQL text itself, so a value that came from one of your users could inject SQL of its own. A ParamRef you build by hand has to name the PostgreSQL type of the value with codec: { codecId: ... }, and the ids start with pg/ and end with @1, so the text column name takes pg/text@1. Leave that out and the query fails with an error whose code is RUNTIME.PARAM_REF_MISSING_CODEC.

Prisma ORM does not know which request a query belongs to, so the value has to come from a place your condition function can read. The example below keeps it in an AsyncLocalStorage from Node.js, which holds one value per request while that request's code runs:

import { AsyncLocalStorage } from 'node:async_hooks';

import postgres from '@prisma/orm-postgres/runtime';

import { BinaryExpr, ColumnRef, ParamRef } from '@prisma/orm-postgres/relational-core/ast';

import { scopeUserSelects } from './scope-user-selects';

import type { Contract } from './contract.d';

import contractJson from './contract.json' with { type: 'json' };

export const currentName = new AsyncLocalStorage<string>();

export const db = postgres<Contract>({

  contractJson,

  url: process.env['DATABASE_URL']!,

  middleware: [

    scopeUserSelects(() => {

      const name = currentName.getStore();

      if (name === undefined) return undefined;

      return BinaryExpr.eq(

        ColumnRef.of('User', 'name'),

        ParamRef.of(name, { codec: { codecId: 'pg/text@1' } }),

      );

    }),

  ],

});

This file imports BinaryExpr without type, because it calls BinaryExpr.eq(...). Keep the type on the BinaryExpr import in scope-user-selects.ts.

Run each request's queries inside currentName.run(...), and await them inside its callback. In a web server, that means wrapping the code of each request handler:

import { currentName, db } from '../prisma/db';

const users = await currentName.run('Mia', async () => {

  return await db.orm.public.User.select('id', 'name').all();

});

Every SELECT whose FROM clause names User then runs with WHERE "User"."name" = $1 added, and a query that already had a WHERE clause keeps it, joined to yours with AND. The await inside the callback matters: .all() returns the query without running it, and the query runs when you await it. With currentName.run('Mia', () => db.orm.public.User.select('id', 'name').all()), the await is outside the callback, so the query runs after run(...) has returned, getStore() gives undefined, and the query gets no condition. The same goes for any query that runs outside currentName.run(...). If every query must have one, throw in the function instead of returning undefined.

The codecId depends on the field's type in your contract:

The temporal in the DateTime id means the value reaches your code as a JavaScript Temporal.Instant rather than a Date.

For a field whose type is not in the table, read the codecId off your own client: db.sql.public.User.columns.name.codecId is the id for the name column of the User table, so print it from any script that imports db. The SQL query builder reference lists every id, in its section on fns.raw and .returns().

BinaryExpr has one method per comparison (eq, neq, gt, gte, lt, lte, like, in, notIn), and AndExpr.of and OrExpr.of combine conditions. They all come from @prisma/orm-postgres/relational-core/ast, so your editor lists them once you import from there.

interceptQuery lets your middleware answer a query itself: return { rows } from it and the database driver never runs. Caches, test fixtures, and circuit breakers all work this way.

The rows you return are raw row objects, keyed by the column aliases in the SQL, holding the values as they come off the wire: numbers for integers, strings for text and timestamps. To see a real one, add onRow to the object queryLogger() returns in src/prisma/query-logger.ts, run your script once, and take it out again, because onRow gets rows in exactly the shape interceptQuery has to return:

async onRow(row) {

  console.log(row);

},

For the SELECT in step 3, whose SQL is SELECT "User"."id" AS "id", "User"."email" AS "email" FROM "public"."User" LIMIT 5, a row object has an id key holding a number and an email key holding a string. The middleware below is a test fixture: it returns one made-up row of that shape in place of the database's answer, so a test can run that query without data in the table. Add it to the same middleware array as the logger, either on its own as middleware: [userFixture()] or next to the logger as middleware: [queryLogger(), userFixture()]:

import type { SqlMiddleware } from '@prisma/orm-postgres/family-runtime';

export function userFixture(): SqlMiddleware {

  return {

    name: 'user-fixture',

    familyId: 'sql',

    async interceptQuery(plan, ctx) {

      if (ctx.scope !== 'runtime') return undefined;

      if (plan.ast.kind !== 'select') return undefined;

      if (plan.ast.from?.kind !== 'table-source') return undefined;

      if (plan.ast.from.name !== 'User') return undefined;

      return { rows: [{ id: 1, email: 'mia@prisma.io' }] };

    },

  };

}

Prisma ORM decodes the rows you return the same way it decodes rows that came from the database, so your caller gets the types the contract gives those columns, even though you handed back plain driver values.

The last three checks make the fixture answer only a SELECT that reads the User table directly, in the same way the beforeCompile example picks its queries. Matching on the SQL text instead, such as plan.sql.includes('FROM "public"."User"'), also catches DELETE FROM "public"."User", so an ORM .deleteAll() would get a made-up row back and delete nothing. On an UPDATE or a DELETE the table name is at plan.ast.table.name.

plan.meta.lane tells you which API built a query, but nothing on the plan says which model or which ORM call it came from, so the table name in the tree is the only thing you have to match on.

The first check, on ctx.scope, lets every query inside a transaction, or on a connection you took from the pool, go on to the database, so the fixture answers only queries sent on db directly. Keep it, because an ORM .update() or .delete() reads its row inside a transaction before it writes, and a made-up row there would send the write to the wrong row.

Return undefined and the query carries on to the database as usual. When several middleware implement interceptQuery, the first one in the array that returns rows answers the query and the rest are not called at all. afterQuery still runs either way, with result.source set to 'middleware', so your logging can tell which queries were answered without a trip to the database. For a call that returns a count, interceptExecute does the same job and returns { stats }, which only ever holds affectedRows, as in { stats: { affectedRows: 0 } }. The cache middleware is the implementation to copy from.

Every hook receives a context object called ctx, holding the following:

ctx.scope is 'runtime' for a query you sent on db directly, 'transaction' inside db.transaction(...), and 'connection' for a query you sent on a connection you took out of the pool yourself with db.runtime().connection(). The cache reads it to leave queries inside a transaction alone.

beforeQuery and beforeExecute take one more argument after ctx, called params, which lets you read and replace the values that will be sent as $1, $2, and so on. params.entries() gives you one entry per value, each with the value itself, the codecId naming its PostgreSQL type, and a ref that identifies which value it is, and you change one by handing that ref back to params.replaceValue. The hook below goes inside the object your middleware returns, next to name and familyId, and it opens with if (!params) return; because the argument is typed as optional even though Prisma ORM always passes it:

async beforeQuery(plan, ctx, params) {

  if (!params) return;

  for (const entry of params.entries()) {

    if (entry.codecId === 'pg/text@1' && entry.value === 'secret') {

      params.replaceValue(entry.ref, 'redacted');

    }

  }

},

You cannot add or remove values this way, only replace the ones the query already has.

Every hook that runs for one query gets the same ctx.planExecutionId, so a middleware that has to remember something between two hooks keeps it in a Map under that ID and takes it out again on the other side. This is what replaces wrapping a query in next(). Copy the pattern whenever the after-hook needs something only the before-hook could work out. afterQuery runs after a success and after a driver failure, but not when a before-hook throws, so in that case the entry stays in the Map. Register timing() after any middleware that can throw, and a query one of them rejects never reaches its beforeQuery, so nothing is left behind:

import type { SqlMiddleware } from '@prisma/orm-postgres/family-runtime';

const startedAt = new Map<string, number>();

export function timing(): SqlMiddleware {

  return {

    name: 'timing',

    familyId: 'sql',

    async beforeQuery(plan, ctx) {

      startedAt.set(ctx.planExecutionId, ctx.now());

    },

    async afterQuery(plan, result, ctx) {

      const started = startedAt.get(ctx.planExecutionId);

      startedAt.delete(ctx.planExecutionId);

      console.log(`${ctx.now() - (started ?? ctx.now())}ms · ${plan.sql}`);

    },

  };

}

Timing itself does not need any of this, because result.latencyMs already tells you how long the query took.

Projects created with npm create prisma@latest install Prisma ORM skills for your coding agent, so you can ask it for the middleware built in the steps above:

• "Write a Prisma ORM middleware that logs every query slower than 250ms, and register it before budgets."
• "Add a beforeCompile middleware that adds an orgId condition, read per request from an AsyncLocalStorage, to every SELECT that reads the User table in its FROM clause."
• "Write an interceptQuery middleware that returns fixture rows for the products table in tests."

• How middleware works: the order Prisma ORM runs your hooks in
• Built-in: budgets, Built-in: lints, and Built-in: cache: middleware Prisma ORM ships, as production examples of these hooks