Select

Drizzle provides two query styles: the select builder for SQL-like queries, and relational queries for loading nested data.

Select Builder

import { drizzle } from '@superapp/db'
import { eq, desc } from 'drizzle-orm'
import * as schema from './generated/schema'

const db = drizzle({ connection, token, schema })

// No need to filter by user_id — the backend automatically scopes
// results to the logged-in user based on the JWT token above.
const orders = await db.select()
  .from(schema.orders)
  .where(eq(schema.orders.status, 'active'))
  .orderBy(desc(schema.orders.createdAt))
  .limit(10)
// → Returns only the current user's active orders

Select Specific Columns

// Even when selecting specific columns, the backend still scopes rows
// to the current user — and may further restrict which columns are visible
// based on the user's role and permission configuration.
const orders = await db.select({
  id: schema.orders.id,
  amount: schema.orders.amount,
  status: schema.orders.status,
}).from(schema.orders)

Filtering

import { eq, gte, and } from 'drizzle-orm'

const activeHighValue = await db.select()
  .from(schema.orders)
  .where(and(
    eq(schema.orders.status, 'active'),
    gte(schema.orders.amount, 100)
  ))

Sorting and Pagination

import { desc } from 'drizzle-orm'

const page2 = await db.select()
  .from(schema.orders)
  .orderBy(desc(schema.orders.createdAt))
  .limit(20)
  .offset(20)

Joins

const ordersWithCustomer = await db.select({
  orderId: schema.orders.id,
  amount: schema.orders.amount,
  customerName: schema.customers.name,
}).from(schema.orders)
  .leftJoin(schema.customers, eq(schema.orders.customerId, schema.customers.id))

Relational Queries

Use db.query for loading nested relations -- similar to Prisma's include.

const ordersWithCustomer = await db.query.orders.findMany({
  with: { customer: true },
  where: eq(schema.orders.status, 'active'),
  orderBy: desc(schema.orders.createdAt),
  limit: 10,
})
// { id, amount, status, customer: { id, name, email } }[]

Find First

Use findFirst() to retrieve a single record. Returns undefined if no match.

const order = await db.query.orders.findFirst({
  where: eq(schema.orders.id, 'ord_abc123'),
})
// order: { id, amount, status, ... } | undefined

With relations:

const order = await db.query.orders.findFirst({
  where: eq(schema.orders.id, 'ord_abc123'),
  with: { customer: true },
})
// order: { id, amount, status, customer: { id, name, email } } | undefined

Or use the select builder with .limit(1):

const [order] = await db.select()
  .from(schema.orders)
  .where(eq(schema.orders.id, 'ord_abc123'))
  .limit(1)

Complex Filters

import { eq, gte, lt, or, and } from 'drizzle-orm'

const filtered = await db.select()
  .from(schema.orders)
  .where(or(
    and(eq(schema.orders.status, 'active'), gte(schema.orders.amount, 500)),
    and(eq(schema.orders.status, 'pending'), lt(schema.orders.amount, 100))
  ))

On this page