Querying Data
Delete
Deleting data using Tauri SQLite ORM.
The db.delete() method allows you to remove records from your database. Like UPDATE, it requires careful use to avoid accidentally deleting all your data.
Basic Delete
To delete records, use .where() to specify which rows to remove:
import { db } from '$lib/db';
import { users } from '$lib/db/schema';
import { eq } from '@type32/tauri-sqlite-orm';
// Delete a specific user
await db.delete(users)
.where(eq(users._.columns.id, 1))
.execute();
WHERE Clause Validation
Critical: DELETE queries require a WHERE clause by default to prevent accidental data loss.
// ❌ This will throw an error!
await db.delete(users).execute();
// Error: DELETE operation on table "users" requires a WHERE clause
// ✅ This works - has a WHERE clause
await db.delete(users)
.where(eq(users._.columns.id, 123))
.execute();
Intentional Global Deletes
If you genuinely want to delete all rows, use .allowGlobalOperation():
// Clear all temporary cache - explicitly allowed
await db.delete(tempCache)
.allowGlobalOperation()
.execute();
Conditional Deletes
Use operators to delete based on complex conditions:
import { and, lt, eq } from '@type32/tauri-sqlite-orm';
// Delete old, inactive users
await db.delete(users)
.where(
and(
lt(users._.columns.lastLogin, oneYearAgo),
eq(users._.columns.isActive, false)
)
)
.execute();
// Delete unpublished drafts older than 30 days
await db.delete(posts)
.where(
and(
eq(posts._.columns.status, 'draft'),
lt(posts._.columns.createdAt, thirtyDaysAgo)
)
)
.execute();
Returning Deleted Data
Use .returning(), .returningAll(), or .returningFirst() to get the deleted records before they're removed:
.returningFirst() - For Single Row Deletes
When deleting a single record, use .returningFirst() for cleaner code:
// ✅ Clean - returns single object
const deletedUser = await db.delete(users)
.where(eq(users._.columns.id, 1))
.returningFirst();
if (deletedUser) {
console.log('Deleted user:', deletedUser.fullName, deletedUser.email);
// Could log this for audit purposes
}
// Type: InferSelectModel<typeof users> | undefined
.returningAll() - For Multiple Rows
When deleting multiple records:
// Get all deleted user data
const deletedUsers = await db.delete(users)
.where(eq(users._.columns.status, 'banned'))
.returningAll();
console.log(`Deleted ${deletedUsers.length} banned users`);
// Type: InferSelectModel<typeof users>[]
.returning() - For Specific Columns
Return only the columns you need:
// Return specific columns only
const deletedInfo = await db.delete(users)
.where(eq(users._.columns.status, 'banned'))
.returning('id', 'email')
.execute();
Soft Deletes
Instead of actually deleting rows, you might want to mark them as deleted (soft delete):
// Instead of deleting, mark as deleted
await db.update(users)
.set({
deletedAt: new Date(),
isDeleted: true
})
.where(eq(users._.columns.id, userId))
.execute();
// Query only non-deleted users
const activeUsers = await db.select(users)
.where(eq(users._.columns.isDeleted, false))
.all();
Cascade Deletes
SQLite supports foreign key constraints with CASCADE. Define them in your schema:
import { sqliteTable, integer, text } from '@type32/tauri-sqlite-orm';
export const users = sqliteTable('users', {
id: integer('id').primaryKey().autoincrement(),
name: text('name').notNull(),
});
export const posts = sqliteTable('posts', {
id: integer('id').primaryKey().autoincrement(),
title: text('title').notNull(),
authorId: integer('author_id').notNull().references(() => users._.columns.id),
// When a user is deleted, their posts will also be deleted (if CASCADE is enabled in SQLite)
});
Best Practices
- Always use WHERE clauses - Prevents accidental data loss
- Consider soft deletes - For user data that might need recovery
- Use
.returningAll()for audit logs - Keep track of what was deleted - Test delete operations - Verify you're deleting the right data
- Use transactions for related deletes - Ensure atomicity
Example: Safe User Deletion
async function deleteUser(userId: number) {
// 1. Check if user exists
const user = await db.select(users)
.where(eq(users._.columns.id, userId))
.first();
if (!user) {
throw new Error('User not found');
}
// 2. Delete in a transaction to ensure atomicity
return await db.transaction(async (tx) => {
// Delete user's posts
await tx.delete(posts)
.where(eq(posts._.columns.authorId, userId))
.execute();
// Delete the user and return their data (single record)
const deletedUser = await tx.delete(users)
.where(eq(users._.columns.id, userId))
.returningFirst();
if (!deletedUser) {
throw new Error('Delete failed');
}
return deletedUser;
});
}
Example: Cleanup Old Records
async function cleanupOldRecords() {
const thirtyDaysAgo = new Date();
thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);
// Delete old temporary files
const result = await db.delete(tempFiles)
.where(lt(tempFiles._.columns.createdAt, thirtyDaysAgo))
.execute();
console.log(`Deleted ${result[0].rowsAffected} old files`);
}
Response Format
Without .returning(), delete operations return the number of affected rows:
const result = await db.delete(users)
.where(eq(users._.columns.id, 1))
.execute();
console.log(result);
// [{ rowsAffected: 1 }]