Drizzle ORM Connection-Scoped RLS Session Context Invariant | AgentRules.org

Rules Stacks Clients Auditor Pruner Self-Heal RFCs Benchmark CLI CI/CD Authors Submit

This specification is maintained by the AgentRules Working Group. Have enhancements or want to claim maintainership?Submit Specification →

backendcritical PriorityID: drizzle-orm-connection-scoped-rls-session-invariant

Drizzle ORM Connection-Scoped RLS Session Context Invariant

Enforces mandatory transactional session-context injection (e.g., SET LOCAL app.current_tenant_id) within Drizzle ORM transactions prior to executing queries under PostgreSQL Row Level Security (RLS). Prevents cross-tenant data leakage caused by asynchronous connection pool reuse when ambient session parameters persist or default to empty scopes.

Prohibited Anti-Pattern: Global Pool Querying Without Local Transactional Tenant Context Injection

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: Drizzle ORM Connection-Scoped RLS Session Context Invariant - Enforces mandatory transactional session-context injection (e.g., SET LOCAL app.current_tenant_id) within Drizzle ORM transactions prior to executing queries under PostgreSQL Row Level Security (RLS). Prevents cross-tenant data leakage caused by asynchronous connection pool reuse when ambient session parameters persist or default to empty scopes.
globs: src/**/*.ts, src/**/*.repository.ts, src/**/*.service.ts, src/db/**/*.ts
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/drizzle-orm-connection-scoped-rls-session-invariant"
---

# Drizzle ORM Connection-Scoped RLS Session Context Invariant

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Global Pool Querying Without Local Transactional Tenant Context Injection

## System Constraints
You are a Principal Backend Architect enforcing strict multi-tenant data isolation using Drizzle ORM and PostgreSQL Row Level Security (RLS). You must reject any direct query invocation on the base database pool without an encapsulated transaction that sets the session-scoped tenant context via `SET LOCAL`. Ensure that connection-pooling environments (e.g., PgBouncer in transaction mode, pg.Pool) never leak context across asynchronous operations.

## Strict Guidelines
- Engineers MUST ALWAYS execute tenant-scoped queries inside an explicit `db.transaction()` block using `SET LOCAL` to bind session variables exclusively to the current transaction lifetime.
- Engineers MUST NOT use session-level `SET` (without `LOCAL`) or `SET SESSION`, as connection pooling (PgBouncer/pg.Pool) will leak the tenant context to subsequent, unrelated asynchronous operations on the same physical connection.
- Engineers MUST NOT execute tenant-sensitive queries directly against the global `db` client instance; queries MUST be invoked on the transaction context (`tx`) passed to the transaction closure.
- Services MUST encapsulate tenant database access behind a standardized high-order function or repository wrapper (e.g., `withTenantContext(tenantId, async (tx) => { ... })`) that guarantees `SET LOCAL` is called prior to any Drizzle query.
- Code SHOULD sanitize and validate tenant identifiers (e.g., UUID validation) using parameterized SQL identifiers or prepared statements (`sql.raw` with strict typeguards or parameterized `set_config`) to prevent SQL injection in session configuration calls.
- Components NEVER assume ambient tenant context exists in connection pools without explicit verification.

## Bad vs Good Implementations
### Unsafe direct query vs. connection-scoped transactional RLS context injection

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Executing `SET app.current_tenant_id` on the shared pool without `LOCAL` and outside an explicit transaction causes the tenant state to stick to the underlying TCP connection. Other requests checking out this connection will silently inherit the previous tenant's identity, leading to severe cross-tenant data leaks under load.
```typescript
import { db } from '@/db';
import { users } from '@/db/schema';
import { eq, sql } from 'drizzle-orm';

export async function getTenantUsers(tenantId: string) {
  // CRITICAL ANTI-PATTERN:
  // 1. Direct pool querying without wrapping in a transaction.
  // 2. SET SESSION persists across connection reuse in connection pools (PgBouncer),
  //    causing cross-tenant data bleed in subsequent asynchronous queries.
  await db.execute(sql`SET app.current_tenant_id = ${tenantId}`);
  
  return await db.select().from(users);
}
```

#### Production Standard (Correct)
> **Standard**: Encapsulates database access within an explicit transaction using `set_config('app.current_tenant_id', value, true)`. The third argument (`true`) ensures connection-local scoping, destroying the session parameter upon COMMIT or ROLLBACK. Queries must execute against the scoped `tx` handle, fully isolating tenants in pooled environments.
```typescript
import { db } from '@/db';
import { users } from '@/db/schema';
import { sql, ExtractTablesWithRelations } from 'drizzle-orm';
import { PgTransaction } from 'drizzle-orm/pg-core';

export type TenantDbTransaction = PgTransaction<any, any, ExtractTablesWithRelations<any>>;

export async function withTenantContext<T>(
  tenantId: string,
  callback: (tx: TenantDbTransaction) => Promise<T>
): Promise<T> {
  // Invariant: Enforce UUID/slug format to prevent configuration poisoning
  if (!/^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i.test(tenantId)) {
    throw new Error('Invalid tenant UUID');
  }

  return await db.transaction(async (tx) => {
    // Use set_config with is_local = true (equivalent to SET LOCAL)
    // This guarantees the variable is reset immediately when the transaction terminates.
    await tx.execute(sql`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`);
    
    return await callback(tx);
  });
}

export async function getTenantUsers(tenantId: string) {
  return await withTenantContext(tenantId, async (tx) => {
    return await tx.select().from(users);
  });
}
```

Recommended MCP Servers (mcp-serverlist.com)

Equip agent with system tools to verify code for this stack.

PostgreSQL MCP Server

Inspect PostgreSQL schema, current active RLS policies, and verify set_config behavior directly within the database.

Recommended Agent Skills (askills.dev)

Ready-to-use executable skill functions tailored to this stack.

Drizzle Kit CLI

CLI tool for Drizzle ORM migrations and schema push to validate PostgreSQL RLS policy definitions.

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Drizzle ORM Connection-Scoped RLS Session Context Invariant

Drizzle ORM Connection-Scoped RLS Session Context Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces mandatory transactional session-context injection (e.g., SET LOCAL app.current_tenant_id) within Drizzle ORM transactions prior to executing queries under PostgreSQL Row Level Security (RLS). Prevents cross-tenant data leakage caused by asynchronous connection pool reuse when ambient session parameters persist or default to empty scopes. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Global Pool Querying Without Local Transactional Tenant Context Injection Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Global Pool Querying Without Local Transactional Tenant Context Injection	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

1

Engineers MUST ALWAYS execute tenant-scoped queries inside an explicit `db.transaction()` block using `SET LOCAL` to bind session variables exclusively to the current transaction lifetime.

Engineers MUST ALWAYS execute tenant-scoped queries inside an explicit `db.transaction()` block using `SET LOCAL` to bind session variables exclusively to the current transaction lifetime.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

2

Engineers MUST NOT use session-level `SET` (without `LOCAL`) or `SET SESSION`, as connection pooling (PgBouncer/pg.Pool) will leak the tenant context to subsequent, unrelated asynchronous operations on the same physical connection.

Engineers MUST NOT use session-level `SET` (without `LOCAL`) or `SET SESSION`, as connection pooling (PgBouncer/pg.Pool) will leak the tenant context to subsequent, unrelated asynchronous operations on the same physical connection.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

3

Engineers MUST NOT execute tenant-sensitive queries directly against the global `db` client instance

Engineers MUST NOT execute tenant-sensitive queries directly against the global `db` client instance; queries MUST be invoked on the transaction context (`tx`) passed to the transaction closure.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

4

Services MUST encapsulate tenant database access behind a standardized high-order function or repository wrapper (e.g., `withTenantContext(tenantId, async (tx) => { ... })`) that guarantees `SET LOCAL` is called prior to any Drizzle query.

Services MUST encapsulate tenant database access behind a standardized high-order function or repository wrapper (e.g., `withTenantContext(tenantId, async (tx) => { ... })`) that guarantees `SET LOCAL` is called prior to any Drizzle query.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

5

Code SHOULD sanitize and validate tenant identifiers (e.g., UUID validation) using parameterized SQL identifiers or prepared statements (`sql.raw` with strict typeguards or parameterized `set_config`) to prevent SQL injection in session configuration calls.

Code SHOULD sanitize and validate tenant identifiers (e.g., UUID validation) using parameterized SQL identifiers or prepared statements (`sql.raw` with strict typeguards or parameterized `set_config`) to prevent SQL injection in session configuration calls.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

6

Components NEVER assume ambient tenant context exists in connection pools without explicit verification.

Components NEVER assume ambient tenant context exists in connection pools without explicit verification.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

Technical Questions & Answers

Frequently Asked Technical Questions

Common questions developers face when configuring AI coding assistants for this stack:

Q:Why is Drizzle ORM Connection-Scoped RLS Session Context Invariant critical for AI coding assistants?

Enforces mandatory transactional session-context injection (e.g., SET LOCAL app.current_tenant_id) within Drizzle ORM transactions prior to executing queries under PostgreSQL Row Level Security (RLS). Prevents cross-tenant data leakage caused by asynchronous connection pool reuse when ambient session parameters persist or default to empty scopes. Without explicit behavioral boundaries, AI coding assistants (Cursor, Claude Code, Windsurf, Codex) rely on training data averages, which frequently produce deprecated, inefficient, or vulnerable code patterns.

Q:How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Global Pool Querying Without Local Transactional Tenant Context Injection) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q:Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q:Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Open Specification · CC BY 4.0·Free for Personal, Team & Commercial In-Repo Use

Citation & Open Source Attribution Guidelines

This specification is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0). You are completely free to adapt and share this rule in your workspace. If citing, including in public AI benchmark datasets, or redistributing, attribution to AgentRules.org is required.

Related BACKEND Rules

View all rules →

Fastify 5 Schema Compilers & Encapsulation Production Standard

Fastify relies on lexical encapsulation contexts to sandbox decorators, hooks, and compiled validation/serialization schemas without memory leaks or cross-tenant bleed. In Fastify 5, misuse of global compilers or failing to encapsulate plugin boundaries causes compiled `fast-json-stringify` serializers and Ajv validators to either crash on duplicate IDs, leak private data across sibling scopes, or bypass JIT compilation optimizations. Production services must enforce strict encapsulation hierarchies and type-safe schema compilation across every routing subtree.

Bun Embedded SQLite WAL Mode & Transaction Synchronization Standard

Production database standard for bun:sqlite. Mandates enabling Write-Ahead Logging (PRAGMA journal_mode = WAL), busy timeouts, and using db.transaction() for atomic operations without lock starvation.

Drizzle ORM Batch API & Interactive Transaction Isolation Standard

Production database standard for Drizzle ORM (PostgreSQL & MySQL). Mandates using db.batch() over HTTP drivers to prevent round-trip waterfalls, explicit db.transaction() with serializable/read-committed isolation, and strictly avoids unawaited query promises.

1 Rule in Stack

Drizzle