---
title: "Modifying Data"
description: "Modifying data involves inserting, updating, upserting, and deleting records across database tables. You can perform these operations safely and expressively using type-safe query builders across P..."
last_updated: "2026-10-12T08:15:21.955407+00:00"
canonical_url: "https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/guide/core-features/modifying-data"
---

## Overview

### Modifying Database Records

Modifying data involves inserting, updating, upserting, and deleting records across database tables. You can perform these operations safely and expressively using type-safe query builders across PostgreSQL, MySQL, SQLite, Gel, and SingleStore databases.

### Inserting Records

To create new rows in a database table, use the `insert` method combined with `.values()`. You can insert a single record, multiple records at once, or use subqueries and returning clauses where supported.

```ts
// Insert one row
await db.insert(cars).values({ brand: 'BMW' });

// Insert multiple rows
await db.insert(cars).values([{ brand: 'BMW' }, { brand: 'Porsche' }]);

// Insert with a returning clause to retrieve inserted data
const insertedCar = await db.insert(cars)
  .values({ brand: 'BMW' })
  .returning();
```

> [!TIP]
> When working with databases that support returning clauses (like PostgreSQL, SQLite, and Gel), you can immediately fetch the newly created rows or specific columns by chaining `.returning()`.

### Updating Records

To modify existing records, use the `update` method along with `.set()` to specify the new values and `.where()` to target specific rows. 

```ts
// Update rows matching a condition
await db.update(cars)
  .set({ color: 'red' })
  .where(eq(cars.brand, 'BMW'));

// Update with a returning clause
const updatedCar = await db.update(cars)
  .set({ color: 'red' })
  .where(eq(cars.id, 1))
  .returning();
```

> [!WARNING]
> Omitting the `.where()` clause on an update query will modify **every single row** in the table. Always double-check your filters before running bulk updates.

### Handling Upserts and Conflicts

#### PostgreSQL, SQLite, and Gel
Use `.onConflictDoNothing()` or `.onConflictDoUpdate()` to handle key collisions gracefully:

```ts
// Skip the insert if a conflict occurs on the target column
await db.insert(cars)
  .values({ id: 1, brand: 'BMW' })
  .onConflictDoNothing({ target: cars.id });

// Update the existing record if a conflict occurs
await db.insert(cars)
  .values({ id: 1, brand: 'BMW' })
  .onConflictDoUpdate({
    target: cars.id,
    set: { brand: 'Porsche' }
  });
```

#### MySQL and SingleStore
Use `.onDuplicateKeyUpdate()` when inserting data into tables with unique or primary key constraints:

```ts
import { sql } from 'drizzle-orm';

await db.insert(cars)
  .values({ id: 1, brand: 'BMW' })
  .onDuplicateKeyUpdate({ set: { brand: 'Porsche' } });
```

### Deleting Records

To remove rows from a table, use the `delete` method with an optional `.where()` filter.

```ts
// Delete specific rows matching a condition
await db.delete(cars).where(eq(cars.color, 'green'));

// Delete with a returning clause
const deletedCar = await db.delete(cars)
  .where(eq(cars.id, 1))
  .returning();
```

> [!CAUTION]
> Running `db.delete(table)` without a `.where()` clause deletes all records in the table. Ensure you include a filter unless you intend to clear the entire table.

## Related

- [Querying Records](https://www.doc0.dev/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/guide/core-features/querying-records)


## Sitemap

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