---
title: Query guide
description: Reading and writing rows with Drizzle — inserts, selects, filters, pagination, joins and relations.
type: how-to
product: database
summary: The queries you write most, in Drizzle, against a Sylphx database.
updated: 2026-09-28
order: 0
---

The examples here use [Drizzle](https://orm.drizzle.team) over the `uri` from
`connect`. Nothing about them is Sylphx-specific: a database is Postgres, and
any client that speaks the wire protocol works.

<Callout tone="note" title="Where the connection comes from">
These samples import `db` from an application module that builds the Drizzle
client from `DATABASE_URL`. The quickstart shows how that value is issued by
`connect` and bound to a Service.
</Callout>

## Create (insert)

Insert single or multiple records:

```ts
import { db } from '@/lib/db'
import { users, posts } from '@/lib/db/schema'

// Insert single record
const newUser = await db.insert(users).values({
  email: 'user@example.com',
  name: 'John Doe',
}).returning()

console.log('Created user:', newUser[0].id)

// Insert multiple records
const newPosts = await db.insert(posts).values([
  { title: 'First Post', authorId: newUser[0].id },
  { title: 'Second Post', authorId: newUser[0].id },
]).returning()

// Insert with conflict handling (upsert)
const upserted = await db.insert(users)
  .values({ email: 'user@example.com', name: 'Updated Name' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Updated Name' },
  })
  .returning()
```

<Callout tone="note" title="Always use returning()">
Add `.returning()` to an insert, update or delete and Postgres answers with
the affected rows in the same round trip. Without it you get a row count and
have to query again for the row you just wrote.
</Callout>

## Read (select)

Query records with type-safe selects:

```ts
import { db } from '@/lib/db'
import { users } from '@/lib/db/schema'

// Select all records
const allUsers = await db.select().from(users)

// Select specific columns
const emails = await db.select({
  email: users.email,
  name: users.name,
}).from(users)

// Select with alias
const userSummary = await db.select({
  id: users.id,
  displayName: users.name,
  contact: users.email,
}).from(users)

// First result only
const firstUser = await db.select().from(users).limit(1)
// firstUser[0] or undefined
```

## Update

Update records with conditions:

```ts
import { db } from '@/lib/db'
import { users } from '@/lib/db/schema'
import { eq } from 'drizzle-orm'

// Update single record
const updated = await db.update(users)
  .set({ name: 'Jane Doe' })
  .where(eq(users.id, userId))
  .returning()

// Update multiple fields
await db.update(users)
  .set({
    name: 'New Name',
    updatedAt: new Date(),
  })
  .where(eq(users.email, 'user@example.com'))

// Increment a counter
import { sql } from 'drizzle-orm'

await db.update(posts)
  .set({ viewCount: sql`${posts.viewCount} + 1` })
  .where(eq(posts.id, postId))
```

<Callout tone="warning" title="Always add a where clause">
An `update` or a `delete` with no `where` applies to every row in the table.
Build the condition first, then pass it — not the other way round.
</Callout>

## Delete

Remove records from the database:

```ts
import { db } from '@/lib/db'
import { users, posts } from '@/lib/db/schema'
import { eq, lt } from 'drizzle-orm'

// Delete single record
const deleted = await db.delete(users)
  .where(eq(users.id, userId))
  .returning()

// Delete with condition
await db.delete(posts)
  .where(eq(posts.authorId, userId))

// Delete old records
await db.delete(sessions)
  .where(lt(sessions.expiresAt, new Date()))
```

## Where clauses and filters

The operators you will reach for most:

```ts
import { db } from '@/lib/db'
import { users, posts } from '@/lib/db/schema'
import { eq, and, or, like, gt, inArray, isNull } from 'drizzle-orm'

// Simple equality
const user = await db.select().from(users)
  .where(eq(users.email, 'user@example.com'))

// AND conditions
const activeAdmins = await db.select().from(users)
  .where(and(
    eq(users.role, 'admin'),
    eq(users.status, 'active')
  ))

// OR conditions
const results = await db.select().from(users)
  .where(or(
    eq(users.role, 'admin'),
    eq(users.role, 'moderator')
  ))

// Pattern matching
const matchingUsers = await db.select().from(users)
  .where(like(users.email, '%@company.com'))

// IN array
const specificUsers = await db.select().from(users)
  .where(inArray(users.id, [id1, id2, id3]))

// Complex nested conditions
const complexQuery = await db.select().from(posts)
  .where(and(
    eq(posts.published, true),
    or(
      gt(posts.viewCount, 1000),
      isNull(posts.deletedAt)
    )
  ))
```

## Pagination

Paginate with limit/offset or with a cursor:

```ts
import { db } from '@/lib/db'
import { posts } from '@/lib/db/schema'

// Basic limit/offset pagination
const pageSize = 20
const page = 2 // 0-indexed

const results = await db.select().from(posts)
  .limit(pageSize)
  .offset(page * pageSize)
  .orderBy(posts.createdAt)

// With total count for pagination UI
import { count } from 'drizzle-orm'

const [items, totalResult] = await Promise.all([
  db.select().from(posts).limit(pageSize).offset(page * pageSize),
  db.select({ count: count() }).from(posts),
])

const totalPages = Math.ceil(totalResult[0].count / pageSize)
```

```ts
import { db } from '@/lib/db'
import { posts } from '@/lib/db/schema'
import { gt, desc } from 'drizzle-orm'

// Cursor-based pagination (better for large datasets)
async function getPosts(cursor?: string, limit = 20) {
  const query = db.select().from(posts)
    .orderBy(desc(posts.createdAt))
    .limit(limit + 1) // Fetch one extra to check if there's more

  if (cursor) {
    query.where(gt(posts.id, cursor))
  }

  const results = await query
  const hasMore = results.length > limit
  const items = hasMore ? results.slice(0, -1) : results
  const nextCursor = hasMore ? items[items.length - 1].id : null

  return { items, nextCursor, hasMore }
}
```

<Callout tone="note" title="Cursor or offset">
`offset` counts rows the server still has to walk, so deep pages get slower
the deeper they go; it is fine for a bounded list and a page count. A cursor
keyed on an ordered, indexed column reads a constant amount of work per page,
and it does not skip or repeat rows when something is inserted mid-scroll.
</Callout>

## Order results

```ts
import { db } from '@/lib/db'
import { posts } from '@/lib/db/schema'
import { asc, desc } from 'drizzle-orm'

// Single column ascending
const oldest = await db.select().from(posts)
  .orderBy(asc(posts.createdAt))

// Single column descending
const newest = await db.select().from(posts)
  .orderBy(desc(posts.createdAt))

// Multiple columns
const sorted = await db.select().from(posts)
  .orderBy(
    desc(posts.pinned),      // Pinned first
    desc(posts.createdAt)    // Then by date
  )

// Dynamic ordering
function getPosts(sortBy: 'date' | 'views', order: 'asc' | 'desc') {
  const column = sortBy === 'date' ? posts.createdAt : posts.viewCount
  const direction = order === 'asc' ? asc : desc

  return db.select().from(posts).orderBy(direction(column))
}
```

## Select specific columns

```ts
import { db } from '@/lib/db'
import { users, posts } from '@/lib/db/schema'

// Select specific columns
const userList = await db.select({
  id: users.id,
  email: users.email,
}).from(users)
// Type: { id: string, email: string }[]

// Omit sensitive fields
const publicUsers = await db.select({
  id: users.id,
  name: users.name,
  avatarUrl: users.avatarUrl,
  // Exclude: email, passwordHash, etc.
}).from(users)

// Computed columns with sql
import { sql } from 'drizzle-orm'

const userStats = await db.select({
  id: users.id,
  name: users.name,
  fullName: sql<string>`${users.firstName} || ' ' || ${users.lastName}`,
}).from(users)
```

## Joins

```ts
import { db } from '@/lib/db'
import { users, posts, comments } from '@/lib/db/schema'
import { eq } from 'drizzle-orm'

// Inner join
const postsWithAuthors = await db.select({
  post: posts,
  author: users,
}).from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))

// Left join (includes posts without authors)
const allPosts = await db.select({
  post: posts,
  author: users,
}).from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))

// Multiple joins
const postsWithDetails = await db.select({
  post: posts,
  author: users,
  commentCount: sql<number>`count(${comments.id})`,
}).from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))
  .leftJoin(comments, eq(comments.postId, posts.id))
  .groupBy(posts.id, users.id)
```

## Relations (the query API)

Define relations once, then ask for the nested shape you want:

```ts
import { relations } from 'drizzle-orm'
import { pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name'),
})

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  authorId: uuid('author_id').references(() => users.id),
})

// Define relations
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}))

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}))
```

```ts
import { db } from '@/lib/db'

// Query with nested relations
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
})
// Type: { id, email, name, posts: Post[] }[]

// Select specific relation columns
const postsWithAuthor = await db.query.posts.findMany({
  with: {
    author: {
      columns: {
        id: true,
        name: true,
      },
    },
  },
})

// Nested relations with filters
const result = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: desc(posts.createdAt),
      limit: 10,
    },
  },
})
```

<Callout tone="note" title="Relations or joins">
A relation is a join the builder writes for you, and it is the clearer option
when the shape you want is nested. Reach for an explicit join when the result
is a projection — aggregates, aliases, a column from three tables at once — or
when you need to see the plan you are asking for.
</Callout>

## Next

<RelatedDocs
	links={[
		{
			href: '/docs/database/transactions',
			label: 'Transactions',
			description: 'ACID guarantees, isolation levels and retries.',
		},
		{
			href: '/docs/database/optimization',
			label: 'Optimization',
			description: 'Indexes, query planning and avoiding N+1.',
		},
		{
			href: '/docs/database/migrations',
			label: 'Migrations',
			description: 'Change the schema that these queries run against.',
		},
	]}
/>