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.

Methods

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

Prerequisites

Before using relational queries, define relations in your schema:

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

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

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

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

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 }[]

Alias Selections

Use select to rename output keys and project computed expressions, mirroring .select() projections. Keys are output aliases; values are columns of the queried table or scalar SqlFns (e.g. lower(...)):

import { lower } from "durcno";

const users = await db.query(Users).findMany({
  select: {
    userId: Users.id,
    name: Users.username,
    lowered: lower(Users.username),
  },
});
// Type: { userId: bigint; name: string; lowered: string }[]

select works in nested with relations too (each level selects from its own table):

const posts = await db.query(Posts).findMany({
  select: {
    postId: Posts.id,
    heading: Posts.title,
  },
  with: {
    comments: {
      select: {
        commentId: Comments.id,
        text: Comments.body,
      },
    },
  },
});

note

select and columns are mutually exclusive at each level (including inside with). The TypeScript compiler rejects combining them, and a runtime error is thrown otherwise. Aggregate functions (e.g. count()) are not supported in select — use scalar columns or scalar SqlFns.

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 { desc, eq } 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 { and, eq, 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 { desc, eq } 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 }[] }[]

Common Table Expressions (CTEs)

Attach CTEs to relational queries with db.with(...):

import { db, eq, isIn } from "durcno";
import { Users } from "./db/schema.ts";

const activeUsers = db.with("activeUsers").as(
  db
    .from(Users)
    .select(() => ({ id: Users.id }))
    .where(() => eq(Users.status, "active")),
);

const users = await db
  .with(activeUsers)
  .query(Users)
  .findMany({
    where: isIn(
      Users.id,
      db.from(activeUsers).select(() => ({ id: activeUsers.id })),
    ),
    with: {
      posts: {},
    },
  });

You can also use subquery SQL functions like exists(...) and isIn(...) directly inside the select option to project boolean flags computed from a CTE:

import { db, eq, exists, isIn, lower } from "durcno";
import { Posts, Users } from "./db/schema.ts";

const users = await db
  .with(activeUsers)
  .query(Users)
  .findMany({
    select: {
      userId: Users.id,
      name: Users.username,
      lowered: lower(Users.username),
      // Correlated subquery using exists() against the CTE:
      isActive: exists(
        db
          .from(activeUsers)
          .select(() => ({ id: activeUsers.id }))
          .where(() => eq(activeUsers.id, Users.id)),
      ),
      // Set-membership check using isIn() against the CTE:
      isInActiveList: isIn(
        Users.id,
        db.from(activeUsers).select(() => ({ id: activeUsers.id })),
      ),
    },
    with: {
      posts: {
        select: {
          postId: Posts.id,
          heading: Posts.title,
        },
      },
    },
  });
// Type: {
//   userId: bigint;
//   name: string;
//   lowered: string;
//   isActive: boolean;
//   isInActiveList: boolean;
//   posts: { postId: bigint; heading: string | null }[];
// }[]

See With for more CTE details and patterns.

Options Reference

Top-level options (findMany / findFirst)

Option	Description
columns	Select or exclude columns ({ col: true } or { col: false })
select	Alias selections ({ alias: column } or { alias: scalarFn(...) }, exclusive w/ columns)
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	✓	✓
select	✓	✓
with	✓	✓
where	✓	✗ (type error)
orderBy	✓	✗ (type error)
limit	✓	✗ (type error)
offset	✓	✗ (type error)