Drizzle Queries

Drizzle ORM is designed to be a thin typed layer on top of SQL. We truly believe we’ve designed the best way to operate an SQL database from TypeScript and it’s time to make it better.

Relational queries are meant to provide you with a great developer experience for querying nested relational data from an SQL database, avoiding multiple joins and complex data mappings.

It is an extension to the existing schema definition and query builder. You can opt-in to use it based on your needs. We’ve made sure you have both the best-in-class developer experience and performance.

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

const db = drizzle(client, { schema });

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

[{
  id: 10,
  name: "Dan",
  posts: [
    {
      id: 1,
      content: "SQL is awesome",
      authorId: 10,
    },
    {
      id: 2,
      content: "But check relational queries",
      authorId: 10,
    }
  ]
}]

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

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

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  content: text('content').notNull(),
  authorId: integer('author_id').notNull(),
});

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

⚠️ If you have SQL schema declared in multiple files you can do it like that

import * as schema1 from './schema1';
import * as schema2 from './schema2';
import { drizzle } from 'drizzle-orm/...';

const db = drizzle(client, { schema: { ...schema1, ...schema2 } });

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

// schema declaration in the first file

// schema declaration in the second file

Modes

Drizzle relational queries always generate exactly one SQL statement to run on the database and it has certain caveats. To have best in class support for every database out there we’ve introduced modes.

Drizzle relational queries use lateral joins of subqueries under the hood and for now PlanetScale does not support them.

When using mysql2 driver with regular MySQL database — you should specify mode: "default" When using mysql2 driver with PlanetScale — you need to specify mode: "planetscale"

import * as schema from './schema';
import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";

const connection = await mysql.createConnection({
  uri: process.env.PLANETSCALE_DATABASE_URL,
});

const db = drizzle(connection, { schema, mode: 'planetscale' });

Declaring relations

One-to-one

Drizzle ORM provides you an API to define one-to-one relations between tables with the relations operator.

An example of a one-to-one relation between users and users, where a user can invite another (this example uses a self reference):

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

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

export const usersRelations = relations(users, ({ one }) => ({
  invitee: one(users, {
    fields: [users.invitedBy],
    references: [users.id],
  }),
}));

Another example would be a user having a profile information stored in separate table. In this case, because the foreign key is stored in the “profile_info” table, the user relation have neither fields or references. This tells Typescript that user.profileInfo is nullable:

import { pgTable, serial, text, integer, jsonb } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

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

export const usersRelations = relations(users, ({ one }) => ({
  profileInfo: one(profileInfo),
}));

export const profileInfo = pgTable('profile_info', {
  id: serial('id').primaryKey(),
  userId: integer('user_id').references(() => users.id),
  metadata: jsonb('metadata'),
});

const user = await queryUserWithProfileInfo();
//____^? type { id: number, profileInfo: { ... } | null  }

One-to-many

Drizzle ORM provides you an API to define one-to-many relations between tables with relations operator.

Example of one-to-many relation between users and posts they’ve written:

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

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

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  content: text('content'),
  authorId: integer('author_id'),
});

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

Now lets add comments to the posts:

...

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  content: text('content'),
  authorId: integer('author_id'),
});

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

export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  text: text('text'),
  authorId: integer('author_id'),
  postId: integer('post_id'),
});

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

Many-to-many

Drizzle ORM provides you an API to define many-to-many relations between tables through so called junction or join tables, they have to be explicitly defined and store associations between related tables.

Example of many-to-many relation between users and groups:

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

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

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

export const groups = pgTable('groups', {
  id: serial('id').primaryKey(),
  name: text('name'),
});

export const groupsRelations = relations(groups, ({ many }) => ({
  usersToGroups: many(usersToGroups),
}));

export const usersToGroups = pgTable(
  'users_to_groups',
  {
    userId: integer('user_id')
      .notNull()
      .references(() => users.id),
    groupId: integer('group_id')
      .notNull()
      .references(() => groups.id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.userId, t.groupId] }),
  }),
);

export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
  group: one(groups, {
    fields: [usersToGroups.groupId],
    references: [groups.id],
  }),
  user: one(users, {
    fields: [usersToGroups.userId],
    references: [users.id],
  }),
}));

Foreign keys

You might’ve noticed that relations look similar to foreign keys — they even have a references property. So what’s the difference?

While foreign keys serve a similar purpose, defining relations between tables, they work on a different level compared to relations.

Foreign keys are a database level constraint, they are checked on every insert/update/delete operation and throw an error if a constraint is violated. On the other hand, relations are a higher level abstraction, they are used to define relations between tables on the application level only. They do not affect the database schema in any way and do not create foreign keys implicitly.

What this means is relations and foreign keys can be used together, but they are not dependent on each other. You can define relations without using foreign keys (and vice versa), which allows them to be used with databases that do not support foreign keys.

The following two examples will work exactly the same in terms of querying the data using Drizzle relational queries.

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

export const usersRelations = relations(users, ({ one, many }) => ({
  profileInfo: one(users, {
    fields: [profileInfo.userId],
    references: [users.id],
  }),
}));

export const profileInfo = pgTable('profile_info', {
  id: serial('id').primaryKey(),
  userId: integer("user_id"),
  metadata: jsonb("metadata"),
});

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

export const usersRelations = relations(users, ({ one, many }) => ({
  profileInfo: one(users, {
    fields: [profileInfo.userId],
    references: [users.id],
  }),
}));

export const profileInfo = pgTable('profile_info', {
  id: serial('id').primaryKey(),
  userId: integer("user_id").references(() => users.id),
  metadata: jsonb("metadata"),
});

Foreign key actions

for more information check postgres foreign keys docs

You can specify actions that should occur when the referenced data in the parent table is modified. These actions are known as “foreign key actions.” PostgreSQL provides several options for these actions.

On Delete/ Update Actions

• CASCADE: When a row in the parent table is deleted, all corresponding rows in the child table will also be deleted. This ensures that no orphaned rows exist in the child table.

• NO ACTION: This is the default action. It prevents the deletion of a row in the parent table if there are related rows in the child table. The DELETE operation in the parent table will fail.

• RESTRICT: Similar to NO ACTION, it prevents the deletion of a parent row if there are dependent rows in the child table. It is essentially the same as NO ACTION and included for compatibility reasons.

• SET DEFAULT: If a row in the parent table is deleted, the foreign key column in the child table will be set to its default value if it has one. If it doesn’t have a default value, the DELETE operation will fail.

• SET NULL: When a row in the parent table is deleted, the foreign key column in the child table will be set to NULL. This action assumes that the foreign key column in the child table allows NULL values.

Analogous to ON DELETE there is also ON UPDATE which is invoked when a referenced column is changed (updated). The possible actions are the same, except that column lists cannot be specified for SET NULL and SET DEFAULT. In this case, CASCADE means that the updated values of the referenced column(s) should be copied into the referencing row(s). in drizzle you can add foreign key action using references() second argument.

type of the actions

export type UpdateDeleteAction = 'cascade' | 'restrict' | 'no action' | 'set null' | 'set default';

// second argument of references interface
actions?: {
    onUpdate?: UpdateDeleteAction;
    onDelete?: UpdateDeleteAction;
  } | undefined

In the following example, adding onDelete: 'cascade' to the author field on the posts schema means that deleting the user will also delete all related Post records.

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

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  name: text('name'),
  author: integer('author').references(() => users.id, {onDelete: 'cascade'}).notNull(),
});

Disambiguating relations

Drizzle also provides the relationName option as a way to disambiguate relations when you define multiple of them between the same two tables. For example, if you define a posts table that has the author and reviewer relations.

import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
 
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
});
 
export const usersRelations = relations(users, ({ many }) => ({
  author: many(posts, { relationName: 'author' }),
  reviewer: many(posts, { relationName: 'reviewer' }),
}));
 
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  content: text('content'),
  authorId: integer('author_id'),
  reviewerId: integer('reviewer_id'),
});
 
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
    relationName: 'author',
  }),
  reviewer: one(users, {
    fields: [posts.reviewerId],
    references: [users.id],
    relationName: 'reviewer',
  }),
}));

Querying

Relational queries are an extension to Drizzle’s original query builder. You need to provide all tables and relations from your schema file/files upon drizzle() initialization and then just use the db.query API.

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

const db = drizzle(client, { schema });

await db.query.users.findMany(...);

// if you have schema in multiple files
import * as schema1 from './schema1';
import * as schema2 from './schema2';
import { drizzle } from 'drizzle-orm/...';

const db = drizzle(client, { schema: { ...schema1, ...schema2 } });

await db.query.users.findMany(...);

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

  import { relations } from 'drizzle-orm';

  export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    name: text('name').notNull(),
    invitedBy: integer('invited_by').references((): AnyPgColumn => users.id),
  });

  export const usersRelations = relations(users, ({ one, many }) => ({
    invitee: one(users, { fields: [users.invitedBy], references: [users.id] }),
    usersToGroups: many(usersToGroups),
    posts: many(posts),
  }));

  export const groups = pgTable('groups', {
    id: serial('id').primaryKey(),
    name: text('name').notNull(),
    description: text('description'),
  });

  export const groupsRelations = relations(groups, ({ many }) => ({
    usersToGroups: many(usersToGroups),
  }));

  export const usersToGroups = pgTable('users_to_groups', {
    id: serial('id').primaryKey(),
    userId: integer('user_id').notNull().references(() => users.id),
    groupId: integer('group_id').notNull().references(() => groups.id),
  }, (t) => ({
    pk: primaryKey(t.userId, t.groupId),
  }));

  export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
    group: one(groups, { fields: [usersToGroups.groupId], references: [groups.id] }),
    user: one(users, { fields: [usersToGroups.userId], references: [users.id] }),
  }));

  export const posts = pgTable('posts', {
    id: serial('id').primaryKey(),
    content: text('content').notNull(),
    authorId: integer('author_id').references(() => users.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  });

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

  export const comments = pgTable('comments', {
    id: serial('id').primaryKey(),
    content: text('content').notNull(),
    creator: integer('creator').references(() => users.id),
    postId: integer('post_id').references(() => posts.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  });

  export const commentsRelations = relations(comments, ({ one, many }) => ({
    post: one(posts, { fields: [comments.postId], references: [posts.id] }),
    author: one(users, { fields: [comments.creator], references: [users.id] }),
    likes: many(commentLikes),
  }));

  export const commentLikes = pgTable('comment_likes', {
    id: serial('id').primaryKey(),
    creator: integer('creator').references(() => users.id),
    commentId: integer('comment_id').references(() => comments.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  });

  export const commentLikesRelations = relations(commentLikes, ({ one }) => ({
    comment: one(comments, { fields: [commentLikes.commentId], references: [comments.id] }),
    author: one(users, { fields: [commentLikes.creator], references: [users.id] }),
  }));

Drizzle provides .findMany() and .findFirst() APIs.

Find many

const users = await db.query.users.findMany();

// result type
const result: {
  id: number;
  name: string;
  verified: boolean;
  invitedBy: number | null;
}[];

Find first

const user = await db.query.users.findFirst();

// result type
const result: {
  id: number;
  name: string;
  verified: boolean;
  invitedBy: number | null;
};

Include relations

With operator lets you combine data from multiple related tables and properly aggregate results.

Getting all posts with comments:

const posts = await db.query.posts.findMany({
  with: {
    comments: true,
  },
});

Getting first post with comments:

const post = await db.query.posts.findFirst({
  with: {
    comments: true,
  },
});

You can chain nested with statements as much as necessary.
For any nested with queries Drizzle will infer types using Core Type API.

Get all users with posts. Each post should contain a list of comments:

const users = await db.query.users.findMany({
  with: {
    posts: {
      with: {
        comments: true,
      },
    },
  },
});

Partial fields select

columns parameter lets you include or omit columns you want to get from the database.

Get all posts with just id, content and include comments:

const posts = await db.query.posts.findMany({
  columns: {
    id: true,
    content: true,
  },
  with: {
    comments: true,
  }
});

Get all posts without content:

const posts = await db.query.posts.findMany({
  columns: {
    content: false,
  },
});

If you include the name field and exclude the id field, id exclusion will be redundant, all fields apart from name would be excluded anyways.

Exclude and Include fields in the same query:

const users = await db.query.users.findMany({
  columns: {
    name: true,
    id: false //ignored
  },
});

// result type
const users: {
  name: string;
};

Only include columns from nested relations:

const res = await db.query.users.findMany({
  columns: {},
  with: {
    posts: true
  }
});

// result type
const res: {
  posts: {
    id: number,
    text: string
  }
}[];

Nested partial fields select

Just like with partial select, you can include or exclude columns of nested relations:

const posts = await db.query.posts.findMany({
  columns: {
    id: true,
    content: true,
  },
  with: {
    comments: {
      columns: {
        authorId: false
      }
    }
  }
});

Select filters

Just like in our SQL-like query builder, relational queries API lets you define filters and conditions with the list of our operators.

You can either import them from drizzle-orm or use from the callback syntax:

import { eq } from 'drizzle-orm';

const users = await db.query.users.findMany({
  where: eq(users.id, 1)
})

const users = await db.query.users.findMany({
  where: (users, { eq }) => eq(users.id, 1),
})

Find post with id=1 and comments that were created before particular date:

await db.query.posts.findMany({
  where: (posts, { eq }) => (eq(posts.id, 1)),
  with: {
    comments: {
      where: (comments, { lt }) => lt(comments.createdAt, new Date()),
    },
  },
});

Limit & Offset

Drizzle ORM provides limit & offset API for queries and for the nested entities.

Find 5 posts:

await db.query.posts.findMany({
  limit: 5,
});

Find posts and get 3 comments at most:

await db.query.posts.findMany({
  with: {
    comments: {
      limit: 3,
    },
  },
});

await db.query.posts.findMany({
  limit: 5,
  offset: 2, // correct ✅
  with: {
    comments: {
      offset: 3, // incorrect ❌
      limit: 3,
    },
  },
});

Find posts with comments from the 5th to the 10th post:

await db.query.posts.findMany({
  limit: 5,
  offset: 5,
  with: {
    comments: true,
  },
});

Order By

Drizzle provides API for ordering in the relational query builder.

You can use same ordering core API or use order by operator from the callback with no imports.

import { desc, asc } from 'drizzle-orm';

await db.query.posts.findMany({
  orderBy: [asc(posts.id)],
});

await db.query.posts.findMany({
  orderBy: (posts, { asc }) => [asc(posts.id)],
});

Order by asc + desc:

await db.query.posts.findMany({
  orderBy: (posts, { asc }) => [asc(posts.id)],
  with: {
    comments: {
      orderBy: (comments, { desc }) => [desc(comments.id)],
    },
  },
});

Include custom fields

Relational query API lets you add custom additional fields. It’s useful when you need to retrieve data and apply additional functions to it.

import { sql } from 'drizzle-orm';

await db.query.users.findMany({
  extras: {
    loweredName: sql`lower(${users.name})`.as('lowered_name'),
  },
})

await db.query.users.findMany({
  extras: {
    loweredName: (users, { sql }) => sql`lower(${users.name})`.as('lowered_name'),
  },
})

lowerName as a key will be included to all fields in returned object.

To retrieve all users with groups, but with the fullName field included (which is a concatenation of firstName and lastName), you can use the following query with the Drizzle relational query builder.

const res = await db.query.users.findMany({
  extras: {
    fullName: sql<string>`concat(${users.name}, " ", ${users.name})`.as('full_name'),
  },
  with: {
    usersToGroups: {
      with: {
        group: true,
      },
    },
  },
});

// result type
const res: {
  id: number;
  name: string;
  verified: boolean;
  invitedBy: number | null;
  fullName: string;
  usersToGroups: {
      group: {
          id: number;
          name: string;
          description: string | null;
      };
  }[];
}[];

To retrieve all posts with comments and add an additional field to calculate the size of the post content and the size of each comment content:

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'),
      },
    },
  },
});

// result type
const res: {
  id: number;
  createdAt: Date;
  content: string;
  authorId: number | null;
  contentLength: number;
  comments: {
      id: number;
      createdAt: Date;
      content: string;
      creator: number | null;
      postId: number | null;
      commentSize: number;
  }[];
};

Prepared statements

Prepared statements are designed to massively improve query performance — see here.

In this section, you can learn how to define placeholders and execute prepared statements using the Drizzle relational query builder.

Placeholder in where

const prepared = db.query.users.findMany({
  where: ((users, { eq }) => eq(users.id, placeholder('id'))),
  with: {
    posts: {
      where: ((users, { eq }) => eq(users.id, 1)),
    },
  },
}).prepare('query_name');

const usersWithPosts = await prepared.execute({ id: 1 });

const prepared = db.query.users.findMany({
  where: ((users, { eq }) => eq(users.id, placeholder('id'))),
  with: {
    posts: {
      where: ((users, { eq }) => eq(users.id, 1)),
    },
  },
}).prepare();

const usersWithPosts = await prepared.execute({ id: 1 });

const prepared = db.query.users.findMany({
  where: ((users, { eq }) => eq(users.id, placeholder('id'))),
  with: {
    posts: {
      where: ((users, { eq }) => eq(users.id, 1)),
    },
  },
}).prepare();

const usersWithPosts = await prepared.execute({ id: 1 });

Placeholder in limit

const prepared = db.query.users.findMany({
  with: {
    posts: {
      limit: placeholder('limit'),
    },
  },
}).prepare('query_name');

const usersWithPosts = await prepared.execute({ limit: 1 });

const prepared = db.query.users.findMany({
  with: {
    posts: {
      limit: placeholder('limit'),
    },
  },
}).prepare();

const usersWithPosts = await prepared.execute({ limit: 1 });

const prepared = db.query.users.findMany({
  with: {
    posts: {
      limit: placeholder('limit'),
    },
  },
}).prepare();

const usersWithPosts = await prepared.execute({ limit: 1 });

Placeholder in offset

const prepared = db.query.users.findMany({
  offset: placeholder('offset'),
  with: {
    posts: true,
  },
}).prepare('query_name');

const usersWithPosts = await prepared.execute({ offset: 1 });

const prepared = db.query.users.findMany({
  offset: placeholder('offset'),
  with: {
    posts: true,
  },
}).prepare();

const usersWithPosts = await prepared.execute({ offset: 1 });

const prepared = db.query.users.findMany({
  offset: placeholder('offset'),
  with: {
    posts: true,
  },
}).prepare();

const usersWithPosts = await prepared.execute({ offset: 1 });

Multiple placeholders

const prepared = db.query.users.findMany({
  limit: placeholder('uLimit'),
  offset: placeholder('uOffset'),
  where: ((users, { eq, or }) => or(eq(users.id, placeholder('id')), eq(users.id, 3))),
  with: {
    posts: {
      where: ((users, { eq }) => eq(users.id, placeholder('pid'))),
      limit: placeholder('pLimit'),
    },
  },
}).prepare('query_name');

const usersWithPosts = await prepared.execute({ pLimit: 1, uLimit: 3, uOffset: 1, id: 2, pid: 6 });

const prepared = db.query.users.findMany({
  limit: placeholder('uLimit'),
  offset: placeholder('uOffset'),
  where: ((users, { eq, or }) => or(eq(users.id, placeholder('id')), eq(users.id, 3))),
  with: {
    posts: {
      where: ((users, { eq }) => eq(users.id, placeholder('pid'))),
      limit: placeholder('pLimit'),
    },
  },
}).prepare();

const usersWithPosts = await prepared.execute({ pLimit: 1, uLimit: 3, uOffset: 1, id: 2, pid: 6 });

const prepared = db.query.users.findMany({
  limit: placeholder('uLimit'),
  offset: placeholder('uOffset'),
  where: ((users, { eq, or }) => or(eq(users.id, placeholder('id')), eq(users.id, 3))),
  with: {
    posts: {
      where: ((users, { eq }) => eq(users.id, placeholder('pid'))),
      limit: placeholder('pLimit'),
    },
  },
}).prepare();

const usersWithPosts = await prepared.execute({ pLimit: 1, uLimit: 3, uOffset: 1, id: 2, pid: 6 });