Relations

Use include and relation select to load related rows with one call and a typed nested result. Relations come from Drizzle's defineRelations. To filter rows by their relations, see relation filters.

Relation reads use a batch loader owned by better-drizzle: one root query plus normally one query per requested relation node. Large parent-key sets are split into sequential queries with at most 10,000 link parameters; composite keys also use at most 200 OR branches per query. The batch size also reserves parameters used by filters, scopes, counts, and pagination. If those arguments leave no room for a parent key, the loader throws INVALID_ARGS. Duplicate keys are fetched once across all batches, and overlapping query results keep each group once. Ordering, pagination, and grouping still apply per parent.

The examples below extend the getting started users → posts schema with comments, profiles, a posts.editorId column, and a memberships junction between users and groups.

Defining relations

better-drizzle reads relations from the Drizzle instance, so declare them with defineRelations and pass the result to drizzle():

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

export const relations = defineRelations(schema, (r) => ({
	users: {
		posts: r.many.posts(),
		profile: r.one.profiles({ from: r.users.id, to: r.profiles.userId }),
	},
	posts: {
		author: r.one.users({ from: r.posts.authorId, to: r.users.id }),
		comments: r.many.comments(),
	},
	comments: {
		post: r.one.posts({ from: r.comments.postId, to: r.posts.id }),
	},
}));

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

export const db = drizzle(process.env.DATABASE_URL!, { relations });
export const client = better(db);

A foreign key (.references(...)) alone is not a relation. Only relations declared in defineRelations are available to include, select, count, relation filters, and relation writes.

When two relations connect the same pair of tables, give both sides the same alias, as Drizzle requires:

export const relations = defineRelations(schema, (r) => ({
	posts: {
		author: r.one.users({ from: r.posts.authorId, to: r.users.id, alias: 'author' }),
		editor: r.one.users({ from: r.posts.editorId, to: r.users.id, alias: 'editor' }),
	},
	users: {
		authoredPosts: r.many.posts({ alias: 'author' }),
		editedPosts: r.many.posts({ alias: 'editor' }),
	},
}));

Loading relations

Use include to keep the base row and attach relations:

const posts = await client.posts.findMany({
	include: { author: true },
	orderBy: [{ id: 'desc' }],
	take: 10,
});

Or select to also narrow the related fields:

const posts = await client.posts.findMany({
	select: {
		id: true,
		title: true,
		author: { select: { id: true, name: true } },
	},
});

Every nested relation accepts its own where, orderBy, take, skip, cursor, select, and include. Pagination is applied per parent in SQL:

const users = await client.users.findMany({
	include: {
		posts: {
			where: { published: true },
			orderBy: { score: 'desc' },
			take: 3,
			select: {
				title: true,
				comments: { select: { body: true } },
			},
		},
	},
});

select and include are mutually exclusive at the same query level. Linking columns needed by the loader are selected internally and removed from the returned payload unless you requested them.

Relation counts

Use count inside include to return counts without loading the related rows. A selected relation can be true or a { where } filter. The result is available under count; to-one relations return 0 or 1. count is a reserved key: a relation named count cannot be loaded through include. See limitations. The old _count spelling is a deprecated alias, see upgrading to 0.4.

const users = await client.users.findMany({
	include: {
		count: {
			select: {
				posts: { where: { published: true } },
				profile: true,
			},
		},
	},
});
// { ..., count: { posts: 3, profile: 1 } }

Counts are projected as correlated subqueries in the SQL that materializes that query level. They do not add a count query per parent row.

Many-to-many relations

Declare many-to-many relations natively with .through() on both ends, naming the junction columns:

export const relations = defineRelations(schema, (r) => ({
	users: {
		groups: r.many.groups({
			from: r.users.id.through(r.memberships.userId),
			to: r.groups.id.through(r.memberships.groupId),
		}),
	},
	groups: {
		users: r.many.users({
			from: r.groups.id.through(r.memberships.groupId),
			to: r.users.id.through(r.memberships.userId),
		}),
	},
}));

The relation then behaves like any other to-many relation - include, select, count, some / every / none, and connect / disconnect / set all work through it, and you never name the junction table in a query:

const users = await client.users.findMany({
	include: { groups: true },
});

Relations better-drizzle cannot load

Two kinds of Drizzle relations are not supported by the better-drizzle loader:

• relations declared with a relation-level where (filtered relations)
• one relations declared through a junction table

They are not part of the better-drizzle relation API: loading one with include or select throws Relation "x" on "t" cannot be loaded: ..., and filtering through one in where throws Relation "x" on "t" cannot be filtered: .... Sorting by one in orderBy throws the same unsupported-relation error. They still work with Drizzle's own relational queries on the underlying Drizzle instance.

Explain plans

.explain() executes the root dialect-specific explain and reports relation queries under deferredRelations, including each path, cardinality, target table, junction table, filtering, sorting, and pagination flags.

Rule of thumb

• Use where to control which rows qualify - including by their relations.
• Use select to control which fields survive.
• Use include to keep the full row and attach relations.