# Authoring custom middleware (/docs/orm/middleware/authoring-custom-middleware) > For the complete Prisma documentation index, see [llms.txt](https://www.prisma.io/docs/llms.txt). A markdown version of any docs page is available by appending `.md` to its URL. Build, register, and run your own Prisma ORM middleware, step by step, starting with a query logger. Location: ORM > Middleware > Authoring custom middleware ## Introduction [#introduction] A middleware you write yourself is a plain object: a `name`, plus the hook functions you want Prisma ORM to call around your statements and transactions. 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, [keep state for one statement](#keep-state-for-one-statement) shows how the later hook gets what the earlier one worked out. [Coming from Prisma ORM 7](https://www.prisma.io/docs/orm/coming-from-prisma-orm-7#not-available-yet) 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. ## Prerequisites [#prerequisites] * 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. [Create a new app with PostgreSQL](https://www.prisma.io/docs/prisma-orm/quickstart/postgresql) sets both up in a few minutes: #### bun ```bash bun create prisma@latest --provider postgres --no-deploy cd my-app bun run db:init ``` #### pnpm ```bash pnpm create prisma@latest --provider postgres --no-deploy cd my-app pnpm run db:init ``` #### yarn ```bash yarn create prisma@latest --provider postgres --no-deploy cd my-app yarn db:init ``` #### npm ```bash npm create prisma@latest -- --provider postgres --no-deploy cd my-app npm run db:init ``` ## 1. Create the middleware [#1-create-the-middleware] Create a new file next to your database setup. The logger needs two hooks. `activityEnded` is the one Prisma ORM calls once at the end of every activity, which is every statement it sends and every transaction you open. Its second argument, `result`, is where the numbers come from: `result.latencyMs` is how long the statement took in milliseconds, and `result.rowCount` is how many rows came back. The SQL text is not on the activity, so `beforeEncode`, which receives the statement after its SQL is written, keeps `plan.sql` in `ctx.state(...)`, an object that belongs to this one statement. The `familyId: "sql"` field says the middleware works with SQL databases only, and [step 5](#5-know-which-family-to-declare) covers when to set it. ```ts title="src/prisma/query-logger.ts" import type { SqlMiddleware } from "@prisma/orm-postgres/family-runtime"; const statementSql = Symbol("query-logger.sql"); export function queryLogger(): SqlMiddleware { return { name: "query-logger", familyId: "sql", async beforeEncode(plan, ctx) { ctx.state(statementSql)["sql"] = plan.sql; }, async activityEnded(activity, result, ctx) { if (result.kind !== "statement") return; const count = result.returns === "rows" ? `${result.rowCount} rows` : `${result.stats?.affectedRows ?? 0} affected`; console.log( `[query-logger] ${count} in ${Math.round(result.latencyMs)}ms · ${ctx.state(statementSql)["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. `activityEnded` also runs for a transaction, which is why the hook returns early unless `result.kind` is `'statement'`. A statement either returns rows or reports how many rows it changed, and `result.returns` says which: `'rows'` with `result.rowCount`, or `'stats'` with `result.stats.affectedRows`. On the ORM API the calls that report a count are `createAndCount`, `updateAndCount`, and `deleteAndCount`; every other call returns rows, including `create`, `update`, `delete`, `updateAll`, and `deleteAll`. A write that failed has no `result.stats`, so the logger prints `0 affected` for it; check `result.outcome`, which is `'succeeded'`, `'failed'`, or `'stopped'`, when you need to tell the two apart. 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` runs it. `db.query(plan)` returns rows and `db.execute(plan)` reports how many rows the statement changed, and the same two hooks see both. ## 2. Register it [#2-register-it] 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: ```ts title="src/prisma/db.ts" import postgres from '@prisma/orm-postgres/runtime'; import { queryLogger } from './query-logger'; import { contractFromJson } from '@prisma/orm-postgres/static'; import type { Contract } from './contract.d'; import contractJson from './contract.json' with { type: 'json' }; export const db = postgres({ contract: contractFromJson(contractJson), url: process.env['DATABASE_URL']!, middleware: [queryLogger()], }); ``` The only line added here is the `middleware` option. Do not replace the `src/prisma/db.ts` that `npm create prisma@latest` generated with the file above, because the generated file has more in it, such as a `connectDatabase` export that the generated `seed.ts` imports. Instead, add the `middleware` option to each `postgres({ ... })` call in it. The generated project can start the app in two ways, and your middleware runs in only one of them. `npm run dev` starts the app with `DATABASE_URL` exported, so the `postgres({ ... })` calls in `db.ts` create the client and your middleware runs. `npm run dev:composer` runs the app with [Prisma Composer](https://www.prisma.io/docs/composer), which describes your services and databases in TypeScript, and the app then gets its client from Composer instead, so the `middleware` option in `db.ts` is never read. Use `npm run dev` while you follow this guide. If you set your project up by hand, [The data contract](https://www.prisma.io/docs/orm/contract-authoring/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. ## 3. Run a query and see the output [#3-run-a-query-and-see-the-output] To see the logger work, put a small script in `src/index.ts` that writes a row and then reads some back: ```ts title="src/index.ts" 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: ```bash npm run dev ``` The logger reports both queries, and prints the SQL that actually ran: ```text no-copy [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 report rows, 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. ## 4. Add an option [#4-add-an-option] Middleware you write for real work usually takes options, so give the logger a threshold in milliseconds and it reports only the statements that took longer than that: ```ts title="src/prisma/query-logger.ts" import type { SqlMiddleware } from "@prisma/orm-postgres/family-runtime"; const statementSql = Symbol("query-logger.sql"); export interface QueryLoggerOptions { /** Only log statements 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 beforeEncode(plan, ctx) { ctx.state(statementSql)["sql"] = plan.sql; }, async activityEnded(activity, result, ctx) { if (result.kind !== "statement" || result.latencyMs < thresholdMs) return; const count = result.returns === "rows" ? `${result.rowCount} rows` : `${result.stats?.affectedRows ?? 0} affected`; console.log( `[query-logger] ${count} in ${Math.round(result.latencyMs)}ms · ${ctx.state(statementSql)["sql"]}`, ); }, }; } ``` Pass the threshold where you register it, replacing the `middleware: [queryLogger()],` line of the `src/prisma/db.ts` from step 2: ```ts title="src/prisma/db.ts (excerpt)" 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 statement moves on, so a slow `activityEnded` delays the `await` in your own code. * If a middleware throws from `beforeEncode`, the ones listed after it do not run that hook, and the statement fails before it reaches the database. It still ends, so every middleware's `activityEnded` sees it with `result.outcome` `'failed'` and a `latencyMs` of `0`. A statement that `budgets` rejects, such as a `SELECT` with no `LIMIT`, reaches the logger that way wherever you list it. The logger has its SQL text only when it is listed before `budgets`, because only then has its own `beforeEncode` already run. A throw from a later middleware's `activityEnded`, as the `budgets` time check does, does not stop the logger's own `activityEnded` from running. * 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](https://www.prisma.io/docs/orm/middleware/built-in-budgets), add the import to the top of `src/prisma/db.ts` and replace its `middleware:` line: ```ts title="src/prisma/db.ts (excerpt)" import { budgets } from '@prisma/orm-postgres/family-runtime'; middleware: [queryLogger({ thresholdMs: 250 }), budgets()], ``` ## 5. Know which family to declare [#5-know-which-family-to-declare] 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 `activity` and `result` rather than `plan.sql` or `plan.ast`, which a SQL query has and a MongoDB query does not. `result` gives you `outcome`, `latencyMs`, `rowCount` or `stats.affectedRows`, and `source`, which is `'driver'` when the database answered the statement 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](https://www.prisma.io/docs/orm/middleware/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: ```ts 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`. ## The hooks [#the-hooks] Prisma ORM runs every statement and every transaction as an activity. Three lifecycle hooks run for every activity, and four interceptors run only for statements, while they run. Implement whichever hooks you need: | Hook | Runs for | Runs | Use it to | | ------------------------------------------ | ------------------------------ | --------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------- | | `activityStarted(activity, ctx)` | every activity | Before anything is compiled or sent | Record that the activity began; throw to fail it | | `beforeCompile(draft, ctx)` | statements | Before Prisma ORM turns the query into SQL text | Rewrite the query, for example add a tenant filter | | `beforeEncode(plan, ctx, params?)` | statements | After the SQL text has been built, before the values are encoded and the driver runs it | Validate, throw to block, or change parameter values | | `intercept.onQuery(plan, ctx)` | statements that return rows | Instead of running the statement | Return `{ rows }` to answer the query yourself | | `intercept.onExecute(plan, ctx)` | statements that return a count | Instead of running the statement | Return `{ stats }` to answer the statement yourself | | `onRow(row, plan, ctx)` | statements that return rows | Once per row the database returns | Count or sample rows, or throw to stop the query | | `activityEnded(activity, result, ctx)` | every activity | After the activity finishes, whether it succeeded or failed | Log `result.outcome`, `result.latencyMs`, `result.rowCount` or `result.stats`, `result.source` | | `activityFinalized(activity, result, ctx)` | every activity | Once its effects are final: after its transaction commits or rolls back, or right after it ends outside a transaction | Act on `result.outcome`, which is `'committed'`, `'rolled-back'`, or `'unknown'`, for example publish an event only for a committed write | Every hook receives `ctx`, written last above, which a hook can leave out of its parameter list when it does not use it. `activity.kind` is `'statement'` or `'transaction'`, so a lifecycle hook that only cares about statements checks it first. For a statement, `activity.returns` is `'rows'` or `'stats'`, and `activity.plan` is the plan the caller sent. Throwing from `onRow` fails the call with your error, and the rows still to come are not passed on to your code. [How middleware works](https://www.prisma.io/docs/orm/middleware/how-middleware-works#the-two-lifecycles) walks through the order with an animation. ### Rewrite queries with beforeCompile [#rewrite-queries-with-beforecompile] `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: ```ts title="src/prisma/scope-user-selects.ts" 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: ```ts title="src/prisma/db.ts" 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 { contractFromJson } from '@prisma/orm-postgres/static'; import type { Contract } from './contract.d'; import contractJson from './contract.json' with { type: 'json' }; export const currentName = new AsyncLocalStorage(); export const db = postgres({ contract: contractFromJson(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: ```ts title="src/routes/users.ts" 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`. > [!WARNING] > Do not rely on this middleware alone to keep one tenant's rows away from another. If you swap `name` for a tenant column, a query that reads `User` in its own `FROM` clause returns only that tenant's rows, but `User` rows loaded with `.include(...)` or joined onto another table are not filtered, so they can belong to any tenant. The `codecId` depends on the field's type in your contract: | Type in your contract | `codecId` | | --------------------- | --------------------------- | | `String` | `pg/text@1` | | `Int` | `pg/int4@1` | | `BigInt` | `pg/int8@1` | | `Float` | `pg/float8@1` | | `Boolean` | `pg/bool@1` | | `DateTime` | `pg/timestamptz-temporal@1` | | `Uuid` | `pg/uuid@1` | 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()`](https://www.prisma.io/docs/orm/reference/sql-query-builder#fnsraw-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. ### Answer queries with intercept.onQuery [#answer-queries-with-interceptonquery] `intercept.onQuery` 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. On PostgreSQL every value that is not SQL `NULL` is the text PostgreSQL prints for it, and a `NULL` is `null`, so an integer arrives as a string such as `'1'`, and so do 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 `intercept.onQuery` has to return: ```ts title="src/prisma/query-logger.ts (excerpt)" 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 and an `email` key, each 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()]`: ```ts title="src/prisma/user-fixture.ts" import type { SqlMiddleware } from '@prisma/orm-postgres/family-runtime'; export function userFixture(): SqlMiddleware { return { name: 'user-fixture', familyId: 'sql', intercept: { async onQuery(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 `intercept.onQuery`, the first one in the array that returns rows answers the query and the rest are not called at all. `activityEnded` still runs either way, with `result.source` set to `'middleware'`, so your logging can tell which statements were answered without a trip to the database. For a call that returns a count, `intercept.onExecute` does the same job and returns `{ stats }`, which only ever holds `affectedRows`, as in `{ stats: { affectedRows: 0 } }`. The [cache middleware](https://www.prisma.io/docs/orm/middleware/built-in-cache) is the implementation to copy from. ## The context object [#the-context-object] Every hook receives a context object called `ctx`. All the hooks of one activity receive the same object, holding the following: | Field | What it gives you | | ----------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `ctx.state(key)` | An object that belongs to this one activity, created empty the first time you ask for it under `key`. See [Keep state for one statement](#keep-state-for-one-statement) | | `ctx.parent` | The context of the transaction a statement runs in, or `undefined` outside one | | `ctx.inTransaction()` | `true` when the activity runs inside a transaction | | `ctx.now()` | The current time in milliseconds, the same value `Date.now()` gives | | `ctx.scope` | Where the statement is running: `'runtime'`, `'connection'`, or `'transaction'` | | `ctx.mode` | The [`mode` option](https://www.prisma.io/docs/orm/reference/transactions-and-runtime#mode) of `postgres(...)`: `'strict'`, the default, or `'permissive'`. The budgets middleware reads it to decide whether a statement over budget throws or is only logged | | `ctx.contentHash(plan)` | A promise of a string that is the same for the same statement and parameters, so `await` it | | `ctx.contract` | Your contract, for when a hook needs to know what your database looks like | | `ctx.signal` | The `AbortSignal` the caller passed, if any | | `ctx.log` | The [`log` option](https://www.prisma.io/docs/orm/reference/transactions-and-runtime#postgresoptions) of `postgres(...)`, such as `console`, with `info`, `warn`, and `error` methods. Without that option, what you pass to it is not printed | `ctx.scope` is `'runtime'` for a statement you sent on `db` directly, `'transaction'` inside `db.transaction(...)`, and `'connection'` for a statement you sent on a connection you took out of the pool yourself with `db.runtime().connection()`. The cache reads it to leave statements inside a transaction alone. `activity.id` is a fresh id for each activity, the same in every hook of that activity, so it is the value to put in a log line when you want to match lines from two hooks. `beforeEncode` takes 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: ```ts title="src/prisma/redact-params.ts (excerpt)" async beforeEncode(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. ### Keep state for one statement [#keep-state-for-one-statement] A middleware that has to remember something between two hooks of one statement keeps it in `ctx.state(key)`. This is what replaces wrapping a query in `next()`. Each activity has its own state object, which Prisma ORM drops once the activity is finalized, so nothing is left behind when a statement fails. Use a `Symbol` of your own as the key, because middleware that pass the same key share the object. The values are typed `unknown`, so check them when you read them back: ```ts title="src/prisma/sql-on-failure.ts" import type { SqlMiddleware } from '@prisma/orm-postgres/family-runtime'; const sqlKey = Symbol('sql-on-failure'); export function sqlOnFailure(): SqlMiddleware { return { name: 'sql-on-failure', familyId: 'sql', async beforeEncode(plan, ctx) { ctx.state(sqlKey)['sql'] = plan.sql; }, async activityEnded(activity, result, ctx) { if (result.kind !== 'statement' || result.outcome !== 'failed') return; const sql = ctx.state(sqlKey)['sql']; console.error(`failed after ${Math.round(result.latencyMs)}ms · ${typeof sql === 'string' ? sql : '(not compiled)'}`); }, }; } ``` A statement that failed before `beforeEncode` ran, for example because another middleware's `beforeCompile` threw, has nothing in its state, which is what the `typeof` check covers. ## Common gotchas [#common-gotchas] > [!WARNING] > * If you throw from `activityEnded` after a statement has succeeded, the call fails even though the database has already done the work, so keep that for cases where failing loudly is the point, the way the [budgets](https://www.prisma.io/docs/orm/middleware/built-in-budgets) latency check does. > * `activityEnded` also runs when the database driver fails, when a statement fails before it reaches the database, and when your code stops reading rows part way through, with `result.outcome` set to `'failed'` or `'stopped'`. A middleware that counts statements or measures latency checks `result.outcome`. A failed write has no `result.stats`. > * If your hook throws while it is handling a failure like that, Prisma ORM passes your error to the client's log and your caller still sees the failure the driver reported. > * `activityEnded` and `activityFinalized` run for transactions too. Check `activity.kind` or `result.kind` when you only care about statements. > * Inside a transaction, `activityFinalized` runs while the transaction still holds its connection. Do not await a query through the same client from it: when the pool has no free connection, that query waits forever. [How middleware works](https://www.prisma.io/docs/orm/middleware/how-middleware-works#activityfinalized-act-once-the-effects-are-final) has the details. ## Prompt your coding agent [#prompt-your-coding-agent] Projects created with `npm create prisma@latest` install [Prisma ORM skills](https://www.prisma.io/docs/ai/tools/skills#available-skills-for-prisma-8) 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 a middleware whose intercept.onQuery returns fixture rows for the products table in tests." ## Next steps [#next-steps] * [How middleware works](https://www.prisma.io/docs/orm/middleware/how-middleware-works): the order Prisma ORM runs your hooks in * [Built-in: budgets](https://www.prisma.io/docs/orm/middleware/built-in-budgets), [Built-in: lints](https://www.prisma.io/docs/orm/middleware/built-in-lints), and [Built-in: cache](https://www.prisma.io/docs/orm/middleware/built-in-cache): middleware Prisma ORM ships, as production examples of these hooks ## Related pages - [`Built-in: budgets`](https://www.prisma.io/docs/orm/middleware/built-in-budgets): The budgets middleware stops queries that read too many rows or take too long. - [`Built-in: cache`](https://www.prisma.io/docs/orm/middleware/built-in-cache): The cache middleware serves repeated reads from an in-memory store, opted in per query with a cache annotation. - [`Built-in: lints`](https://www.prisma.io/docs/orm/middleware/built-in-lints): The lints middleware inspects each query's structure before it runs and blocks or warns on risky shapes. - [`How middleware works`](https://www.prisma.io/docs/orm/middleware/how-middleware-works): Middleware runs your code before and after every query, so one policy can cover your whole app.