--- title: "Query Builder Foundation" description: "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..." last_updated: "2026-10-12T08:14:00.746407+00:00" canonical_url: "https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/core-architecture/query-builder-foundation" ---
Relevant source files The following files were used as context for generating this wiki page: - [drizzle-orm/src/gel-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/dialect.ts) - [drizzle-orm/src/sqlite-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/dialect.ts) - [drizzle-orm/src/gel-core/query-builders/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/select.ts) - [drizzle-orm/src/mysql-core/query-builders/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/query-builders/select.ts) - [drizzle-orm/src/sqlite-core/query-builders/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/select.ts) - [drizzle-orm/src/singlestore-core/query-builders/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/query-builders/select.ts) - [drizzle-orm/src/pg-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/dialect.ts) - [drizzle-orm/src/mysql-core/query-builders/query.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/query-builders/query.ts) - [drizzle-orm/src/gel-core/query-builders/query-builder.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/query-builder.ts) - [drizzle-orm/src/pg-core/query-builders/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/select.ts) - [drizzle-orm/src/mysql-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/dialect.ts) - [drizzle-orm/src/gel-core/db.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/db.ts) - [drizzle-orm/src/sql/sql.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts) - [drizzle-orm/src/singlestore-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/dialect.ts) - [drizzle-orm/src/pg-core/query-builders/query-builder.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/query-builder.ts) - [drizzle-orm/src/gel-core/query-builders/query.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/query.ts) - [drizzle-orm/src/pg-core/query-builders/query.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/query.ts) - [drizzle-orm/src/sqlite-core/query-builders/query.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/query.ts) - [drizzle-orm/src/gel-core/query-builders/insert.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/insert.ts) - [drizzle-orm/src/sqlite-core/query-builders/query-builder.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/query-builder.ts) - [drizzle-orm/type-tests/geldb/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/select.ts) - [drizzle-orm/src/sqlite-core/db.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/db.ts) - [drizzle-orm/src/singlestore-core/query-builders/query-builder.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/query-builders/query-builder.ts) - [drizzle-orm/src/sqlite-core/query-builders/update.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/update.ts) - [drizzle-orm/src/mysql-core/query-builders/query-builder.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/query-builders/query-builder.ts) - [drizzle-orm/src/sqlite-core/query-builders/count.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/count.ts) - [drizzle-orm/src/sqlite-core/query-builders/raw.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/raw.ts) - [drizzle-orm/src/gel-core/query-builders/raw.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/raw.ts) - [drizzle-orm/src/query-builders/query-builder.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/query-builders/query-builder.ts) - [drizzle-orm/src/query-builders/select.types.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/query-builders/select.types.ts)
## 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/query-builders/query-builder.ts#L4-L18), [drizzle-orm/src/gel-core/query-builders/select.ts:1013-1060](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/select.ts#L1013-L1060), [drizzle-orm/src/sqlite-core/query-builders/select.ts:129-160](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/select.ts#L129-L160), [drizzle-orm/src/singlestore-core/query-builders/select.ts:133-160](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/query-builders/select.ts#L133-L160) ## 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. ```typescript export abstract class TypedQueryBuilder 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/query-builders/query-builder.ts#L4-L18) 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. | Type Identifier | Parameters | Purpose | | :--- | :--- | :--- | | `JoinType` | none | Defines accepted join variations (`inner`, `left`, `right`, `full`, `cross`) | | `JoinNullability` | none | Specifies row presence nullability (`nullable`, `not-null`) | | `SelectMode` | none | Controls result shape categorization (`partial`, `single`, `multiple`) | | `SetOperator` | none | Specifies set operation types (`union`, `intersect`, `except`) | Sources: [drizzle-orm/src/query-builders/select.types.ts:9-11](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/query-builders/select.types.ts#L9-L11), [drizzle-orm/src/query-builders/select.types.ts:37-37](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/query-builders/select.types.ts#L37-L37), [drizzle-orm/src/query-builders/select.types.ts:175-175](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/query-builders/select.types.ts#L175-L175) > [!NOTE] > `TypedQueryBuilder` does not manage database connections or session execution directly. Concrete subclasses must implement `getSQL()` to translate internal configuration trees into parameterized `SQL` instances that drivers can execute. ## 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. | Module Path | Entity Kind Identifier | Dialect Class | Config Type | | :--- | :--- | :--- | :--- | | `drizzle-orm/src/gel-core/query-builders/query-builder.ts` | `GelQueryBuilder` | `GelDialect` | `GelDialectConfig` | | `drizzle-orm/src/pg-core/query-builders/query-builder.ts` | `PgQueryBuilder` | `PgDialect` | `PgDialectConfig` | | `drizzle-orm/src/sqlite-core/query-builders/query-builder.ts` | `SQLiteQueryBuilder` | `SQLiteDialect`, `SQLiteSyncDialect` | `SQLiteDialectConfig` | | `drizzle-orm/src/singlestore-core/query-builders/query-builder.ts` | `SingleStoreQueryBuilder` | `SingleStoreDialect` | `SingleStoreDialectConfig` | | `drizzle-orm/src/mysql-core/query-builders/query-builder.ts` | `MySqlQueryBuilder` | `MySqlDialect` | `MySqlDialectConfig` | Sources: [drizzle-orm/src/gel-core/query-builders/query-builder.ts:13-17](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/query-builder.ts#L13-L17), [drizzle-orm/src/pg-core/query-builders/query-builder.ts:13-17](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/query-builder.ts#L13-L17), [drizzle-orm/src/sqlite-core/query-builders/query-builder.ts:12-16](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/query-builder.ts#L12-L16), [drizzle-orm/src/singlestore-core/query-builders/query-builder.ts:12-16](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/query-builders/query-builder.ts#L12-L16), [drizzle-orm/src/mysql-core/query-builders/query-builder.ts:12-16](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/query-builders/query-builder.ts#L12-L16) > [!NOTE] > The constructor accepts either an instantiated dialect instance or a dialect configuration object, resolving and storing them conditionally via the `is()` helper check. Sources: [drizzle-orm/src/gel-core/query-builders/query-builder.ts:19-22](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/query-builder.ts#L19-L22), [drizzle-orm/src/pg-core/query-builders/query-builder.ts:19-22](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/query-builder.ts#L19-L22) ### 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`. ```typescript 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/query-builder.ts#L36-L44), [drizzle-orm/src/sqlite-core/query-builders/query-builder.ts:35-43](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/query-builder.ts#L35-L43), [drizzle-orm/src/singlestore-core/query-builders/query-builder.ts:35-43](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/query-builders/query-builder.ts#L35-L43), [drizzle-orm/src/mysql-core/query-builders/query-builder.ts:35-43](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/query-builders/query-builder.ts#L35-L43), [drizzle-orm/src/gel-core/query-builders/query-builder.ts:35-39](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/query-builder.ts#L35-L39) > [!WARNING] > `SelectionProxyHandler` configures `sqlAliasedBehavior` as `'alias'` and `sqlBehavior` as `'error'` for proxying field selections on subqueries, ensuring unaliased column references throw errors during compilation. Sources: [drizzle-orm/src/gel-core/query-builders/query-builder.ts:36-39](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/query-builder.ts#L36-L39), [drizzle-orm/src/pg-core/query-builders/query-builder.ts:36-44](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/query-builder.ts#L36-L44), [drizzle-orm/src/sqlite-core/query-builders/query-builder.ts:35-43](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/query-builder.ts#L35-L43), [drizzle-orm/src/singlestore-core/query-builders/query-builder.ts:35-43](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/query-builders/query-builder.ts#L35-L43), [drizzle-orm/src/mysql-core/query-builders/query-builder.ts:35-43](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/query-builders/query-builder.ts#L35-L43) ## Thenable Execution via QueryPromise ### Relational and Raw Query Execution Lifecycle Relational query builders such as `MySqlRelationalQuery`, `GelRelationalQuery`, `PgRelationalQuery`, and `SQLiteRelationalQuery` extend `QueryPromise`, 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/query-builders/query.ts#L76-L79), [drizzle-orm/src/mysql-core/query-builders/query.ts:154-156](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/query-builders/query.ts#L154-L156), [drizzle-orm/src/gel-core/query-builders/query.ts:65-67](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/query.ts#L65-L67), [drizzle-orm/src/gel-core/query-builders/query.ts:145-149](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/query.ts#L145-L149), [drizzle-orm/src/pg-core/query-builders/query.ts:65-67](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/query.ts#L65-L67), [drizzle-orm/src/pg-core/query-builders/query.ts:152-156](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/query.ts#L152-L156), [drizzle-orm/src/sqlite-core/query-builders/query.ts:98-100](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/query.ts#L98-L100), [drizzle-orm/src/sqlite-core/query-builders/query.ts:196-198](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/query.ts#L196-L198) > [!NOTE] > `SQLiteSyncRelationalQuery` subclasses `SQLiteRelationalQuery` to provide a synchronous `.sync()` method alongside standard async promise resolution, dispatching directly to `.executeRaw()` using the driver's synchronous retrieval methods. Sources: [drizzle-orm/src/sqlite-core/query-builders/query.ts:201-206](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/query.ts#L201-L206) ### 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()`. ```typescript export class SQLiteRaw extends QueryPromise implements RunnableQuery, SQLWrapper, PreparedQuery { static override readonly [entityKind]: string = 'SQLiteRaw'; declare readonly _: { readonly dialect: 'sqlite'; readonly result: TResult; }; /** @internal */ config: SQLiteRawConfig; constructor( public execute: () => Promise, /** @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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/raw.ts#L15-L38) | Class Name | Dialect | Implemented Interfaces / Base | Execution Action Methods | | :--- | :--- | :--- | :--- | | `MySqlRelationalQuery` | MySQL | `QueryPromise` | `execute()`, `prepare()` | | `GelRelationalQuery` | Gel | `QueryPromise`, `RunnableQuery`, `SQLWrapper` | `execute()`, `prepare()`, `_prepare()` | | `PgRelationalQuery` | PostgreSQL | `QueryPromise`, `RunnableQuery`, `SQLWrapper` | `execute()`, `prepare()`, `_prepare()` | | `SQLiteRelationalQuery` | SQLite | `QueryPromise`, `RunnableQuery`, `SQLWrapper` | `execute()`, `prepare()`, `executeRaw()` | | `SQLiteSyncRelationalQuery` | SQLite | Extends `SQLiteRelationalQuery` | `sync()`, `executeRaw()` | | `SQLiteRaw` | SQLite | `QueryPromise`, `RunnableQuery`, `SQLWrapper`, `PreparedQuery` | `execute()`, `mapResult()`, `getQuery()` | | `GelRaw` | Gel | `QueryPromise`, `RunnableQuery`, `SQLWrapper`, `PreparedQuery` | `execute()`, `mapResult()`, `getQuery()` | Sources: [drizzle-orm/src/mysql-core/query-builders/query.ts:76-79](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/query-builders/query.ts#L76-L79), [drizzle-orm/src/mysql-core/query-builders/query.ts:99-112](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/query-builders/query.ts#L99-L112), [drizzle-orm/src/gel-core/query-builders/query.ts:65-67](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/query.ts#L65-L67), [drizzle-orm/src/gel-core/query-builders/query.ts:112-114](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/query.ts#L112-L114), [drizzle-orm/src/pg-core/query-builders/query.ts:65-67](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/query.ts#L65-L67), [drizzle-orm/src/pg-core/query-builders/query.ts:112-114](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/query.ts#L112-L114), [drizzle-orm/src/sqlite-core/query-builders/query.ts:98-100](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/query.ts#L98-L100), [drizzle-orm/src/sqlite-core/query-builders/query.ts:164-166](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/query.ts#L164-L166), [drizzle-orm/src/sqlite-core/query-builders/query.ts:201-206](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/query.ts#L201-L206), [drizzle-orm/src/sqlite-core/query-builders/raw.ts:15-17](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/raw.ts#L15-L17), [drizzle-orm/src/sqlite-core/query-builders/raw.ts:40-47](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/raw.ts#L40-L47), [drizzle-orm/src/gel-core/query-builders/raw.ts:9-11](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/raw.ts#L9-L11), [drizzle-orm/src/gel-core/query-builders/raw.ts:33-39](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/raw.ts#L33-L39) ## Count Query Builder Foundation ### SQLWrapper and Promise Integration Mechanics The `SQLiteCountBuilder` class extends `SQL` while implementing both `Promise` 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`. ```typescript export class SQLiteCountBuilder< TSession extends SQLiteSession, > extends SQL implements Promise, SQLWrapper { private sql: SQL; static override readonly [entityKind] = 'SQLiteCountBuilderAsync'; [Symbol.toStringTag] = 'SQLiteCountBuilderAsync'; private session: TSession; ``` Sources: [drizzle-orm/src/sqlite-core/query-builders/count.ts:7-16](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/count.ts#L7-L16) 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. ```typescript constructor( readonly params: { source: SQLiteTable | SQLiteView | SQL | SQLWrapper; filters?: SQL; 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/count.ts#L31-L46) > [!NOTE] > `SQLiteCountBuilder` maintains two distinct SQL compilation methods: `buildEmbeddedCount` wraps the query inside parentheses for use as an expression, whereas `buildCount` constructs a standalone executable statement. ### 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)`. ```typescript private static buildEmbeddedCount( source: SQLiteTable | SQLiteView | SQL | SQLWrapper, filters?: SQL, ): SQL { return sql`(select count(*) from ${source}${sql.raw(' where ').if(filters)}${filters})`; } private static buildCount( source: SQLiteTable | SQLiteView | SQL | SQLWrapper, filters?: SQL, ): SQL { return sql`select count(*) from ${source}${sql.raw(' where ').if(filters)}${filters}`; } ``` Sources: [drizzle-orm/src/sqlite-core/query-builders/count.ts:17-30](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/count.ts#L17-L30) 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. ```typescript then( onfulfilled?: ((value: number) => TResult1 | PromiseLike) | null | undefined, onrejected?: ((reason: any) => TResult2 | PromiseLike) | null | undefined, ): Promise { return Promise.resolve(this.session.count(this.sql)).then( onfulfilled, onrejected, ); } catch( onRejected?: ((reason: any) => never | PromiseLike) | null | undefined, ): Promise { return this.then(undefined, onRejected); } finally(onFinally?: (() => void) | null | undefined): Promise { return this.then( (value) => { onFinally?.(); return value; }, (reason) => { onFinally?.(); throw reason; }, ); } ``` Sources: [drizzle-orm/src/sqlite-core/query-builders/count.ts:48-75](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/count.ts#L48-L75) | Method Name | Parameters | Return Type | Purpose | | :--- | :--- | :--- | :--- | | `buildEmbeddedCount` | `source`, `filters` | `SQL` | Constructs a parenthesized subquery expression for SQL composition. | | `buildCount` | `source`, `filters` | `SQL` | Constructs an executable standalone `select count(*)` query statement. | | `then` | `onfulfilled`, `onrejected` | `Promise` | Executes session count lookup and hooks promise fulfillment. | | `catch` | `onRejected` | `Promise` | Handles promise rejection flows. | | `finally` | `onFinally` | `Promise` | Executes cleanup callbacks regardless of success or failure. | Sources: [drizzle-orm/src/sqlite-core/query-builders/count.ts:17-30](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/count.ts#L17-L30), [drizzle-orm/src/sqlite-core/query-builders/count.ts:48-75](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/count.ts#L48-L75) ## 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. ```typescript 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 { 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/update.ts#L205-L223) 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. ```typescript export 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 implements RunnableQuery, SQLWrapper { static override readonly [entityKind]: string = 'SQLiteUpdate'; /** @internal */ config: SQLiteUpdateConfig; constructor( table: TTable, set: UpdateSet, private session: SQLiteSession, private dialect: SQLiteDialect, withList?: Subquery[], ) { super(); this.config = { set, table, withList, joins: [] }; } ``` Sources: [drizzle-orm/src/sqlite-core/query-builders/update.ts:225-254](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/update.ts#L225-L254) > [!NOTE] > `SQLiteUpdateBase` enforces entity identification by overriding the static `[entityKind]` symbol with the string `'SQLiteUpdate'`. ### 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. ```typescript from( source: TFrom, ): SQLiteUpdateWithJoins { this.config.from = source; ``` Sources: [drizzle-orm/src/sqlite-core/query-builders/update.ts:255-258](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/update.ts#L255-L258) | Generic Parameter | Default Value | Purpose | | :--- | :--- | :--- | | `TTable` | `SQLiteTable` | Represents the target database table being updated. | | `TResultType` | `'sync' \| 'async'` | Determines whether query resolution is synchronous or asynchronous. | | `TRunResult` | `unknown` | Defines the raw execution result type when no returning clause is used. | | `TFrom` | `undefined` | Models additional source tables, subqueries, views, or raw SQL fragments for joins. | | `TReturning` | `undefined` | Specifies the shape of returned rows if a returning clause is supplied. | | `TDynamic` | `false` | Indicates whether dynamic method chaining restrictions are relaxed. | | `TExcludedMethods` | `never` | Tracks methods that have been disabled via prior builder chains. | Sources: [drizzle-orm/src/sqlite-core/query-builders/update.ts:205-223](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/update.ts#L205-L223) ## 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. ```typescript export type JoinType = 'inner' | 'left' | 'right' | 'full' | 'cross'; export type JoinNullability = 'nullable' | 'not-null'; export type ApplyNullability = TNullability extends 'nullable' ? T | null : TNullability extends 'null' ? null : T; ``` Sources: [drizzle-orm/src/query-builders/select.types.ts:9-15](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/query-builders/select.types.ts#L9-L15) | Join Type | Nullability Mapping Behavior | | :--- | :--- | | `inner` | Appends `'not-null'` to joined table selection. | | `left` | Appends `'nullable'` to joined table selection. | | `right` | Sets existing nullability map to `'nullable'` and assigns `'not-null'` to the joined table. | | `full` | Sets all existing entries and the joined table to `'nullable'`. | | `cross` | Appends `'not-null'` to joined table selection. | Sources: [drizzle-orm/src/query-builders/select.types.ts:137-147](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/query-builders/select.types.ts#L137-L147) > [!WARNING] > Attempting to reference raw, unaliased `SQL` expressions inside subquery selections triggers a `DrizzleTypeError` requiring an explicit alias assignment via `.as()`. Sources: [drizzle-orm/src/query-builders/select.types.ts:122-124](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/query-builders/select.types.ts#L122-L124) ### 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. ```typescript export type SelectMode = 'partial' | 'single' | 'multiple'; export type SelectResult< TResult, TSelectMode extends SelectMode, TNullabilityMap extends Record, > = TSelectMode extends 'partial' ? SelectPartialResult : TSelectMode extends 'single' ? SelectResultFields : ApplyNotNullMapToJoins, TNullabilityMap>; ``` Sources: [drizzle-orm/src/query-builders/select.types.ts:37-45](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/query-builders/select.types.ts#L37-L45) > [!NOTE] > Dynamic query wrappers accept both `GelSelect` and `GelSelectQueryBuilder` instances when enabling runtime flexibility via `$dynamic()`. Sources: [drizzle-orm/type-tests/geldb/select.ts:1004-1020](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/geldb/select.ts#L1004-L1020) ## Related - [SQL Template Engine](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/core-architecture/sql-template-engine) - [Sessions and Transactions](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/core-architecture/sessions-and-transactions) ## Sitemap See the full [sitemap](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/llms.txt) for all pages in this wiki.