Overview
Modifying Database Records
Modifying data involves inserting, updating, upserting, and deleting records across database tables. You can perform these operations safely and expressively using type-safe query builders across PostgreSQL, MySQL, SQLite, Gel, and SingleStore databases.
Inserting Records
To create new rows in a database table, use the insert method combined with .values(). You can insert a single record, multiple records at once, or use subqueries and returning clauses where supported.
// Insert one row
await db.insert(cars).values({ brand: 'BMW' });
// Insert multiple rows
await db.insert(cars).values([{ brand: 'BMW' }, { brand: 'Porsche' }]);
// Insert with a returning clause to retrieve inserted data
const insertedCar = await db.insert(cars)
.values({ brand: 'BMW' })
.returning();Tip
When working with databases that support returning clauses (like PostgreSQL, SQLite, and Gel), you can immediately fetch the newly created rows or specific columns by chaining .returning().
Updating Records
To modify existing records, use the update method along with .set() to specify the new values and .where() to target specific rows.
// Update rows matching a condition
await db.update(cars)
.set({ color: 'red' })
.where(eq(cars.brand, 'BMW'));
// Update with a returning clause
const updatedCar = await db.update(cars)
.set({ color: 'red' })
.where(eq(cars.id, 1))
.returning();Warning
Omitting the .where() clause on an update query will modify every single row in the table. Always double-check your filters before running bulk updates.
Handling Upserts and Conflicts
PostgreSQL, SQLite, and Gel
Use .onConflictDoNothing() or .onConflictDoUpdate() to handle key collisions gracefully:
// Skip the insert if a conflict occurs on the target column
await db.insert(cars)
.values({ id: 1, brand: 'BMW' })
.onConflictDoNothing({ target: cars.id });
// Update the existing record if a conflict occurs
await db.insert(cars)
.values({ id: 1, brand: 'BMW' })
.onConflictDoUpdate({
target: cars.id,
set: { brand: 'Porsche' }
});MySQL and SingleStore
Use .onDuplicateKeyUpdate() when inserting data into tables with unique or primary key constraints:
import { sql } from 'drizzle-orm';
await db.insert(cars)
.values({ id: 1, brand: 'BMW' })
.onDuplicateKeyUpdate({ set: { brand: 'Porsche' } });Deleting Records
To remove rows from a table, use the delete method with an optional .where() filter.
// Delete specific rows matching a condition
await db.delete(cars).where(eq(cars.color, 'green'));
// Delete with a returning clause
const deletedCar = await db.delete(cars)
.where(eq(cars.id, 1))
.returning();Caution
Running db.delete(table) without a .where() clause deletes all records in the table. Ensure you include a filter unless you intend to clear the entire table.