Relational Query

Use db.query() to fetch data with related records in a single query. The Relational Query Builder (RQB) leverages your schema's relations to automatically handle joins.

Prerequisites

Before using relational queries, define relations in your schema:

import { table, pk, varchar, bigint, relations, many, fk } from "durcno";

export const Users = table("public", "users", {
  id: pk(),
  username: varchar({ length: 50, notNull }),
});

export const Posts = table("public", "posts", {
  id: pk(),
  userId: bigint({ notNull }).references(() => Users.id),
  title: varchar({ length: 255 }),
});

// Define relations
export const UsersRelations = relations(Users, () => ({
  posts: many(Posts, Posts.userId),
}));

export const PostsRelations = relations(Posts, () => ({
  author: fk(Posts.userId, Users),
}));

Basic Usage

Find Many

Fetch multiple records with options:

import { db } from "./db/index.ts";
import { Users } from "./db/schema.ts";

const users = await db.query(Users).findMany({
  limit: 10,
});
// Type: { id: bigint; username: string }[]

Find First

Fetch a single record (returns null if not found):

import { eq } from "durcno";

const user = await db.query(Users).findFirst({
  where: eq(Users.id, 1n),
});
// Type: { id: bigint; username: string } | null

Selecting Columns

Include Specific Columns

Use true to include columns:

const users = await db.query(Users).findMany({
  columns: {
    id: true,
    username: true,
  },
});
// Type: { id: bigint; username: string }[]

Exclude Columns

Use false to exclude columns:

const users = await db.query(Users).findMany({
  columns: {
    email: false, // Exclude email
  },
});
// Type: { id: bigint; username: string; type: "admin" | "user"; createdAt: Date }[]

Loading Relations

Use with to include related records:

// Load users with their posts
const usersWithPosts = await db.query(Users).findMany({
  with: {
    posts: {},
  },
});
// Type: { id: bigint; username: string; posts: { id: bigint; userId: bigint; title: string }[] }[]

Nested Relations

Load nested relations recursively:

const usersWithPostsAndComments = await db.query(Users).findMany({
  with: {
    posts: {
      with: {
        comments: {},
      },
    },
  },
});

Selective Columns in Relations

Specify which columns to include from related records:

const users = await db.query(Users).findMany({
  columns: {
    username: true,
  },
  with: {
    posts: {
      columns: {
        title: true,
      },
    },
  },
});
// Type: { username: string; posts: { title: string }[] }[]

Filtering, Ordering, Limiting & Offsetting Nested Many Relations

where, orderBy, limit, and offset can be applied inside a with block for many (one-to-many) relations:

import { eq, desc } from "durcno";

const posts = await db.query(Posts).findMany({
  with: {
    comments: {
      columns: { id: true, body: true },
      where: eq(Comments.isEdited, true),
      orderBy: desc(Comments.createdAt),
      limit: 5,
      offset: 10,
    },
  },
});
// Each post includes only edited comments, sorted newest-first, offset by 10, capped at 5

note

where, orderBy, limit, and offset are not available on nested fk (many-to-one) or one (one-to-one) relations. The join condition already uniquely identifies the related row, so further filtering is not meaningful — the TypeScript compiler will reject such usage.

Filtering

Use where to filter results:

import { eq, and, gte } from "durcno";

const admins = await db.query(Users).findMany({
  where: eq(Users.type, "admin"),
});

const recentAdmins = await db.query(Users).findMany({
  where: and(
    eq(Users.type, "admin"),
    gte(Users.createdAt, new Date("2024-01-01")),
  ),
});

Ordering

Use orderBy with asc() or desc():

import { asc, desc } from "durcno";

const users = await db.query(Users).findMany({
  orderBy: asc(Users.username),
});

const recentUsers = await db.query(Users).findMany({
  orderBy: desc(Users.createdAt),
});

Pagination

Use limit and offset. Both accept number or bigint:

// First page
const page1 = await db.query(Users).findMany({
  limit: 10,
  offset: 0,
});

// Second page
const page2 = await db.query(Users).findMany({
  limit: 10,
  offset: 10,
});

// Using bigint values
const page3 = await db.query(Users).findMany({
  limit: 10n,
  offset: 20n,
});

Complete Example

Combining all options:

import { eq, desc } from "durcno";

const result = await db.query(Users).findMany({
  columns: {
    id: true,
    username: true,
  },
  where: eq(Users.type, "admin"),
  orderBy: desc(Users.createdAt),
  limit: 10,
  offset: 0,
  with: {
    posts: {
      columns: {
        id: true,
        title: true,
      },
      limit: 5,
      orderBy: desc(Posts.createdAt),
    },
  },
});
// Type: {
//   id: bigint;
//   username: string;
//   posts: { id: bigint; title: string }[]
// }[]

Relation Types

Many-to-One (fk)

A single related record when the foreign key is on the current table (e.g., post's author):

export const PostsRelations = relations(Posts, () => ({
  author: fk(Posts.userId, Users),
}));

const posts = await db.query(Posts).findMany({
  with: {
    author: {},
  },
});
// Type: { ...; author: { id: bigint; username: string } }[]
// Note: Result is non-null because Posts.userId has notNull constraint

:::tip Nullability The result type depends on the FK column's nullability:

• notNull FK → result is T
• Nullable FK → result is T | null :::

One-to-Many (many)

Multiple related records (e.g., user's posts):

export const UsersRelations = relations(Users, () => ({
  posts: many(Posts, Posts.userId),
}));

const users = await db.query(Users).findMany({
  with: {
    posts: {},
  },
});
// Type: { ...; posts: { id: bigint; userId: bigint; title: string }[] }[]

Options Reference

Top-level options (findMany / findFirst)

Option	Description
columns	Select or exclude columns ({ col: true } or { col: false })
where	Filter condition
orderBy	Sort order (asc(col) or desc(col))
limit	Maximum number of results (number or bigint)
offset	Number of results to skip (number or bigint)
with	Related records to include

Nested relation options (inside with)

Option	many (one-to-many)	fk / one (many-to-one / one-to-one)
columns	✓	✓
with	✓	✓
where	✓	✗ (type error)
orderBy	✓	✗ (type error)
limit	✓	✗ (type error)
offset	✓	✗ (type error)

Methods Reference

Method	Description
.findMany(options)	Fetch multiple records
.findFirst(options)	Fetch first matching record or null