Relational Query Engine

Overview

The Relational Query Engine serves as Drizzle's high-level declarative query layer, translating nested TypeScript query definitions into optimized, dialect-specific SQL execution plans across multiple database engines. It solves the complexity of querying graph-shaped relational data structures by bridging typed schema definitions with underlying driver sessions through dedicated query builder classes like PgRelationalQuery, MySqlRelationalQuery, GelRelationalQuery, SingleStoreRelationalQuery, and SQLiteRelationalQuery. Key architectural decisions include encapsulating query promises and execution lifecycles, supporting nested filtering, pagination, and sorting via findMany and findFirst, and normalizing graph relations across primary and non-primary key constraints. It interacts closely with underlying database dialects to construct lateral subqueries, manage table aliases, apply selection masks, and execute row-mapping transformations on fetched database records.

Sources: drizzle-orm/src/pg-core/query-builders/query.ts:19-110, drizzle-orm/src/mysql-core/query-builders/query.ts:23-74, drizzle-orm/src/gel-core/query-builders/query.ts:19-63, drizzle-orm/src/singlestore-core/query-builders/query.ts:22-70, drizzle-orm/src/sqlite-core/query-builders/query.ts:22-96

Relation Declarations and Metadata Modeling

Schema Definition and Helpers

The relational metadata layer begins with public schema definition helpers and typed relation descriptors. The relations function accepts a table and a callback yielding a record of Relation objects, automatically binding each entry to its field name via value.withFieldName(key). The helpers createOne and createMany construct One and Many relation descriptors respectively, where createOne evaluates nullability by inspecting whether all component fields have notNull: true.

Copyexport function relations<
	TTableName extends string,
	TRelations extends Record<string, Relation<any>>,
>(
	table: AnyTable<{ name: TTableName }>,
	relations: (helpers: TableRelationsHelpers<TTableName>) => TRelations,
): Relations<TTableName, TRelations> {
	return new Relations<TTableName, TRelations>(
		table,
		(helpers: TableRelationsHelpers<TTableName>) =>
			Object.fromEntries(
				Object.entries(relations(helpers)).map(([key, value]) => [
					key,
					value.withFieldName(key),
				]),
			) as TRelations,
	);
}

Sources: drizzle-orm/src/relations.ts:502-519

The engine extracts complete relational schemas using extractTablesRelationalConfig, which iterates over raw schema entries, normalizes single-default schema wrappers, and resolves forward references through relationsBuffer when relations are declared before their target tables.

Sources: drizzle-orm/src/relations.ts:416-500

Graph Relation Normalization

Relation graph normalization resolves mapping fields between source and target tables when explicit join configurations are omitted. The normalizeRelation function inspects whether a relation is an instance of One containing an explicit RelationConfig. If explicit fields and references are present, they are returned directly. Otherwise, the engine looks up the referenced table configuration in the schema and searches for matching reverse relations.

Copyexport function normalizeRelation(
	schema: TablesRelationalConfig,
	tableNamesMap: Record<string, string>,
	relation: Relation,
): NormalizedRelation {
	if (is(relation, One) && relation.config) {
		return {
			fields: relation.config.fields,
			references: relation.config.references,
		};
	}
...

Sources: drizzle-orm/src/relations.ts:559-569

Reverse relation matching evaluates either matching explicit relationName properties or linking back via referencedTable === relation.sourceTable. If multiple reverse relations match without a disambiguating name, an error is thrown. When a single reverse One relation is found, its fields and references are inverted to establish the normalized connection path.

Relational Query Builder Classes

Relational Query Builder Construction and Methods

Dialect-specific relational query builders coordinate schema definitions, table mappings, and session handlers to instantiate executable queries. Each dialect exports a RelationalQueryBuilder class that exposes findMany and findFirst methods accepting type-safe configuration objects restricted by KnownKeysOnly. The findFirst method wraps the query configuration by setting limit: 1 and marks the internal execution mode as 'first', whereas findMany defaults to 'many'.

Sources: drizzle-orm/src/pg-core/query-builders/query.ts:19-63, drizzle-orm/src/mysql-core/query-builders/query.ts:23-74, drizzle-orm/src/gel-core/query-builders/query.ts:19-63, drizzle-orm/src/singlestore-core/query-builders/query.ts:22-70, drizzle-orm/src/sqlite-core/query-builders/query.ts:22-96

CopyfindMany<TConfig extends DBQueryConfig<'many', true, TSchema, TFields>>(
	config?: KnownKeysOnly<TConfig, DBQueryConfig<'many', true, TSchema, TFields>>,
): PgRelationalQuery<BuildQueryResult<TSchema, TFields, TConfig>[]> {
	return new PgRelationalQuery(
		this.fullSchema,
		this.schema,
		this.tableNamesMap,
		this.table,
		this.tableConfig,
		this.dialect,
		this.session,
		config ? (config as DBQueryConfig<'many', true>) : {},
		'many',
	);
}
 
findFirst<TSelection extends Omit<DBQueryConfig<'many', true, TSchema, TFields>, 'limit'>>(
	config?: KnownKeysOnly<TSelection, Omit<DBQueryConfig<'many', true, TSchema, TFields>, 'limit'>>,
): PgRelationalQuery<BuildQueryResult<TSchema, TFields, TSelection> | undefined> {
	return new PgRelationalQuery(
		this.fullSchema,
		this.schema,
		this.tableNamesMap,
		this.table,
		this.tableConfig,
		this.dialect,
		this.session,
		config ? { ...(config as DBQueryConfig<'many', true> | undefined), limit: 1 } : { limit: 1 },
		'first',
	);
}

Sources: drizzle-orm/src/pg-core/query-builders/query.ts:32-62

Execution Lifecycle and Query Compilation

Relational query runner classes inherit from QueryPromise and implement query compilation, statement preparation, and row mapping. When .execute() or .prepare() is invoked, the runner calls an internal compilation method such as _toSQL() or _getQuery() which delegates to the dialect to build the underlying SQL abstract syntax tree (AST). For instance, PostgreSQL and Gel invoke dialect.buildRelationalQueryWithoutPK, whereas MySQL checks the connection mode (planetscale) to determine whether to build queries without lateral subqueries.

Copyprivate _getQuery() {
	const query = this.mode === 'planetscale'
		? this.dialect.buildRelationalQueryWithoutLateralSubqueries({
			fullSchema: this.fullSchema,
			schema: this.schema,
			tableNamesMap: this.tableNamesMap,
			table: this.table,
			tableConfig: this.tableConfig,
			queryConfig: this.config,
			tableAlias: this.tableConfig.tsName,
		})
		: this.dialect.buildRelationalQuery({
			fullSchema: this.fullSchema,
			schema: this.schema,
			tableNamesMap: this.tableNamesMap,
			table: this.table,
			tableConfig: this.tableConfig,
			queryConfig: this.config,
			tableAlias: this.tableConfig.tsName,
		});
	return query;
}

Sources: drizzle-orm/src/mysql-core/query-builders/query.ts:114-135

Once compiled into SQL, the query is passed to the session via prepareQuery (or prepareOneTimeQuery in SQLite). The session callback processes raw database rows by executing mapRelationalRow, passing the schema table configuration, row selection mask, and column mapping utility. If the query execution mode is set to 'first', the result mapper extracts and returns the first element (rows[0]), otherwise returning the full array.

Copy	(rawRows, mapColumnValue) => {
		const rows = rawRows.map((row) =>
			mapRelationalRow(this.schema, this.tableConfig, row, query.selection, mapColumnValue)
		);
		if (this.mode === 'first') {
			return rows[0] as TResult;
		}
		return rows as TResult;
	},
);

Sources: drizzle-orm/src/pg-core/query-builders/query.ts:99-108

Lateral Joins and Subquery Compilation

Dialect-Specific Subquery Strategies

Different database engines impose distinct constraints on how relational joins and nested subqueries are compiled. Drizzle accommodates these engine-specific capabilities by delegating query construction to dialect methods tailored to each backend's support for lateral subqueries, correlated references, and table primary key requirements.

Copyprivate _getQuery() {
	const query = this.mode === 'planetscale'
		? this.dialect.buildRelationalQueryWithoutLateralSubqueries({
			fullSchema: this.fullSchema,
			schema: this.schema,
			tableNamesMap: this.tableNamesMap,
			table: this.table,
			tableConfig: this.tableConfig,
			queryConfig: this.config,
			tableAlias: this.tableConfig.tsName,
		})
		: this.dialect.buildRelationalQuery({
			fullSchema: this.fullSchema,
			schema: this.schema,
			tableNamesMap: this.tableNamesMap,
			table: this.table,
			tableConfig: this.tableConfig,
			queryConfig: this.config,
			tableAlias: this.tableConfig.tsName,
		});
	return query;
}

Sources: drizzle-orm/src/mysql-core/query-builders/query.ts:114-135

As shown in the MySQL relational query builder, the dialect branch inspection explicitly checks whether the execution target is running under PlanetScale mode. When this.mode === 'planetscale', the query builder invokes buildRelationalQueryWithoutLateralSubqueries instead of standard buildRelationalQuery. This branching is required because serverless MySQL hosting environments often lack full lateral join support or enforce restrictions on correlated subquery constructs.

Keyless and Specialized Compilation Methods

Beyond MySQL's conditional dispatch, PostgreSQL and Gel employ buildRelationalQueryWithoutPK to handle relational queries where primary key assumptions or table structures require specialized AST generation.

Copyprivate _getQuery() {
	return this.dialect.buildRelationalQueryWithoutPK({
		fullSchema: this.fullSchema,
		schema: this.schema,
		tableNamesMap: this.tableNamesMap,
		table: this.table,
		tableConfig: this.tableConfig,
		queryConfig: this.config,
		tableAlias: this.tableConfig.tsName,
	});
}

Sources: drizzle-orm/src/pg-core/query-builders/query.ts:116-126

Sources: drizzle-orm/src/pg-core/query-builders/query.ts:116-126, drizzle-orm/src/mysql-core/query-builders/query.ts:114-135, drizzle-orm/src/gel-core/query-builders/query.ts:116-126, drizzle-orm/src/singlestore-core/query-builders/query.ts:109-119, drizzle-orm/src/sqlite-core/query-builders/query.ts:129-138

Selection Masking and Dynamic Aliasing

Selection Proxy Wrapping and Proxy Handlers

Selection masking and dynamic aliasing in Drizzle are governed by SelectionProxyHandler, which implements the JavaScript ProxyHandler interface across subqueries, record sets, and database views. When properties are accessed on a selection proxy, the handler intercepts symbol and string lookups to manage _ metadata configurations, view configurations, and column selections.

Copyget(subquery: T, prop: string | symbol): any {
	if (prop === '_') {
		return {
			...subquery['_' as keyof typeof subquery],
			selectedFields: new Proxy(
				(subquery as Subquery)._.selectedFields,
				this as ProxyHandler<Record<string, unknown>>,
			),
		};
	}
 
	if (prop === ViewBaseConfig) {
		return {
			...subquery[ViewBaseConfig as keyof typeof subquery],
			selectedFields: new Proxy(
				(subquery as View)[ViewBaseConfig].selectedFields,
				this as ProxyHandler<Record<string, unknown>>,
			),
		};
	}

Sources: drizzle-orm/src/selection-proxy.ts:47-66

The proxy handler evaluates accessed properties to determine whether they represent stored columns, raw SQL expressions, aliased SQL chunks, or nested objects. When a column is encountered and an alias is configured, the handler wraps the column and its underlying table within ColumnAliasProxyHandler and TableAliasProxyHandler respectively.

Copyif (is(value, Column)) {
	if (this.config.alias) {
		return new Proxy(
			value,
			new ColumnAliasProxyHandler(
				new Proxy(
					value.table,
					new TableAliasProxyHandler(this.config.alias, this.config.replaceOriginalName ?? false),
				),
			),
		);
	}
	return value;
}

Sources: drizzle-orm/src/selection-proxy.ts:100-113

Column Projection Scoping and SQL Behavior

The configuration options accepted by SelectionProxyHandler control how raw SQL expressions and aliased fields behave during query building and view generation.

Sources: drizzle-orm/src/selection-proxy.ts:13-41

Result Hydration and Row Mapping

Relational Row Transformation and Mapping Lifecycles

After database sessions execute compiled relational queries, raw tabular rows returned by drivers require structural reshaping into nested object graphs matching the user's schema and selection configuration. Across PostgreSQL, MySQL, SQLite, SingleStore, and Gel dialects, this post-query phase is driven by mapRelationalRow, which processes raw row data and column value mappers provided by underlying database drivers.

Copy(rawRows, mapColumnValue) => {
	const rows = rawRows.map((row) =>
		mapRelationalRow(this.schema, this.tableConfig, row, query.selection, mapColumnValue)
	);
	if (this.mode === 'first') {
		return rows[0] as TResult;
	}
	return rows as TResult;
},

Sources: drizzle-orm/src/pg-core/query-builders/query.ts:99-107

For asynchronous and synchronous session configurations, the execution pipeline wraps the raw rows mapper inside prepared query definitions. Depending on whether the query mode is configured as 'many' or 'first', the result handler returns either an array of fully hydrated entities or extracts the initial index element.

Copy(rawRows, mapColumnValue) => {
	const rows = rawRows.map((row) =>
		mapRelationalRow(this.schema, this.tableConfig, row, query.selection, mapColumnValue)
	);
	if (this.mode === 'first') {
		return rows[0] as TResult;
	}
	return rows as TResult;
},

Sources: drizzle-orm/src/sqlite-core/query-builders/query.ts:152-160

Execution and Preparation Modalities

Different query runner implementations manage row mapping through dialect-specific prepared statements and query promises.

Sources: drizzle-orm/src/pg-core/query-builders/query.ts:90-108, drizzle-orm/src/mysql-core/query-builders/query.ts:99-111, drizzle-orm/src/sqlite-core/query-builders/query.ts:142-161, drizzle-orm/src/gel-core/query-builders/query.ts:90-108, drizzle-orm/src/singlestore-core/query-builders/query.ts:94-106

Related

• Table and Column Models
• Query Builder Foundation