Update Queries

Update existing data in your database using Drizzle ORM’s type-safe update query builder with filtering, joins, and returning capabilities.

Drizzle ORM provides a type-safe API for updating existing records in your database tables.

Basic Update

Update rows with the set() method:

import { db } from './db';
import { users } from './schema';
import { eq } from 'drizzle-orm';

const result = await db.update(users)
  .set({ name: 'John Updated' })
  .where(eq(users.id, 1));

Always include a where() clause unless you intentionally want to update all rows in the table.

Update Multiple Columns

Update several columns at once:

await db.update(users)
  .set({
    name: 'John Doe',
    email: '[e-mail]',
    age: 31,
    updatedAt: new Date(),
  })
  .where(eq(users.id, 1));

Update with Expressions

Use SQL expressions and column references:

Increment Values

import { sql } from 'drizzle-orm';

// Increment age by 1
await db.update(users)
  .set({ age: sql`${users.age} + 1` })
  .where(eq(users.id, 1));

// Increment views count
await db.update(posts)
  .set({ views: sql`${posts.views} + 1` })
  .where(eq(posts.id, 100));

Conditional Updates

Update rows based on multiple conditions:

import { and, eq, gt } from 'drizzle-orm';

// Update users matching multiple conditions
await db.update(users)
  .set({ role: 'senior' })
  .where(and(
    eq(users.active, true),
    gt(users.experience, 5)
  ));

// Update with OR condition
import { or } from 'drizzle-orm';

await db.update(users)
  .set({ verified: true })
  .where(or(
    eq(users.email, '[e-mail]'),
    eq(users.role, 'admin')
  ));

Update with Returning

Get the updated row(s) back from the database:

Return All Columns

const [updatedUser] = await db.update(users)
  .set({ name: 'John Updated' })
  .where(eq(users.id, 1))
  .returning();

console.log(updatedUser); // Full user object

.returning() is supported in PostgreSQL, SQLite, and MySQL 8.0+. It’s not available in older MySQL versions.

Update with Joins (PostgreSQL)

Update using data from joined tables:

Using FROM clause

import { db } from './db';
import { users, departments } from './schema';

// Update users based on department data
await db.update(users)
  .set({ 
    salary: sql`${users.salary} * ${departments.salaryMultiplier}`,
  })
  .from(departments)
  .where(eq(users.departmentId, departments.id));

Bulk Updates

Update multiple rows efficiently:

import { inArray } from 'drizzle-orm';

// Update multiple users by ID
await db.update(users)
  .set({ status: 'inactive' })
  .where(inArray(users.id, [1, 2, 3, 4, 5]));

// Update based on array of values
const emails = ['[e-mail]', '[e-mail]'];
await db.update(users)
  .set({ verified: true })
  .where(inArray(users.email, emails));

Update All Rows

Update every row in a table (use with caution):

// Update all users
await db.update(users)
  .set({ migrated: true });

// This affects ALL rows - be careful!

Omitting the where() clause updates ALL rows. Always double-check before running such queries.

Pattern Matching Updates

Update based on pattern matching:

import { like, ilike } from 'drizzle-orm';

// Update users with gmail addresses
await db.update(users)
  .set({ emailProvider: 'gmail' })
  .where(like(users.email, '[e-mail]'));

// Case-insensitive pattern match
await db.update(posts)
  .set({ category: 'technology' })
  .where(ilike(posts.title, '%tech%'));

Null and Not Null Updates

Update based on null values:

import { isNull, isNotNull } from 'drizzle-orm';

// Set default value for null columns
await db.update(users)
  .set({ bio: 'No bio provided' })
  .where(isNull(users.bio));

// Update only rows with existing data
await db.update(users)
  .set({ verified: true })
  .where(isNotNull(users.email));

Update with Subqueries

Use subqueries to calculate update values:

import { sql } from 'drizzle-orm';

// Update with aggregated value from subquery
await db.update(users)
  .set({
    postCount: sql`(
      SELECT COUNT(*) 
      FROM ${posts} 
      WHERE ${posts.userId} = ${users.id}
    )`,
  })
  .where(eq(users.id, 1));

Type Safety

Drizzle ensures type safety for updates:

await db.update(users)
  .set({
    name: 'John Doe',
    age: 30,
    // TypeScript error: 'invalid' is not a valid column
    // invalid: 'value',
    // TypeScript error: age must be a number
    // age: 'thirty',
  })
  .where(eq(users.id, 1));

Update in Transactions

Update within a transaction for consistency:

await db.transaction(async (tx) => {
  // Update user
  await tx.update(users)
    .set({ credits: sql`${users.credits} - 100` })
    .where(eq(users.id, 1));

  // Record transaction
  await tx.insert(transactions).values({
    userId: 1,
    amount: -100,
    type: 'purchase',
  });
});

Common Update Patterns

1. Toggle Boolean

   await db.update(users)
     .set({ active: sql`NOT ${users.active}` })
     .where(eq(users.id, 1));
   
2. Touch Updated Timestamp

   await db.update(users)
     .set({ updatedAt: sql`now()` })
     .where(eq(users.id, 1));
   
3. Increment Counter

   await db.update(posts)
     .set({ views: sql`${posts.views} + 1` })
     .where(eq(posts.slug, 'my-post'));
   
4. JSON Field Update (PostgreSQL)

   await db.update(users)
     .set({ 
       settings: sql`jsonb_set(
         ${users.settings}, 
         '{notifications}', 
         'true'
       )`,
     })
     .where(eq(users.id, 1));
   

Performance Tips

Index Your Where Columns

Ensure columns used in WHERE clauses are indexed for faster updates

Batch Updates

Use inArray() to update multiple rows in one query instead of loops

Avoid Full Table Updates

Always use WHERE clauses unless you really need to update all rows

Use Transactions

Wrap related updates in transactions to ensure data consistency

Next Steps

Delete Queries

Learn how to delete data

Select Queries

Query your updated data

Joins

Use joins in your updates

Transactions

Ensure data consistency