SQL Template Engine

Overview

The SQL template engine in Drizzle ORM provides a powerful programmatic foundation for constructing typesafe SQL queries, expressions, and database operations through tagged template literals and abstract syntax tree (AST) nodes. It solves the complexity of dynamic query composition by turning nested SQL chunks, parameters, tables, columns, and subqueries into structured trees that compile cleanly into driver-ready queries with proper parameter binding and escaping. Core classes like SQL, StringChunk, and Param manage query structures, while helper functions for conditions, aggregates, and vector operations enable expressive query building across dialects.

Sources: drizzle-orm/src/sql/sql.ts:1-73, drizzle-orm/src/sql/sql.ts:89-130, drizzle-orm/src/sql/expressions/conditions.ts:1-35, drizzle-orm/src/sql/functions/aggregate.ts:1-21, drizzle-orm/src/sql/functions/vector.ts:1-39

SQL Chunks and AST Structure

Chunk Union and AST Node Types

The core AST representation revolves around the Chunk union type and the SQL class, which hold fragments, metadata, and nested child nodes. Every part of a compiled query starts as a chunk or a wrapper implementing the SQLWrapper interface.

The Chunk type defines all valid nodes that can be assembled inside an SQL statement tree. These include raw string pieces, database tables, views, columns, raw name identifiers, parameters, placeholders, and nested SQL instances.

Copyexport type Chunk =
	| string
	| Table
	| View
	| AnyColumn
	| Name
	| Param
	| Placeholder
	| SQL;

Sources: drizzle-orm/src/sql/sql.ts:22-30

Any value or schema model implementing the getSQL() method satisfies the SQLWrapper interface, allowing custom builders and database constructs to integrate directly into query compilation trees.

Copyexport interface SQLWrapper {
	getSQL(): SQL;
	shouldOmitSQLParens?(): boolean;
}

Sources: drizzle-orm/src/sql/sql.ts:65-68

The table below lists the core AST classes and wrappers, their entity kinds, and their runtime roles during query generation.

Sources: drizzle-orm/src/sql/sql.ts:18-20, drizzle-orm/src/sql/sql.ts:89-96, drizzle-orm/src/sql/sql.ts:103-110

Sources: drizzle-orm/src/sql/sql.ts:13-17

SQL Container and Used Tables Tracking

The SQL class manages an array of query chunks (queryChunks: SQLChunk[]). Upon instantiation, the constructor inspects each chunk; if the chunk is a Table instance, it extracts schema prefixes and table names, registering them in the usedTables array for reference tracking.

Copy	constructor(readonly queryChunks: SQLChunk[]) {
		for (const chunk of queryChunks) {
			if (is(chunk, Table)) {
				const schemaName = chunk[Table.Symbol.Schema];
 
				this.usedTables.push(
					schemaName === undefined
						? chunk[Table.Symbol.Name]
						: schemaName + '.' + chunk[Table.Symbol.Name],
				);
			}
		}
	}

Sources: drizzle-orm/src/sql/sql.ts:118-130

Sources: drizzle-orm/src/sql/sql.ts:188-193

Template Tag and Parameter Binding

Query Configuration and Typings Values

Query building configurations dictate how placeholders, parameters, escape sequences, and casing rules are processed during AST compilation. The BuildQueryConfig interface provides the required callbacks and state containers for name escaping, parameter formatting, string literals, casing caches, and index generation contexts.

Copyexport interface BuildQueryConfig {
	casing: CasingCache;
	escapeName(name: string): string;
	escapeParam(num: number, value: unknown): string;
	escapeString(str: string): string;
	prepareTyping?: (encoder: DriverValueEncoder<unknown, unknown>) => QueryTypingsValue;
	paramStartIndex?: { value: number };
	inlineParams?: boolean;
	invokeSource?: 'indexes' | undefined;
}

Sources: drizzle-orm/src/sql/sql.ts:32-41

Supported driver typing annotations determine how database parameter values are serialized or cast across driver boundaries. The table below outlines every allowed QueryTypingsValue constant recognized by the query compiler.

Sources: drizzle-orm/src/sql/sql.ts:43-43

Sources: drizzle-orm/src/sql/sql.ts:13-17

Parameter Binding and Inline Mapping

Parameter processing inspects chunks during query building to determine whether values should be bound via driver parameters or serialized directly into the SQL text string. When inlineParams is active, or when dealing with unmapped parameters, values are converted via mapInlineParam.

Copy	private mapInlineParam(
		chunk: unknown,
		{ escapeString }: BuildQueryConfig,
	): string {
		if (chunk === null) {
			return 'null';
		}
		if (typeof chunk === 'number' || typeof chunk === 'boolean') {
			return chunk.toString();
		}
		if (typeof chunk === 'string') {
			return escapeString(chunk);
		}
		if (typeof chunk === 'object') {
			const mappedValueAsString = chunk.toString();
			if (mappedValueAsString === '[object Object]') {
				return escapeString(JSON.stringify(chunk));
			}
			return escapeString(mappedValueAsString);
		}
		throw new Error('Unexpected param value: ' + chunk);
	}

Sources: drizzle-orm/src/sql/sql.ts:302-323

Sources: drizzle-orm/src/sql/sql.ts:315-321

Query Resolution and Placeholder Substitution

Query Building Control Flow

The buildQueryFromSourceParams method processes arrays of query chunks recursively, dispatching each node type through specialized serialization branches. Telemetry spans wrap query generation via tracer.startActiveSpan, emitting query text and parameters to tracing diagnostics.

Copy	toQuery(config: BuildQueryConfig): QueryWithTypings {
		return tracer.startActiveSpan('drizzle.buildSQL', (span) => {
			const query = this.buildQueryFromSourceParams(this.queryChunks, config);
			span?.setAttributes({
				'drizzle.query.text': query.sql,
				'drizzle.query.params': JSON.stringify(query.params),
			});
			return query;
		});
	}

Sources: drizzle-orm/src/sql/sql.ts:137-146

Sources: drizzle-orm/src/sql/sql.ts:176-186

Chunk Serialization Handlers

Different AST chunk types trigger distinct formatting rules during query generation, handling identifiers, schemas, parameters, and wrapper structures.

Sources: drizzle-orm/src/sql/sql.ts:164-282

Sources: drizzle-orm/src/sql/sql.ts:238-242

Comparison and Logical Expressions

Parameter Binding and Condition Operators

Condition tree constructors leverage template literals and helper functions defined in drizzle-orm/src/sql/expressions/conditions.ts to build SQL AST nodes. The bindIfParam function inspects values against column encoders, wrapping raw values into Param instances when appropriate while leaving columns, tables, views, placeholders, parameters, and SQL wrappers unhindered.

Copyexport function bindIfParam(value: unknown, column: SQLWrapper): SQLChunk {
	if (
		isDriverValueEncoder(column)
		&& !isSQLWrapper(value)
		&& !is(value, Param)
		&& !is(value, Placeholder)
		&& !is(value, Column)
		&& !is(value, Table)
		&& !is(value, View)
	) {
		return new Param(value, column);
	}
	return value as SQLChunk;
}

Sources: drizzle-orm/src/sql/expressions/conditions.ts:17-30

Binary operators construct equality, inequality, and directional comparisons using sql`` template tags combined with bindIfParam`.

Sources: drizzle-orm/src/sql/expressions/conditions.ts:62-86, drizzle-orm/src/sql/expressions/conditions.ts:195-253

Sources: drizzle-orm/src/sql/expressions/conditions.ts:46-51

Logical and Array Membership Combinators

Logical combinators filter out undefined conditions dynamically before assembling expression trees. If an and or or call receives an empty list of valid conditions, it returns undefined. If it receives a single valid condition, it returns that SQL instance directly without wrapping parentheses.

Copyexport function and(
	...unfilteredConditions: (SQLWrapper | undefined)[]
): SQL | undefined {
	const conditions = unfilteredConditions.filter(
		(c): c is Exclude<typeof c, undefined> => c !== undefined,
	);
 
	if (conditions.length === 0) {
		return undefined;
	}
 
	if (conditions.length === 1) {
		return new SQL(conditions);
	}
 
	return new SQL([
		new StringChunk('('),
		sql.join(conditions, new StringChunk(' and ')),
		new StringChunk(')'),
	]);
}

Sources: drizzle-orm/src/sql/expressions/conditions.ts:105-125

Negation, array membership, and null checks complete the condition toolkit. The not function prefixes any expression with the not keyword. Array checks handle empty arrays specially for notInArray, returning a literal true expression when no values are provided.

Copyexport function notInArray(
	column: SQLWrapper,
	values: (unknown | Placeholder)[] | SQLWrapper,
): SQL {
	if (Array.isArray(values)) {
		if (values.length === 0) {
			return sql`true`;
		}
		return sql`${column} not in ${values.map((v) => bindIfParam(v, column))}`;
	}
 
	return sql`${column} not in ${bindIfParam(values, column)}`;
}

Sources: drizzle-orm/src/sql/expressions/conditions.ts:322-334

Sources: drizzle-orm/src/sql/expressions/conditions.ts:326-329

Dialect Expressions and String Functions

Dialect Expression Helpers and String Operations

Dialect-specific modules provide specialized string manipulation helpers that integrate driver parameter binding with database-native string syntax. Both the Gel and SingleStore core modules export custom concat and substring functions that accept column references or aliased fields along with string values, placeholders, or wrapped SQL fragments.

Copyexport function concat(column: GelColumn | SQL.Aliased, value: string | Placeholder | SQLWrapper): SQL {
	return sql`${column} || ${bindIfParam(value, column)}`;
}
 
export function substring(
	column: GelColumn | SQL.Aliased,
	{ from, for: _for }: { from?: number | Placeholder | SQLWrapper; for?: number | Placeholder | SQLWrapper },
): SQL {
	const chunks: SQLChunk[] = [sql`substring(`, column];
	if (from !== undefined) {
		chunks.push(sql` from `, bindIfParam(from, column));
	}
	if (_for !== undefined) {
		chunks.push(sql` for `, bindIfParam(_for, column));
	}
	chunks.push(sql`)`);
	return sql.join(chunks);
}

Sources: drizzle-orm/src/gel-core/expressions.ts:8-25, drizzle-orm/src/singlestore-core/expressions.ts:8-25

The concat helper utilizes the standard concatenation operator (||) and passes the right-hand value through bindIfParam to ensure proper encoder mapping and driver parameterization. The substring helper constructs dynamic chunk lists for extraction queries, conditionally appending from and for modifiers when defined.

Sources: drizzle-orm/src/gel-core/expressions.ts:8-25, drizzle-orm/src/singlestore-core/expressions.ts:8-25

Aggregate and Vector Functions

Standard Aggregate Builders

Drizzle provides standard aggregate function wrappers constructed via the sql template literal. The count function accepts an optional expression and defaults to * via sql.raw('*') when omitted, mapping its resulting type to number. Distinct variants such as countDistinct, avgDistinct, and sumDistinct prepend the distinct keyword inside the expression block.

Copyexport function count(expression?: SQLWrapper): SQL<number> {
	return sql`count(${expression || sql.raw('*')})`.mapWith(Number);
}
 
export function countDistinct(expression: SQLWrapper): SQL<number> {
	return sql`count(distinct ${expression})`.mapWith(Number);
}

Sources: drizzle-orm/src/sql/functions/aggregate.ts:19-37

The following table summarizes the available aggregate functions, their SQL template structures, and their return type mappings:

Sources: drizzle-orm/src/sql/functions/aggregate.ts:113-115, drizzle-orm/src/sql/functions/aggregate.ts:127-129

Vector Distance Operator Abstractions

Vector distance operations combine column references or SQL wrappers with distance operators and target values. The internal helper toSql serializes array inputs via JSON.stringify, while non-array inputs are embedded directly into the template string.

Copyfunction toSql(value: number[] | string[]): string {
	return JSON.stringify(value);
}
 
export function l2Distance(
	column: SQLWrapper | AnyColumn,
	value: number[] | string[] | TypedQueryBuilder<any> | string,
): SQL {
	if (Array.isArray(value)) {
		return sql`${column} <-> ${toSql(value)}`;
	}
	return sql`${column} <-> ${value}`;
}

Sources: drizzle-orm/src/sql/functions/vector.ts:5-39

The vector distance helpers and their corresponding operators are mapped below:

Sources: drizzle-orm/src/sql/functions/vector.ts:5-39

Related

• Query Builder Foundation
• Table and Column Models