---
title: "Update"
description: "Updating data using Tauri SQLite ORM."
canonical_url: "https://docs.ctrl-neo.dev/en/tauri-sqlite-orm/querying-data/updating-data"
---
# Update

> Updating data using Tauri SQLite ORM.

The `db.update()` method allows you to modify existing records in your database. It returns a query builder that lets you specify which records to update and what changes to make.

## Basic Update

To update records, use `.set()` to specify the new values and `.where()` to target specific rows:

```ts
import { db } from '$lib/db';
import { users } from '$lib/db/schema';
import { eq } from '@type32/tauri-sqlite-orm';

// Update a user's name
await db.update(users)
  .set({ fullName: 'Jane Smith' })
  .where(eq(users._.columns.id, 1))
  .execute();
```

## WHERE Clause Validation

**Important:** For safety, UPDATE queries **require** a WHERE clause by default. This prevents accidental mass updates.

```ts
// ❌ This will throw an error!
await db.update(users)
  .set({ isActive: false })
  .execute();
// Error: UPDATE operation on table "users" requires a WHERE clause

// ✅ This works - has a WHERE clause
await db.update(users)
  .set({ isActive: false })
  .where(eq(users._.columns.id, 123))
  .execute();
```

### Intentional Global Updates

If you genuinely want to update all rows, use `.allowGlobalOperation()`:

```ts
// Reset all daily counts - explicitly allowed
await db.update(stats)
  .set({ dailyCount: 0 })
  .allowGlobalOperation()
  .execute();
```

## Updating Multiple Columns

You can update multiple columns in one operation:

```ts
await db.update(users)
  .set({
    fullName: 'Jane Smith',
    email: 'jane.smith@example.com',
    updatedAt: new Date()
  })
  .where(eq(users._.columns.id, 1))
  .execute();
```

## Increment and Decrement

For counters and numeric fields, use atomic increment/decrement operations to avoid race conditions:

```ts
// Increment views by 1
await db.update(posts)
  .increment('views')
  .where(eq(posts._.columns.id, 123))
  .execute();

// Increment by a specific amount
await db.update(posts)
  .increment('views', 5)
  .where(eq(posts._.columns.id, 123))
  .execute();

// Decrement likes
await db.update(posts)
  .decrement('likes')
  .where(eq(posts._.columns.id, 123))
  .execute();

// Decrement by a specific amount
await db.update(posts)
  .decrement('likes', 2)
  .where(eq(posts._.columns.id, 123))
  .execute();
```

### Why Use Increment/Decrement?

These methods are better than reading and updating because they're atomic:

```ts
// ❌ BAD: Race condition possible
const post = await db.select(posts).where(eq(posts._.columns.id, 123)).first();
await db.update(posts)
  .set({ views: post.views + 1 })
  .where(eq(posts._.columns.id, 123))
  .execute();

// ✅ GOOD: Atomic operation, no race condition
await db.update(posts)
  .increment('views')
  .where(eq(posts._.columns.id, 123))
  .execute();
```

### Combining Operations

You can combine increment/decrement with regular `.set()`:

```ts
await db.update(posts)
  .set({ lastViewed: new Date() })
  .increment('views')
  .where(eq(posts._.columns.id, 123))
  .execute();

// Update multiple counters at once
await db.update(posts)
  .increment('views', 5)
  .increment('shares')
  .decrement('reportCount')
  .where(eq(posts._.columns.id, 123))
  .execute();
```

## Conditional Updates

You can use complex WHERE clauses to update only specific records:

```ts
import { and, gt, lt } from '@type32/tauri-sqlite-orm';

// Archive old, inactive posts
await db.update(posts)
  .set({ status: 'archived' })
  .where(
    and(
      lt(posts._.columns.createdAt, oneYearAgo),
      eq(posts._.columns.status, 'draft')
    )
  )
  .execute();
```

## Returning Updated Data

Use `.returning()`, `.returningAll()`, or `.returningFirst()` to get the updated records back:

### `.returningFirst()` - For Single Row Updates

When updating a single record, use `.returningFirst()` for cleaner code:

```ts
// ✅ Clean - returns single object
const updatedUser = await db.update(users)
  .set({ fullName: 'Jane Smith' })
  .where(eq(users._.columns.id, 1))
  .returningFirst();

if (updatedUser) {
  console.log('Updated:', updatedUser.fullName);
}
// Type: InferSelectModel<typeof users> | undefined
```

### `.returningAll()` - For Multiple Rows

When updating multiple records:

```ts
// Return all updated records
const updatedUsers = await db.update(users)
  .set({ isActive: true })
  .where(eq(users._.columns.status, 'pending'))
  .returningAll();

console.log(`Updated ${updatedUsers.length} users`);
// Type: InferSelectModel<typeof users>[]
```

### `.returning()` - For Specific Columns

Return only the columns you need:

```ts
// Return specific columns
const updated = await db.update(users)
  .set({ fullName: 'Jane Smith' })
  .where(eq(users._.columns.id, 1))
  .returning('id', 'fullName', 'email')
  .execute();
```

## Upsert

To insert a record or update it if it already exists, use `db.upsert()`:

```ts
// Insert or update user by email
await db.upsert(
  users,
  { email: 'john@example.com', fullName: 'John Doe', isActive: true },
  ['email']  // Conflict target (unique column)
);

// Equivalent to:
await db.insert(users)
  .values({ email: 'john@example.com', fullName: 'John Doe', isActive: true })
  .onConflictDoUpdate({
    target: users._.columns.email,
    set: { email: 'john@example.com', fullName: 'John Doe', isActive: true }
  })
  .execute();
```

## Best Practices

1. **Always use WHERE clauses** - Prevents accidental mass updates
2. **Use increment/decrement for counters** - Avoids race conditions
3. **Use .returningAll() when you need the updated data** - More efficient than separate SELECT
4. **Document global operations** - Add comments when using `.allowGlobalOperation()`

## Example: User Profile Update

```ts
async function updateUserProfile(userId: number, updates: {
  fullName?: string;
  email?: string;
  bio?: string;
}) {
  // Validate user exists first
  const exists = await db.select(users)
    .where(eq(users._.columns.id, userId))
    .exists();
  
  if (!exists) {
    throw new Error('User not found');
  }
  
  // Update and return the updated user (single record)
  const updatedUser = await db.update(users)
    .set({
      ...updates,
      updatedAt: new Date()
    })
    .where(eq(users._.columns.id, userId))
    .returningFirst();
  
  if (!updatedUser) {
    throw new Error('Update failed');
  }
  
  return updatedUser;
}
```


## Sitemap

See the full [sitemap](https://docs.ctrl-neo.dev/sitemap.md) for all pages.