@cfast/db

Overview

@cfast/db wraps Drizzle ORM and returns lazy Operation objects instead of promises. An Operation knows which permissions it requires (.permissions) and can be inspected before execution. When you call .run(), permissions are checked first — if denied, a ForbiddenError is thrown before any SQL touches the database.

This is application-level Row-Level Security for D1, which has no native RLS. The permission check is structural: you cannot get a query result without going through .run(), and .run() always checks @cfast/permissions grants.

Installation

pnpm add @cfast/db

Peer dependencies: drizzle-orm, @cfast/permissions.

Quick Setup

Create a permission-aware database instance per request:

import { createDb } from "@cfast/db";
import { permissions } from "./permissions";
import * as schema from "./schema";

export async function loader({ context, request }) {
  const user = await auth.requireUser(request);

  const db = createDb({
    d1: context.env.DB,
    schema,
    permissions,
    user,
  });

  // Permission filters applied automatically based on user's role
  const visiblePosts = await db.query(posts).findMany().run({});
  return { posts: visiblePosts };
}

The Db instance captures the user at creation time. Permission checks and WHERE clause injection use this captured user, so you must create a new instance per request.

Core Concepts

Lazy Operations

Every method on db returns an Operation<TResult> with two properties:

• .permissions — An array of PermissionDescriptor objects available immediately, without executing any SQL. Each descriptor has { action, table }.
• .run(params) — Checks permissions, applies permission WHERE clauses, executes via Drizzle, and returns results. Throws ForbiddenError if the user’s role lacks a required grant.

This two-phase design enables UI adaptation (check .permissions on the client to decide whether to show an edit button), upfront composition (merge permissions from multiple operations before any SQL runs), and introspection for logging and admin dashboards.

Automatic WHERE Injection

For "read" grants with a where clause, the filter is automatically appended to every SELECT query on that table. The resulting SQL combines the user’s filter and permission filters:

user_filter AND (perm_filter_1 OR perm_filter_2)

If any matching grant has no where clause, the user has unrestricted access — no permission filter is added. For "update" and "delete" grants, the permission where clause is AND’d with the user-supplied condition, narrowing which rows can be affected.

Caching

The cache layer manages table-level version counters. Mutations automatically bump the version for affected tables, causing subsequent reads to generate different cache keys:

const db = createDb({
  d1: env.DB,
  schema,
  permissions,
  user,
  cache: {
    backend: "cache-api",       // "cache-api" | "kv"
    ttl: "30s",
    staleWhileRevalidate: "5m",
    exclude: ["sessions"],      // tables that should never be cached
  },
});

Cache keys embed the user’s role and table version: cfast:{role}:v{version}:{hash(sql)}. An anonymous user’s cached result can never be served to an editor.

Common Patterns

Reading Data

// Find many with options
const techPosts = db.query(posts).findMany({
  columns: { id: true, title: true },
  where: eq(posts.category, "tech"),
  orderBy: desc(posts.createdAt),
  limit: 10,
  with: { comments: true },
});

await techPosts.run({});

// Find first
const post = db.query(posts).findFirst({
  where: eq(posts.id, "abc-123"),
});

await post.run({}); // returns TResult | undefined

Row-Level _can Annotations

Every findMany and findFirst result includes a _can: Record<string, boolean> object with per-action booleans, evaluated from the user’s grants at query time. No opt-in required — permissions are first-class on every row.

const posts = await db.query(postsTable).findMany().run({});

posts[0]._can;
// { read: true, create: true, update: true, delete: false }

// Type: WithCan<Post>[]
type WithCan<T> = T & { _can: Record<string, boolean> };

Use _can in your UI to show or hide per-row actions (edit buttons, delete links) without a separate permission check. See the permissions guide for the full semantics, the difference from can(), and UI patterns.

_can is not added in unsafe() mode or when user is null.

Performance: adds 3—5 computed SQL columns per query (one per granted CRUD action). Unrestricted grants are literal 1 and essentially free.

Writing Data

// Insert
const createPost = db.insert(posts)
  .values({ title: "Hello World", authorId: user.id })
  .returning();

const inserted = await createPost.run({});

// Update with row-level permission filtering
const publishPost = db.update(posts)
  .set({ published: true })
  .where(eq(posts.id, "abc-123"));

await publishPost.run({});
// User role: WHERE id = 'abc-123' AND author_id = 'user-123'
// Editor role: WHERE id = 'abc-123' (unrestricted grant)

// Delete
const removePost = db.delete(posts)
  .where(eq(posts.id, "abc-123"));

await removePost.run({});

Composing Operations

Use compose() to merge multiple operations into one with combined, deduplicated permissions. The executor function lets you wire data dependencies between sub-operations:

import { compose } from "@cfast/db";

const publishWorkflow = compose(
  [
    db.update(posts).set({ published: true }).where(eq(posts.id, "abc")),
    db.insert(auditLogs).values({
      id: crypto.randomUUID(),
      action: "publish",
      targetId: "abc",
      userId: user.id,
    }),
  ],
  async (doUpdate, doAudit) => {
    await doUpdate({});
    await doAudit({});
    return { published: true };
  },
);

publishWorkflow.permissions;
// [{ action: "update", table: posts }, { action: "create", table: auditLogs }]

await publishWorkflow.run({});

Composed operations can themselves be nested inside other compose() calls.

Self-Referential Foreign Keys

Drizzle cannot infer the type of a references() callback that points back at the same table. Without an explicit return type, TypeScript fails with TS7022: 'tableName' implicitly has type 'any' because it does not have a type annotation.

Annotate the callback with AnySQLiteColumn (or AnyPgColumn / AnyMySqlColumn for other dialects):

import { sqliteTable, text, integer, type AnySQLiteColumn } from "drizzle-orm/sqlite-core";

export const folders = sqliteTable("folders", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  // Self-reference: parentFolderId points back at folders.id
  parentFolderId: text("parent_folder_id").references(
    (): AnySQLiteColumn => folders.id,
  ),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
});

The same pattern applies to any table that needs to reference itself — e.g. comment threads with parentCommentId, org charts with managerId, or category trees.

Bypassing Permissions with unsafe()

For system operations without an authenticated user (cron jobs, migrations, background tasks), use db.unsafe():

const op = db.unsafe().delete(posts).where(eq(posts.id, "abc"));
op.permissions; // [] -- empty, no permissions required
await op.run({}); // executes without any permission check

unsafe() returns a scoped Db instance — it does not affect the original. Use git grep '.unsafe()' to audit every permission bypass in your codebase.

Type-Safe .with() Relations

When you pass a typed schema to createDb(), relation queries with with automatically infer the full result type — including nested relations. You no longer need as unknown as casts:

import { relations } from "drizzle-orm";

const recipesRelations = relations(recipes, ({ many }) => ({
  ingredients: many(ingredients),
}));
const schema = { recipes, ingredients, recipesRelations, ingredientsRelations };

const db = createDb({ d1: env.DB, schema, grants, user });

// Result type: { id: string; title: string; ingredients: { id: string; name: string }[] }[]
const result = await db.query(recipes).findMany({ with: { ingredients: true } }).run({});

This works for findMany, findFirst, nested with (e.g., ingredients -> recipe), and through db.unsafe(). If you do not pass relations in the schema, the base row type is inferred as before.

Date Serialization with toJSON()

React Router loaders must return JSON-serializable data. toJSON() recursively converts Date fields to ISO 8601 strings with correct types:

import { toJSON } from "@cfast/db";

export async function loader({ context }) {
  const db = createDb({ ... });
  const posts = await db.query(postsTable).findMany().run({});
  return { posts: toJSON(posts) };
  // Type: { createdAt: string; title: string; ... }[] -- Date fields become string
}

Works on single objects, arrays, and nested structures. Non-Date primitives pass through unchanged.

Clearing the Lookup Cache

When you use with (relation lookups) on queries, @cfast/db caches the resolved lookup metadata per Db instance. In tests that reuse a single Db across multiple grant configurations or schema changes, stale cache entries can cause unexpected results.

Call db.clearLookupCache() to reset it:

db.clearLookupCache(); // clears the per-instance `with` lookup cache

// Now the next query with `with` will re-resolve lookups
const posts = await db.query(postsTable).findMany({ with: { comments: true } }).run({});

This is primarily useful in test suites. In production, each request creates a fresh Db instance, so the cache stays scoped to a single request lifecycle.

Seeding

@cfast/db includes a schema-driven seed generator with bundled faker. See the full Seeding guide for details. Quick overview:

import { seed } from "@cfast/db/seed";
await seed(db); // introspects schema, generates data, inserts

The engine introspects your Drizzle schema to auto-generate realistic data, handling foreign keys, topological ordering, and many-to-many deduplication. Use .seed() methods on columns and tables for fine-grained control.