Overview
Mutation methods modify data in your database. All mutations can optionally return affected rows using .return() or .returnFirst().
insert
Insert one or more rows into the table.
Syntax
model.insert(data) => ModelInMutableResult
Parameters
data
TableInsert | TableInsert[]
required
Object (or array of objects) containing the data to insert.Must match the table schema. Optional columns can be omitted.
Return Type
Returns a ModelInMutableResult which extends Promise<void> with optional return methods:
.return() - Return all inserted rows.returnFirst() - Return first inserted row
Examples
Insert Single
Insert with Return
Insert Multiple
Partial Return
const user = await userModel
.insert({
name: "Alex",
email: "[email protected]",
age: 25,
})
.returnFirst();
console.log(user.id); // Auto-generated ID
const users = await userModel
.insert([
{ name: "Alex", email: "[email protected]", age: 25 },
{ name: "Anna", email: "[email protected]", age: 30 },
])
.return();
console.log(users.length); // 2
const user = await userModel
.insert({
name: "Alex",
email: "[email protected]",
age: 25,
})
.returnFirst({ id: true, name: true });
// Only id and name are returned
update
Update rows matching the current where conditions.
Syntax
model.update(data) => ModelInMutableResult
Parameters
data
Partial<TableInsert>
required
Object containing fields to update.All fields are optional - only provided fields will be updated.
Return Type
Returns a ModelInMutableResult with optional return methods.
Examples
Update Single
Update with Return
Update Multiple
Omit Sensitive Fields
await userModel
.where({ id: esc(1) })
.update({ name: "Updated Name" });
const updated = await userModel
.where({ id: esc(1) })
.update({ name: "New Name", age: 26 })
.returnFirst();
console.log(updated.name); // "New Name"
const updated = await userModel
.where({ isVerified: esc(false) })
.update({ isVerified: true })
.return();
console.log(`Updated ${updated.length} users`);
const user = await userModel
.where({ id: esc(1) })
.update({ secretField: "new-secret" })
.returnFirst()
.omit({ secretField: true });
// secretField is excluded from the result
Always use .where() before .update() to specify which rows to update. Without a where clause, ALL rows will be updated.
delete
Delete rows matching the current where conditions.
Syntax
model.delete() => ModelInMutableResult
Return Type
Returns a ModelInMutableResult with optional return methods.
Examples
Delete Single
Delete with Return
Delete Multiple
Conditional Delete
await userModel
.where({ id: esc(1) })
.delete();
const deleted = await userModel
.where({ id: esc(1) })
.delete()
.returnFirst();
if (deleted) {
console.log(`Deleted user: ${deleted.name}`);
}
const deleted = await userModel
.where({ isVerified: esc(false) })
.delete()
.return();
console.log(`Deleted ${deleted.length} unverified users`);
const count = await userModel
.where({
lastLoginAt: { isNull: true },
createdAt: esc.lt(new Date('2020-01-01'))
})
.delete()
.return();
Always use .where() before .delete() to specify which rows to delete. Without a where clause, ALL rows will be deleted.
upsert
Insert a row or update it if a conflict occurs on the target column(s).
Syntax
model.upsert({
insert,
update,
target?,
updateWhere?
}) => ModelInMutableResult
Parameters
Data to insert if no conflict occurs.
update
Partial<TableInsert> | UpdateFunction
required
Data to update if a conflict occurs.Can be a plain object or a function receiving a context object with:
excluded(field) - Value from the INSERT attemptinserted(field) - Existing value in the table
Column(s) to check for conflicts. Defaults to primary key(s).import * as schema from "./schema";
target: schema.user.email
// or
target: [schema.user.email, schema.user.username]
Function to add additional WHERE conditions to the UPDATE clause.updateWhere: (c) => ({
updatedAt: esc.lt(c.excluded("updatedAt"))
})
Return Type
Returns a ModelInMutableResult with optional return methods.
Examples
Basic Upsert
With Return
Dynamic Update
Conditional Update
import * as schema from "./schema";
await userModel.upsert({
insert: {
name: "Alex",
email: "[email protected]",
age: 25
},
update: {
name: "Alex Updated",
age: 26
},
target: schema.user.email,
});
import * as schema from "./schema";
const user = await userModel
.upsert({
insert: {
name: "Alex",
email: "[email protected]",
age: 25
},
update: { name: "Alex Updated" },
target: schema.user.email,
})
.returnFirst();
console.log(user.id);
import * as schema from "./schema";
await userModel.upsert({
insert: {
name: "Alex",
email: "[email protected]",
loginCount: 1
},
update: (c) => ({
// Increment login count using SQL
loginCount: sql`${c.inserted("loginCount")} + 1`,
lastLoginAt: c.excluded("lastLoginAt"),
}),
target: schema.user.email,
});
import * as schema from "./schema";
await userModel.upsert({
insert: {
name: "Alex",
email: "[email protected]",
updatedAt: new Date(),
},
update: { name: "Alex Updated" },
target: schema.user.email,
// Only update if new data is newer
updateWhere: (c) => ({
updatedAt: esc.lt(c.excluded("updatedAt"))
}),
});
Return Methods
All mutation methods return a ModelInMutableResult which provides these chainable methods:
.return()
Return all affected rows.
const users = await userModel
.insert([...])
.return();
// users: User[]
const users = await userModel
.insert([...])
.return({ id: true, name: true });
// Only id and name are returned
.returnFirst()
Return the first affected row (or undefined if no rows affected).
const user = await userModel
.insert({ ... })
.returnFirst();
// user: User | undefined
const user = await userModel
.insert({ ... })
.returnFirst({ id: true, name: true });
.omit()
Exclude specific fields from the returned result (post-query, not SQL).
const user = await userModel
.update({ secretField: "value" })
.returnFirst()
.omit({ secretField: true, password: true });
// secretField and password are excluded from result
.safe()
Wrap the result in a { data, error } object instead of throwing errors.
const result = await userModel
.insert({ ... })
.returnFirst()
.safe();
if (result.error) {
console.error(result.error);
} else {
console.log(result.data);
}
Dialect Differences
PostgreSQL & SQLite
Natively support .returning() - all fields are returned efficiently:
const user = await userModel.insert({ ... }).returnFirst();
// Uses RETURNING clause
MySQL
Does not support RETURNING. Only returns primary key fields:
const result = await userModel.insert({ ... }).returnFirst();
// result: { id: number } - only primary key
const { id } = await userModel.insert({ ... }).returnFirst();
const user = await userModel.where({ id: esc(id) }).findFirst();
Type Safety
All mutation methods are fully typed:
// ✅ TypeScript validates insert data
await userModel.insert({
name: "Alex", // ✅ Valid
age: 25, // ✅ Valid
// invalid: 123 // ❌ Type error: unknown field
});
// ✅ Update data is partial
await userModel.update({
name: "New Name", // ✅ Can update just one field
});
Notes
Mutation methods that modify the database always return Promise<void> unless you chain .return() or .returnFirst().
MySQL Limitation: .return() methods only return primary key values on MySQL. Use .findFirst() for full row data.