Relational 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.

Relational queries 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.

index.ts

schema.ts

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,
		}
	]
}]

One-to-one

Drizzle ORM provides you an API to define one-to-one relations between tables with relations operator.
Example of one-to-one relation between users and users who invited them to the service, 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 usersConfig = relations(users, ({ one, many }) => ({
	invitee: one(users, {
		fields: [users.invitedBy],
		references: [users.id],
	}),
}));

Another example would be a user having a profile information stored in separate table

ℹ️

Foreign keys are SQL entities and used to check relational constrains, they're not mandatory for relations module

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 usersConfig = 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"),
});

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 usersConfig = relations(users, ({ many }) => ({
	posts: many(posts),
}));
 
export const posts = pgTable('posts', {
	id: serial('id').primaryKey(),
	content: text('content'),
	authorId: integer('author_id'),
});
 
export const postsConfig = relations(posts, ({ one }) => ({
	author: one(users, {
		fields: [posts.ownerId],
		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 postsConfig = relations(posts, ({ one, many }) => ({
	author: one(users, {
		fields: [posts.ownerId],
		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 commentsConfig = 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 one-to-many relation between users and groups.

import { pgTable, serial, text, integer, boolean, primaryKey } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
 
export const users = pgTable('users', {
	id: serial('id').primaryKey(),
	name: text('name'),
});
 
export const usersConfig = relations(users, ({ many }) => ({
	usersToGroups: many(usersToGroupsTable),
}));
 
export const groups = pgTable('groups', {
	id: serial('id').primaryKey(),
	name: text('name'),
});
 
export const groupsConfig = 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(() => groupsTable.id),
	}, (t) => ({
		pk: primaryKey(t.userId, t.groupId),
	}),
);
 
export const usersToGroupsConfig = relations(usersToGroups, ({ one }) => ({
	group: one(groups, {
		fields: [usersToGroups.groupId],
		references: [groups.id],
	}),
	user: one(users, {
		fields: [usersToGroups.userId],
		references: [users.id],
	}),
}));

Querying

Relation queries are extension to our original query builder.
You need to provide all tables and relations from your schema file/files upon drizzle() initialization and then just use db.query API.

ℹ️

drizzle import depends on the database driver you're using

index.ts

schema.ts

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(...);

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

Find many

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

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

Find first

💡

.findFirst() will add limit 1 to the query

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 InferModel from Core 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

💡

Drizzle performs partial selects on the query level, no additional data is transfered 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,
	},
});

ℹ️

When both true and false select options are present, all false options are ignored

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

This callback is useful in situations where you need to query against relations, which are only available in the callback. For example, if you need to retrieve all users who have more than 0 posts.

const usersWithPosts = await db.query.users.findMany({
	where: (table, { sql }) => (sql`json_array_length(${table.posts}) > 0`),
	with: {
		posts: true,
	},
});

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

⚠️

offset is only available for top level query

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.

⚠️

As of now aggregations are not supported in extras, please use core queries for that

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

⚠️

You have to explicitly specify .as("<name_for_column>")

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: {
			columns: {
				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;
    ownerId: 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 });

Placeholder in limit

const prepared = db.query.users.findMany({
  		with: {
  			posts: {
  				limit: placeholder('limit'),
  			},
  		},
  	}).prepare('query_name');
 
  	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 });

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

SQL queries [CRUD]Joins