## Why Drizzle? Drizzle has become one of the most popular TypeScript ORMs. It is lightweight, SQL-first, and gives developers direct control over the queries that hit the database. Its type-safe query builder maps closely to SQL, which makes it a natural target for translating Cerbos query plans into efficient database filters. The adapter works with every SQL dialect Drizzle supports \-- SQLite, PostgreSQL, MySQL, and PlanetScale.
## How it works `queryPlanToDrizzle` takes a Cerbos `PlanResourcesResponse` and a mapper that associates Cerbos attribute paths with Drizzle columns. It walks the expression tree and returns a Drizzle `SQL` fragment that slots straight into a `.where()` clause. ```ts import { queryPlanToDrizzle, PlanKind } from "@cerbos/orm-drizzle"; import { resources } from "./schema"; const plan = await cerbos.planResources({ principal: { id: "user1", roles: ["USER"] }, resource: { kind: "document" }, action: "view", }); const result = queryPlanToDrizzle({ queryPlan: plan, mapper: { "request.resource.attr.status": resources.status, "request.resource.attr.owner": resources.ownerId, }, }); switch (result.kind) { case PlanKind.ALWAYS_ALLOWED: return await db.select().from(resources); case PlanKind.ALWAYS_DENIED: return []; case PlanKind.CONDITIONAL: return await db.select().from(resources).where(result.filter); } ``` Because the adapter produces a standard Drizzle SQL fragment, you can compose it with your own conditions using `and()` or `or()` like any other filter.
## Relation support Real-world authorization policies rarely check flat columns alone. A policy might grant access to documents owned by a specific department, where the department is a row in another table. The Drizzle adapter handles this with relation mappings that generate `EXISTS` subqueries, including support for nested relations and many-to-many joins. ```ts const result = queryPlanToDrizzle({ queryPlan: plan, mapper: { "request.resource.attr.tags": { relation: { type: "many", table: resourceTags, sourceColumn: resources.id, targetColumn: resourceTags.resourceId, fields: { name: { relation: { type: "one", table: tags, sourceColumn: resourceTags.tagId, targetColumn: tags.id, field: tags.name, }, }, }, }, }, }, }); ```
## Full example Consider a policy that allows users to view published documents, or any document they own: ``` # policies/document.yaml apiVersion: api.cerbos.dev/v1 resourcePolicy: resource: document version: default rules: - actions: ["view"] effect: EFFECT_ALLOW roles: ["USER"] condition: match: any: of: - expr: request.resource.attr.status == "published" - expr: request.resource.attr.ownerId == request.principal.id ``` With Drizzle, the adapter turns this into a composable SQL filter: ```ts import { GRPC as Cerbos } from "@cerbos/grpc"; import { queryPlanToDrizzle, PlanKind } from "@cerbos/orm-drizzle"; import { eq, and } from "drizzle-orm"; import { db } from "./db"; import { documents } from "./schema"; const cerbos = new Cerbos("localhost:3592", { tls: false }); async function listDocuments(userId: string) { const plan = await cerbos.planResources({ principal: { id: userId, roles: ["USER"] }, resource: { kind: "document" }, action: "view", }); const result = queryPlanToDrizzle({ queryPlan: plan, mapper: { "request.resource.attr.status": documents.status, "request.resource.attr.ownerId": documents.ownerId, }, }); switch (result.kind) { case PlanKind.ALWAYS_ALLOWED: return await db.select().from(documents); case PlanKind.ALWAYS_DENIED: return []; case PlanKind.CONDITIONAL: return await db .select() .from(documents) .where(and(eq(documents.deleted, false), result.filter)); } } ``` The adapter produces a standard Drizzle SQL fragment, so you can compose it with your own conditions using `and()` or `or()` like any other filter.
## Supported operators The adapter covers the full range of operators that Cerbos can emit in a query plan: - **Logical:** `and`, `or`, `not` - **Comparison:** `eq`, `ne`, `lt`, `gt`, `le`, `ge`, `in` - **String:** `contains`, `startsWith`, `endsWith` - **Existence:** `isSet` - **Collections:** `hasIntersection`, `exists`, `exists_one`, `all`
## Get started ```shell npm install @cerbos/orm-drizzle ``` The full documentation and source are available on [GitHub](https://github.com/cerbos/query-plan-adapters/tree/main/drizzle). If you have questions or run into issues, join the [Cerbos community Slack](https://cerbos.dev/slack). ## Closing thoughts Try [Cerbos Hub](https://hub.cerbos.cloud/) to version and distribute the policies these query plans come from, or [book a session](https://www.cerbos.dev/workshop) to talk through your Convex schema with our engineers. Go deeper: [How to adopt externalized authorization](https://solutions.cerbos.dev/how-to-adopt-externalized-authorization) (eBook) for a structured, in-depth approach to navigating the externalized authorization transformation. ## FAQ ### How do you filter a Drizzle ORM query using Cerbos authorization policies? You filter a Drizzle query with Cerbos by calling PlanResources and passing the result to the @cerbos/orm-drizzle adapter, which returns a Drizzle SQL expression you drop straight into .where() on your existing query builder chain. The database applies the authorization condition, so rows the user cannot access are never read. ### Can the Cerbos Drizzle adapter handle policies that reference related tables? Yes. The Cerbos Drizzle adapter supports relation mapping and generates EXISTS subqueries automatically for one-to-one and one-to-many relations. That means a policy condition on a related record, such as ownership held on a parent row, becomes part of the same SQL query rather than a second round trip. ### Which databases does the Cerbos Drizzle query plan adapter support? The Cerbos Drizzle adapter works wherever Drizzle ORM does, covering PostgreSQL, MySQL, SQLite and PlanetScale. Because the adapter emits a Drizzle SQL expression rather than raw dialect-specific SQL, the same authorization policy applies across those databases without rewriting the filter. ### Do I need Cerbos Hub to use the Drizzle query plan adapter? No, the Drizzle query plan adapter calls a Cerbos policy decision point and works without Cerbos Hub. Hub is what adds policy version control, automated testing before a change rolls out, distribution to every decision point without a redeploy, and retained audit logs. The adapter behaves the same either way.