Getting Started
Database Drivers
Schema Integrations
Seed Generators
Utilities and Testing
The following files were used as context for generating this wiki page:
Select queries in Drizzle ORM are responsible for transforming high-level, type-safe builder configurations into raw SQL statements appropriate for the target database dialect (PostgreSQL, MySQL, or Gel). This subsystem provides the bridge between the developer's intuitive query builder API and the database-specific syntax requirements, ensuring that complex joins, conditional clauses, and set operations are rendered into valid, performant queries.
The architecture centers around dialect-specific classes—PgDialect, GelDialect, and MySqlDialect—which implement common query-building interfaces. By isolating dialect-specific concerns like quoting identifiers (escapeName), param handling (escapeParam), and specific SQL syntax patterns (like limit or lockingClause), the system maintains a unified developer experience regardless of the underlying database engine.
At a high level, the flow involves taking a SelectConfig object, validating the query's structural integrity, building component chunks (e.g., CTEs, selections, joins, where clauses), and joining them into a final object. This modular design allows Drizzle to handle complex features like nested relational queries and set operations consistently across different SQL dialects.
SQLbuildSelectQuery MechanismThe buildSelectQuery method is the heart of the selection subsystem. It accepts a configuration object and orchestrates the assembly of various SQL components. A critical validation step occurs before building: it iterates over selected fields to ensure that every referenced column exists within the query's tables or joins.
If a field is found that does not belong to any table currently in the query, buildSelectQuery throws an error to prevent invalid SQL execution. This check is performed by comparing the table of the field with the table(s) defined in the query configuration, including those added via joins.
// Example of validation logic (from PgDialect)
for (const f of fieldsList) {
if (is(f.field, Column) && getTableName(f.field.table) !== getTableName(table) && !joins?.some(...)) {
throw new Error(`Your field references a column "${tableName}"."${f.field.name}", but the table is not part of the query!`);
}
}The buildSelection method manages the complexity of selecting columns versus expressions. It performs a case-based analysis on each field in the ordered selection list:
isSingleTable is enabled, it attempts to normalize column references by applying the dialect-specific column casing.isSingleTable is true, it replaces the column object with its cased identifier.The isSingleTable flag is crucial: it determines whether columns need to be prefixed with their table names, which is often unnecessary in single-table queries but required in joins to resolve ambiguity.
The buildJoins mechanism processes an array of JoinConfig objects. It iterates through the meta-data, determining the join type (e.g., inner, left) and handling specific database features like lateral joins.
The logic builds a dynamic SQL join array:
on clause).USE, FORCE, and IGNORE index hints.Tip
Use .leftJoin() or .innerJoin() in the query builder to trigger these specific buildJoins branches. Ensuring your aliases are unique is critical, as they serve as the keys for later selection resolution.
When setOperators are present in the SelectConfig, buildSelectQuery delegates to buildSetOperations. This function uses recursion to collapse multiple set operations into a single query structure.
The recursive mechanism (buildSetOperations) pulls the first operator, processes it against the base query (leftSelect), and if there are more operators, it feeds the resulting SQL back into the function recursively. This effectively nests set operations, preserving order and precedence according to SQL standards.
The query lifecycle traces from the user-facing builder methods to the final dialect-specific execution.
The following example demonstrates building a complex joined query with custom SQL selection:
import { sql } from 'drizzle-orm';
import { db } from './db';
import { users, cities } from './tables';
// The query builder will internally call PgDialect.buildSelectQuery()
const result = await db
.select({
id: users.id,
text: users.text,
city: {
id: cities.id,
name: cities.name,
},
custom: sql<string>`upper(${users.text})`.as('custom_text')
})
.from(users)
.leftJoin(cities, eq(users.id, cities.id))
.where(eq(users.id, 1));Caution
The validation logic in buildSelectQuery is strict. If you use a column in a selection that belongs to a table not present in the .from() or .join() clauses, the dialect will throw an error immediately, preventing the query from being sent to the database. Always verify your join path.