Start

Agents

App store purchases

Auth

Builds

Credits

Database

Flags

Generated Assets

Hosting

Jobs and cron

Localization

Monitoring

Network

Notifications

Organizations

Queues

Realtime

Runners

Sandboxes

Storage

Webhooks

Workflows

Getting Started

Installation

Authentication

AI Services

KV Store

Overview

Webhooks

Deploy & Infrastructure

Reference

On this page

Create (insert)
Read (select)
Update
Delete
Where clauses and filters
Pagination
Order results
Select specific columns
Joins
Relations (the query API)
Next

How-to

Query guide

The queries you write most, in Drizzle, against a Sylphx database.

Checked 2026-09-28

The examples here use Drizzle over the uri from connect. Nothing about them is Sylphx-specific: a database is Postgres, and any client that speaks the wire protocol works.

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.

#Create (insert)

Insert single or multiple records:

TypeScript

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

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.

#Read (select)

Query records with type-safe selects:

TypeScript

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:

TypeScript

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

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.

#Delete

Remove records from the database:

TypeScript

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:

TypeScript

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:

TypeScript

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)

TypeScript

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 }
}

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.

#Order results

TypeScript

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

TypeScript

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

TypeScript

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:

TypeScript

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],
  }),
}))

TypeScript

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,
    },
  },
})

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.

#Next

Was this page helpful?

Tell us what is missing, wrong, or unclear — this opens a real inbox.

Send feedback

Platform links