Middleware

When declarative where clauses, validate rules, and defaults/overwrite are not enough, middleware lets you wrap the query execution with custom TypeScript. Middleware is defined inside an operation block. Every parameter is destructured — you work directly with user, db, where, input, and pass overrides to next() to control what gets executed.

permissions: {
  manage_tasks: {
    table: 'main.tasks',
    roles: ['member'],
    select: {
      columns: '*',
      where: { organization_id: { $eq: '$user.current_org_id' } },
      middleware: async ({ user, db, where }, next) => {
        // Before: tighten the where clause based on a dynamic lookup
        const activeTeams = await db
          .select({ id: teams.id })
          .from(teams)
          .innerJoin(teamMembers, eq(teamMembers.teamId, teams.id))
          .where(and(
            eq(teamMembers.userId, user.id),
            eq(teamMembers.status, 'active'),
          ))

        const rows = await next({
          where: { ...where, team_id: { $in: activeTeams.map(t => t.id) } },
        })

        // After: attach computed data before returning
        return rows.map(row => ({
          ...row,
          is_mine: row.assigned_to === user.id,
        }))
      },
    },
  },
}

How It Works

Middleware wraps the query with two hooks — before and after next(). You decide what runs at each stage:

select: {
  columns: '*',
  middleware: async ({ user, db, where, input, columns }, next) => {
    // BEFORE — runs before the query
    // Validate access, look up data, modify the request

    const rows = await next({
      where: { ...where, status: { $ne: 'archived' } },
    })

    // AFTER — runs after the query
    // Transform results, run side effects, enrich rows

    return rows
  },
}

Stage	What happens
Before `next()`	Validate access, look up data, compute overrides, modify the request
`next(overrides)`	Executes the query — pass overrides to change what runs
After `next()`	Transform results, run side effects, enrich with computed data
Skip `next()`	Throw to deny the request entirely

  6. Permission Check — where, validate, default, overwrite applied
    |
    v
  6a. middleware (before next) <-- modify request here
    |
    v
  7. Query Execution <-- next() triggers this
    |
    v
  7a. middleware (after next) <-- transform results here
    |
    v
  8. Audit Log

Parameters

Every parameter is destructured — no wrapper object:

middleware: async ({ user, db, table, operation, columns, query, input, where }, next) => {

Parameter	Type	Description
`user`	`UserSession`	Resolved session (same as `$user.*` in where clauses)
`db`	`DrizzleInstance`	Drizzle query builder — run any query
`table`	`string`	Target table, e.g. `'main.orders'`
`operation`	`'select' \| 'insert' \| 'update' \| 'delete'`	Detected operation
`columns`	`string[]`	Columns the client is requesting or writing
`query`	`{ sql: string, params: any[] }`	The client's raw parameterized SQL
`input`	`Record<string, any> \| undefined`	Request body values (writes only)
`where`	`Record<string, any> \| undefined`	Resolved where clause (after merging and `$user.*` substitution)

Modifying the Request

Pass overrides to next() to change what gets executed. This is how you modify the incoming request before it hits the database.

Evaluate and Modify Where Clauses

The where parameter contains the fully resolved where clause — after declarative where, $user.* substitution, and sql fragments are merged. You can read it, extend it, or replace it entirely:

// Extend: add conditions to the existing where
middleware: async ({ user, db, where }, next) => {
  const teamIds = await getActiveTeamIds(db, user.id)

  return next({
    where: {
      ...where,
      team_id: { $in: teamIds },
      status: { $ne: 'archived' },
    },
  })
},

// Replace: swap the where clause entirely based on role
middleware: async ({ user, where }, next) => {
  if (user.roles.includes('admin')) {
    // Admins see everything in their org
    return next({
      where: { organization_id: { $eq: user.current_org_id } },
    })
  }
  // Everyone else keeps the original restrictive where
  return next()
},

// Conditional: evaluate the incoming where and decide
middleware: async ({ user, db, where }, next) => {
  // If the client is filtering by a specific project, verify access
  if (where?.project_id) {
    const hasAccess = await db.query.projectMembers.findFirst({
      where: and(
        eq(projectMembers.projectId, where.project_id.$eq ?? where.project_id),
        eq(projectMembers.userId, user.id),
      ),
    })
    if (!hasAccess) {
      throw new PermissionError('Not a member of this project')
    }
  }
  return next()
},

Transform Input

Modify the request body before it's written to the database. Useful for normalizing values, computing derived fields, or injecting server-side data:

// Normalize and enrich input on insert
middleware: async ({ user, db, input }, next) => {
  const product = await db.query.products.findFirst({
    where: eq(products.id, input.product_id),
  })
  if (!product) throw new PermissionError('Product not found')

  return next({
    input: {
      ...input,
      email: input.email?.toLowerCase().trim(),
      unit_price: product.price,
      total: product.price * input.quantity,
    },
  })
},

// Conditionally modify input on update
middleware: async ({ user, input }, next) => {
  if (input?.status === 'published') {
    return next({
      input: {
        ...input,
        published_at: new Date().toISOString(),
        published_by: user.id,
      },
    })
  }
  return next()
},

Restrict Columns

Narrow the column list based on runtime conditions — subscriptions, roles, or any dynamic lookup:

// Free users see basic columns, paid see everything
middleware: async ({ user, db }, next) => {
  const sub = await db.query.subscriptions.findFirst({
    where: eq(subscriptions.organizationId, user.current_org_id),
    columns: { plan: true },
  })

  if (sub?.plan === 'free') {
    return next({ columns: ['id', 'name', 'status', 'created_at'] })
  }
  return next()
},

Override Summary

Override	Effect
`where`	Replaces the where clause used in the WHERE
`input`	Replaces the input data for writes
`columns`	Replaces the column list
`db`	Replaces the database instance (use for transactions)

If you call next() with no arguments, the query executes unchanged.

Transforming Results

Code after next() runs once the query completes. Use it to transform, enrich, or redact rows before they reach the client:

// Redact PII for non-clinical staff
middleware: async ({ user }, next) => {
  const rows = await next()

  if (!user.roles.includes('clinical')) {
    return rows.map(row => ({
      ...row,
      email: row.email ? `${row.email[0]}***@***` : null,
      phone: row.phone ? '***-****' : null,
      ssn: row.ssn ? '***-**-' + row.ssn.slice(-4) : null,
    }))
  }
  return rows
},

// Enrich rows with computed data from another table
middleware: async ({ db }, next) => {
  const rows = await next()
  if (rows.length === 0) return rows

  const counts = await db
    .select({ orderId: orderItems.orderId, itemCount: count() })
    .from(orderItems)
    .where(inArray(orderItems.orderId, rows.map(r => r.id)))
    .groupBy(orderItems.orderId)

  const countMap = new Map(counts.map(c => [c.orderId, c.itemCount]))
  return rows.map(row => ({
    ...row,
    item_count: countMap.get(row.id) ?? 0,
  }))
},

Transactions

Wrap next() in db.transaction() and pass the transaction as db — if anything throws after the query, everything rolls back:

middleware: async ({ user, db, input }, next) => {
  return db.transaction(async (tx) => {
    // The main query runs inside the transaction
    const rows = await next({ db: tx })

    // This runs after the query — if it throws, both the
    // main query and this update are rolled back
    await tx.insert(auditLogs).values({
      userId: user.id,
      action: 'order_created',
      orderId: rows[0]?.id,
      timestamp: new Date(),
    })

    return rows
  })
},

This is especially useful for writes that need atomic side effects — stock decrements, balance updates, counter increments, or any operation where partial completion is not acceptable.

Examples

E-Commerce: Order Creation with Stock Validation

An online store lets customers place orders through a dashboard built with Drizzle on the client. The client sends an INSERT with just the product and quantity — but the server needs to verify stock availability, compute the total price from the catalog, decrement inventory, and queue a warehouse fulfillment job. All of this must happen atomically: if the warehouse job fails to insert, the order and stock change must roll back too.

create_order: {
  table: 'main.orders',
  roles: ['customer'],
  insert: {
    columns: ['product_id', 'quantity', 'shipping_address'],
    overwrite: {
      customer_id: '$user.id',
      organization_id: '$user.current_org_id',
      status: 'pending',
    },
    validate: {
      quantity: { $gte: 1, $lte: 100 },
    },
    middleware: async ({ user, db, input }, next) => {
      // Before: validate product and check stock
      const product = await db.query.products.findFirst({
        where: and(
          eq(products.id, input.product_id),
          eq(products.organizationId, user.current_org_id),
        ),
      })
      if (!product) {
        throw new PermissionError('Product not found')
      }
      if (product.stock < input.quantity) {
        throw new PermissionError(
          `Only ${product.stock} units available`,
        )
      }

      // Execute atomically: insert order + decrement stock + queue job
      return db.transaction(async (tx) => {
        const rows = await next({
          db: tx,
          // Before: enrich input with server-computed values
          input: {
            ...input,
            unit_price: product.price,
            total: product.price * input.quantity,
          },
        })

        // After: side effects inside the transaction
        await tx
          .update(products)
          .set({ stock: sql`stock - ${input.quantity}` })
          .where(eq(products.id, input.product_id))

        await tx.insert(warehouseJobs).values({
          orderId: rows[0].id,
          productId: input.product_id,
          quantity: input.quantity,
          status: 'queued',
        })

        return rows
      })
    },
  },
}

Project Management: Dynamic Where from Team Membership

A project management app where users belong to multiple teams. Tasks should only be visible to members of the team that owns them. The team membership is complex — users can be active or inactive, and membership can expire — so a simple declarative where with $user.team_ids isn't enough. The middleware evaluates the user's current memberships and injects a dynamic where clause.

view_tasks: {
  table: 'main.tasks',
  roles: ['member'],
  select: {
    columns: '*',
    middleware: async ({ user, db, where }, next) => {
      // Before: look up active teams and inject into where
      const activeTeams = await db
        .select({ id: teams.id })
        .from(teams)
        .innerJoin(teamMembers, eq(teamMembers.teamId, teams.id))
        .where(
          and(
            eq(teamMembers.userId, user.id),
            eq(teamMembers.status, 'active'),
            gt(teamMembers.expiresAt, new Date()),
          ),
        )

      if (activeTeams.length === 0) {
        throw new PermissionError('No active team memberships')
      }

      return next({
        where: {
          ...where,
          team_id: { $in: activeTeams.map(t => t.id) },
        },
      })
    },
  },
}

Multi-Tenant: Cascading Delete with Usage Recalculation

A multi-tenant platform tracks per-organization resource usage in a denormalized organizations.project_count column for fast billing lookups. When a user deletes a project, the count must be recalculated. Both the delete and the count update must succeed or fail together.

delete_project: {
  table: 'main.projects',
  roles: ['admin'],
  delete: {
    where: {
      organization_id: { $eq: '$user.current_org_id' },
    },
    middleware: async ({ user, db }, next) => {
      return db.transaction(async (tx) => {
        const rows = await next({ db: tx })

        // After: recalculate count — if this fails, the delete rolls back
        const [{ total }] = await tx
          .select({ total: count() })
          .from(projects)
          .where(eq(projects.organizationId, user.current_org_id))

        await tx
          .update(organizations)
          .set({ projectCount: total })
          .where(eq(organizations.id, user.current_org_id))

        return rows
      })
    },
  },
}

Approval Workflow: Role-Based Status Transitions

A procurement system where purchase orders go through an approval workflow: draft -> submitted -> approved -> fulfilled. The middleware evaluates the incoming input to enforce who can set which status, based on the current state and the user's role.

manage_purchase_orders: {
  table: 'main.purchase_orders',
  roles: ['member', 'manager'],
  select: {
    columns: '*',
    where: { organization_id: { $in: '$user.org_ids' } },
  },
  update: {
    columns: '*',
    where: { organization_id: { $in: '$user.org_ids' } },
    validate: {
      status: { $in: ['draft', 'submitted', 'approved', 'fulfilled'] },
    },
    overwrite: { updated_by: '$user.id' },
    middleware: async ({ user, db, input, where }, next) => {
      // Before: evaluate the incoming request and enforce transitions
      if (!input?.status) return next()

      const [order] = await db
        .select({ status: purchaseOrders.status, createdBy: purchaseOrders.createdBy })
        .from(purchaseOrders)
        .where(eq(purchaseOrders.id, input.id))
        .limit(1)

      if (!order) throw new PermissionError('Order not found')

      // draft -> submitted: only the creator
      if (input.status === 'submitted' && order.status === 'draft') {
        if (order.createdBy !== user.id) {
          throw new PermissionError('Only the creator can submit this order')
        }
      }

      // submitted -> approved: only managers
      if (input.status === 'approved' && order.status === 'submitted') {
        if (!user.roles.includes('manager')) {
          throw new PermissionError('Only managers can approve orders')
        }
      }

      // No skipping steps
      const allowedTransitions = {
        draft: ['submitted'],
        submitted: ['approved', 'draft'],
        approved: ['fulfilled'],
      }
      if (!allowedTransitions[order.status]?.includes(input.status)) {
        throw new PermissionError(
          `Cannot transition from ${order.status} to ${input.status}`,
        )
      }

      return next()
    },
  },
}

Combining with Declarative Rules

Middleware works alongside where clauses, validate rules, defaults, overwrite, and raw SQL. The engine applies them in order — declarative rules run first, then middleware wraps the execution:

1. where       -> WHERE clause injected
2. validate    -> Input values validated
3. default     -> Missing values filled
4. overwrite   -> Server values forced
5. sql         -> SQL fragment appended to WHERE
6. middleware   -> Your TypeScript wraps the execution (before + after)
7. Query executes (inside next())

Use declarative rules for the common cases and middleware for the edge cases:

manage_orders: {
  table: 'main.orders',
  roles: ['member', 'manager'],
  select: {
    columns: '*',
    where: { organization_id: { $in: '$user.org_ids' } },
  },
  insert: {
    columns: ['amount', 'status', 'customer_id'],
    validate: { amount: { $gte: 0 } },
    overwrite: { created_by: '$user.id' },
  },
  update: {
    columns: ['amount', 'status'],
    where: { organization_id: { $in: '$user.org_ids' } },
    validate: { amount: { $gte: 0 } },
    overwrite: { updated_by: '$user.id' },
    middleware: async ({ user, db, input }, next) => {
      // Before: enforce business rules on the incoming request
      if (input?.status === 'approved') {
        const membership = await db.query.orgMembers.findFirst({
          where: and(
            eq(orgMembers.userId, user.id),
            eq(orgMembers.orgId, user.current_org_id),
          ),
          columns: { role: true },
        })
        if (membership?.role !== 'manager') {
          throw new PermissionError('Only managers can approve orders')
        }
      }

      const rows = await next()

      // After: strip sensitive fields for non-managers
      if (!user.roles.includes('manager')) {
        return rows.map(({ internal_notes, ...row }) => row)
      }
      return rows
    },
  },
}

Error Handling

Throw PermissionError to reject with 403 Forbidden:

middleware: async (params, next) => {
  throw new PermissionError('Reason shown in error response')
},

Any other thrown error returns 500 Internal Server Error and is logged but not exposed to the client. When using transactions, any throw inside db.transaction() automatically rolls back all queries within that transaction.

When to Use

Use Case	Recommendation
Static row filtering	Use where clauses
Value validation	Use validate
Fill missing values	Use default
Force server values	Use overwrite
Date math, computed WHERE	Use raw SQL
Dynamic where from lookups	Use `middleware` with `next({ where })`
Input normalization / enrichment	Use `middleware` with `next({ input })`
Runtime column restriction	Use `middleware` with `next({ columns })`
Result transformation / redaction	Use `middleware` (after `next()`)
Business rule enforcement	Use `middleware` (before `next()`)
Atomic writes with side effects	Use `middleware` with `db.transaction()`

Prefer declarative rules when possible. Use middleware only when the built-in mechanisms cannot express your logic.

On this page