--- title: "Dialects And Schema Query Builders" description: "The Drizzle ORM query building architecture relies on dialect-specific compiler components coupled with fluent query builder classes to transform high-level method chains into parameterized SQL AST..." last_updated: "2026-10-12T08:14:00.734644+00:00" canonical_url: "https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/dialects-and-schema/dialects-and-schema-query-builders" ---
Relevant source files The following files were used as context for generating this wiki page: - [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/gel-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/dialect.ts) - [drizzle-orm/src/pg-core/query-builders/insert.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/insert.ts) - [drizzle-orm/type-tests/pg/tables.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/tables.ts) - [drizzle-orm/src/pg-core/query-builders/refresh-materialized-view.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/refresh-materialized-view.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/singlestore-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/dialect.ts) - [drizzle-orm/src/gel-core/query-builders/refresh-materialized-view.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/refresh-materialized-view.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/pg-core/query-builders/update.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/update.ts) - [drizzle-orm/src/pg-core/db.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/db.ts) - [drizzle-orm/src/sqlite-core/query-builders/insert.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/insert.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/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/pg-core/query-builders/index.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/index.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/singlestore-core/query-builders/insert.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/query-builders/insert.ts) - [drizzle-orm/src/pg-core/view.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/view.ts) - [drizzle-orm/src/pg-core/query-builders/delete.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/delete.ts) - [drizzle-orm/type-tests/sqlite/insert.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/sqlite/insert.ts) - [drizzle-orm/type-tests/pg/insert.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/type-tests/pg/insert.ts) - [drizzle-orm/src/gel-core/query-builders/index.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/index.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/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/query-builders/update.ts](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/update.ts)
## Overview The Drizzle ORM query building architecture relies on dialect-specific compiler components coupled with fluent query builder classes to transform high-level method chains into parameterized SQL AST fragments. Each database dialect (PostgreSQL, Gel, SQLite, MySQL, and SingleStore) provides its own dialect driver class that manages identifier escaping, parameter formatting, casing cache transformations, and clause assembly for insert, update, delete, and select operations. Sources: [drizzle-orm/src/pg-core/dialect.ts:609-618](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/dialect.ts#L609-L618) ```mermaid flowchart TD QB["Query Builder API
(.insert(), .select(), .update())"] --> Config["Query Configuration Object"] Config --> Dialect["Dialect Compiler
(PgDialect, SQLiteDialect, etc.)"] Dialect --> SQLAST["SQL AST & Chunks"] SQLAST --> QueryWithTypings["sql.toQuery()"] QueryWithTypings --> PreparedQuery["Prepared Statement / Session Execution"] ``` Sources: [drizzle-orm/src/sqlite-core/dialect.ts:596-604](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/dialect.ts#L596-L604) --- ## Gel Query Builders The Gel query builder subsystem shares architectural lineages with PostgreSQL-core query builders, utilizing `GelDialect`, `GelInsertBase`, `GelSelectBase`, and `GelUpdateBase` to compile queries targeting Gel databases. Insert configurations handle column filtering via `col.shouldDisableInsert()`, fallback to column default functions (`col.defaultFn`) or `onUpdateFn`, and compile `returning` and `on conflict` clauses when present. Sources: [drizzle-orm/src/gel-core/dialect.ts:508-575](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/dialect.ts#L508-L575) ```typescript const inserted = await db.insert(users) .values({ name: 'Gel User' }) .returning(); ``` Sources: [drizzle-orm/src/gel-core/query-builders/insert.ts:220-279](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/insert.ts#L220-L279) The Gel selection framework leverages `GelSelectBuilder` and `GelSelectQueryBuilderBase` to manage complex relation projections, join configurations, and set operators (`union`, `intersect`, `except`) with strict key validation checks. Sources: [drizzle-orm/src/gel-core/query-builders/select.ts:60-145](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/select.ts#L60-L145) > [!NOTE] > Gel query builder methods validate set operator compatibility by inspecting whether left and right selection keys match identically and maintain the same order. Sources: [drizzle-orm/src/gel-core/query-builders/select.ts:515-520](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/select.ts#L515-L520) ```typescript if (!haveSameKeys((leftSelect as any).getSelectedFields(), setOperator.rightSelect.getSelectedFields())) { throw new Error( 'Set operator error (union / intersect / except): selected fields are not the same or are in a different order', ); } ``` Sources: [drizzle-orm/src/gel-core/query-builders/select.ts:1119-1125](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/gel-core/query-builders/select.ts#L1119-L1125) --- ## MySQL Query Builders MySQL-core query compilation is managed by `MySqlDialect` alongside `MySqlInsertConfig` and related query builders. When compiling insert queries via `buildInsertQuery`, the dialect filters out insert-disabled columns and iterates over input value records. Sources: [drizzle-orm/src/mysql-core/dialect.ts:561-567](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/dialect.ts#L561-L567) ```typescript buildInsertQuery({ table, values: valuesOrSelect, ignore, onConflict, select, }: MySqlInsertConfig): { sql: SQL; generatedIds: Record[] } { ``` Sources: [drizzle-orm/src/mysql-core/dialect.ts:561-567](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/dialect.ts#L561-L567) For each column entry during an insert operation, if a field value is `undefined` or an unassigned `Param`, the dialect checks column defaults (`col.defaultFn`) or update functions (`col.onUpdateFn`), populates generated ID tracking dictionaries, and constructs `ignore` and `on duplicate key` clauses. Sources: [drizzle-orm/src/mysql-core/dialect.ts:586-645](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/mysql-core/dialect.ts#L586-L645) > [!IMPORTANT] > MySQL does not support a native `do nothing` conflict clause directly; developers achieve this behavior by setting a unique column to its own value (e.g., `{ id: sql`id` }`) within `.onDuplicateKeyUpdate()`. Sources: [drizzle-orm/src/singlestore-core/query-builders/insert.ts:246-253](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/query-builders/insert.ts#L246-L253) ```typescript onDuplicateKeyUpdate( config: SingleStoreInsertOnDuplicateKeyUpdateConfig, ): SingleStoreInsertWithout { const setSql = this.dialect.buildUpdateSet(this.config.table, mapUpdateSet(this.config.table, config.set)); this.config.onConflict = sql`update ${setSql}`; return this as any; } ``` Sources: [drizzle-orm/src/singlestore-core/query-builders/insert.ts:246-253](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/query-builders/insert.ts#L246-L253) --- ## PostgreSQL Query Builders PostgreSQL dialect compilation (`PgDialect`) drives advanced clause generation including CTEs (`withList`), column casing identifiers, system value overrides (`overriding system value`), and conflict resolution. Sources: [drizzle-orm/src/pg-core/dialect.ts:513-515](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/dialect.ts#L513-L515) ```typescript buildInsertQuery( { table, values: valuesOrSelect, onConflict, returning, withList, select, overridingSystemValue_ }: PgInsertConfig, ): SQL { ``` Sources: [drizzle-orm/src/pg-core/dialect.ts:513-515](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/dialect.ts#L513-L515) The `PgInsertBase` class provides conflict handling methods `.onConflictDoNothing()` and `.onConflictDoUpdate()`. Sources: [drizzle-orm/src/pg-core/query-builders/insert.ts:369-389](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/insert.ts#L369-L389) ```typescript onConflictDoUpdate( config: PgInsertOnConflictDoUpdateConfig, ): PgInsertWithout { if (config.where && (config.targetWhere || config.setWhere)) { throw new Error( 'You cannot use both "where" and "targetWhere"/"setWhere" at the same time - "where" is deprecated, use "targetWhere" or "setWhere" instead.', ); } const whereSql = config.where ? sql` where ${config.where}` : undefined; const targetWhereSql = config.targetWhere ? sql` where ${config.targetWhere}` : undefined; const setWhereSql = config.setWhere ? sql` where ${config.setWhere}` : undefined; const setSql = this.dialect.buildUpdateSet(this.config.table, mapUpdateSet(this.config.table, config.set)); let targetColumn = ''; targetColumn = Array.isArray(config.target) ? config.target.map((it) => this.dialect.escapeName(this.dialect.casing.getColumnCasing(it))).join(',') : this.dialect.escapeName(this.dialect.casing.getColumnCasing(config.target)); this.config.onConflict = sql`(${ sql.raw(targetColumn) })${targetWhereSql} do update set ${setSql}${whereSql}${setWhereSql}`; return this as any; } ``` Sources: [drizzle-orm/src/pg-core/query-builders/insert.ts:369-389](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/insert.ts#L369-L389) PostgreSQL views and materialized views support execution refreshing via `PgRefreshMaterializedView`. Sources: [drizzle-orm/src/pg-core/query-builders/refresh-materialized-view.ts:30-65](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/refresh-materialized-view.ts#L30-L65) ```typescript export class PgRefreshMaterializedView extends QueryPromise> implements RunnableQuery, 'pg'>, SQLWrapper { static override readonly [entityKind]: string = 'PgRefreshMaterializedView'; private config: { view: PgMaterializedView; concurrently?: boolean; withNoData?: boolean; }; constructor( view: PgMaterializedView, private session: PgSession, private dialect: PgDialect, ) { super(); this.config = { view }; } concurrently(): this { if (this.config.withNoData !== undefined) { throw new Error('Cannot use concurrently and withNoData together'); } this.config.concurrently = true; return this; } withNoData(): this { if (this.config.concurrently !== undefined) { throw new Error('Cannot use concurrently and withNoData together'); } this.config.withNoData = true; return this; } ``` Sources: [drizzle-orm/src/pg-core/query-builders/refresh-materialized-view.ts:30-65](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/refresh-materialized-view.ts#L30-L65) > [!CAUTION] > Attempting to combine `.concurrently()` and `.withNoData()` on a materialized view refresh triggers an explicit error invariant check in both PostgreSQL and Gel implementations. Sources: [drizzle-orm/src/pg-core/query-builders/refresh-materialized-view.ts:51-54](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/refresh-materialized-view.ts#L51-L54) ```typescript if (this.config.withNoData !== undefined) { throw new Error('Cannot use concurrently and withNoData together'); } ``` Sources: [drizzle-orm/src/pg-core/query-builders/refresh-materialized-view.ts:51-54](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/pg-core/query-builders/refresh-materialized-view.ts#L51-L54) --- ## SingleStore Query Builders SingleStore query building is directed by `SingleStoreDialect` and `SingleStoreInsertBase`. The dialect handles migration bootstrapping by verifying and creating the `__drizzle_migrations` table via `session.execute()`, and executes insert queries supporting `ignore` flags and duplicate key updates. Sources: [drizzle-orm/src/singlestore-core/dialect.ts:53-66](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/dialect.ts#L53-L66) ```typescript buildInsertQuery({ table, values, ignore, onConflict, }: SingleStoreInsertConfig): { sql: SQL; generatedIds: Record[]; } { ``` Sources: [drizzle-orm/src/singlestore-core/dialect.ts:519-527](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/dialect.ts#L519-L527) When executing inserts, SingleStore tracks primary key auto-increment columns to generate response IDs, formatting clauses with `on duplicate key update`. Sources: [drizzle-orm/src/singlestore-core/query-builders/insert.ts:246-253](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/query-builders/insert.ts#L246-L253) ```typescript onDuplicateKeyUpdate( config: SingleStoreInsertOnDuplicateKeyUpdateConfig, ): SingleStoreInsertWithout { const setSql = this.dialect.buildUpdateSet(this.config.table, mapUpdateSet(this.config.table, config.set)); this.config.onConflict = sql`update ${setSql}`; return this as any; } ``` Sources: [drizzle-orm/src/singlestore-core/query-builders/insert.ts:246-253](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/singlestore-core/query-builders/insert.ts#L246-L253) --- ## SQLite Query Builders SQLite query compilation (`SQLiteDialect`) manages parameter formatting (`?`), string escaping (`escapeString`), and identifier quoting (`escapeName`). Sources: [drizzle-orm/src/sqlite-core/dialect.ts:57-67](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/dialect.ts#L57-L67) ```typescript escapeName(name: string): string { return `"${name.replace(/"/g, '""')}"`; } escapeParam(_num: number): string { return '?'; } escapeString(str: string): string { return `'${str.replace(/'/g, "''")}'`; } ``` Sources: [drizzle-orm/src/sqlite-core/dialect.ts:57-67](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/dialect.ts#L57-L67) The `SQLiteInsertBase` builder handles table insertion records, falling back to column defaults or `null` values when parameters are omitted, and supports `.returning()` and `.onConflictDoNothing()` / `.onConflictDoUpdate()` clauses. Sources: [drizzle-orm/src/sqlite-core/query-builders/insert.ts:306-317](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/insert.ts#L306-L317) ```typescript onConflictDoNothing(config: { target?: IndexColumn | IndexColumn[]; where?: SQL } = {}): this { if (!this.config.onConflict) this.config.onConflict = []; if (config.target === undefined) { this.config.onConflict.push(sql` on conflict do nothing`); } else { const targetSql = Array.isArray(config.target) ? sql`${config.target}` : sql`${[config.target]}`; const whereSql = config.where ? sql` where ${config.where}` : sql``; this.config.onConflict.push(sql` on conflict ${targetSql} do nothing${whereSql}`); } return this; } ``` Sources: [drizzle-orm/src/sqlite-core/query-builders/insert.ts:306-317](https://github.com/blade47/drizzle-orm/blob/48e5406027103a9fca6eb66417187c4a8b5c6aa3/drizzle-orm/src/sqlite-core/query-builders/insert.ts#L306-L317) ## Related - [Dialects And Schema Schema Definition](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/dialects-and-schema/dialects-and-schema-schema-definition) - [Drivers And Runtimes](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/drivers-and-runtimes/drivers-and-runtimes) ## Sitemap See the full [sitemap](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/llms.txt) for all pages in this wiki.