CRUD and the four chain methods
Reading and writing Postgres data with Drizzle's typed query builder.
Last chapter you made the schema the source of truth: tables, their relations, and every TypeScript row type derived from them. This lesson is where that pays off, because a schema you can’t read from or write to does nothing.
Almost every feature does the same four things to its data: create rows, read them, update them, delete them. That is CRUD , and SQL spells the four operations INSERT, SELECT, UPDATE, DELETE. Drizzle wraps each in a typed, autocompleted TypeScript call, because it is an ORM : it maps your tables to code instead of leaving you to concatenate SQL by hand.
By the end you’ll know the four entry points (db.select, db.insert, db.update, db.delete) and the four methods that shape a result set (where, orderBy, limit, offset), the one omission that can erase a table in production, and why SQL injection is something you essentially never have to think about here.
The db client and the query builder
Section titled “The db client and the query builder”Every query in your app goes through one object, db, the single call site for every read and write. You import it from db/index.ts alongside the tables from db/schema.ts.
import { db } from '@/db';import { invoices } from '@/db/schema';When you call db.select(), no query runs. You get back a query builder, an object that collects instructions. Each method you call on it (.from(), .where(), and the rest) adds an instruction and hands the builder back, which is why the calls chain. The builder sits fully assembled but inert until you await it.
const allInvoices = await db.select().from(invoices);That await is the trigger. The builder is a thenable : only when you await it (or call .execute()) does Drizzle assemble the SQL and send it to Postgres. Because it builds the SQL at that moment, the order you chain the methods in doesn’t matter: .where().orderBy() and .orderBy().where() produce the identical query. Pick an order that reads well.
Now we can read some rows.
Reading rows with db.select
Section titled “Reading rows with db.select”The simplest read is the whole table:
const allInvoices = await db.select().from(invoices);allInvoices is typed as Invoice[], the full row shape you derived last chapter (typeof invoices.$inferSelect). You annotate nothing: Drizzle reads the type off the invoices table, so the result can never drift from the schema.
.from() is mandatory. db.select() alone doesn’t know which table you mean.
For a list view you often want only some columns, say the id and the amount. You narrow the read with a projection, an object naming exactly the columns you want:
const rows = await db .select({ id: invoices.id, amountDue: invoices.amountDue }) .from(invoices);The return type follows the projection: pick the columns and the type comes with them, no shape written by hand. Each key on the left is the field name in TypeScript; the value on the right is the column it reads from.
amountDue comes back as string, not number. That’s the rule for the whole course: money is a numeric column, and numeric maps to string so you never lose a cent to floating-point rounding. Format it for display, never run it through parseFloat.
Now try it. The database below is seeded with invoices for one organization. Write the query that returns just the id and amountDue of every invoice.
Return the id and amountDue of every invoice — nothing else.
View schema & seed rows
export const organizations = pgTable('organizations', {
id: integer('id').primaryKey(),
name: text('name').notNull(),
});
export const invoices = pgTable('invoices', {
id: integer('id').primaryKey(),
organizationId: integer('organization_id')
.references(() => organizations.id)
.notNull(),
amountDue: numeric('amount_due', { precision: 12, scale: 2 }).notNull(),
status: text('status').notNull(),
createdAt: timestamp('created_at').notNull(),
}); INSERT INTO organizations (id, name) VALUES (1, 'Acme'); INSERT INTO invoices (id, organization_id, amount_due, status, created_at) VALUES (1, 1, '120.00', 'draft', '2026-05-02 09:00Z'), (2, 1, '0.00', 'sent', '2026-05-05 09:00Z'), (3, 1, '450.50', 'paid', '2026-05-08 09:00Z'), (4, 1, '89.99', 'sent', '2026-05-11 09:00Z');
- Query returns the 4 expected rows (any order)
Reading every row is rare. The next method, where, scopes a query to the rows you want.
Filtering rows with where
Section titled “Filtering rows with where”A feature almost never wants all the invoices. It wants the sent ones, the ones over a threshold, or the ones belonging to the organization the user is currently viewing. where filters the rows before they come back.
It doesn’t take a string, though. Instead of where('status = sent'), you pass a condition built from helper functions in drizzle-orm. The one you’ll reach for first is eq, for equals:
const sentInvoices = await db .select() .from(invoices) .where(eq(invoices.status, 'sent'));Read eq(invoices.status, 'sent') as “the status column equals 'sent'”: first the column, then the value to compare against. There’s a helper for every comparison, and each reads like the SQL it emits, so the table below is reference, not memorization:
One Postgres detail surprises newcomers: like is case-sensitive, so like(invoices.status, 'SENT') won’t match a stored 'sent'. For the case-insensitive matching that user-facing search almost always needs, use ilike:
where(ilike(organizations.name, '%acme%'))Combining conditions
Section titled “Combining conditions”One condition is rarely enough. You filter by status and organization, or by one status or another. and(...), or(...), and not(...) each take one or more conditions and join them. This is the shape you’ll write most:
const orgSentInvoices = await db .select() .from(invoices) .where(and(eq(invoices.organizationId, orgId), eq(invoices.status, 'sent')));That reads as “sent invoices belonging to this organization,” and the eq(invoices.organizationId, orgId) part carries more weight than it looks. Your app is multi-tenant: many organizations share one database, so a query that forgets to scope by organizationId can leak one tenant’s invoices to another. Filter by it explicitly, in every query.
Parameterized queries stop SQL injection
Section titled “Parameterized queries stop SQL injection”When you wrote eq(invoices.status, 'sent'), that 'sent' was not glued into a SQL string. Drizzle sends the SQL text and the value to Postgres separately: the query goes out as ... WHERE status = $1, and 'sent' rides along as the value bound to $1. This is a parameterized query , and the $1 is a placeholder .
A value that is never part of the SQL text can never be run as SQL. If a user types '; DROP TABLE invoices; -- into a search box and it lands in your eq, Postgres compares against it as a literal string that matches no invoice, rather than executing it. That attack, where hostile input changes what a query means, is SQL injection , behind some of the largest breaches on record. Through Drizzle’s helpers there is no SQL string for the input to escape into, so it’s structurally impossible. Compare the two ways to write the same filter:
// the bad old way — build the SQL as a string, then run itconst query = `SELECT * FROM invoices WHERE status = '${userInput}'`;The injection. userInput is spliced straight into the SQL text. Type ' OR '1'='1 and the WHERE clause matches every row; a richer payload reaches the rest of your database. The string is the query, so the input becomes code.
await db.select().from(invoices).where(eq(invoices.status, userInput));The safe form. userInput is bound as $1, never concatenated, so it stays data compared against the column. This is the default; you’d have to go out of your way to lose it.
There is one way to lose this protection: sql.raw(userInput) splices its argument in verbatim, like the unsafe tab above, so never point it at user input.
Now compose a real filter yourself. The exercise seeds invoices across two organizations with varied statuses and amounts. Return the sent invoices for organization 1 whose amountDue is above 100 — an and(...) of three conditions: the org, the status, and the threshold.
Return every sent invoice for organization 1 whose amountDue is above 100 — keep the full row. (Above means strictly greater than 100, so an invoice sitting exactly at 100.00 does not qualify.)
View schema & seed rows
export const organizations = pgTable('organizations', {
id: integer('id').primaryKey(),
name: text('name').notNull(),
});
export const invoices = pgTable('invoices', {
id: integer('id').primaryKey(),
organizationId: integer('organization_id')
.references(() => organizations.id)
.notNull(),
amountDue: numeric('amount_due', { precision: 12, scale: 2 }).notNull(),
status: text('status').notNull(),
createdAt: timestamp('created_at').notNull(),
}); INSERT INTO organizations (id, name) VALUES (1, 'Acme'), (2, 'Globex'); INSERT INTO invoices (id, organization_id, amount_due, status, created_at) VALUES (1, 1, '80.00', 'sent', '2026-05-02 09:00Z'), (2, 1, '150.00', 'sent', '2026-05-05 09:00Z'), (3, 1, '200.00', 'draft', '2026-05-08 09:00Z'), (4, 1, '450.00', 'sent', '2026-05-11 09:00Z'), (5, 2, '120.00', 'sent', '2026-05-14 09:00Z'), (6, 1, '99.99', 'paid', '2026-05-17 09:00Z'), (7, 1, '100.00', 'sent', '2026-05-20 09:00Z');
- Query returns the 2 expected rows (any order)
You can now read rows and filter them. The last three methods don’t filter; they shape the result set you’ve already selected, setting its order and its size.
Sorting and paging with orderBy, limit, and offset
Section titled “Sorting and paging with orderBy, limit, and offset”Postgres returns rows in no guaranteed order unless you ask for one, and orderBy is how you ask. Wrap a column in asc(...) or desc(...), both from drizzle-orm, to set the direction:
const recent = await db .select() .from(invoices) .where(eq(invoices.organizationId, orgId)) .orderBy(desc(invoices.createdAt));That returns the newest invoices first. It looks complete, but it hides a bug that works on your laptop and fails in production.
Always add a tiebreaker
Section titled “Always add a tiebreaker”Two invoices created in the same millisecond share a createdAt value, and Postgres has no instruction for ordering rows that tie on it. It returns them in whatever order is convenient, which can shift after a row is updated. The sort is then non-deterministic: a paginated list can show the same invoice twice, or a row can appear to jump position.
The fix is one more key, a unique tiebreaker, almost always the primary key:
.orderBy(desc(invoices.createdAt), asc(invoices.id))Ties on createdAt now break by id, so the same query returns the same rows in the same order every time. Pair any non-unique sort column with the primary key.
Slicing the result with limit and offset
Section titled “Slicing the result with limit and offset”limit caps how many rows come back; offset skips rows from the front. Together they page:
.orderBy(desc(invoices.createdAt), asc(invoices.id)).limit(20).offset(40)That is page three at twenty rows a page: skip the first forty, take the next twenty. It is the right tool for small, fixed lists like an admin table of a few hundred rows; cursor pagination, later in this chapter, handles large or shifting data better.
The order you chain .where(), .orderBy(), and .limit() makes no difference to the SQL, so order them to read well: filter, then sort, then slice.
Return the five most recent sent invoices for organization 1, newest first, with ties broken by id.
Return the five most recent sent invoices for organization 1 — newest first, with ties on createdAt broken by id (ascending). Note that invoices 7 and 8 share a createdAt, so the tiebreaker decides which comes first.
View schema & seed rows
export const organizations = pgTable('organizations', {
id: integer('id').primaryKey(),
name: text('name').notNull(),
});
export const invoices = pgTable('invoices', {
id: integer('id').primaryKey(),
organizationId: integer('organization_id')
.references(() => organizations.id)
.notNull(),
amountDue: numeric('amount_due', { precision: 12, scale: 2 }).notNull(),
status: text('status').notNull(),
createdAt: timestamp('created_at').notNull(),
}); INSERT INTO organizations (id, name) VALUES (1, 'Acme'), (2, 'Globex'); INSERT INTO invoices (id, organization_id, amount_due, status, created_at) VALUES (1, 1, '120.00', 'paid', '2026-05-08 09:00Z'), (2, 1, '150.00', 'sent', '2026-05-14 09:00Z'), (3, 1, '200.00', 'draft', '2026-05-30 09:00Z'), (4, 1, '450.00', 'sent', '2026-05-20 09:00Z'), (5, 2, '300.00', 'sent', '2026-05-29 09:00Z'), (6, 1, '99.99', 'sent', '2026-05-25 09:00Z'), (7, 1, '80.00', 'sent', '2026-05-28 09:00Z'), (8, 1, '60.00', 'sent', '2026-05-28 09:00Z'), (9, 1, '40.00', 'sent', '2026-05-10 09:00Z');
- Query returns the 5 expected rows in order
That covers reads. The other half of CRUD is putting rows back.
Writing rows: insert, update, and delete
Section titled “Writing rows: insert, update, and delete”A write can change or destroy data you already have, so the three write paths deserve more care than the reads.
Inserting uses db.insert(table).values(...). What you pass to .values() is the $inferInsert shape from the last chapter: columns with a default (createdAt via .defaultNow(), the primary key) you leave out, .notNull() columns with no default are required, and generated columns are rejected.
await db.insert(invoices).values({ organizationId: orgId, amountDue: '0.00', status: 'draft', dueDate: '2026-07-01',});Note amountDue is the string '0.00', not the number 0: numeric is a string on writes too, same as on reads. To insert several rows, pass .values() an array and Drizzle batches them into one statement.
Updating uses db.update(table).set(...).where(...). .set() takes the columns you want to change:
await db.update(invoices).set({ status: 'paid' }).where(eq(invoices.id, id));Deleting uses db.delete(table).where(...):
await db.delete(invoices).where(eq(invoices.id, id));The missing where that empties the table
Section titled “The missing where that empties the table”Both the update and the delete end in a .where(...). Take it away:
await db.update(invoices).set({ status: 'void' });await db.delete(invoices);The first sets every invoice in the table to 'void'. The second empties the table, every row gone. Drizzle does not warn you: the types check, the code compiles, and the statement runs without error. It is valid SQL, because an UPDATE or DELETE with no WHERE means “every row” by definition, and nothing in the type system catches the missing clause.
So the habit is simple: every update and every delete carries a where. When you genuinely mean “all rows,” say so in a comment. To catch slips, turn on eslint-plugin-drizzle’s enforce-update-with-where and enforce-delete-with-where, which flag an unqualified update or delete before the code runs.
Which of these statements changes more than one row?
await db.update(invoices).set({ status: 'paid' }).where(eq(invoices.id, id));await db.update(invoices).set({ status: 'void' });await db.delete(invoices).where(eq(invoices.id, id));where, so the set applies to every row in invoices. The other two are scoped by eq(invoices.id, id) and touch at most one row. The missing where is the entire difference.Getting the written row back with .returning()
Section titled “Getting the written row back with .returning()”After a write you often need the row back: which id did the new invoice get, what does the updated one look like now? The obvious answer is a second query, insert then select, but that means two round-trips with a window between them where another request could change things.
Append .returning() to any insert, update, or delete and the statement hands the affected rows straight back in the full $inferSelect shape, or a projected subset if you pass one, just like select:
const [created] = await db .insert(invoices) .values({ organizationId: orgId, amountDue: '0.00', status: 'draft', dueDate: '2026-07-01' }) .returning();One statement, no follow-up select: created is the row that landed in the table, with its generated id, its createdAt, and everything else. .returning() hands back an array, so you destructure the one row out. Whenever you write a row and then immediately select it back, reach for .returning() instead.
const [created] = await db .insert(invoices) .values({ organizationId: orgId, amountDue: '120.00', status: 'draft', dueDate: '2026-07-01' }) .returning();
const [updated] = await db .update(invoices) .set({ status: 'paid' }) .where(eq(invoices.id, id)) .returning({ id: invoices.id, status: invoices.status });Insert a new draft invoice. .values() takes the $inferInsert shape, so amountDue is the string '120.00'; id and createdAt are omitted because their defaults fill them in.
const [created] = await db .insert(invoices) .values({ organizationId: orgId, amountDue: '120.00', status: 'draft', dueDate: '2026-07-01' }) .returning();
const [updated] = await db .update(invoices) .set({ status: 'paid' }) .where(eq(invoices.id, id)) .returning({ id: invoices.id, status: invoices.status });.returning() hands the new row back, and [created] destructures it from the array. One statement, no follow-up select to learn the generated id.
const [created] = await db .insert(invoices) .values({ organizationId: orgId, amountDue: '120.00', status: 'draft', dueDate: '2026-07-01' }) .returning();
const [updated] = await db .update(invoices) .set({ status: 'paid' }) .where(eq(invoices.id, id)) .returning({ id: invoices.id, status: invoices.status });A separate write: mark an invoice paid. .set() lists only the columns that change, here just status.
const [created] = await db .insert(invoices) .values({ organizationId: orgId, amountDue: '120.00', status: 'draft', dueDate: '2026-07-01' }) .returning();
const [updated] = await db .update(invoices) .set({ status: 'paid' }) .where(eq(invoices.id, id)) .returning({ id: invoices.id, status: invoices.status });This where keeps the update to the one invoice whose id the caller passed. Drop it and every invoice in the table is marked paid.
const [created] = await db .insert(invoices) .values({ organizationId: orgId, amountDue: '120.00', status: 'draft', dueDate: '2026-07-01' }) .returning();
const [updated] = await db .update(invoices) .set({ status: 'paid' }) .where(eq(invoices.id, id)) .returning({ id: invoices.id, status: invoices.status });A projected .returning(), just the two columns we care about, so each row is shaped { id: string; status: '…' }. updated reflects the new state without a second read.
One last point about deletes: in most web apps you rarely hard-delete a row. The usual pattern is a soft delete, an update that sets the deletedAt timestamp already on your tables, so the record survives for audit and can be recovered. A real delete is reserved for things like offboarding a whole tenant or expiring old audit logs. The next chapter covers soft delete and the filtering it needs; for now, know that db.delete removes data for good.
Now write a row yourself and read it back in one statement. Mark invoice 1 as 'paid' with a correct where, and .returning() just its id and status.
Mark invoice 1 as paid, and return its id and status so the caller doesn't need a second query. The where is the guard — without it, every invoice in the table flips to paid.
View schema & seed rows
export const organizations = pgTable('organizations', {
id: integer('id').primaryKey(),
name: text('name').notNull(),
});
export const invoices = pgTable('invoices', {
id: integer('id').primaryKey(),
organizationId: integer('organization_id')
.references(() => organizations.id)
.notNull(),
amountDue: numeric('amount_due', { precision: 12, scale: 2 }).notNull(),
status: text('status').notNull(),
createdAt: timestamp('created_at').notNull(),
}); INSERT INTO organizations (id, name) VALUES (1, 'Acme'); INSERT INTO invoices (id, organization_id, amount_due, status, created_at) VALUES (1, 1, '120.00', 'sent', '2026-05-02 09:00Z'), (2, 1, '0.00', 'sent', '2026-05-05 09:00Z'), (3, 1, '450.50', 'sent', '2026-05-08 09:00Z'), (4, 1, '89.99', 'draft','2026-05-11 09:00Z');
- Query returns the 1 expected row (any order)
Reading a single row
Section titled “Reading a single row”You have an invoice’s id and want that one invoice, but db.select() always returns an array, with no findOne shortcut. Cap the query at one row and destructure the first element:
const [invoice] = await db .select() .from(invoices) .where(eq(invoices.id, id)) .limit(1);invoice is now a single Invoice, or undefined if no row matched the id, from a deleted invoice or a bad id in a URL. The | undefined forces you to handle that missing case downstream.
Drizzle’s relational query API does offer db.query.invoices.findFirst(...), covered later in this chapter, but on the plain SQL builder, .limit(1) plus a destructure is the way.
Drizzle reference docs
Section titled “Drizzle reference docs”The official Drizzle query docs are the reference you’ll return to. Bookmark them now.
The full select API: projections, where, orderBy, limit, offset.
values, batch inserts, and .returning().
set, where, and returning on the two mutation paths.
Every operator helper: eq, gt, inArray, ilike, and the rest.
External resources
Section titled “External resources”These two interactive labs let you run a SQL injection attack yourself and watch input become executable code. Seeing it firsthand makes Drizzle’s parameterization much easier to appreciate.