Relations

Relations are where better-drizzle saves the most repetition. Once your Drizzle relations are defined, you can load and filter by related rows with full type inference.

Relation reads use a batch loader owned by better-drizzle: one root query plus one query per requested relation node. They do not issue one query per parent row.

The examples below assume a users → posts schema with relations defined (see getting started).

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

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.

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

A junction table with exactly two required foreign-key relations and no additional required columns is inferred automatically. The virtual relation uses the target schema key:

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

Disable inference or identify an explicit junction in better() when your schema has multiple possible paths:

const client = better(db, {
	schema,
	relations: {
		manyToMany: [
			{
				through: 'userGroups',
				left: { relation: 'user' },
				right: { relation: 'group' },
			},
		],
	},
});

Ambiguous paths fail during use instead of choosing a junction silently. Set relations.inferManyToMany to false to require explicit configuration.

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.

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

Filtering by related rows

where can constrain a row by its relations. The operators depend on the relation's cardinality.

To-one relations: is / isNot

For a "belongs to" / "has one" relation, use is (and isNot) with a nested filter:

const posts = await client.posts.findMany({
	where: {
		author: {
			is: { active: true },
		},
	},
	include: { author: true },
});

To-many relations: some / every / none

For a "has many" relation, choose the quantifier:

// Users who have at least one published post
const authors = await client.users.findMany({
	where: {
		posts: { some: { published: true } },
	},
});

// Users with no unpublished posts
const fullyPublished = await client.users.findMany({
	where: {
		posts: { none: { published: false } },
	},
});

Combining relation and scalar filters

Relation filters compose with scalar filters and logical operators like anything else:

const posts = await client.posts.findMany({
	where: {
		AND: [
			{ published: true },
			{
				author: {
					is: { email: { endsWith: '@company.com' } },
				},
			},
		],
	},
	select: {
		id: true,
		title: true,
		author: { select: { id: true, email: true } },
	},
});

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.