Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/fajarnugraha37/drizzle-castor/llms.txt

Use this file to discover all available pages before exploring further.

This guide walks you through every step required to go from a blank project to executing type-safe database queries with Drizzle Castor. You will install the package, write a Drizzle schema, configure the schema builder with profiles and policies, define table relations, and run your first queries — all with full TypeScript inference.
1

Install the package

Add Drizzle Castor and its required peer dependency to your project. Drizzle ORM is required because Drizzle Castor builds directly on top of it.
bun add @fajarnugraha37/drizzle-castor drizzle-orm
If you prefer ESM-first or Deno environments, you can also install from JSR. See the installation page for JSR commands.
2

Set up your Drizzle schema

Create your standard Drizzle ORM schema. Drizzle Castor reads column types and references directly from this schema to provide full type inference throughout your application — you do not need to change anything here.
schema.ts
import { int, primaryKey, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const usersTable = sqliteTable("users", {
  id: int("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  email: text("email").unique().notNull(),
  age: int("age"),
  tags: text({ mode: 'json' }).$type<string[]>(),
  persona: text({ mode: 'json' }).$type<{ hobbies: string[]; skills: string[] }>(),
  settings: text({ mode: 'json' }).$type<{ 
    theme: string; 
    notifications: boolean;
  }>(),
  companyId: int("company_id").references(() => companiesTable.id),
  createdAt: int("created_at").$default(() => Date.now()),
  updatedAt: int("updated_at").$default(() => Date.now()).$onUpdate(() => Date.now()),
  deletedFlag: int("deleted_flag").default(0),
  deletedAt: int("deleted_at"),
});

export const postsTable = sqliteTable("posts", {
  id: int("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  userId: int("userId").references(() => usersTable.id),
  createdAt: int("created_at").$default(() => Date.now()),
  deletedFlag: int("deleted_flag").default(0),
});

export const profilesTable = sqliteTable("profiles", {
  id: int("id").primaryKey({ autoIncrement: true }),
  bio: text("bio"),
  avatarUrl: text("avatar_url"),
  userId: int("user_id").references(() => usersTable.id),
});

export const companiesTable = sqliteTable("companies", {
  id: int("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
});
3

Initialize SchemaBuilder with createSchemaBuilder

Import createSchemaBuilder and pass your Drizzle database instance together with your table definitions. The third argument sets the execution mode.
db.ts
import { drizzle } from "drizzle-orm/bun-sqlite";
import { createSchemaBuilder } from "@fajarnugraha37/drizzle-castor";
import { usersTable, postsTable, profilesTable } from "./schema";

const db = drizzle("sqlite.db");

export const schemaMetadataBuilder = createSchemaBuilder(db, [
  usersTable,
  postsTable,
  profilesTable
] as const, "lenient");
The execution mode controls how Drizzle Castor handles tables without an explicit policy:
  • lenient (default) — tables without a policy allow all actions.
  • strict — every table must have an explicit policy or an AccessDeniedError is thrown.
4

Configure profiles and logger

Chain .profiles() to register the valid role names your application uses, and .withLogger() to enable the built-in hybrid logger. TypeScript uses the profile list to give you autocomplete and type-checking when you pass a profile to a repository method.
db.ts
export const schemaMetadataBuilder = createSchemaBuilder(db, [
  usersTable,
  postsTable,
  profilesTable
] as const, "lenient")
  .profiles(['default', 'public', 'admin', 'user'] as const)
  .withLogger({ 
    level: 'INFO', 
    format: '%d{HH:mm:ss} %p [%c] (%t) %s'
  });
5

Define table relations

Use .table() to declare relationships between your Drizzle tables. Drizzle Castor uses these declarations to build the JOIN clauses needed when you use dot-notation paths such as "posts.title" in projections or filters. You can also enable soft-delete behaviour here.
db.ts
schemaMetadataBuilder.table("users", {
  oneToOne: [
    {
      relationName: "profile",
      relatedTable: "profiles",
      localKey: "users.id",
      foreignKey: "profiles.userId",
    }
  ],
  oneToMany: [
    {
      relationName: "posts",
      relatedTable: "posts",
      localKey: "users.id",
      foreignKey: "posts.userId",
    },
  ],
  softDelete: {
    deleteValue: { deletedFlag: 1 },
    restoreValue: { deletedFlag: 0 },
  },
});
Relations are defined separately from your physical Drizzle schema. You do not need to modify your sqliteTable (or pgTable / mysqlTable) definitions.
6

Set up policies

Define who can do what on each table using .policies(). Policies can be static objects for simple cases or asynchronous functions that resolve rules dynamically from the execution context.
db.ts
// Global fallback policy — applies to all tables not otherwise specified
schemaMetadataBuilder.policies((ctx, tableName, profiles) => {
  if (profiles.includes("admin")) return { allowedActions: "*" };
  return { allowedActions: ["read"] };
});

// Table-specific policies
schemaMetadataBuilder.policies('users', {
  // Static policy — read-only with field restrictions
  public: { 
    allowedActions: ["read"],
    allowedFilters: ["name", "email", "settings.theme"],
    allowedProjections: ["name", "profile.bio"],
  },

  // Static wildcard — full access
  admin: {
    allowedActions: "*",
    allowedSets: "*",
    allowedProjections: "*",
    allowedFilters: "*",
    allowedSorts: "*"
  },

  // Dynamic policy — resolved per request
  user: async (ctx) => {
    const isOwner = ctx.params.id === ctx.state.userId;
    return {
      allowedActions: isOwner ? ["read", "update"] : ["read"],
      allowedSets: ["settings.theme", "persona.skills"],
      allowedProjections: ["*"],
      allowedFilters: "*",
      allowedSorts: "*"
    };
  }
});
Available actions are "create", "read", "update", "softDelete", "restore", and "hardDelete". Use "*" to grant all of them at once.
7

Build and create a repository with repoFactory

Call .build() to finalise the configuration and then use repoFactory() to create a typed repository for a specific table. The returned object provides all CRUD methods with full TypeScript inference.
db.ts
// Finalise configuration
export const schemaMetadata = schemaMetadataBuilder.build();

// Create a typed repository for the "users" table
const userRepo = schemaMetadata.repoFactory("users");
8

Run your first createOne, searchMany, and searchPage queries

Every repository method accepts a profile string as its last argument. The RBAC engine validates the profile and applies field-level trimming before the query reaches the database.
queries.ts
// Create a single record
const newUser = await userRepo.createOne({
  name: "Jane Doe",
  email: "jane@example.com"
}, "admin");

// Fetch multiple records with filtering and ordering
const users = await userRepo.searchMany({
  order: { createdAt: "desc" }
}, "admin");

// Fetch a single record with deep relations and JSON extraction
const user = await userRepo.searchOne({
  projection: [
    "name", 
    "profile.bio",            // 1:1 relation
    "posts.title",            // 1:N relation
    "settings.theme",         // JSON object extraction
    "persona.skills.0"        // JSON array index extraction
  ], 
  filter: {
    $or: [
      { name: { $like: "%Jane%" } },
      { "settings.theme": { $eq: "dark" } }
    ]
  },
  order: {
    "createdAt": "desc"
  }
}, "admin");

// Paginate results
const page = await userRepo.searchPage({
  page: 1,
  pageSize: 10,
  filter: { "posts.title": { $notIsNull: true } }
}, "public");
// Returns:
// { 
//   data: [{ id: 1, name: "Jane", posts: [...] }, ...], 
//   meta: { currentPage: 1, pageSize: 10, totalPages: 5, totalItems: 42 } 
// }
Unlike raw SQL, searchOne and searchMany automatically hydrate flat rows into clean, nested JavaScript objects that mirror your query shape. See JSON querying for the full filter operator reference.
9

Subscribe to telemetry events

Before calling .build(), register event listeners on the builder to capture non-blocking telemetry. The execution event fires after every repository method completes; the security event fires when an access control decision is made.
db.ts
schemaMetadataBuilder.on('execution', (ev) => {
  // ev contains: action, tableName, duration, status, traceId
  console.log(`${ev.action} on ${ev.tableName} took ${ev.duration}ms`);
});

schemaMetadataBuilder.on('security', (ev) => {
  console.warn(`[Audit] Security event on ${ev.tableName}: ${ev.message}`);
});
You can also register Koa-style middlewares with .use() for full lifecycle control over every repository call. See middleware pipeline for details.

​
What’s next

JSON querying

Explore every filter operator, projection path, and sort key supported by the AST translator.

Access control

Deep dive into profiles, policies, and intelligent data trimming.

Relations

Learn how to define one-to-one, one-to-many, and many-to-many relationships.

Middleware pipeline

Compose custom middlewares and understand the execution context.

Telemetry

Reference for all telemetry events and their payload shapes.

Soft deletes

Configure declarative soft deletes and the restore workflow.

Build docs developers (and LLMs) love

Get started for freeTalk to us