Relational Queries

Overview

Understanding Relational Queries

Relational Queries allow you to fetch nested relations and related records in a structured, hierarchical format without writing manual SQL JOIN statements. Instead of manually stitching together rows from multiple tables and mapping them in your application code, you can use the built-in query syntax to query related tables cleanly.

Key Concepts

To use relational queries effectively, you should understand the following core components:

• findMany(): Retrieves a list of records matching your query criteria, returning an array of nested objects.
• findFirst(): Retrieves a single record (the first match), returning an object or undefined.
• with: A configuration property used to specify which related records or nested relations to include in the result.
• columns: A property used to include or exclude specific columns from the selected table.
• extras: Allows you to add custom computed fields or SQL expressions to your query selection.

Querying Nested Relations

You can fetch related records at multiple levels of nesting using the with property. The query builder fully preserves type safety and returns neatly structured objects that reflect your relational tree.

Copyconst result = await db.query.users.findMany({
  where: (users, { sql }) => sql`char_length(${users.name} > 1)`,
  limit: 10,
  orderBy: (users, { asc, desc }) => [asc(users.name), desc(users.id)],
  with: {
    posts: {
      with: {
        author: true,
        comments: {
          with: {
            author: true,
          },
        },
      },
    },
  },
});

Selecting Specific Columns and Extras

By default, all table columns are selected. You can customize which columns to retrieve, or add computed fields using extras.

Copyconst result = await db.query.users.findMany({
  columns: {
    id: true,
    name: true,
  },
  with: {
    posts: {
      columns: {
        authorId: true,
      },
      extras: {
        lowerTitle: sql<string>`lower(${posts.title})`.as('lower_title'),
      },
    },
  },
});

Configuration Options Reference

The following options can be passed to findMany and findFirst query configurations:

Related

• Querying Records
• Declaring Schemas