UPDATE Operations

Learn how to update existing data in your database using bun-query-builder.

Basic Update

Update with Conditions

await db
  .updateTable('users')
  .set({ active: false })
  .where({ id: 1 })
  .execute()

Update Multiple Columns

await db
  .updateTable('users')
  .set({
    name: 'John Smith',
    email: 'john.smith@example.com',
    updated_at: new Date()
  })
  .where({ id: 1 })
  .execute()

Update with Returning

Return the updated rows:

const updated = await db
  .updateTable('users')
  .set({ status: 'verified' })
  .where({ email_verified: true })
  .returning(['id', 'name', 'status'])
  .execute()

console.log(`Updated ${updated.length} users`)

Conditional Updates

Update with Multiple Conditions

await db
  .updateTable('users')
  .set({ subscription: 'expired' })
  .where('subscription_ends_at', '<', new Date())
  .andWhere('subscription', '!=', 'lifetime')
  .execute()

Update with IN Clause

await db
  .updateTable('posts')
  .set({ status: 'archived' })
  .whereIn('id', [1, 2, 3, 4, 5])
  .execute()

Increment and Decrement

Increment Values

// Increment a column
await db
  .updateTable('posts')
  .increment('views', 1)
  .where({ id: 1 })
  .execute()

// Increment by custom amount
await db
  .updateTable('users')
  .increment('points', 100)
  .where({ id: userId })
  .execute()

Decrement Values

// Decrement a column
await db
  .updateTable('products')
  .decrement('stock', 1)
  .where({ id: productId })
  .execute()

// Ensure non-negative
await db
  .updateTable('products')
  .decrement('stock', quantity)
  .where({ id: productId })
  .where('stock', '>=', quantity)
  .execute()

Batch Updates

updateMany

Update multiple records matching conditions:

await db.updateMany(
  'users',
  { department: 'engineering' },  // where
  { manager_id: newManagerId }     // set
)

Bulk Update with Different Values

// Update multiple records with different values
for (const update of updates) {
  await db
    .updateTable('products')
    .set({ price: update.newPrice })
    .where({ id: update.productId })
    .execute()
}

// Or use transaction for atomicity
await db.transaction(async (trx) => {
  for (const update of updates) {
    await trx
      .updateTable('products')
      .set({ price: update.newPrice })
      .where({ id: update.productId })
      .execute()
  }
})

Raw Updates

Using SQL Expressions

await db
  .updateTable('users')
  .set({
    login_count: db.raw('login_count + 1'),
    last_login: db.raw('CURRENT_TIMESTAMP')
  })
  .where({ id: userId })
  .execute()

JSON Updates

// Update JSON field
await db
  .updateTable('users')
  .set({
    preferences: db.raw("JSON_SET(preferences, '$.theme', 'dark')")
  })
  .where({ id: userId })
  .execute()

Model Hooks

Hooks triggered during updates:

const db = createQueryBuilder({
  schema,
  meta,
  hooks: {
    beforeUpdate: async ({ table, data, where }) => {
      // Add audit timestamp
      data.updated_at = new Date()

      // Validate data
      if (table === 'users' && data.email) {
        if (!isValidEmail(data.email)) {
          throw new Error('Invalid email format')
        }
      }

      return data
    },
    afterUpdate: async ({ table, data, where, result }) => {
      // Log changes
      await logChange(table, where, data)

      // Clear cache
      if (table === 'users') {
        await cache.invalidate(`user:${where.id}`)
      }
    }
  }
})

Soft Deletes

Soft Delete a Record

await db
  .updateTable('users')
  .set({ deleted_at: new Date() })
  .where({ id: 1 })
  .execute()

Restore Soft Deleted

await db
  .updateTable('users')
  .set({ deleted_at: null })
  .where({ id: 1 })
  .execute()

Examples

Update User Profile

async function updateProfile(userId: number, updates: ProfileUpdate) {
  const allowedFields = ['name', 'bio', 'avatar_url', 'website']

  const sanitized = Object.fromEntries(
    Object.entries(updates).filter(([key]) => allowedFields.includes(key))
  )

  if (Object.keys(sanitized).length === 0) {
    return null
  }

  const user = await db
    .updateTable('users')
    .set(sanitized)
    .where({ id: userId })
    .returning(['id', 'name', 'bio', 'avatar_url', 'website'])
    .execute()

  return user
}

Bulk Status Update

async function archiveOldPosts(daysOld: number) {
  const cutoffDate = new Date()
  cutoffDate.setDate(cutoffDate.getDate() - daysOld)

  const result = await db
    .updateTable('posts')
    .set({
      status: 'archived',
      archived_at: new Date()
    })
    .where('created_at', '<', cutoffDate)
    .andWhere('status', '=', 'published')
    .returning(['id'])
    .execute()

  console.log(`Archived ${result.length} posts`)
  return result
}

Inventory Management

async function processOrder(orderId: number, items: OrderItem[]) {
  return db.transaction(async (trx) => {
    for (const item of items) {
      // Check stock
      const product = await trx
        .selectFrom('products')
        .where({ id: item.product_id })
        .first()

      if (product.stock < item.quantity) {
        throw new Error(`Insufficient stock for ${product.name}`)
      }

      // Decrement stock
      await trx
        .updateTable('products')
        .decrement('stock', item.quantity)
        .where({ id: item.product_id })
        .execute()
    }

    // Update order status
    await trx
      .updateTable('orders')
      .set({ status: 'processing' })
      .where({ id: orderId })
      .execute()
  })
}

Optimistic Locking

async function updateWithVersion(
  table: string,
  id: number,
  data: Record<string, any>,
  currentVersion: number
) {
  const result = await db
    .updateTable(table)
    .set({
      ...data,
      version: currentVersion + 1
    })
    .where({ id })
    .andWhere('version', '=', currentVersion)
    .execute()

  if (result.rowsAffected === 0) {
    throw new Error('Concurrent modification detected')
  }

  return result
}

Performance Tips

1. Use Indexes: Ensure WHERE clause columns are indexed
2. Batch Updates: Use updateMany for bulk operations
3. Limit Scope: Be specific with WHERE conditions
4. Use Transactions: Group related updates

// Good: Targeted update with index
await db.updateTable('users').set({ active: false }).where({ id: 1 }).execute()

// Avoid: Table scan
await db.updateTable('users').set({ active: false }).where('name', 'LIKE', '%test%').execute()

Next Steps

• Learn about SELECT queries
• Explore WHERE conditions
• Master transactions