relations

11 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Foreign key actions with references method

When using the `references()` method on a column, pass foreign key actions as the second argument: `references(() => users.id, { onDelete: 'cascade', onUpdate: 'cascade' })`. The UpdateDeleteAction type includes: 'cascade', 'restrict', 'no action', 'set null', 'set default'.

Foreign key actions in CockroachDB

CockroachDB supports the following foreign key actions: CASCADE (deletes related rows when parent is deleted), NO ACTION (default, prevents deletion if related rows exist), RESTRICT (same as NO ACTION for compatibility), SET DEFAULT (sets foreign key column to default value when parent is deleted), SET NULL (sets foreign key column to NULL when parent is deleted). ON UPDATE actions work analogously to ON DELETE, with CASCADE copying updated values to referencing rows.

Cursor-based pagination with relational queries

Drizzle's relational queries API simplifies cursor-based pagination. Use db.query.users.findMany() with where and orderBy callbacks. Example: await db.query.users.findMany({ where: (users, { gt }) => (cursor ? gt(users.id, cursor) : undefined), orderBy: (users, { asc }) => asc(users.id), limit: pageSize }). The cursor is the id of the last row from the previous page.

Add extra computed columns with relational queries

To add computed or extra columns to relational queries while keeping all table columns, use the `extras` option: `await db.query.posts.findMany({ extras: { titleLength: (t) => sql<number>\`length(${t.title})\`.as("title_length") } });`. The result includes all original columns plus the extra computed columns.

Exclude columns with relational queries

To exclude specific columns in relational queries, use the `columns` option with false values: `await db.query.posts.findMany({ columns: { content: false } });`. The result includes all columns except those marked as false.

Include or exclude columns in related tables with relational queries

When using relational queries with the `with` option to load related tables, you can control column inclusion and exclusion for each relation. Use `columns` option within each relation to specify which columns to include or exclude: `await db.query.posts.findMany({ columns: { id: true }, with: { comments: { columns: { userId: false, postId: false } }, user: true } });`

Select all columns with relational queries

To include all columns using relational queries, call the query method without a columns option: `await db.query.posts.findMany();`. This returns all columns from the table with proper types.

Select specific columns with relational queries

To include only specific columns in relational queries, use the `columns` option with a truthy map: `await db.query.posts.findMany({ columns: { title: true } });`. Only the specified columns are returned.

Relational queries API for pagination

Drizzle's relational queries API provides a simpler way to implement limit/offset pagination. Use db.query.users.findMany({ orderBy: (users, { asc }) => asc(users.id), limit: pageSize, offset: (page - 1) * pageSize }).

Relational queries with Queries API

Drizzle provides a Queries API for fetching relational, nested data from the database efficiently without manually handling joins or data mapping. Example: const result = await db._query.users.findMany({ with: { posts: true } });

Relational query API example

Example of relational query: `const result = await db._query.users.findMany({ with: { posts: true } });` - fetches users with their related posts using the relational Query API.

Give your agent this brain