Middleware

Run custom TypeScript that wraps query execution, with full access to the database, user session, and query data.

When declarative filters, checks, and presets are not enough, middleware lets you wrap the query execution with custom TypeScript. Every parameter is destructured — you work directly with user, db, filter, input, and pass overrides to next() to modify what gets executed.

permissions: {
  create_order: {
    name: 'Create order',
    table: 'main.orders',
    operations: { insert: true },
    columns: ['product_id', 'quantity'],
    middleware: async ({ user, db, operation, input }, next) => {
      // Look up the product to validate stock
      const product = await db.query.products.findFirst({
        where: eq(products.id, input.product_id),
      })
      if (!product || product.stock < input.quantity) {
        throw new PermissionError('Insufficient stock')
      }

      // Execute the insert inside a transaction so we can
      // decrement stock atomically — if anything fails, both
      // the insert and the stock update roll back
      return db.transaction(async (tx) => {
        const rows = await next({ db: tx })

        await tx
          .update(products)
          .set({ stock: product.stock - input.quantity })
          .where(eq(products.id, input.product_id))

        return rows
      })
    },
  },
}

How It Works

The middleware function receives destructured query context and a next() function. Call next() to execute the query — pass overrides to modify what gets executed:

middleware: async ({ user, db, table, operation, columns, query, input, filter }, next) => {
  // 1. Before query — validate, compute new values
  const rows = await next({ filter: { ...filter, status: { $ne: 'archived' } } })
  // 2. After query — transform rows, run side effects
  return rows
}

Before next() — validate access, look up data, compute overrides
next(overrides) — executes the query with any overrides applied
After next() — transform results, run side effects
Skip next() — throw to deny the request entirely

  6. Permission Check — filters, checks, presets applied
    │
    ▼
  6a. middleware (before next) ← your code runs here
    │
    ▼
  7. DuckDB Execution ← next() triggers this
    │
    ▼
  7a. middleware (after next) ← your code runs here
    │
    ▼
  8. Audit Log

Parameters

Every parameter is destructured — no wrapper object:

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

Parameter	Type	Description
`user`	`UserSession`	Resolved session (same as `$user.*` in filters)
`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)
`filter`	`Record<string, any> \| undefined`	Resolved filter (after merging and `$user.*` substitution)

Passing Overrides to next()

Pass an object to next() to override any parameter before execution:

middleware: async ({ filter, columns, input }, next) => {
  return next({
    // Extend the filter
    filter: { ...filter, status: { $ne: 'archived' } },
    // Restrict columns
    columns: ['id', 'name', 'status'],
    // Normalize input
    input: { ...input, email: input?.email?.toLowerCase().trim() },
  })
},

Override	Effect
`filter`	Replaces the filter used in the WHERE clause
`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.

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: {
  name: 'Create order',
  table: 'main.orders',
  operations: { insert: true },
  columns: ['product_id', 'quantity', 'shipping_address'],
  preset: {
    customer_id: '$user.id',
    organization_id: '$user.current_org_id',
    status: 'pending',
  },
  check: {
    quantity: { $gte: 1, $lte: 100 },
  },
  middleware: async ({ user, db, input }, next) => {
    // Validate: product exists in this org and has enough 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,
        input: {
          ...input,
          unit_price: product.price,
          total: product.price * input.quantity,
        },
      })

      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: Team-Scoped Task Visibility

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 filter with $user.team_ids isn't enough. The middleware looks up the user's currently active team memberships at query time and injects a dynamic filter.

view_tasks: {
  name: 'View team tasks',
  table: 'main.tasks',
  operations: { select: true },
  columns: '*',
  middleware: async ({ user, db, filter }, next) => {
    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({
      filter: {
        ...filter,
        team_id: { $in: activeTeams.map(t => t.id) },
      },
    })
  },
}

SaaS: Feature Gating by Subscription Plan

A SaaS analytics platform where free-tier users can see basic metrics (name, status, created date) but paid users get access to all columns including revenue data, conversion rates, and custom fields. The plan is stored in a separate subscriptions table, so you can't express this with a static columns list — it depends on the user's current plan at query time.

view_campaigns: {
  name: 'View campaigns',
  table: 'main.campaigns',
  operations: { select: true },
  filter: { organization_id: { $eq: '$user.current_org_id' } },
  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()
  },
}

Healthcare: PII Redaction for Non-Clinical Staff

A hospital management system where administrative staff can look up patient records to schedule appointments, but only clinical staff (doctors, nurses) should see personally identifiable information. Admin staff sees masked emails, phone numbers, and social security numbers. The redaction happens after the query returns, so the database query itself is unchanged — only the response is sanitized.

view_patients: {
  name: 'View patients',
  table: 'main.patients',
  operations: { select: true },
  columns: '*',
  filter: { facility_id: { $eq: '$user.facility_id' } },
  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,
        date_of_birth: null,
      }))
    }
    return rows
  },
}

Marketplace: Order List with Item Counts

A marketplace dashboard shows sellers a list of their orders. Each order row should include the number of line items, but item_count isn't stored on the orders table — it lives in order_items. Rather than forcing the client to make a second query and join the data, the middleware fetches item counts in a single batch query after the main query returns and attaches them to each row.

view_orders: {
  name: 'View seller orders',
  table: 'main.orders',
  operations: { select: true },
  columns: '*',
  filter: { seller_id: { $eq: '$user.id' } },
  middleware: async ({ db }, next) => {
    const rows = await next()
    if (rows.length === 0) return rows

    const orderIds = rows.map(r => r.id)
    const counts = await db
      .select({
        orderId: orderItems.orderId,
        itemCount: count(),
      })
      .from(orderItems)
      .where(inArray(orderItems.orderId, orderIds))
      .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,
    }))
  },
}

Multi-Tenant: Cascading Delete with Usage Recalculation

A multi-tenant platform tracks per-organization resource usage (number of projects, storage used, etc.) 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 — otherwise the billing data drifts out of sync.

delete_project: {
  name: 'Delete project',
  table: 'main.projects',
  operations: { delete: true },
  filter: {
    organization_id: { $eq: '$user.current_org_id' },
  },
  middleware: async ({ user, db }, next) => {
    return db.transaction(async (tx) => {
      const rows = await next({ db: tx })

      // Recalculate project 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. Only managers can move an order to approved, and only the original creator can move it from draft to submitted. The declarative check can validate that status is one of the allowed values, but it can't express "who is allowed to set which status" — that requires looking up the user's role and the order's current state.

manage_purchase_orders: {
  name: 'Manage purchase orders',
  table: 'main.purchase_orders',
  operations: { select: true, update: true },
  filter: { organization_id: { $in: '$user.org_ids' } },
  check: {
    status: { $in: ['draft', 'submitted', 'approved', 'fulfilled'] },
  },
  preset: { updated_by: '$user.id' },
  middleware: async ({ user, db, operation, input, filter }, next) => {
    if (operation !== 'update' || !input?.status) return next()

    // Look up the current order to check the transition
    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 (e.g., draft → approved)
    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 filters, checks, presets, and custom SQL. The engine applies them in order:

1. filter      → WHERE clause injected
2. check       → Input values validated
3. preset      → Server values injected
4. customSql   → SQL fragment appended to WHERE
5. middleware   → Your TypeScript wraps the execution
6. Query executes (inside next())

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

manage_orders: {
  name: 'Manage orders',
  table: 'main.orders',
  operations: { select: true, insert: true, update: true },
  filter: { organization_id: { $in: '$user.org_ids' } },
  check: { amount: { $gte: 0 } },
  preset: { updated_by: '$user.id' },
  middleware: async ({ user, db, operation, input }, next) => {
    if (operation === 'update' && 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()

    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 filters
Value validation	Use checks
Auto-set columns	Use presets
Date math, computed WHERE	Use custom SQL
Lookup-based access control	Use `middleware`
Business rule validation	Use `middleware`
Input transformation	Use `middleware`
Result transformation	Use `middleware`
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