Where Clauses
Restrict which rows users can read, update, and delete — grouped by operation.
Where clauses inject SQL WHERE conditions into every query so users only see and touch rows they're allowed to. They work the same way as Drizzle ORM filters — if you know Drizzle, you already know how this works.
select
Controls which rows and columns users can read. The where clause is appended to every SELECT query.
permissions: {
view_orders: {
table: 'main.orders',
roles: ['member'],
select: {
columns: ['id', 'amount', 'status', 'customer_id', 'created_at'],
where: {
organization_id: { $in: '$user.org_ids' },
status: { $ne: 'deleted' },
},
},
},
}-- What the engine generates
SELECT id, amount, status, customer_id, created_at
FROM main.orders
WHERE organization_id IN ('org_1', 'org_2')
AND status != 'deleted'The user's Drizzle query on the client runs normally — the engine silently adds the WHERE conditions. If the user tries to select columns outside the columns list, they're stripped.
insert
Controls which columns users can create and what values are allowed. Inserts don't use where — use validate, default, and overwrite instead.
permissions: {
create_orders: {
table: 'main.orders',
roles: ['member'],
insert: {
columns: ['amount', 'status', 'customer_id'],
validate: {
amount: { $gte: 0 },
status: { $in: ['draft'] },
},
default: { status: 'draft' },
overwrite: {
created_by: '$user.id',
organization_id: '$user.current_org_id',
},
},
},
}-- User sends: { amount: 500, customer_id: 'cust_1' }
-- Engine validates, fills defaults, forces overwrites, then runs:
INSERT INTO main.orders (amount, customer_id, status, created_by, organization_id)
VALUES (500, 'cust_1', 'draft', 'usr_123', 'org_1')| Key | Purpose |
|---|---|
columns | Allowed columns — anything else is rejected |
validate | Must pass or the request returns 403 |
default | Fills value if the client didn't send it (client can override) |
overwrite | Always set from server — client cannot override |
update
Controls which rows users can modify and what values are allowed. The where clause restricts which rows are updatable.
permissions: {
edit_orders: {
table: 'main.orders',
roles: ['member'],
update: {
columns: ['amount', 'status'],
where: {
organization_id: { $in: '$user.org_ids' },
status: { $ne: 'completed' },
},
validate: { amount: { $gte: 0 } },
overwrite: { updated_by: '$user.id' },
},
},
}-- User sends: db.update(orders).set({ amount: 500 }).where(eq(orders.id, 'order_42'))
-- Engine injects permission where clause + overwrite:
UPDATE main.orders
SET amount = 500, updated_by = 'usr_123'
WHERE id = 'order_42'
AND organization_id IN ('org_1', 'org_2')
AND status != 'completed'The user's own where (e.g. eq(orders.id, 'order_42')) is AND'd with the permission's where. If the row doesn't match both, nothing is updated.
delete
Controls which rows users can remove. The where clause restricts which rows are deletable.
permissions: {
delete_drafts: {
table: 'main.orders',
roles: ['member'],
delete: {
where: {
organization_id: { $in: '$user.org_ids' },
status: { $eq: 'draft' },
},
},
},
}-- User sends: db.delete(orders).where(eq(orders.id, 'order_42'))
-- Engine injects permission where clause:
DELETE FROM main.orders
WHERE id = 'order_42'
AND organization_id IN ('org_1', 'org_2')
AND status = 'draft'Only rows matching both the user's filter and the permission's where are deleted.
Learn More
- Operators — Full operator reference
- Validate — Input validation for insert and update
- Defaults & Overwrite — Auto-fill and force values on writes
- Raw SQL — Escape hatch for complex WHERE conditions
- Drizzle ORM Filtering — Our where syntax mirrors Drizzle's patterns