Querying 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:
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.
// ❌ 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():
// 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:
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:
// 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:
// ❌ 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():
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:
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:
// ✅ 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:
// 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:
// 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():
// 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
- Always use WHERE clauses - Prevents accidental mass updates
- Use increment/decrement for counters - Avoids race conditions
- Use
.returningAll()when you need the updated data - More efficient than separate SELECT - Document global operations - Add comments when using
.allowGlobalOperation()
Example: User Profile Update
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;
}