Update

Use db.update() to modify existing rows in a table. The update builder provides type-safe column updates with filtering support.

Methods

Builder methods (UpdateBuilder)

Method	Description
.set()	Provide column values to update

Query methods (UpdateQuery)

Method	Description
.where(condition)	Filter which rows are updated
.returning()	Specify columns to return after update

Basic Usage

Update with WHERE

Always use .where() to specify which rows to update:

import { db } from "./db";
import { Users } from "./db/schema";
import { eq } from "durcno";

// Update a specific user's email
await db
  .update(Users)
  .set({ email: "newemail@example.com" })
  .where(eq(Users.id, 1));

Update Multiple Columns

Pass multiple columns to .set():

await db
  .update(Users)
  .set({
    email: "updated@example.com",
    type: "admin",
  })
  .where(eq(Users.id, 1));

warning

Without a .where() clause, the update will affect all rows in the table. Always include a filter unless you intend to update every row.

Partial Updates

All columns in .set() are optional—only specify the columns you want to change:

// Only update the email, leave other columns unchanged
await db.update(Users).set({ email: "new@example.com" }).where(eq(Users.id, 1));

Complex Filters

Use filter operators for complex conditions:

import { and, eq, lte } from "durcno";

// Update all inactive users created before 2024
await db
  .update(Users)
  .set({ type: "user" })
  .where(
    and(eq(Users.type, "admin"), lte(Users.createdAt, new Date("2024-01-01"))),
  );

Returning Updated Data

Use .returning() to get data back from updated rows:

// Return specific columns
const updated = await db
  .update(Users)
  .set({ email: "updated@example.com" })
  .where(eq(Users.id, 1))
  .returning({ id: true, email: true });
// Type: { id: number; email: string | null }[]

// Return all columns except some
const updated = await db
  .update(Users)
  .set({ email: "updated@example.com" })
  .where(eq(Users.id, 1))
  .returning({ createdAt: false });
// Type: { id: number; username: string; email: string | null; type: "admin" | "user" }[]

Without Returning

Without .returning(), the update returns null:

const result = await db
  .update(Users)
  .set({ email: "updated@example.com" })
  .where(eq(Users.id, 1));
// Type: null

Auto-generated Values with .$updateFn()

Columns with .$updateFn() automatically generate values on every update, even if not explicitly included in .set():

// Schema with `.$updateFn()`
const Posts = table("public", "posts", {
  id: pk(),
  title: varchar({ length: 200, notNull }),
  updatedAt: timestamp({ notNull }).$updateFn(() => new Date()),
});

// `updatedAt` is automatically set by `.$updateFn()`
await db.update(Posts).set({ title: "New Title" }).where(eq(Posts.id, 1));
// updatedAt will be auto-generated

// You can still override with an explicit value
await db
  .update(Posts)
  .set({
    title: "New Title",
    updatedAt: new Date("2024-06-15"), // Override updateFn
  })
  .where(eq(Posts.id, 1));

tip

See Dynamic Value Generation for more details on .$insertFn() and .$updateFn().

Using SQL Expressions

Use sql() for raw SQL expressions in updates:

import { sql } from "durcno";

// Set createdAt to current timestamp
await db
  .update(Users)
  .set({ createdAt: sql`NOW()` })
  .where(eq(Users.id, 1));

Primary Key Restriction

Primary key columns cannot be updated:

// ❌ TypeScript Error - cannot update primary key
await db
  .update(Users)
  .set({ id: 999 }) // Error!
  .where(eq(Users.id, 1));

Method Chaining Order

Methods can be chained in any order:

// These are equivalent
await db
  .update(Users)
  .set({ email: "a@b.com" })
  .where(eq(Users.id, 1))
  .returning({ id: true });
await db
  .update(Users)
  .set({ email: "a@b.com" })
  .returning({ id: true })
  .where(eq(Users.id, 1));

Type Safety

Durcno ensures type safety for update values:

// ✅ Valid - correct types
await db
  .update(Users)
  .set({ email: "valid@example.com" })
  .where(eq(Users.id, 1));

// ❌ TypeScript Error - wrong type
await db
  .update(Users)
  .set({ email: 123 }) // email expects string
  .where(eq(Users.id, 1));

// ❌ TypeScript Error - invalid enum value
await db
  .update(Users)
  .set({ type: "superadmin" }) // Not in enum
  .where(eq(Users.id, 1));

Related

• Filters — Complete list of filter operators for .where() conditions.