Getting Started
Database Drivers
Schema Integrations
Seed Generators
Utilities and Testing
The following files were used as context for generating this wiki page:
Data mutations in Drizzle ORM facilitate the controlled modification of database state via INSERT, UPDATE, and DELETE operations. By decoupling query construction from execution, the system provides a type-safe abstraction over SQL dialects while ensuring that complex column constraints, default values, and conflict resolution strategies are handled consistently across different database providers (MySQL, SingleStore, Gel, PostgreSQL).
The architecture treats mutations as composable SQL builders. When a developer invokes a mutation method—such as .values() on an insert query builder—they are populating a configuration object that captures the intent. The dialect then translates this configuration into an executable object. This mechanism relies on the dialect-specific classes (e.g., , , ) to enforce casing conventions, handle parameter escaping, and generate vendor-specific SQL clauses, such as for MySQL/SingleStore or for PostgreSQL-style dialects.
SQLMySqlDialectSingleStoreDialectGelDialectON DUPLICATE KEY UPDATERETURNINGBeyond mere abstraction, this layer serves as a safety guard against common SQL pitfalls. By programmatically constructing these queries using the schema configuration (e.g., table-specific column definitions, default function execution, and runtime param injection), Drizzle prevents issues like missing mandatory fields or invalid identifier escaping before the query ever hits the database driver.
The INSERT mutation lifecycle centers on capturing values and resolving default behaviors before serialization. For SingleStore and Gel, the process begins with query builder classes (SingleStoreInsertBuilder, GelInsertBuilder), which collect data from the user and map them into Param or SQL wrappers.
When getSQL() or prepare() is called, the dialect orchestrates the final transformation. A critical mechanism here is the column iteration loop within buildInsertQuery. It filters columns based on shouldDisableInsert() (typically used for auto-generated IDs or specific DB-controlled fields) and checks for missing values to apply defaultFn logic.
db.insert(table).values(...) → Initializes SingleStoreInsertBase or GelInsertBase with user-provided configuration..values() → Sanitizes and parameterizes the input objects..prepare() → Invokes dialect.buildInsertQuery() to construct the raw SQL and collect metadata about generated IDs..execute() → Calls session.prepareQuery() to pass the SQL to the driver and perform the operation.Sources: drizzle-orm/src/singlestore-core/query-builders/insert.ts:50-282, drizzle-orm/src/gel-core/query-builders/insert.ts:237-405
Data mutations frequently require handling race conditions where existing unique constraints might be violated. The onDuplicateKeyUpdate (MySQL/SingleStore) and onConflict (Postgres/Gel) patterns are primary mechanisms for this.
For MySQL/SingleStore, the onDuplicateKeyUpdate method injects a specific onConflict clause into the configuration. The dialect builder then appends on duplicate key update followed by the result of buildUpdateSet. This allows developers to pass partial update objects, which the dialect then converts into standard SQL set expressions.
Tip
To simulate a "do nothing" behavior on conflict in MySQL/SingleStore, use the onDuplicateKeyUpdate method and set a column to itself: onDuplicateKeyUpdate({ set: { id: sqlid } }). This effectively performs a no-op update that satisfies the clause requirement.
Sources: drizzle-orm/src/singlestore-core/query-builders/insert.ts:246-252, drizzle-orm/src/mysql-core/dialect.ts:638-640
The buildUpdateSet function is the engine behind UPDATE operations. It accepts a table definition and a set of changes, iterating through the table's columns to generate the col = value assignments.
Key logic inside this method handles two specific cases:
onUpdateFn (like a timestamp auto-updater) and no explicit value is provided, the builder executes the function to generate the SQL clause.// Core logic for building update sets in MySQL/SingleStore
const columnNames = Object.keys(tableColumns).filter(
(colName) =>
set[colName] !== undefined
|| tableColumns[colName]?.onUpdateFn !== undefined,
);
// Each result is formatted as: `column_name = value`Sources: drizzle-orm/src/mysql-core/dialect.ts:149-176, drizzle-orm/src/singlestore-core/dialect.ts:148-175
Some dialects (Postgres, Gel) support a RETURNING clause to fetch the modified row state directly from the mutation. In the Drizzle dialect implementations, buildSelection is shared by both SELECT and RETURNING queries.
When returning is provided in a mutation config, the dialect wraps the fields in the returning SQL keyword. If isSingleTable is true, the builder avoids prefixing columns with table identifiers, which is often cleaner for single-target mutations.
The following example demonstrates how to perform a mutation in the SingleStore dialect, using the builder API to handle potential unique key conflicts.
import { sql } from 'drizzle-orm';
// Assuming 'users' is a defined SingleStoreTable
await db.insert(users)
.values({ id: 1, email: 'john@example.com' })
.onDuplicateKeyUpdate({
set: {
email: 'new_email@example.com',
updatedAt: sql`now()` // Use SQL function for timestamp
}
});This request flows through:
SingleStoreInsertBuilder.values(): Registers the insert value.SingleStoreInsertBase.onDuplicateKeyUpdate(): Sets the conflict configuration.dialect.buildUpdateSet(): Computes the SQL chunk for the update clause.dialect.buildInsertQuery(): Aggregates the insert and update chunks into the final statement.