Skip to main content
Version: Next

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)​

MethodDescription
.set()Provide column values to update

Query methods (UpdateQuery)​

MethodDescription
.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));
  • Filters — Complete list of filter operators for .where() conditions.