Relational Queries v2

The examples use a small publishing app to show how relation definitions, queries, and results fit together. Before/After comparisons show what changes from RQBv1. Each feature also has a concrete v2 example.

Tables and relations do different jobs

The publishing app has four PostgreSQL tables:

A user can write many posts. Each post has one author.

A post can have several tags, and a tag can be used on several posts. The postTags table stores those links.

import { boolean, integer, pgTable, primaryKey, text } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: integer().primaryKey(),
  name: text().notNull(),
});

export const posts = pgTable('posts', {
  id: integer().primaryKey(),
  authorId: integer().notNull().references(() => users.id),
  title: text().notNull(),
  published: boolean().notNull(),
});

export const tags = pgTable('tags', {
  id: integer().primaryKey(),
  name: text().notNull(),
});

export const postTags = pgTable('postTags', {
  postId: integer().notNull().references(() => posts.id),
  tagId: integer().notNull().references(() => tags.id),
}, (t) => [primaryKey({ columns: [t.postId, t.tagId] })]);

pgTable() describes a database table. primaryKey() makes the ID unique and required. It does not generate the ID for you. notNull() makes the other columns required.

posts.authorId stores the author’s user ID. .references(() => users.id) adds a foreign key, so the database can check that the author exists.

On postTags, the pair of IDs is the primary key. That prevents the same tag being added to the same post twice.

A relation definition tells db.query how to load related data. Defining posts.author lets you ask for a post with its author. It doesn’t change your database or create a foreign key. Relations fundamentals (Drizzle docs, opens in a new tab)

Relations defined together with defineRelations

In RQBv1, you called relations() separately for each table. In RQBv2, defineRelations() takes your tables and lets you define their relations together.

Here, * as schema is everything exported from schema.ts. The callback’s r helper gives you autocomplete for the tables and their columns.

One-to-one, one-to-many, many-to-one, many-to-many

A post has one author. A user can write many posts. These are two ways to look at the same connection.

From a user to their posts, this is one-to-many. From a post to its author, it is many-to-one. Same link, opposite direction.

“Many” means there can be several, not that there must be: a user can have zero, one, or several posts.

Let’s follow the connections. Choose a relationship type, then press Start or use the step buttons.

The Selected card is the item we’re looking at. Follow the lines to the related items in lime.

The code view follows each step. The Schema, Relation, and Query & result tabs let you inspect each part. Highlighted lines show the columns and relation used in that step.

These snippets use pgTable, integer, and primaryKey from drizzle-orm/pg-core, and defineRelations from drizzle-orm. Other columns are omitted to focus on the link.

The query uses the db client configured below. with includes the named relation, and columns selects the fields to return.

Relationship type

Many-to-oneOne-to-manyOne-to-oneMany-to-many

Start

1. 1
2. 2
3. 3
4. 4

Step 1 of 4. Look at Post 1. Its authorId tells us who wrote it.

Step 2 of 4. The highlighted from and to columns match authorId: 1 to Bob’s id: 1.

Step 3 of 4. with.author includes the author. Because the relation uses one, Post 1 gets a single author: Bob.

Step 4 of 4. Post 2 has the same author as Post 1. Several posts can share an author; each post has one author.

Step 1 of 4. Look at Post 1. Its authorId tells us who wrote it.

Who wrote this post?

Selected Related

SchemaRelationQuery & result

Schema · relation columns

1// Other columns omitted.
2const users = pgTable('users', {
3  id: integer().primaryKey(),
4});
5 
6const posts = pgTable('posts', {
7  id: integer().primaryKey(),
8  authorId: integer().notNull()
9    .references(() => users.id),
10});

Who wrote this post?

Selected Related

SchemaRelationQuery & result

Relation definition

1const relations = defineRelations(
2  { users, posts },
3  (r) => ({
4    posts: {
5      author: r.one.users({
6        from: r.posts.authorId,
7        to: r.users.id,
8      }),
9    },
10  }),
11);

Who wrote this post?

Selected Related

SchemaRelationQuery & result

Query

1const result = await db.query.posts.findFirst({
2  where: { id: 1 },
3  columns: { id: true },
4  with: {
5    author: {
6      columns: { id: true },
7    },
8  },
9});

Result

1{
2  "id": 1,
3  "author": {
4    "id": 1
5  }
6}

Who wrote this post?

Selected Related

SchemaRelationQuery & result

Query

1const result = await db.query.posts.findFirst({
2  where: { id: 2 },
3  columns: { id: true },
4  with: {
5    author: {
6      columns: { id: true },
7    },
8  },
9});

Result

1{
2  "id": 2,
3  "author": {
4    "id": 1
5  }
6}

Who wrote this post?

Selected Related

SchemaRelationQuery & result

Schema · relation columns

1// Other columns omitted.
2const users = pgTable('users', {
3  id: integer().primaryKey(),
4});
5 
6const posts = pgTable('posts', {
7  id: integer().primaryKey(),
8  authorId: integer().notNull()
9    .references(() => users.id),
10});

Ask what you want to load. A post’s author? Use one to load that author. A user’s posts? Use many to load a list of their posts.

This comparison shows two relations: users.posts loads a user’s posts, and posts.author loads a post’s author.

import { relations } from 'drizzle-orm';
import { users, posts } from './schema';

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

import { defineRelations } from 'drizzle-orm';
import * as schema from './schema';

export const relations = defineRelations(schema, (r) => ({
  users: {
    posts: r.many.posts({
      from: r.users.id,
      to: r.posts.authorId,
    }),
  },

  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),
  },
}));

Here’s how the APIs line up:

For posts.author, from is posts.authorId and to is users.id.

For users.posts, the direction is reversed: from is users.id and to is posts.authorId.

Relation names are chosen by the application. Here, posts and author are the names used in with and returned in the results.

Links that use multiple columns and the change from relationName to alias appear in less common relation definitions. The migration guide (Drizzle docs, opens in a new tab) has the full API comparison.

A separate relations option in drizzle

The legacy configuration combined tables and relations in schema. In v1, the schema option is gone from drizzle(). RQBv2 receives the result of defineRelations() through the relations option instead.

Your table definitions and schema.ts file still stay. It is the initialization option that was removed, not the schema you use to describe your database.

The configuration difference is visible in db.ts:

import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';
import * as oldRelations from './relations';

export const db = drizzle('postgresql://localhost:5432/blog', {
  schema: { ...schema, ...oldRelations },
});

import { drizzle } from 'drizzle-orm/node-postgres';
import { relations } from './relations';

export const db = drizzle('postgresql://localhost:5432/blog', { relations });

The examples keep tables in schema.ts and relations in relations.ts. db.query uses the relations supplied to drizzle() to load related data.

For MySQL, RQBv2 no longer needs the mode: 'default' or mode: 'planetscale' option. Connection details depend on the driver. Configuration migration (Drizzle docs, opens in a new tab)

Use db.query without relationships

RQB is also useful for basic queries against a single table. You might prefer db.query.users.findMany() or findFirst() with options such as where, columns, and orderBy, even when you do not need to load related data.

These queries only need to know the table and its columns. Relationships become necessary when a query needs to follow a connection to another table. Call defineRelations(schema) without a callback to register your tables, then pass its result to the relations option:

import { defineRelations } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';

const db = drizzle('postgresql://localhost:5432/blog', {
  relations: defineRelations(schema),
});

const result = await db.query.users.findMany({
  orderBy: { id: 'asc' },
});

This example uses the schema.ts above and returns users ordered by ID, without loading their posts. Despite its name, defineRelations() also handles this table-only setup. If you previously passed schema just to use basic db.query methods, this is the replacement in v1.

Add the optional callback when you need to define relationships for queries such as with: { posts: true }.

Foreign keys in your table definitions do not automatically create those query relationships.

If you only use the SQL-style builder, such as db.select().from(schema.users), you do not need to pass relations at all.

Users and their posts in one query

Each query example uses db from the configuration above. The examples share these users and posts so their results are easy to compare.

The sample users are:

And these four posts. authorId identifies the author in the table above. published is true for a published post and false for a draft:

Emma has no posts.

This query loads Bob and his posts. where chooses Bob by his ID, with.posts includes his posts, and orderBy sorts them. findMany() returns a list, even when only one user matches:

const result = await db.query.users.findMany({
  where: (user, { eq }) => eq(user.id, 1),
  with: {
    posts: { orderBy: (post, { asc }) => [asc(post.id)] },
  },
});

const result = await db.query.users.findMany({
  where: { id: 1 },
  with: {
    posts: { orderBy: { id: 'asc' } },
  },
});

where: { id: 1 } selects Bob. His result includes Post 1 and Post 2, in that order.

Post 2 is included even though it is a draft: the query has no filter on published.

Combine filters with AND, OR and NOT

Multiple fields in where mean all conditions must match. This example selects posts that are published and written by user 1. Only post 1 matches.

columns: { id: true } keeps just the ID in the result:

const result = await db.query.posts.findMany({
  columns: { id: true },
  where: { published: true, authorId: 1 },
});

Use AND to write the conditions as a list. This produces the same result:

const result = await db.query.posts.findMany({
  columns: { id: true },
  where: { AND: [{ published: true }, { authorId: 1 }] },
});

Use OR when either condition can match. With the same two conditions, we now get every published post, plus any post by user 1. Post 1, Post 2 and Post 4 match.

const result = await db.query.posts.findMany({
  columns: { id: true },
  where: { OR: [{ published: true }, { authorId: 1 }] },
  orderBy: { id: 'asc' },
});

Use NOT to exclude a condition. This returns the unpublished posts, 2 and 3:

const result = await db.query.posts.findMany({
  columns: { id: true },
  where: { NOT: { published: true } },
  orderBy: { id: 'asc' },
});

You can also nest these groups. See the filter reference (Drizzle docs, opens in a new tab) for the other operators, including ranges, lists of values, and null checks.

Choose the fields you return

columns: { title: true } includes only title. To return everything except particular columns, mark those columns false instead:

const result = await db.query.posts.findFirst({
  where: { id: 1 },
  columns: { authorId: false, published: false },
});

This returns { id: 1, title: 'Post 1' }. findFirst() returns the first matching post, or undefined if nothing matches.

Use either true to pick columns or false to leave columns out. If you mix them, the true entries take priority.

To return only relations, use columns: {} with with. Partial field selection (Drizzle docs, opens in a new tab)

Filter users, included posts, or both

There are two different questions:

1. Which users should be returned?
2. Which posts should appear inside each returned user?

Put the condition in where to choose users who have at least one published post:

const result = await db.query.users.findMany({
  where: { posts: { published: true } },
  orderBy: { id: 'asc' },
  with: { posts: true },
});

We get Bob and Alice. Bob still has both Post 1 and Post 2: with: { posts: true } includes all his posts.

Choosing users based on their posts is new in RQBv2. Filtering by relations (Drizzle docs, opens in a new tab)

Put where inside with.posts to choose which posts each user gets:

const result = await db.query.users.findMany({
  orderBy: { id: 'asc' },
  with: {
    posts: { where: { published: true } },
  },
});

All four users remain. Sam and Emma have posts: [], an empty list, because neither has a published post.

Combine both conditions when you want users who have published posts and only those published posts in their result:

const result = await db.query.users.findMany({
  where: { posts: { published: true } },
  with: {
    posts: {
      where: { published: true },
      columns: { title: true },
    },
  },
  orderBy: { name: 'asc' },
});

columns: { title: true } keeps just the title for each post. orderBy sorts the users by name, so Alice comes before Bob:

[
  { "id": 3, "name": "Alice", "posts": [{ "title": "Post 4" }] },
  { "id": 1, "name": "Bob", "posts": [{ "title": "Post 1" }] }
]

Put where at the top to choose the users. Put it inside with.posts to choose which posts come with each user. Relational query reference (Drizzle docs, opens in a new tab)

Filters defined once and reused

A relation can carry its own filter. Here, users.publishedPosts includes where: { published: true }, so every query using that relation gets only published posts. The complete definition also includes posts and author:

import { defineRelations } from 'drizzle-orm';
import * as schema from './schema';

export const relations = defineRelations(schema, (r) => ({
  users: {
    posts: r.many.posts({
      from: r.users.id,
      to: r.posts.authorId,
    }),

    publishedPosts: r.many.posts({
      from: r.users.id,
      to: r.posts.authorId,
      where: { published: true },
    }),
  },

  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),
  },
}));

A query includes the filtered relation by name:

const result = await db.query.users.findMany({
  orderBy: { id: 'asc' },
  with: { publishedPosts: true },
});

Bob gets Post 1, and Alice gets Post 4. Sam and Emma remain in the result with publishedPosts: []. The filter chooses which posts to include without removing any users.

A second query loads only Bob (user 1) with the same relation. The filter does not need to be repeated:

const result = await db.query.users.findMany({
  where: { id: 1 },
  with: { publishedPosts: true },
});

The two relations can coexist: posts includes all posts, while publishedPosts includes only published ones. Predefined filters (Drizzle docs, opens in a new tab)

Paginate an included relation

In v2, you can use offset inside with too. Here, offset: 1 skips each user’s first post and limit: 1 takes the next one:

const result = await db.query.users.findMany({
  orderBy: { id: 'asc' },
  with: {
    posts: {
      orderBy: { id: 'asc' },
      offset: 1,
      limit: 1,
    },
  },
});

The orderBy, offset, and limit inside with.posts apply separately to each user’s posts. This query still returns all users.

Bob gets Post 2. The other users have no second post, so they get posts: [].

Sorting by the unique post ID keeps the order predictable. Nested offset (Drizzle docs, opens in a new tab)

Direct access to many-to-many relations with through

A post can have many tags, and a tag can belong to many posts. The postTags table links posts.id to tags.id.

For this example, tag 1 is “SQL” and tag 2 is “TypeScript”. Post 1 has both tags, and post 4 has “SQL”. The postTags pairs are (1, 1), (1, 2), and (4, 1).

In RQBv1, you go through postTags to get the tags. Each item in post.postTags contains a tag. To get a list of tags, you’d write post.postTags.map(link => link.tag).

The posts.tags relation uses through to connect posts directly to their tags. This comparison shows the complete definitions:

import { relations } from 'drizzle-orm';
import { users, posts, tags, postTags } from './schema';

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),

  postTags: many(postTags),
}));

export const postTagsRelations = relations(postTags, ({ one }) => ({
  post: one(posts, {
    fields: [postTags.postId],
    references: [posts.id],
  }),

  tag: one(tags, {
    fields: [postTags.tagId],
    references: [tags.id],
  }),
}));

import { defineRelations } from 'drizzle-orm';
import * as schema from './schema';

export const relations = defineRelations(schema, (r) => ({
  users: {
    posts: r.many.posts({
      from: r.users.id,
      to: r.posts.authorId,
    }),

    publishedPosts: r.many.posts({
      from: r.users.id,
      to: r.posts.authorId,
      where: { published: true },
    }),
  },

  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),

    tags: r.many.tags({
      from: r.posts.id.through(r.postTags.postId),
      to: r.tags.id.through(r.postTags.tagId),
    }),
  },
}));

The two through calls tell Drizzle how to follow postTags:

• r.posts.id.through(r.postTags.postId) links a post to its linking rows.
• r.tags.id.through(r.postTags.tagId) links those rows to tags.

Both queries below keep the post’s ID and title. The v2 query asks for tags directly:

const result = await db.query.posts.findMany({
  columns: { id: true, title: true },
  orderBy: (post, { asc }) => [asc(post.id)],
  with: {
    postTags: {
      orderBy: (link, { asc }) => [asc(link.tagId)],
      columns: {},
      with: { tag: true },
    },
  },
});

const result = await db.query.posts.findMany({
  columns: { id: true, title: true },
  orderBy: { id: 'asc' },
  with: { tags: { orderBy: { id: 'asc' } } },
});

The v2 query returns:

[
  {
    "id": 1,
    "title": "Post 1",
    "tags": [
      { "id": 1, "name": "SQL" },
      { "id": 2, "name": "TypeScript" }
    ]
  },
  { "id": 2, "title": "Post 2", "tags": [] },
  { "id": 3, "title": "Post 3", "tags": [] },
  { "id": 4, "title": "Post 4", "tags": [{ "id": 1, "name": "SQL" }] }
]

Post 1’s tags list contains SQL and TypeScript. Post 4 has SQL, and the other posts have no tags.

You can read each post’s tags directly instead of calling post.postTags.map(link => link.tag). The query uses the same tags name in with. Many-to-many migration (Drizzle docs, opens in a new tab)

A separate postTags relation remains useful when the linking table stores extra data, such as the date a tag was added.

Flexible relation definitions

Define just the side you need

A many relation can stand on its own. With explicit from and to columns, users.posts does not need a reverse posts.author definition:

import { defineRelations } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';

const relations = defineRelations(schema, (r) => ({
  users: {
    posts: r.many.posts({
      from: r.users.id,
      to: r.posts.authorId,
    }),
  },
}));

const db = drizzle('postgresql://localhost:5432/blog', { relations });

const result = await db.query.users.findMany({
  where: { id: 1 }, with: { posts: true },
});

This is an alternative to our full relations.ts. It only defines users.posts. Because it includes from and to, it doesn’t need a posts.author relation to work out the link. Define many without one (Drizzle docs, opens in a new tab)

Keep definitions in separate files

defineRelationsPart() supports separate groups of relations that are combined in drizzle():

import { defineRelationsPart } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';

export const usersPart = defineRelationsPart(schema, (r) => ({
  users: {
    posts: r.many.posts({
      from: r.users.id,
      to: r.posts.authorId,
    }),
  },
}));

export const postsPart = defineRelationsPart(schema, (r) => ({
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),
  },
}));

export const db = drizzle('postgresql://localhost:5432/blog', {
  relations: { ...usersPart, ...postsPart },
});

The two parts can live in separate files, such as users.relations.ts and posts.relations.ts. A single relations.ts file is also supported.

In rc4, each part is built separately. usersPart can’t use a relation from postsPart to work out which columns to match. That’s why both parts above include from and to.

These parts register only users and posts for relational queries. To keep every schema table available, create a base with defineRelations(schema) and combine it as { ...base, ...usersPart, ...postsPart }.

Each table’s relations belong in one part. If two parts both define users, the later part replaces all the earlier part’s users relations.

Handle less common relation definitions

The next examples show separate schema variations: a guaranteed author, an optional reviewer, and notes attached to post/tag pairs. Each includes its own relation definitions and db configuration.

Tell TypeScript when a relation always exists

By default, a one relation can return null. In our schema, posts.authorId is required and references an existing user, so every post has an author. We can express that guarantee to TypeScript with optional: false:

import { defineRelations } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';

const relations = defineRelations(schema, (r) => ({
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
      optional: false,
    }),
  },
}));

const db = drizzle('postgresql://localhost:5432/blog', { relations });

const result = await db.query.posts.findMany({
  where: { id: 1 },
  with: { author: true },
});

TypeScript now lets you use post.author.name without checking for null.

Only set optional: false when the matching row is guaranteed to exist. It doesn’t check your data or add a database constraint. Leave it out if the author can be missing or filtered out. Relation optionality (Drizzle docs, opens in a new tab)

Distinguish two links to the same table

Suppose a post has both an author and a reviewer. Both are rows in users, but they play different roles.

This variation includes reviewerId. It’s nullable because a post might not have a reviewer yet. The code shows only the columns needed for these links.

The legacy relationName option becomes alias. Matching aliases tell Drizzle which reverse relation belongs to each link:

import { defineRelations } from 'drizzle-orm';
import { integer, pgTable } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';
import { users } from './schema';

const posts = pgTable('posts', {
  id: integer().primaryKey(),
  authorId: integer().notNull().references(() => users.id),
  reviewerId: integer().references(() => users.id),
});

const relations = defineRelations({ users, posts }, (r) => ({
  users: {
    writtenPosts: r.many.posts({ alias: 'author' }),

    reviewedPosts: r.many.posts({ alias: 'reviewer' }),
  },

  posts: {
    author: r.one.users({
      from: r.posts.authorId, to: r.users.id, alias: 'author',
    }),

    reviewer: r.one.users({
      from: r.posts.reviewerId, to: r.users.id, alias: 'reviewer',
    }),
  },
}));

const db = drizzle('postgresql://localhost:5432/blog', { relations });

const result = await db.query.users.findMany({
  where: { id: 2 },
  with: { writtenPosts: true, reviewedPosts: true },
});

For this example, user 2 wrote post 3 and reviews post 1. The query returns post 3 in writtenPosts and post 1 in reviewedPosts.

Notice that the reviewer link uses reviewerId, not authorId.

writtenPosts and reviewedPosts are the names callers use in with.

The aliases 'author' and 'reviewer' tell Drizzle which definitions belong together. The result still uses the names writtenPosts and reviewedPosts. Disambiguating relations (Drizzle docs, opens in a new tab)

Link rows using more than one column

Single-column links use from: column and to: column. Both also accept arrays. For a multi-column link, each position in from matches the same position in to.

Our postTags rows are identified by both a post ID and a tag ID. Suppose we add an optional note for each post/tag pair. The tagNotes table below stores those notes:

import { defineRelations } from 'drizzle-orm';
import { integer, pgTable, primaryKey, text } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';
import { postTags } from './schema';

const tagNotes = pgTable('tagNotes', {
  postId: integer().notNull(),
  tagId: integer().notNull(),
  note: text().notNull(),
}, (t) => [primaryKey({ columns: [t.postId, t.tagId] })]);

const relations = defineRelations({ postTags, tagNotes }, (r) => ({
  postTags: {
    note: r.one.tagNotes({
      from: [r.postTags.postId, r.postTags.tagId],
      to: [r.tagNotes.postId, r.tagNotes.tagId],
    }),
  },
}));

const db = drizzle('postgresql://localhost:5432/blog', { relations });

const result = await db.query.postTags.findMany({
  orderBy: { postId: 'asc', tagId: 'asc' },
  with: { note: true },
});

The sample data has notes for pairs (1, 1) and (4, 1). Each pair gets its own note in the result. Pair (1, 2) gets note: null.

Matching both IDs prevents post 1’s SQL note from appearing on post 4. The from/to options (Drizzle docs, opens in a new tab)

Add computed fields and reuse queries

Computed fields and prepared queries were already available in RQBv1. RQBv2 keeps these capabilities alongside its new relation features.

Add a computed field with extras

This query returns a lowercase title while keeping the original title in the database. Each extras property can provide its own callback:

const result = await db.query.posts.findMany({
  columns: { id: true },
  extras: {
    lowerTitle: (post, { sql }) => sql<string>`lower(${post.title})`,
  },
  orderBy: { id: 'asc' },
});

Post 1 becomes { id: 1, lowerTitle: 'post 1' }. The key lowerTitle determines the returned field name. An SQL .as() alias on an extra is ignored.

sql<string> tells TypeScript what to expect. It doesn’t convert the value at runtime. Computed fields (Drizzle docs, opens in a new tab)

Count related rows with a subquery

A computed field can also count each user’s posts. db.$count() builds a count query, and eq() means equal. Matching posts.authorId to user.id counts only that user’s posts:

import { eq } from 'drizzle-orm';
import { posts } from './schema';

const result = await db.query.users.findMany({
  columns: { id: true },
  extras: {
    postCount: (user) => db.$count(posts, eq(posts.authorId, user.id)),
  },
  orderBy: { id: 'asc' },
});

Users 1, 2, 3 and 4 get counts of 2, 1, 1 and 0.

The count query uses user.id from the surrounding query. That’s what makes it a correlated subquery.

For counts in extras, use a subquery like this. A bare count(*) over the main query’s rows isn’t supported. Subqueries in extras (Drizzle docs, opens in a new tab)

Write a custom SQL filter

Use RAW when your filter needs a SQL expression:

const result = await db.query.posts.findMany({
  where: {
    RAW: (post, { sql }) => sql`lower(${post.title}) = 'post 1'`,
  },
  orderBy: { id: 'asc' },
});

lower() converts the title to lowercase. Comparing it with 'post 1' finds Post 1 without depending on capitalization.

Use the callback’s post.title here. It refers to the posts being filtered in this query. Importing posts.title directly can refer to the wrong SQL table name when Drizzle loads related data. Query filters and callback scope (Drizzle docs, opens in a new tab)

Use callback columns in custom sorting too

Use orderBy: { title: 'asc' } to sort by title. For an expression, such as sorting titles without considering case, use a callback:

const result = await db.query.posts.findMany({
  columns: { id: true, title: true },
  orderBy: (post, { sql }) => sql`lower(${post.title}) asc`,
});

This returns Post 1, Post 2, Post 3 and Post 4. Use the callback’s post for columns here, just as we did in RAW. Callback scope and sorting (Drizzle docs, opens in a new tab)

Reuse a query with placeholders

With a prepared query, you write the query once and pass in values when you run it. Here we reuse the same query to load posts by two different authors:

import { placeholder } from 'drizzle-orm';

const postsByAuthor = db.query.posts.findMany({
  columns: { id: true },
  where: { authorId: placeholder('authorId') },
  orderBy: { id: 'asc' },
}).prepare('posts_by_author');

const result = await postsByAuthor.execute({ authorId: 1 });

const otherPosts = await postsByAuthor.execute({ authorId: 3 });

The first call returns posts 1 and 2. The second returns post 4. The authorId placeholder gets its value from each execute() call.

You can also use placeholders for limit and offset. Check your driver’s support for prepared queries before using this pattern. Prepared relational queries (Drizzle docs, opens in a new tab)

If you use custom column types

Custom column types can need special handling when their values appear in relational results.

When Drizzle loads related data in PostgreSQL, it can use JSON to bring the results back. A codec tells Drizzle how to read a value from those results. For example, a PostgreSQL bigint may be too large for a JavaScript number to represent exactly.

Suppose each post has a statistics row with a view count. This separate schema example adds postStats and uses the driver’s 'bigint' codec for its view count:

import { defineRelations } from 'drizzle-orm';
import { customType, integer, pgTable } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';
import { posts } from './schema';

const customBigint = customType<{ data: bigint; driverData: string }>({
  dataType: () => 'bigint',
  codec: 'bigint',
});

const postStats = pgTable('postStats', {
  postId: integer().primaryKey().references(() => posts.id),
  views: customBigint().notNull(),
});

const relations = defineRelations({ posts, postStats }, (r) => ({
  posts: {
    stats: r.one.postStats({ from: r.posts.id, to: r.postStats.postId }),
  },
}));

const db = drizzle('postgresql://localhost:5432/blog', { relations });

const result = await db.query.posts.findFirst({
  where: { id: 1 },
  columns: { id: true },
  with: { stats: { columns: { views: true } } },
});

With 9007199254740993 stored for post 1, the result’s stats.views is the exact JavaScript bigint 9007199254740993n.

For a regular bigint column, use the built-in bigint builder with bigint mode. This example is for an existing custom type that needs a codec.

A custom type’s toDriver and fromDriver functions need to agree with the codec on the value’s format. Available codecs depend on the driver. Custom types and codecs (Drizzle docs, opens in a new tab)

Can drizzle-kit pull do part of the migration?

For an existing database, drizzle-kit pull can generate relation definitions in the new syntax:

❯ npx drizzle-kit pull

❯ pnpm drizzle-kit pull

❯ yarn drizzle-kit pull

❯ bunx drizzle-kit pull

The command reads the database and writes schema and relation files locally. Use the generated definitions as a starting point, then review the relation names and filters for your app.

Generated relations and migration options (Drizzle docs, opens in a new tab) covers this workflow.

Further reading

The examples are typechecked against legacy Drizzle and rc4, and their results are tested against an in-memory PostgreSQL database. Connection details and support for prepared queries and codecs depend on the driver.

The Relational Queries v1 to v2 guide (Drizzle docs, opens in a new tab) covers migration steps. The Relations (Drizzle docs, opens in a new tab) and Queries (Drizzle docs, opens in a new tab) references cover the APIs.

For other changes from 0.x to v1, see Migrations in v1 and the official upgrade guide (Drizzle docs, opens in a new tab).