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.

SearchQuery<T> is the unified input type for all read operations in drizzle-castor. It collects every dimension of a query — which fields to return, how to filter rows, how to sort them, and how to paginate — into a single typed object. You pass it to searchOne, searchMany, or searchPage and the library’s AST compiler handles the rest: building joins, injecting JSON extraction functions, and running the CTE split-query strategy for accurate pagination.

​
Type definition

type SearchQuery<T> = {
  projection?: ValidPath<T>[];
  filter?: FilterQuery<T>;
  order?: OrderQuery<T>;
  page?: number;
  pageSize?: number;
};
All fields are optional. Omitting projection returns the full entity. Omitting filter removes the WHERE clause. Omitting order returns rows in database-natural order. page and pageSize are meaningful only when calling searchPage.

​
Fields

​
body.projection
string[]
An array of ValidPath<T> strings selecting which fields to include in the result. Paths may reference direct columns, dot-notation JSON sub-paths, and relational traversals. Omit this field to return the full entity type.When a relational path is included (for example "posts.title"), the compiler automatically fetches the related table’s primary key as well — this is required for the hydration step that collapses flat SQL rows into nested JavaScript objects.
projection: [
  "name",
  "profile.bio",            // 1:1 relation
  "posts.title",            // 1:N relation
  "posts.comments.content", // nested 1:N relation
  "settings.theme",         // JSON object path
  "persona.skills.0"        // JSON array index
]
​
body.filter
FilterQuery<T>
A FilterQuery<T> object describing WHERE conditions. Supports comparison, string, null, range, collection, array-containment, and logical operators. See the FilterQuery reference for the full operator list.
filter: {
  $or: [
    { name: { $like: "%John%" } },
    { "settings.theme": { $eq: "dark" } },
    { "posts.title": { $like: "%Drizzle%" } }
  ]
}
​
body.order
OrderQuery<T>
An OrderQuery<T> object controlling the ORDER BY clause. Keys are ValidPath<T> strings; values are OrderFieldConfig (see below). Multiple keys are applied in the order they appear in the object.
order: {
  createdAt: "desc",
  "posts.comments.createdAt": { direction: "desc", nulls: "last" }
}
​
body.page
number
1-indexed page number. Only meaningful when calling searchPage. The first page is 1.
​
body.pageSize
number
Number of records per page. Only meaningful when calling searchPage.

​
OrderFieldConfig

Each value in an order object is an OrderFieldConfig: 
type OrderDirection = "asc" | "desc";
type NullsPosition = "first" | "last";
type AggregateFunction = "min" | "max" | "avg" | "sum" | "count" | (string & {});

type OrderFieldConfig =
  | OrderDirection
  | {
      direction?: OrderDirection;
      nulls?: NullsPosition;
      aggregate?: AggregateFunction;
    };
Pass "asc" or "desc" directly for a basic sort with no null-handling or aggregation.
order: {
  createdAt: "desc",
  name: "asc"
}
Translates to:
ORDER BY "users"."created_at" DESC, "users"."name" ASC
Use the object form with nulls: "first" or nulls: "last" to control where NULL values appear in the result set.
order: {
  "posts.comments.createdAt": {
    direction: "desc",
    nulls: "last"
  }
}
Translates to:
ORDER BY "rel_posts_comments"."created_at" DESC NULLS LAST
Set aggregate to apply an aggregation function before ordering. This is useful when grouping results by a related field and ordering by a derived value such as comment count.Supported values: "min", "max", "avg", "sum", "count".
order: {
  id: { aggregate: "count", direction: "desc" }
}
Translates to:
ORDER BY COUNT("users"."id") DESC
You can also pass an arbitrary SQL function name as a string for advanced use cases, as the AggregateFunction type includes string & {} to allow this.

​
Usage examples

​
Fetch a single record with deep relations

const user = await userRepo.searchOne({
  projection: [
    "name",
    "profile.bio",
    "posts.title",
    "posts.comments.content",
    "settings.theme",
    "persona.skills.0"
  ],
  filter: {
    $or: [
      { name: { $like: "%John%" } },
      { "settings.theme": { $eq: "dark" } },
      { "persona.skills.0": { $eq: "Node.js" } },
      { "posts.title": { $like: "%Drizzle%" } }
    ]
  },
  order: {
    createdAt: "desc",
    "posts.comments.createdAt": "desc"
  }
}, "admin");
The hydrated result collapses the flat SQL rows into a nested object: 
{
  "id": 1,
  "name": "John Doe",
  "settings": { "theme": "dark" },
  "persona": { "skills": ["Node.js"] },
  "profile": { "bio": "Backend Developer" },
  "posts": [
    {
      "id": 201,
      "title": "Learning Drizzle",
      "comments": [
        { "id": 301, "content": "Great post!" }
      ]
    }
  ]
}

​
Fetch a list without pagination

const users = await userRepo.searchMany({
  order: { createdAt: "desc" }
}, "admin");

​
Paginated search with searchPage

const page = await userRepo.searchPage({
  page: 1,
  pageSize: 10,
  filter: { "posts.comments.content": { $notIsNull: true } },
  order: { createdAt: "desc" }
}, "public");

​
searchPage response shape

searchPage wraps the results in a meta envelope: 
{
  data: DbQueryResult<TEntity, Q>[];
  meta: {
    currentPage: number;
    pageSize: number;
    totalPages: number;
    totalItems: number;
  }
}
​
data
object[]
Array of hydrated entity objects shaped by the projection field (or the full entity type if projection is omitted).
​
meta
object
Pagination metadata.

​
Pagination internals

To avoid Cartesian fan-out when joining one-to-many relationships, searchPage uses a CTE split-query strategy:
  1. CTE query — fetches exactly the requested page of base entity IDs using filters, grouping, and LIMIT/OFFSET.
  2. Main query — INNER JOINs against the CTE result to hydrate the full relational graph for only those IDs.
This guarantees that pageSize: 10 returns exactly 10 top-level entities, regardless of how many related rows each entity has.

​
RBAC interaction

The RBAC engine trims the SearchQuery before it reaches the AST compiler:
  • allowedProjections — unauthorized paths are removed from projection.
  • allowedFilters — unauthorized paths are removed from filter (recursively, including inside $and/$or/$not).
  • allowedSorts — unauthorized paths are removed from order.
If all paths in a clause are trimmed and the resulting clause is empty, an AccessDeniedError is thrown.

Build docs developers (and LLMs) love

Get started for freeTalk to us