Global Filters
A global filter is a where that Turbine adds to every query on a table: reads, updates, deletes, and the relation queries that reach the table. Use it for soft delete and multi-tenancy.
Configuration#
import { turbine } from './generated/turbine/index.js';
const db = turbine({
connectionString: process.env.DATABASE_URL,
globalFilters: {
posts: { deletedAt: null }, // soft delete
users: { deletedAt: null },
orders: () => ({ tenantId: currentTenant() }), // evaluated per query
},
});await db.posts.findMany();
// SELECT ... FROM "posts" WHERE "deleted_at" IS NULL
await db.users.findMany({ where: { role: 'admin' } });
// SELECT ... FROM "users" WHERE "role" = $1 AND "deleted_at" IS NULLKey each filter by table name (blog_posts) or client accessor (blogPosts). A value is a where object, a function that returns one, or null for no filter. Values are always bound as parameters.
The constructor compiles every static filter and throws ValidationError (E003), naming the key, for:
- a key that names no table, or two keys for one table;
- a value that is not a where object, a function or
null, or a static filter holdingundefined; - a static filter naming something that is not a column or relation, or that a query would refuse;
- static filters that reach each other in a cycle through relation filters.
A generated client also types globalFilters per table, so a misspelled table or column is a compile error.
Function filters, per-request tenancy#
A function filter runs each time a query is built, so it can read per-request state:
const db = turbine({
connectionString: process.env.DATABASE_URL,
globalFilters: {
orders: () => ({ tenantId: getCurrentTenantId() }),
invoices: () => (isAdmin() ? null : { tenantId: getCurrentTenantId() }),
},
});
await db.orders.findMany();
// SELECT ... FROM "orders" WHERE "tenant_id" = $1 -- params: ['t-42']Function filters fail closed. Returning undefined, or a filter holding it ({ tenantId: undefined } when no tenant is set), throws ValidationError (E003) rather than run unfiltered. Return null or {} to skip the filter on purpose, as invoices does for admins.
Filters flow into relations#
A table's filter applies wherever its rows appear, including under another table's with:
// globalFilters: { posts: { deletedAt: null } }
const users = await db.users.findMany({
with: { posts: true, _count: { posts: true } },
where: { posts: { some: { published: true } } },
});
// users[n].posts, the _count and the `some` filter all skip deleted postsThat covers with relations on every load strategy, the relation filters some, every, none and is, relation _count, and ordering by _count.
A filter can contain a relation filter, such as posts: { author: { is: { active: true } } }, and the users filter then applies inside it. If function filters lead back to a table whose filter is being built, that step reads unfiltered, ending the chain.
Mutations and the empty-where guard#
Filters apply to update, updateMany, delete, deleteMany and the conflict update of upsert. create and createMany are never filtered.
On MySQL, SQL Server and PowDB, an upsert on a filtered table runs as a filtered lookup and then an update or insert, in one transaction, so another tenant's row is never updated. A batched buildUpsert on those engines throws UnsupportedFeatureError (E017), since a batch has no round trip for the lookup.
A filter does not satisfy the empty-where guard, which checks only the where you pass:
// globalFilters: { users: { deletedAt: null } }
await db.users.deleteMany({ where: {} });
// ❌ ValidationError (E003)
await db.users.deleteMany({ where: { orgId: 1 } });
// ✅ deletes org 1's users where "deleted_at" IS NULLOpting out, skipGlobalFilters#
import { UNSAFE } from 'turbine-orm';
// Skip every filter on this call
const all = await db.posts.findMany({ skipGlobalFilters: UNSAFE });
// Skip only the posts filter. The array starts with UNSAFE.
const some = await db.users.findMany({
with: { posts: true },
skipGlobalFilters: [UNSAFE, 'posts'],
});Each entry after UNSAFE names a table, by table name or accessor. An entry that names no table, or [UNSAFE] alone, throws ValidationError (E003). So do skipGlobalFilters: true and ['posts']; the next section explains why.
Privilege options and the UNSAFE symbol#
Three query options remove a safety boundary rather than change a result. Each is turned on only by the UNSAFE symbol exported from turbine-orm:
| Option | Removes |
|---|---|
skipGlobalFilters | The tenant or soft-delete filter |
includePii | The PII column projection |
allowFullTableScan | The empty-where guard on a mutation |
These options sit beside where, so a handler that spreads a request body into the args would let the caller set them:
app.get('/users', (req, res) => db.users.findMany({ ...req.body }));If they accepted true, a body of {"skipGlobalFilters": true} would drop the tenant filter. JSON.parse, query strings and form bodies cannot produce a symbol, so untrusted input can never enable these options. UNSAFE is Symbol.for('turbine-orm.UNSAFE'), so the ESM and CJS builds share it.
Upgrading from true#
Passing true throws ValidationError (E003). The option types are Unsafe, so TypeScript lists every call site to change:
| Before | After |
|---|---|
skipGlobalFilters: true | skipGlobalFilters: UNSAFE |
skipGlobalFilters: ['posts'] | skipGlobalFilters: [UNSAFE, 'posts'] |
includePii: true | includePii: UNSAFE |
allowFullTableScan: true | allowFullTableScan: UNSAFE |
A boolean does not typecheck, so add the key conditionally:
await db.sessions.deleteMany({
where: {},
...(purgeEverything ? { allowFullTableScan: UNSAFE } : {}),
});At runtime, false, null and undefined mean "not enabled" and never throw. Any other value throws, with a message that ends: If you did not write this option, an untrusted object was spread into these query args. turbine-orm/prisma-compat forwards these options unchanged.
See also#
- Filtering and sorting:
whereoperators. - Read Replicas: filters apply the same way on replicas.
- Transactions: RLS
sessionContext, tenancy enforced by the database.