Query Builder Foundation

Overview

The Query Builder Foundation defines the architectural and type-level framework for constructing, composing, and executing declarative SQL queries across all supported database dialects. It centers on foundational base classes such as TypedQueryBuilder and QueryPromise that standardize asynchronous execution, thenable resolutions, and fluent method chaining across select, insert, update, and count operations. By pairing abstract query specifications with concrete dialect implementations, the foundation enables type-safe schema selection, join nullability mapping, and expression compilation without relying on pre-instantiated database connections.

Sources: drizzle-orm/src/query-builders/query-builder.ts:4-18, drizzle-orm/src/gel-core/query-builders/select.ts:1013-1060, drizzle-orm/src/sqlite-core/query-builders/select.ts:129-160, drizzle-orm/src/singlestore-core/query-builders/select.ts:133-160

Core Query Builder Abstractions

Core Query Base Abstractions

TypedQueryBuilder and Selection Contracts

The foundational contract for all query builders is established by the TypedQueryBuilder abstract class. It implements the SQLWrapper interface and exposes structural type generics for the selected fields, result shape, and builder configuration. The class defines an internal _ property structure alongside helper methods for extracting selected fields and enforcing SQL compilation.

Copyexport abstract class TypedQueryBuilder<TSelection, TResult = unknown, TConfig = unknown> implements SQLWrapper {
	static readonly [entityKind]: string = 'TypedQueryBuilder';
 
	declare _: {
		selectedFields: TSelection;
		result: TResult;
		config?: TConfig;
	};
 
	/** @internal */
	getSelectedFields(): TSelection {
		return this._.selectedFields;
	}
 
	abstract getSQL(): SQL;
}

Sources: drizzle-orm/src/query-builders/query-builder.ts:4-18

Type-level calculations for queries rely on structural mapping utilities defined across select operations. These type definitions govern join semantics, result flattening, and subquery validation.

Sources: drizzle-orm/src/query-builders/select.types.ts:9-11, drizzle-orm/src/query-builders/select.types.ts:37-37, drizzle-orm/src/query-builders/select.types.ts:175-175

Standalone Query Builder Instantiation

Dialect Query Builder Classes and Entity Kinds

Each database dialect provides a standalone QueryBuilder class that enables constructing queries without an active database session or connection pool. These classes hold an internal entity kind identifier and support lazy loading of their respective dialect instances to prevent circular dependencies during module initialization.

Sources: drizzle-orm/src/gel-core/query-builders/query-builder.ts:13-17, drizzle-orm/src/pg-core/query-builders/query-builder.ts:13-17, drizzle-orm/src/sqlite-core/query-builders/query-builder.ts:12-16, drizzle-orm/src/singlestore-core/query-builders/query-builder.ts:12-16, drizzle-orm/src/mysql-core/query-builders/query-builder.ts:12-16

Proxy Initialization and Subquery Aliasing

The query builder provides $with and with() methods to construct Common Table Expressions (CTEs) and subqueries. When a subquery builder or callback is passed to $with, it wraps the resulting WithSubquery instance in a JavaScript Proxy managed by SelectionProxyHandler.

Copy			return new Proxy(
				new WithSubquery(
					qb.getSQL(),
					selection ?? ('getSelectedFields' in qb ? qb.getSelectedFields() ?? {} : {}) as SelectedFields,
					alias,
					true,
				),
				new SelectionProxyHandler({ alias, sqlAliasedBehavior: 'alias', sqlBehavior: 'error' }),
			) as any;

Sources: drizzle-orm/src/pg-core/query-builders/query-builder.ts:36-44, drizzle-orm/src/sqlite-core/query-builders/query-builder.ts:35-43, drizzle-orm/src/singlestore-core/query-builders/query-builder.ts:35-43, drizzle-orm/src/mysql-core/query-builders/query-builder.ts:35-43, drizzle-orm/src/gel-core/query-builders/query-builder.ts:35-39

Thenable Execution via QueryPromise

Relational and Raw Query Execution Lifecycle

Relational query builders such as MySqlRelationalQuery, GelRelationalQuery, PgRelationalQuery, and SQLiteRelationalQuery extend QueryPromise<TResult>, inheriting then and catch methods that enable them to be awaited directly as asynchronous promises. Each query class implements an execute() method that resolves its underlying session execution path. For PostgreSQL and Gel builders, execution wraps the query in an OpenTelemetry active span via tracer.startActiveSpan('drizzle.operation', ...) before triggering _prepare().execute(). Sources: drizzle-orm/src/mysql-core/query-builders/query.ts:76-79, drizzle-orm/src/mysql-core/query-builders/query.ts:154-156, drizzle-orm/src/gel-core/query-builders/query.ts:65-67, drizzle-orm/src/gel-core/query-builders/query.ts:145-149, drizzle-orm/src/pg-core/query-builders/query.ts:65-67, drizzle-orm/src/pg-core/query-builders/query.ts:152-156, drizzle-orm/src/sqlite-core/query-builders/query.ts:98-100, drizzle-orm/src/sqlite-core/query-builders/query.ts:196-198

Raw Execution and Statement Preparation

Raw query wrappers like SQLiteRaw and GelRaw extend QueryPromise while implementing RunnableQuery, SQLWrapper, and PreparedQuery simultaneously. They accept an explicit execute callback function during construction and supply internal query compilation methods such as getSQL(), getQuery(), and mapResult().

Copyexport class SQLiteRaw<TResult> extends QueryPromise<TResult>
	implements RunnableQuery<TResult, 'sqlite'>, SQLWrapper, PreparedQuery
{
	static override readonly [entityKind]: string = 'SQLiteRaw';
 
	declare readonly _: {
		readonly dialect: 'sqlite';
		readonly result: TResult;
	};
 
	/** @internal */
	config: SQLiteRawConfig;
 
	constructor(
		public execute: () => Promise<TResult>,
		/** @internal */
		public getSQL: () => SQL,
		action: SQLiteRawAction,
		private dialect: SQLiteAsyncDialect,
		private mapBatchResult: (result: unknown) => unknown,
	) {
		super();
		this.config = { action };
	}
...

Sources: drizzle-orm/src/sqlite-core/query-builders/raw.ts:15-38

Sources: drizzle-orm/src/mysql-core/query-builders/query.ts:76-79, drizzle-orm/src/mysql-core/query-builders/query.ts:99-112, drizzle-orm/src/gel-core/query-builders/query.ts:65-67, drizzle-orm/src/gel-core/query-builders/query.ts:112-114, drizzle-orm/src/pg-core/query-builders/query.ts:65-67, drizzle-orm/src/pg-core/query-builders/query.ts:112-114, drizzle-orm/src/sqlite-core/query-builders/query.ts:98-100, drizzle-orm/src/sqlite-core/query-builders/query.ts:164-166, drizzle-orm/src/sqlite-core/query-builders/query.ts:201-206, drizzle-orm/src/sqlite-core/query-builders/raw.ts:15-17, drizzle-orm/src/sqlite-core/query-builders/raw.ts:40-47, drizzle-orm/src/gel-core/query-builders/raw.ts:9-11, drizzle-orm/src/gel-core/query-builders/raw.ts:33-39

Count Query Builder Foundation

SQLWrapper and Promise Integration Mechanics

The SQLiteCountBuilder class extends SQL<number> while implementing both Promise<number> and SQLWrapper. This dual-contract design enables the builder to function simultaneously as a raw SQL chunk container and as an awaitable promise that resolves directly to a numeric count result via its underlying SQLiteSession.

Copyexport class SQLiteCountBuilder<
	TSession extends SQLiteSession<any, any, any, any>,
> extends SQL<number> implements Promise<number>, SQLWrapper {
	private sql: SQL<number>;
 
	static override readonly [entityKind] = 'SQLiteCountBuilderAsync';
	[Symbol.toStringTag] = 'SQLiteCountBuilderAsync';
 
	private session: TSession;
 

Sources: drizzle-orm/src/sqlite-core/query-builders/count.ts:7-16

The constructor accepts a parameter object containing a table or view source, optional filter expressions, and a session instance. It initializes the superclass SQL instance with an embedded count representation using buildEmbeddedCount, while storing a separate standalone count statement generated by buildCount in a private sql property for session execution.

Copyconstructor(
	readonly params: {
		source: SQLiteTable | SQLiteView | SQL | SQLWrapper;
		filters?: SQL<unknown>;
		session: TSession;
	},
) {
	super(SQLiteCountBuilder.buildEmbeddedCount(params.source, params.filters).queryChunks);
 
	this.session = params.session;
 
	this.sql = SQLiteCountBuilder.buildCount(
		params.source,
		params.filters,
	);
}

Sources: drizzle-orm/src/sqlite-core/query-builders/count.ts:31-46

Query Compilation Methods and Lifecycle Handlers

Query compilation relies on private static helper methods that dynamically inject a where clause only when filters are present, leveraging sql.raw(' where ').if(filters).

Copyprivate static buildEmbeddedCount(
	source: SQLiteTable | SQLiteView | SQL | SQLWrapper,
	filters?: SQL<unknown>,
): SQL<number> {
	return sql<number>`(select count(*) from ${source}${sql.raw(' where ').if(filters)}${filters})`;
}
 
private static buildCount(
	source: SQLiteTable | SQLiteView | SQL | SQLWrapper,
	filters?: SQL<unknown>,
): SQL<number> {
	return sql<number>`select count(*) from ${source}${sql.raw(' where ').if(filters)}${filters}`;
}
 

Sources: drizzle-orm/src/sqlite-core/query-builders/count.ts:17-30

Asynchronous promise resolution is implemented via standard then, catch, and finally methods. The then method delegates execution to this.session.count(this.sql), resolving the returned promise with the numeric outcome of the query.

Copythen<TResult1 = number, TResult2 = never>(
	onfulfilled?: ((value: number) => TResult1 | PromiseLike<TResult1>) | null | undefined,
	onrejected?: ((reason: any) => TResult2 | PromiseLike<TResult2>) | null | undefined,
): Promise<TResult1 | TResult2> {
	return Promise.resolve(this.session.count(this.sql)).then(
		onfulfilled,
		onrejected,
	);
}
 
catch(
	onRejected?: ((reason: any) => never | PromiseLike<never>) | null | undefined,
): Promise<number> {
	return this.then(undefined, onRejected);
}
 
finally(onFinally?: (() => void) | null | undefined): Promise<number> {
	return this.then(
		(value) => {
			onFinally?.();
			return value;
		},
		(reason) => {
			onFinally?.();
			throw reason;
		},
	);
}

Sources: drizzle-orm/src/sqlite-core/query-builders/count.ts:48-75

Sources: drizzle-orm/src/sqlite-core/query-builders/count.ts:17-30, drizzle-orm/src/sqlite-core/query-builders/count.ts:48-75

Update Mutation Query Builders

Mutation Query Builder Structure and Generic Contracts

The SQLite update infrastructure relies on SQLiteUpdateBase and the SQLiteUpdate interface to define typing boundaries, result configurations, and execution handles. The SQLiteUpdate interface extends SQLWrapper and QueryPromise, resolving to either TRunResult when no returning clause is specified, or TReturning[] when a returning clause is active.

Copy	TResultType extends 'sync' | 'async' = 'sync' | 'async',
	TRunResult = unknown,
	TFrom extends SQLiteTable | Subquery | SQLiteViewBase | SQL | undefined = undefined,
	TReturning = undefined,
	TDynamic extends boolean = false,
	TExcludedMethods extends string = never,
> extends SQLWrapper, QueryPromise<TReturning extends undefined ? TRunResult : TReturning[]> {
	readonly _: {
		readonly dialect: 'sqlite';
		readonly table: TTable;
		readonly resultType: TResultType;
		readonly runResult: TRunResult;
		readonly from: TFrom;
		readonly returning: TReturning;
		readonly dynamic: TDynamic;
		readonly excludedMethods: TExcludedMethods;
		readonly result: TReturning extends undefined ? TRunResult : TReturning[];
	};
}

Sources: drizzle-orm/src/sqlite-core/query-builders/update.ts:205-223

The base implementation class SQLiteUpdateBase provides the underlying property structure and initializes the builder configuration via its constructor, storing the target table, update set mapping, session instance, dialect reference, and optional CTE with-list.

Copyexport class SQLiteUpdateBase<
	TTable extends SQLiteTable = SQLiteTable,
	// eslint-disable-next-line @typescript-eslint/no-unused-vars
	TResultType extends 'sync' | 'async' = 'sync' | 'async',
	TRunResult = unknown,
	TFrom extends SQLiteTable | Subquery | SQLiteViewBase | SQL | undefined = undefined,
	TReturning = undefined,
	// eslint-disable-next-line @typescript-eslint/no-unused-vars
	TDynamic extends boolean = false,
	// eslint-disable-next-line @typescript-eslint/no-unused-vars
	TExcludedMethods extends string = never,
> extends QueryPromise<TReturning extends undefined ? TRunResult : TReturning[]>
	implements RunnableQuery<TReturning extends undefined ? TRunResult : TReturning[], 'sqlite'>, SQLWrapper
{
	static override readonly [entityKind]: string = 'SQLiteUpdate';
 
	/** @internal */
	config: SQLiteUpdateConfig;
 
	constructor(
		table: TTable,
		set: UpdateSet,
		private session: SQLiteSession<any, any, any, any>,
		private dialect: SQLiteDialect,
		withList?: Subquery[],
	) {
		super();
		this.config = { set, table, withList, joins: [] };
	}
 

Sources: drizzle-orm/src/sqlite-core/query-builders/update.ts:225-254

Update Set Mapping and Execution Lifecycle

Query construction proceeds by capturing mutation fields through the constructor's set parameter (UpdateSet) and configuring secondary clauses such as source tables via the from method, which returns an extended SQLiteUpdateWithJoins builder instance.

Copyfrom<TFrom extends SQLiteTable | Subquery | SQLiteViewBase | SQL>(
	source: TFrom,
): SQLiteUpdateWithJoins<this, TDynamic, TFrom> {
	this.config.from = source;

Sources: drizzle-orm/src/sqlite-core/query-builders/update.ts:255-258

Sources: drizzle-orm/src/sqlite-core/query-builders/update.ts:205-223

Type-Level Query Composition and Typing

Selection Validation and Nullability Mappings

Type-level composition handles join semantics, select modes, and nullability propagation through conditional mapped types. The JoinType union specifies supported join categories, while JoinNullability and ApplyNullability manage column and table row optionality based on join participation.

Copyexport type JoinType = 'inner' | 'left' | 'right' | 'full' | 'cross';
 
export type JoinNullability = 'nullable' | 'not-null';
 
export type ApplyNullability<T, TNullability extends JoinNullability> = TNullability extends 'nullable' ? T | null
	: TNullability extends 'null' ? null
	: T;

Sources: drizzle-orm/src/query-builders/select.types.ts:9-15

Sources: drizzle-orm/src/query-builders/select.types.ts:137-147

Sources: drizzle-orm/src/query-builders/select.types.ts:122-124

Result Inference and Dynamic Builders

Select execution results resolve dynamically across 'partial', 'single', and 'multiple' modes using SelectResult. Type testing frameworks validate these inferences against expected output shapes, ensuring that chained methods like where, having, groupBy, orderBy, and for respect single-invocation constraints.

Copyexport type SelectMode = 'partial' | 'single' | 'multiple';
 
export type SelectResult<
	TResult,
	TSelectMode extends SelectMode,
	TNullabilityMap extends Record<string, JoinNullability>,
> = TSelectMode extends 'partial' ? SelectPartialResult<TResult, TNullabilityMap>
	: TSelectMode extends 'single' ? SelectResultFields<TResult>
	: ApplyNotNullMapToJoins<SelectResultFields<TResult>, TNullabilityMap>;

Sources: drizzle-orm/src/query-builders/select.types.ts:37-45

Sources: drizzle-orm/type-tests/geldb/select.ts:1004-1020

Related

• SQL Template Engine
• Sessions and Transactions