Authoring custom middleware

Introduction

In this guide, you build your own middleware from scratch: a query logger that prints every query with its row count and latency. You create one file, register it in one place, run a query, and see the output.

A custom middleware is a plain object with a name and the hooks you implement. There is no base class and no registration API beyond the middleware array you already know from How middleware works.

This entire guide was run end to end on a fresh create-prisma project against a live Prisma Postgres database; the outputs shown are from that run.

Prerequisites

• A Prisma ORM project with a working src/prisma/db.ts. The PostgreSQL quickstart sets one up in a few minutes:

bun create prisma@latest --provider postgres
cd my-app
bun run db:init

1. Create the middleware

Create a new file next to your database setup. The middleware implements one hook, afterQuery, which runs once after every row-returning query with the outcome:

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}`,
      );
    },
  };
}

afterQuery is the row-query half of the lifecycle. Statements run through runtime.execute(plan), which report affected rows instead of returning them, go down the other path and end in afterExecute. If you want the logger to cover those too, add a second hook that reads result.stats.affectedRows, which is where the write path puts its count:

    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}`,
      );
    },

Typing the object as SqlMiddleware gives you inferred parameter types on every hook, so you rarely import the plan or result types yourself. familyId: "sql" says this middleware is for SQL databases; step 5 explains when to set it.

2. Register it

Add the middleware to the middleware array in your client setup. This is the complete file after the change:

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()],
});

That is the whole wiring. Every query on this client now passes through the logger, whether it comes from the ORM API or the SQL query builder.

3. Run a query and see the output

Put a small script in src/index.ts that writes and reads:

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();

Run it:

npm run dev

The logger reports both queries, with the SQL the runtime actually executed:

[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

If you see the query result but no [query-logger] lines, the query ran on a client that does not have the middleware registered; check that your script imports db from the file you edited in step 2.

4. Add an option

Real middleware usually takes options. Add a threshold so the logger only reports slow queries:

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}`,
      );
    },
  };
}

Register it with a threshold:

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

Run the script again and the logger goes quiet, because both queries finish in under 250ms. Drop the threshold back to see them again.

Hooks run in registration order, and a middleware that throws stops the ones after it. Register your logger before middleware that can throw, such as budgets, so the queries you most want to see still get logged.

5. Know which family to declare

Prisma ORM groups databases into families: SQL (PostgreSQL) and document (MongoDB). Set familyId: 'sql' when your middleware touches anything SQL-shaped, like plan.sql in the logger above. The runtime then rejects it on MongoDB at startup with RUNTIME.MIDDLEWARE_FAMILY_MISMATCH, instead of failing at query time.

Leave familyId out only when your middleware uses hooks that work on both families: interceptQuery, onRow, and afterQuery, reading only the result fields both databases have. Such a middleware runs on PostgreSQL and MongoDB alike; the cache works this way.

You have a working, configurable middleware. The rest of this page describes the other hooks and options you reach for as your middleware grows.

The hooks

afterQuery is one hook on one of two lifecycles. Row queries and non-returning writes take different paths, and beforeCompile is the only hook they share. Implement any subset:

There is no generic intercept and no fallback between the two lifecycles. A middleware that wants to cover both registers the same function under both names; that is how lints and budgets apply to reads and writes alike.

How middleware works walks the order with an animation. Two hooks deserve a closer look here, because they change the query rather than observe it.

Rewrite queries with beforeCompile

beforeCompile sees the typed AST before it becomes SQL. Return a new draft to rewrite the query. This middleware adds a predicate to every SELECT on 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(predicate: BinaryExpr): 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 where = draft.ast.where ? AndExpr.of([draft.ast.where, predicate]) : predicate;
      return { ...draft, ast: draft.ast.withWhere(where) };
    },
  };
}

Return undefined to pass the query through unchanged. Because each middleware's returned draft feeds the next, rewrites compose in registration order.

Answer queries with interceptQuery

Return { rows } from interceptQuery and the driver never runs. Caches, test fixtures, rate limiters, and circuit breakers all fit this hook. Return raw row objects; the runtime decodes them normally, and afterQuery reports source: 'middleware' so the short-circuit stays visible to your logging. interceptExecute is the write-path counterpart and returns { stats }. The cache middleware is the reference implementation.

The context object

Every hook receives a context (ctx) as its last argument:

The postgres(...) client does not expose a way to attach a log sink yet, so events sent to ctx.log are not printed anywhere by default. For output you can see today, log directly to your own logger, as the query logger above does with console.log.

Common gotchas

Prompt your coding agent

Projects scaffolded with create-prisma@latest install Prisma ORM skills for your coding agent. Prompts that map to this guide:

• "Write a Prisma ORM middleware that logs every query slower than 250ms, and register it before budgets."
• "Add a beforeCompile middleware that scopes every SELECT on the user table to the current tenant."
• "Write an interceptQuery middleware that returns fixture rows for the products table in tests."

Next steps

• How middleware works: the lifecycle your hooks plug into
• Built-in: budgets, Built-in: lints, and Built-in: cache as production examples of the hook surface