drizzle-orm/relations

76 notes in this subject, read out of this brain and free to use. This is page 2 of 2.

Example: nested extras for calculating field lengths

This example retrieves posts with comments and adds custom fields for content lengths: const res = await db._query.posts.findMany({ extras: (table, { sql }) => ({ contentLength: (sql<number>`length(${table.content})`).as('content_length'), }), with: { comments: { extras: { commentSize: sql<number>`length(${comments.content})`.as('comment_size'), }, }, }, });

Prepared statements in relational queries

Prepared statements are supported in the Drizzle relational query builder to improve query performance. You can define placeholders and execute prepared statements using the relational query API.

Relational queries overview and purpose

Relational queries in Drizzle ORM provide a developer-friendly way to query nested relational data from an SQL database, avoiding multiple joins and complex data mappings. They are an extension to the existing schema definition and query builder that can be opted into based on needs. Drizzle ensures both best-in-class developer experience and performance.

limit and offset in relational queries

The limit parameter restricts the number of records returned. The offset parameter skips a specified number of records. However, offset is only available for top-level queries, not nested entities. Example: await db._query.posts.findMany({ limit: 5, offset: 2, with: { comments: { limit: 3 } } }).

orderBy in relational queries

Drizzle provides orderBy API for ordering results in relational queries. You can use core API with imported operators or callback syntax. Example: await db._query.posts.findMany({ orderBy: [asc(posts.id)] }) or await db._query.posts.findMany({ orderBy: (posts, { asc }) => [asc(posts.id)] }). OrderBy can be applied to nested relations independently.

Example: relational query with findMany and nested with

This example shows fetching all users with their posts and nested comments: const users = await db._query.users.findMany({ with: { posts: { with: { comments: true, }, }, }, });

Relational query initialization with schema

To use relational queries, provide all tables and relations from schema files upon drizzle() initialization, then use the db._query API. If schema is split across multiple files, merge them with spread syntax: drizzle({ schema: { ...schema1, ...schema2 } }).

findMany() relational query method

findMany() retrieves multiple records from a table using the relational query builder. It returns an array of records with their defined columns.

findFirst() relational query method

findFirst() retrieves a single record from a table using the relational query builder. It automatically adds LIMIT 1 to the generated SQL query.

with operator for including related data

The 'with' operator in relational queries lets you combine data from multiple related tables and properly aggregate results. You can nest 'with' statements to fetch related data at multiple levels. Example: await db._query.users.findMany({ with: { posts: { with: { comments: true } } } }) fetches users with their posts and each post's comments.

columns parameter for partial field selection

The 'columns' parameter controls which columns to include or exclude from results. Set columns to true to include, false to exclude. Drizzle performs partial selects at the query level with no additional data transferred from the database. When both true and false options are present, false options are ignored. Example: { columns: { id: true, content: true } } returns only id and content fields.

Nested partial field selection in relations

You can include or exclude columns of nested relations using the columns parameter within nested 'with' statements. Example: await db._query.posts.findMany({ columns: { id: true, content: true }, with: { comments: { columns: { authorId: false } } } }) returns posts with specified columns and comments without the authorId field.

where filters in relational queries

Relational queries support filters and conditions using operators. You can either import operators from 'drizzle-orm' or use callback syntax. Example: await db._query.users.findMany({ where: eq(users.id, 1) }) or await db._query.users.findMany({ where: (users, { eq }) => eq(users.id, 1) }). Filters can be applied to both top-level and nested queries.

createSelectSchema function for SingleStore

The createSelectSchema function from drizzle-orm/valibot generates a Valibot schema that validates the shape of data queried from the database. It can be used to validate API responses. The schema includes all columns from the table definition, and parsing will fail if not all required columns are present in the data being parsed.

Relational Queries require upgrade to v2 syntax

If you were using Relational Queries in Drizzle v0, you must upgrade to v2 of the relational queries syntax. This includes migrating both the relations definition and the query syntax.

Nile tenants table schema from introspection

The built-in Nile tenants table has columns: id (uuid, primary key, default uuid_generate_v7()), name (text), created (timestamp, default LOCALTIMESTAMP), updated (timestamp, default LOCALTIMESTAMP), and deleted (timestamp, nullable).

Give your agent this brain