---
title: "Data Mutations"
description: "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 provide..."
last_updated: "2026-08-13T14:49:35.713971+00:00"
canonical_url: "https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/query-engine/data-mutations"
---

<details>
<summary>Relevant source files</summary>

The following files were used as context for generating this wiki page:

- [drizzle-orm/src/mysql-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts)
- [drizzle-orm/src/singlestore-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/dialect.ts)
- [drizzle-orm/src/gel-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts)
- [drizzle-orm/src/singlestore-core/query-builders/insert.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/insert.ts)
- [drizzle-orm/src/gel-core/query-builders/insert.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/query-builders/insert.ts)
- [drizzle-orm/src/gel-core/db.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/db.ts)
- [drizzle-orm/src/pg-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts)
</details>

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 `SQL` object. This mechanism relies on the dialect-specific classes (e.g., `MySqlDialect`, `SingleStoreDialect`, `GelDialect`) to enforce casing conventions, handle parameter escaping, and generate vendor-specific SQL clauses, such as `ON DUPLICATE KEY UPDATE` for MySQL/SingleStore or `RETURNING` for PostgreSQL-style dialects.

Beyond 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.

## Insert Lifecycle and Execution

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.

### Call Chain: Executing an Insert
1. `db.insert(table).values(...)` → Initializes `SingleStoreInsertBase` or `GelInsertBase` with user-provided configuration.
2. `.values()` → Sanitizes and parameterizes the input objects.
3. `.prepare()` → Invokes `dialect.buildInsertQuery()` to construct the raw `SQL` and collect metadata about generated IDs.
4. `.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](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/insert.ts#L50-L282), [drizzle-orm/src/gel-core/query-builders/insert.ts:237-405](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/query-builders/insert.ts#L237-L405)

## Conflict Resolution

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: sql`id` } })`. This effectively performs a no-op update that satisfies the clause requirement.

Sources: [drizzle-orm/src/singlestore-core/query-builders/insert.ts:246-252](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/insert.ts#L246-L252), [drizzle-orm/src/mysql-core/dialect.ts:638-640](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L638-L640)

## The Update Set Mechanism

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:
1. **Direct Value Assignment:** User-provided values are mapped to parameters.
2. **Default/Update Functions:** If a column has an `onUpdateFn` (like a timestamp auto-updater) and no explicit value is provided, the builder executes the function to generate the `SQL` clause.

```typescript
// 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](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L149-L176), [drizzle-orm/src/singlestore-core/dialect.ts:148-175](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/dialect.ts#L148-L175)

## Returning Clauses

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.

Sources: [drizzle-orm/src/gel-core/dialect.ts:180-182](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts#L180-L182), [drizzle-orm/src/pg-core/dialect.ts:192-194](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L192-L194)

## Worked Example: Inserting with Conflict Handling

The following example demonstrates how to perform a mutation in the `SingleStore` dialect, using the builder API to handle potential unique key conflicts.

```typescript
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:
1. `SingleStoreInsertBuilder.values()`: Registers the insert value.
2. `SingleStoreInsertBase.onDuplicateKeyUpdate()`: Sets the conflict configuration.
3. `dialect.buildUpdateSet()`: Computes the `SQL` chunk for the update clause.
4. `dialect.buildInsertQuery()`: Aggregates the `insert` and `update` chunks into the final statement.

Sources: [drizzle-orm/src/singlestore-core/query-builders/insert.ts:61-252](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/singlestore-core/query-builders/insert.ts#L61-L252)

## Design Trade-offs

| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Separate Dialect Build Logic** | Platform-specific syntax (e.g., MySQL vs. Postgres) is isolated. | Requires maintaining parallel logic for shared concepts. |
| **Manual Loop-based Construction** | Full control over chunk ordering and escaping. | Higher complexity compared to string-template alternatives. |
| **Proxy-based Selection** | Allows flexible field aliasing and naming. | Introduces runtime overhead in property lookups. |

Sources: [drizzle-orm/src/mysql-core/dialect.ts:44-1000](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L44-L1000), [drizzle-orm/src/gel-core/dialect.ts:51-1436](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts#L51-L1436)

## Related

- [Query Builder Core](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/query-engine/query-builder-core)


## Sitemap

See the full [sitemap](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/llms.txt) for all pages in this wiki.