--- title: "SQL Template Engine" description: "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 a..." 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/sql-template-engine" ---
Relevant source files The following files were used as context for generating this wiki page: - [drizzle-orm/src/sql/sql.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts) - [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/singlestore-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/dialect.ts) - [drizzle-orm/src/sql/expressions/conditions.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/expressions/conditions.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/sqlite-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/dialect.ts) - [drizzle-orm/src/gel-core/expressions.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/expressions.ts) - [drizzle-orm/src/singlestore-core/expressions.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/expressions.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/query-builders/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/select.ts) - [drizzle-orm/src/gel-core/session.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/session.ts) - [drizzle-orm/type-tests/sqlite/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/sqlite/select.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/sql/expressions/select.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/expressions/select.ts) - [drizzle-orm/src/sql/functions/aggregate.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts) - [drizzle-orm/src/sql/functions/vector.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/vector.ts) - [drizzle-orm/src/d1/migrator.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/d1/migrator.ts) - [drizzle-orm/src/durable-sqlite/migrator.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/durable-sqlite/migrator.ts) - [drizzle-orm/src/libsql/migrator.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/libsql/migrator.ts)
## 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L1-L73), [drizzle-orm/src/sql/sql.ts:89-130](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L89-L130), [drizzle-orm/src/sql/expressions/conditions.ts:1-35](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/expressions/conditions.ts#L1-L35), [drizzle-orm/src/sql/functions/aggregate.ts:1-21](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts#L1-L21), [drizzle-orm/src/sql/functions/vector.ts:1-39](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/vector.ts#L1-L39) ## 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. ```typescript export type Chunk = | string | Table | View | AnyColumn | Name | Param | Placeholder | SQL; ``` Sources: [drizzle-orm/src/sql/sql.ts:22-30](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L22-L30) 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. ```typescript export interface SQLWrapper { getSQL(): SQL; shouldOmitSQLParens?(): boolean; } ``` Sources: [drizzle-orm/src/sql/sql.ts:65-68](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L65-L68) The table below lists the core AST classes and wrappers, their entity kinds, and their runtime roles during query generation. | Class / Interface | Entity Kind | Role in AST | | :--- | :--- | :--- | | `SQL` | `'SQL'` | Root or nested container holding an array of `SQLChunk` instances, track-used tables, and driver encoders. | | `StringChunk` | `'StringChunk'` | Holds raw literal SQL string arrays that require no escaping or parameterization. | | `SQLWrapper` | N/A (Interface) | Protocol implemented by entities capable of returning an internal `SQL` AST via `getSQL()`. | | `FakePrimitiveParam` | `'FakePrimitiveParam'` | Type-level marker indicating a primitive parameter used in templates; never instantiated at runtime. | Sources: [drizzle-orm/src/sql/sql.ts:18-20](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L18-L20), [drizzle-orm/src/sql/sql.ts:89-96](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L89-L96), [drizzle-orm/src/sql/sql.ts:103-110](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L103-L110) > [!NOTE] > `FakePrimitiveParam` exists strictly at the TypeScript type level to represent primitive parameter values within `sql` template tags, remaining completely uninstantiated during runtime execution. Sources: [drizzle-orm/src/sql/sql.ts:13-17](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L13-L17) ### 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. ```typescript 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L118-L130) > [!IMPORTANT] > When compiling subqueries or nested expressions, `SQL` instances propagate their `shouldInlineParams` flag down the tree during recursive query building to override default parameter placeholder behavior. Sources: [drizzle-orm/src/sql/sql.ts:188-193](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L188-L193) ## 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. ```typescript export interface BuildQueryConfig { casing: CasingCache; escapeName(name: string): string; escapeParam(num: number, value: unknown): string; escapeString(str: string): string; prepareTyping?: (encoder: DriverValueEncoder) => QueryTypingsValue; paramStartIndex?: { value: number }; inlineParams?: boolean; invokeSource?: 'indexes' | undefined; } ``` Sources: [drizzle-orm/src/sql/sql.ts:32-41](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L32-L41) 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. | Query Typing Value | Description / Driver Mapping | | :--- | :--- | | `'json'` | Indicates JSON-encoded columns or payload structures. | | `'decimal'` | Represents arbitrary-precision numeric decimal types. | | `'time'` | Designates time-of-day temporal values. | | `'timestamp'` | Represents combined date and time stamps. | | `'uuid'` | Designates universally unique identifier types. | | `'date'` | Represents calendar date values. | | `'none'` | Default untyped driver parameter binding. | Sources: [drizzle-orm/src/sql/sql.ts:43-43](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L43-L43) > [!NOTE] > `FakePrimitiveParam` serves as a type-level marker representing primitive parameters inside template string literals without incurring runtime instantiation overhead. Sources: [drizzle-orm/src/sql/sql.ts:13-17](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L13-L17) ### 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`. ```typescript 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L302-L323) > [!WARNING] > Object parameters whose `toString()` representation evaluates to `[object Object]` are automatically serialized via `JSON.stringify` before string escaping when parameter inlining is enabled. Sources: [drizzle-orm/src/sql/sql.ts:315-321](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L315-L321) ## 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. ```typescript 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L137-L146) > [!IMPORTANT] > When query chunks contain arrays, the compiler automatically parenthesizes the items and joins them with commas, constructing an expanded chunk array before re-invoking `buildQueryFromSourceParams`. Sources: [drizzle-orm/src/sql/sql.ts:176-186](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L176-L186) ### Chunk Serialization Handlers Different AST chunk types trigger distinct formatting rules during query generation, handling identifiers, schemas, parameters, and wrapper structures. | Chunk Type | Serialization Behavior | | :--- | :--- | | `StringChunk` | Concatenates literal string segments with empty parameter arrays. | | `Name` | Escapes identifier names using `escapeName`. | | `Table` | Formats schema-qualified table names or bare names when aliased. | | `Column` | Resolves column casing via `casing.getColumnCasing` and prefixes table identifiers unless queried within `indexes`. | | `View` | Formats schema-qualified view identifiers or alias names. | | `Param` | Binds parameters through `escapeParam`, tracking parameter indices and driver encoders. | | `Placeholder` | Generates parameter placeholders using the active `paramStartIndex`. | | `Subquery` | Formats inline subqueries with parentheses and aliases, or referenced CTE names when `isWith` is set. | Sources: [drizzle-orm/src/sql/sql.ts:164-282](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L164-L282) > [!NOTE] > If a `Param` chunk contains a nested `SQL` instance after encoder mapping, the compiler unwraps and processes it recursively via `buildQueryFromSourceParams`. Sources: [drizzle-orm/src/sql/sql.ts:238-242](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/sql.ts#L238-L242) ## 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. ```typescript export 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/expressions/conditions.ts#L17-L30) Binary operators construct equality, inequality, and directional comparisons using `sql`` template tags combined with `bindIfParam`. | Operator Function | SQL Expression Generated | Description | | :--- | :--- | :--- | | `eq` | `${left} = ${bindIfParam(right, left)}` | Tests equality between two expressions. | | `ne` | `${left} <> ${bindIfParam(right, left)}` | Tests inequality between two expressions. | | `gt` | `${left} > ${bindIfParam(right, left)}` | Tests strictly greater-than. | | `gte` | `${left} >= ${bindIfParam(right, left)}` | Tests greater-than or equal-to. | | `lt` | `${left} < ${bindIfParam(right, left)}` | Tests strictly less-than. | | `lte` | `${left} <= ${bindIfParam(right, left)}` | Tests less-than or equal-to. | Sources: [drizzle-orm/src/sql/expressions/conditions.ts:62-86](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/expressions/conditions.ts#L62-L86), [drizzle-orm/src/sql/expressions/conditions.ts:195-253](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/expressions/conditions.ts#L195-L253) > [!WARNING] > The SQL standard specifies that two `NULL` values are not equal. To check for `NULL` values, developers should use `isNull` instead of `eq(column, null)`. Sources: [drizzle-orm/src/sql/expressions/conditions.ts:46-51](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/expressions/conditions.ts#L46-L51) ### 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. ```typescript export function and( ...unfilteredConditions: (SQLWrapper | undefined)[] ): SQL | undefined { const conditions = unfilteredConditions.filter( (c): c is Exclude => 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/expressions/conditions.ts#L105-L125) 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. ```typescript export 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/expressions/conditions.ts#L322-L334) > [!NOTE] > Passing an empty array to `notInArray` evaluates to `true` to preserve valid SQL syntax when filtering dynamically. Sources: [drizzle-orm/src/sql/expressions/conditions.ts:326-329](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/expressions/conditions.ts#L326-L329) ## 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. ```typescript export 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/expressions.ts#L8-L25), [drizzle-orm/src/singlestore-core/expressions.ts:8-25](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/expressions.ts#L8-L25) 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. > [!NOTE] > Both Gel and SingleStore implement identical signatures and AST assembly logic for `concat` and `substring`, utilizing `sql.join` to merge fragmented expression chunks into valid parameterized statements. Sources: [drizzle-orm/src/gel-core/expressions.ts:8-25](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/expressions.ts#L8-L25), [drizzle-orm/src/singlestore-core/expressions.ts:8-25](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/expressions.ts#L8-L25) ## 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. ```typescript export function count(expression?: SQLWrapper): SQL { return sql`count(${expression || sql.raw('*')})`.mapWith(Number); } export function countDistinct(expression: SQLWrapper): SQL { return sql`count(distinct ${expression})`.mapWith(Number); } ``` Sources: [drizzle-orm/src/sql/functions/aggregate.ts:19-37](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts#L19-L37) The following table summarizes the available aggregate functions, their SQL template structures, and their return type mappings: | Function | Template Expression | Return Type | Source Lines | | :--- | :--- | :--- | :--- | | `count` | `count(${expression \|\| sql.raw('*')})` | `number` | [aggregate.ts:19-21](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts#L19-L21) | | `countDistinct` | `count(distinct ${expression})` | `number` | [aggregate.ts:35-37](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts#L35-L37) | | `avg` | `avg(${expression})` | `string \| null` | [aggregate.ts:51-53](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts#L51-L53) | | `avgDistinct` | `avg(distinct ${expression})` | `string \| null` | [aggregate.ts:67-69](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts#L67-L69) | | `sum` | `sum(${expression})` | `string \| null` | [aggregate.ts:83-85](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts#L83-L85) | | `sumDistinct` | `sum(distinct ${expression})` | `string \| null` | [aggregate.ts:99-101](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts#L99-L101) | | `max` | `max(${expression})` | Column data type or `string \| null` | [aggregate.ts:113-115](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts#L113-L115) | | `min` | `min(${expression})` | Column data type or `string \| null` | [aggregate.ts:127-129](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts#L127-L129) | > [!NOTE] > Functions like `max` and `min` dynamically inspect whether the passed expression is an instance of `Column`. If so, they map the result to the column's native data type; otherwise, they fall back to `String`. Sources: [drizzle-orm/src/sql/functions/aggregate.ts:113-115](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts#L113-L115), [drizzle-orm/src/sql/functions/aggregate.ts:127-129](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/aggregate.ts#L127-L129) ### 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. ```typescript function toSql(value: number[] | string[]): string { return JSON.stringify(value); } export function l2Distance( column: SQLWrapper | AnyColumn, value: number[] | string[] | TypedQueryBuilder | 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](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/vector.ts#L5-L39) The vector distance helpers and their corresponding operators are mapped below: | Function Name | Operator | Description | Source Lines | | :--- | :--- | :--- | :--- | | `l2Distance` | `<->` | Euclidean (L2) distance | [vector.ts:31-39](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/vector.ts#L31-L39) | | `l1Distance` | `<+>` | Taxicab (L1) distance | [vector.ts:61-69](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/vector.ts#L61-L69) | | `innerProduct` | `<#>` | Negative inner product | [vector.ts:93-101](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/vector.ts#L93-L101) | | `cosineDistance` | `<=>` | Cosine distance | [vector.ts:125-133](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/vector.ts#L125-L133) | | `hammingDistance` | `<~>` | Hamming distance | [vector.ts:150-158](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/vector.ts#L150-L158) | | `jaccardDistance` | `<%>` | Jaccard distance | [vector.ts:170-178](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/vector.ts#L170-L178) | > [!WARNING] > Passing raw JavaScript arrays to vector distance functions triggers `JSON.stringify` serialization via `toSql`, whereas passing query builders or string expressions embeds them directly into the generated AST without JSON wrapping. Sources: [drizzle-orm/src/sql/functions/vector.ts:5-39](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sql/functions/vector.ts#L5-L39) ## Related - [Query Builder Foundation](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/core-architecture/query-builder-foundation) - [Table and Column Models](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/core-architecture/table-and-column-models) ## Sitemap See the full [sitemap](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/llms.txt) for all pages in this wiki.